19 dicas, truques e atalhos fáceis do Excel

Às vezes, o Excel parece bom demais para ser verdade. Tudo o que preciso fazer é inserir uma fórmula, e praticamente qualquer coisa que eu precise fazer manualmente pode ser feita automaticamente.

Precisa mesclar duas planilhas com dados semelhantes? O Excel pode fazer isso.

Precisa fazer matemática simples? O Excel pode fazer isso.

Precisa combinar informações em várias células? O Excel pode fazer isso.

Neste post, apresentarei as melhores dicas, truques e atalhos que você pode usar agora para levar seu jogo do Excel para o próximo nível. Não é necessário conhecimento avançado em Excel.

O que é o Excel?

O Microsoft Excel é um poderoso software de visualização e análise de dados, que usa planilhas para armazenar, organizar e rastrear conjuntos de dados com fórmulas e funções. O Excel é usado por profissionais de marketing, contadores, analistas de dados e outros profissionais. Faz parte do conjunto de produtos Microsoft Office. As alternativas incluem o Google Sheets e o Numbers.

Encontre mais alternativas ao Excel aqui.

O Excel é usado principalmente para criar documentos financeiros por causa de seus fortes poderes computacionais. Muitas vezes, você encontrará o software em escritórios e equipes de contabilidade porque permite que os contadores vejam automaticamente somas, médias e totais. Com o Excel, eles podem facilmente entender os dados de seus negócios.

Embora o Excel seja conhecido principalmente como uma ferramenta de contabilidade, profissionais de qualquer área podem usar seus recursos e fórmulas – especialmente profissionais de marketing – porque pode ser usado para rastrear qualquer tipo de dados. Ele elimina a necessidade de gastar horas e horas contando células ou copiando e colando números de desempenho. O Excel normalmente tem um atalho ou solução rápida que acelera o processo.

Você também pode baixar modelos do Excel abaixo para todas as suas necessidades de marketing.

Depois de baixar os modelos, é hora de começar a usar o software. Vamos cobrir o básico primeiro.

Noções básicas do Excel

Se você está apenas começando com o Excel, existem alguns comandos básicos com os quais sugerimos que você se familiarize. São coisas como:

• Criando uma nova planilha do zero.
• Executar cálculos básicos como somar, subtrair, multiplicar e dividir.
• Escrever e formatar o texto e os títulos das colunas.
• Usando os recursos de preenchimento automático do Excel.
• Adicionar ou excluir colunas, linhas e planilhas individuais. (Abaixo, veremos como adicionar coisas como várias colunas e linhas.)
• Manter os títulos das colunas e linhas visíveis à medida que você passa por eles em uma planilha, para que você saiba quais dados está preenchendo à medida que avança no documento.
• Classificando seus dados em ordem alfabética.

Vamos explorar alguns deles com mais profundidade.

Por exemplo, por que o preenchimento automático é importante?

Se você tem algum conhecimento básico de Excel, provavelmente já conhece esse truque rápido. Mas para cobrir nossas bases, permita-me mostrar a você a glória do preenchimento automático. Isso permite preencher rapidamente células adjacentes com vários tipos de dados, incluindo valores, séries e fórmulas.

Existem várias maneiras de implantar esse recurso, mas a alça de preenchimento está entre as mais fáceis. Selecione as células que você deseja que sejam a origem, localize a alça de preenchimento no canto inferior direito da célula e arraste a alça de preenchimento para cobrir as células que deseja preencher ou apenas clique duas vezes:

Da mesma forma, a classificação é um recurso importante que você deseja conhecer ao organizar seus dados no Excel.

Às vezes você pode ter uma lista de dados que não tem organização alguma. Talvez você tenha exportado uma lista de seus contatos de marketing ou postagens de blog. Seja qual for o caso, Recurso de classificação do Excel irá ajudá-lo a alfabetizar qualquer lista.

Clique nos dados na coluna que você deseja classificar. Em seguida, clique na guia "Dados" na barra de ferramentas e procure a opção "Classificar" à esquerda. Se o “A” estiver em cima do “Z”, você pode clicar nesse botão uma vez. Se o “Z” estiver em cima do “A”, clique no botão duas vezes. Quando o “A” estiver no topo do “Z”, isso significa que sua lista será classificada em ordem alfabética. No entanto, quando o “Z” estiver no topo do “A”, isso significa que sua lista será classificada em ordem alfabética inversa.

Vamos explorar mais os fundamentos do Excel (junto com os recursos avançados) a seguir.

Como usar o Excel

Para usar o Excel, você só precisa inserir os dados nas linhas e colunas. E então você usará fórmulas e funções para transformar esses dados em insights.

Vamos examinar as melhores fórmulas e funções que você precisa conhecer. Mas primeiro, vamos dar uma olhada nos tipos de documentos que você pode criar usando o software. Dessa forma, você tem uma compreensão abrangente de como pode usar o Excel no seu dia-a-dia.

Documentos que você pode criar no Excel

Não tem certeza de como você pode realmente usar o Excel em sua equipe? Aqui está uma lista de documentos que você pode criar:

• Demonstrações de renda: você pode usar uma planilha do Excel para acompanhar a atividade de vendas e a saúde financeira de uma empresa.
• Balanços: Os balanços estão entre os tipos mais comuns de documentos que você pode criar com o Excel. Ele permite que você tenha uma visão holística da situação financeira de uma empresa.
• Calendário: você pode criar facilmente um calendário mensal de planilha para acompanhar eventos ou outras informações sensíveis a datas.

Aqui estão alguns documentos que você pode criar especificamente para profissionais de marketing.

Esta é apenas uma pequena amostra dos tipos de documentos comerciais e de marketing que você pode criar no Excel. Criamos uma extensa lista de modelos do Excel que você pode usar agora para marketing, faturamento, gerenciamento de projetos, orçamento e muito mais.

No espírito de trabalhar com mais eficiência e evitar o trabalho manual tedioso, aqui estão algumas fórmulas e funções do Excel que você precisa conhecer.

Fórmulas do Excel

É fácil ficar sobrecarregado com a ampla variedade de fórmulas do Excel que você pode usar para entender seus dados. Se você está apenas começando a usar o Excel, pode confiar nas fórmulas a seguir para realizar algumas funções complexas — sem aumentar a complexidade do seu caminho de aprendizado.

• Sinal de igual: Antes de criar qualquer fórmula, você precisará escrever um sinal de igual (=) na célula onde deseja que o resultado apareça.
• Adição : Para adicionar os valores de duas ou mais células, use o sinal + . Exemplo: =C5+D3 .
• Subtração : Para subtrair os valores de duas ou mais células, use o sinal – . Exemplo: =C5-D3 .
• Multiplicação : Para multiplicar os valores de duas ou mais células, use o sinal * . Exemplo: =C5*D3 .
• Divisão : Para dividir os valores de duas ou mais células, use o sinal / . Exemplo: =C5/D3 .

Juntando tudo isso, você pode criar uma fórmula que soma, subtrai, multiplica e divide tudo em uma célula. Exemplo: =(C5-D3)/((A5+B6)*3) .

Para fórmulas mais complexas, você precisará usar parênteses ao redor das expressões para evitar o uso acidental da ordem de operações PEMDAS. Tenha em mente que você pode usar números simples em suas fórmulas.

Funções do Excel

As funções do Excel automatizam algumas das tarefas que você usaria em uma fórmula típica. Por exemplo, em vez de usar o sinal + para somar um intervalo de células, você usaria a função SUM. Vejamos mais algumas funções que ajudarão a automatizar cálculos e tarefas.

• SOMA: A função SOMA adiciona automaticamente um intervalo de células ou números. Para completar uma soma, você deve inserir a célula inicial e a célula final com dois pontos entre elas. Aqui está o que parece: SUM(Cell1:Cell2) . Exemplo: =SOMA(C5:C30) .
• MÉDIA: A função MÉDIA calcula a média dos valores de um intervalo de células. A sintaxe é a mesma da função SUM: AVERAGE(Cell1:Cell2). Exemplo: =MÉDIA(C5:C30) .
• IF: A função IF permite retornar valores com base em um teste lógico. A sintaxe é a seguinte: IF(logical_test, value_if_true, [value_if_false]) . Exemplo: =IF(A2>B2,"Sobre o orçamento","OK") .
• PROCV: A função PROCV ajuda você a pesquisar qualquer coisa nas linhas da sua planilha. A sintaxe é: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)) . Exemplo: =PROCV([@Advogado],tbl_Advogados,4,FALSO).
• INDEX: A função INDEX retorna um valor dentro de um intervalo. A sintaxe é a seguinte: INDEX(array, row_num, [column_num]) .
• MATCH: A função MATCH procura um determinado item em um intervalo de células e retorna a posição desse item. Pode ser usado em conjunto com a função INDEX. A sintaxe é: MATCH(lookup_value, lookup_array, [match_type]) .
• CONT.SE: A função CONT.SE retorna o número de células que atendem a um determinado critério ou possuem um determinado valor. A sintaxe é: CONT.SE(intervalo, critérios). Exemplo: =CONT.SE(A2:A5,"Londres").

Ok, pronto para entrar no âmago da questão? Vamos lá. (E para todos os fãs de Harry Potter por aí... de nada.)

Observação : os GIFs e os visuais são de uma versão anterior do Excel. Quando aplicável, a cópia foi atualizada para fornecer instruções aos usuários das versões mais recentes e antigas do Excel.

1. Use tabelas dinâmicas para reconhecer e entender os dados.

As tabelas dinâmicas são usadas para reorganizar dados em uma planilha. Eles não alteram os dados que você tem, mas podem somar valores e comparar diferentes informações em sua planilha, dependendo do que você gostaria que eles fizessem.

Vamos dar uma olhada em um exemplo. Digamos que eu queira dar uma olhada em quantas pessoas estão em cada casa em Hogwarts. Você pode estar pensando que não tenho muitos dados, mas para conjuntos de dados mais longos, isso será útil.

Para criar a Tabela Dinâmica, vou em Dados > Tabela Dinâmica . Se você estiver usando a versão mais recente do Excel, vá para Inserir > Tabela Dinâmica . O Excel preencherá automaticamente sua Tabela Dinâmica, mas você sempre poderá alterar a ordem dos dados. Então, você tem quatro opções para escolher.

• Filtro de relatório : permite que você veja apenas determinadas linhas em seu conjunto de dados. Por exemplo, se eu quisesse criar um filtro por casa, poderia optar por incluir apenas alunos da Grifinória em vez de todos os alunos.
• Rótulos de coluna : esses seriam seus cabeçalhos no conjunto de dados.
• Rótulos de linha : podem ser suas linhas no conjunto de dados. Os rótulos de linha e coluna podem conter dados de suas colunas (por exemplo, o nome pode ser arrastado para o rótulo de linha ou coluna - depende apenas de como você deseja ver os dados).
• Valor : Esta seção permite que você veja seus dados de forma diferente. Em vez de apenas extrair qualquer valor numérico, você pode somar, contar, média, max, min, contar números ou fazer algumas outras manipulações com seus dados. Na verdade, por padrão, quando você arrasta um campo para Valor, ele sempre faz uma contagem.

Como quero contar o número de alunos em cada casa, vou para o construtor de tabela dinâmica e arrasto a coluna Casa para os Rótulos de Linha e os Valores. Isto irá resumir o número de alunos associados a cada casa.

2. Adicione mais de uma linha ou coluna.

Conforme você brinca com seus dados, pode descobrir que está constantemente precisando adicionar mais linhas e colunas. Às vezes, você pode até precisar adicionar centenas de linhas. Fazer isso um por um seria super tedioso. Felizmente, há sempre uma maneira mais fácil.

Para adicionar várias linhas ou colunas em uma planilha, realce o mesmo número de linhas ou colunas preexistentes que você deseja adicionar. Em seguida, clique com o botão direito do mouse e selecione “Inserir”.

No exemplo abaixo, quero adicionar mais três linhas. Ao destacar três linhas e clicar em inserir, posso adicionar mais três linhas em branco à minha planilha de maneira rápida e fácil.

3. Use filtros para simplificar seus dados.

Quando você está analisando conjuntos de dados muito grandes, geralmente não precisa examinar todas as linhas ao mesmo tempo. Às vezes, você só quer ver os dados que se encaixam em determinados critérios.

É aí que entram os filtros.

Os filtros permitem que você reduza seus dados para ver apenas determinadas linhas de uma vez. No Excel, um filtro pode ser adicionado a cada coluna em seus dados e, a partir daí, você pode escolher quais células deseja visualizar de uma só vez.

Vamos dar uma olhada no exemplo abaixo. Adicione um filtro clicando na guia Dados e selecionando "Filtro". Clicando na seta ao lado dos cabeçalhos das colunas, você poderá escolher se deseja que seus dados sejam organizados em ordem crescente ou decrescente, bem como quais linhas específicas deseja mostrar.

No meu exemplo de Harry Potter, digamos que eu só queira ver os alunos da Grifinória. Ao selecionar o filtro Grifinória, as outras linhas desaparecem.

Dica profissional : Copie e cole os valores na planilha quando um Filtro estiver ativado para fazer análises adicionais em outra planilha.

4. Remova os pontos ou conjuntos de dados duplicados.

Conjuntos de dados maiores tendem a ter conteúdo duplicado. Você pode ter uma lista de vários contatos em uma empresa e deseja apenas ver o número de empresas que possui. Em situações como essa, remover as duplicatas é bastante útil.

Para remover suas duplicatas, realce a linha ou coluna da qual você deseja remover duplicatas. Em seguida, vá para a guia Dados e selecione “Remover duplicatas” (que está sob o subtítulo Ferramentas na versão mais antiga do Excel). Um pop-up aparecerá para confirmar com quais dados você deseja trabalhar. Selecione “Remover Duplicatas” e pronto.

Você também pode usar esse recurso para remover uma linha inteira com base em um valor de coluna duplicado. Portanto, se você tiver três linhas com as informações de Harry Potter e precisar ver apenas uma, poderá selecionar todo o conjunto de dados e remover duplicatas com base no email. Sua lista resultante terá apenas nomes exclusivos sem duplicatas.

5. Transponha linhas em colunas.

Quando você tem linhas de dados em sua planilha, pode decidir que realmente deseja transformar os itens em uma dessas linhas em colunas (ou vice-versa). Levaria muito tempo para copiar e colar cada cabeçalho individual - mas o que o recurso de transposição permite que você faça é simplesmente mover seus dados de linha para colunas ou vice-versa.

Comece destacando a coluna que você deseja transpor em linhas. Clique com o botão direito do mouse e selecione “Copiar”. Em seguida, selecione as células em sua planilha onde deseja que sua primeira linha ou coluna comece. Clique com o botão direito do mouse na célula e selecione “Colar especial”. Um módulo aparecerá - na parte inferior, você verá uma opção para transpor. Marque essa caixa e selecione OK. Sua coluna agora será transferida para uma linha ou vice-versa.

Nas versões mais recentes do Excel, um menu suspenso aparecerá em vez de um pop-up.

6. Divida as informações de texto entre as colunas.

E se você quiser dividir as informações que estão em uma célula em duas células diferentes? Por exemplo, talvez você queira extrair o nome da empresa de alguém pelo endereço de e-mail. Ou talvez você queira separar o nome completo de alguém em nome e sobrenome para seus modelos de marketing por e-mail.

Graças ao Excel, ambos são possíveis. Primeiro, destaque a coluna que você deseja dividir. Em seguida, vá para a guia Dados e selecione "Texto para colunas". Um módulo aparecerá com informações adicionais.

Primeiro, você precisa selecionar “Delimitado” ou “Largura Fixa”.

• “Delimitado” significa que você deseja dividir a coluna com base em caracteres como vírgulas, espaços ou tabulações.
• “Largura Fixa” significa que você deseja selecionar o local exato em todas as colunas em que deseja que a divisão ocorra.

No caso de exemplo abaixo, vamos selecionar “Delimitado” para que possamos separar o nome completo em nome e sobrenome.

Então, é hora de escolher os Delimitadores. Pode ser uma tabulação, ponto e vírgula, vírgula, espaço ou qualquer outra coisa. (“Outra coisa” pode ser o sinal “@” usado em um endereço de e-mail, por exemplo.) Em nosso exemplo, vamos escolher o espaço. O Excel mostrará uma visualização de como serão suas novas colunas.

Quando estiver satisfeito com a visualização, pressione "Avançar". Esta página permitirá que você selecione Formatos avançados, se desejar. Quando terminar, clique em “Concluir”.

7. Use fórmulas para cálculos simples.

Além de fazer cálculos bastante complexos, o Excel pode ajudá-lo a fazer aritmética simples, como adicionar, subtrair, multiplicar ou dividir qualquer um dos seus dados.

• Para adicionar, use o sinal +.
• Para subtrair, use o sinal –.
• Para multiplicar, use o sinal *.
• Para dividir, use o sinal /.

Você também pode usar parênteses para garantir que determinados cálculos sejam feitos primeiro. No exemplo abaixo (10+10*10), o segundo e o terceiro 10 foram multiplicados antes de somar os 10 adicionais. No entanto, se fizermos (10+10)*10, o primeiro e o segundo 10 serão somados primeiro .

8. Obtenha a média dos números em suas células.

Se você deseja a média de um conjunto de números, pode usar a fórmula =AVERAGE(Cell1:Cell2) . Se você deseja resumir uma coluna de números, pode usar a fórmula =SUM(Cell1:Cell2) .

9. Use a formatação condicional para fazer com que as células mudem de cor automaticamente com base nos dados.

A formatação condicional permite alterar a cor de uma célula com base nas informações contidas na célula. Por exemplo, se você deseja sinalizar determinados números que estão acima da média ou entre os 10% principais dos dados em sua planilha, você pode fazer isso. Se você deseja colorir as semelhanças entre diferentes linhas no Excel, você pode fazer isso. Isso ajudará você a ver rapidamente as informações que são importantes para você.

Para começar, destaque o grupo de células no qual você deseja usar a formatação condicional. Em seguida, escolha “Formatação Condicional” no menu inicial e selecione sua lógica no menu suspenso. (Você também pode criar sua própria regra se quiser algo diferente.) Uma janela aparecerá solicitando que você forneça mais informações sobre sua regra de formatação. Selecione "OK" quando terminar e você verá seus resultados aparecerem automaticamente.

10. Use a fórmula IF Excel para automatizar certas funções do Excel.

Às vezes, não queremos contar o número de vezes que um valor aparece. Em vez disso, queremos inserir informações diferentes em uma célula se houver uma célula correspondente com essa informação.

Por exemplo, na situação abaixo, quero dar dez pontos a todos que pertencem à casa da Grifinória. Em vez de digitar manualmente 10's ao lado do nome de cada aluno da Grifinória, posso usar a fórmula IF Excel para dizer que, se o aluno estiver na Grifinória, ele deverá obter dez pontos.

A fórmula é: IF(logical_test, value_if_true, [value_if_false])

Exemplo mostrado abaixo: =SE(D2=”Grifinória”,”10″,”0″)

Em termos gerais, a fórmula seria SE(Teste Lógico, valor de verdadeiro, valor de falso). Vamos nos aprofundar em cada uma dessas variáveis.

• Logical_Test : O teste lógico é a parte “IF” da instrução. Neste caso, a lógica é D2=”Gryffindor” porque queremos ter certeza de que a célula correspondente ao aluno diz “Gryffindor”. Certifique-se de colocar Grifinória entre aspas aqui.
• Value_if_True : Isto é o que queremos que a célula mostre se o valor for verdadeiro. Nesse caso, queremos que a célula mostre “10” para indicar que o aluno recebeu os 10 pontos. Use aspas apenas se desejar que o resultado seja um texto em vez de um número.
• Value_if_False : Isto é o que queremos que a célula mostre se o valor for falso. Neste caso, para qualquer aluno que não seja da Grifinória, queremos que a célula mostre “0”. Use aspas apenas se desejar que o resultado seja um texto em vez de um número.

Nota : No exemplo acima, dei 10 pontos a todos na Grifinória. Se mais tarde eu quisesse somar o número total de pontos, não conseguiria porque os 10 estão entre aspas, tornando-os texto e não um número que o Excel pode somar.

O verdadeiro poder da função IF vem quando você encadeia várias instruções IF juntos, ou aninhá-los. Isso permite que você defina várias condições, obtenha resultados mais específicos e, por fim, organize seus dados em partes mais gerenciáveis.

Os intervalos são uma maneira de segmentar seus dados para uma melhor análise. Por exemplo, você pode categorizar dados em valores menores que 10, 11 a 50 ou 51 a 100. Veja como isso ocorre na prática:

=SE(B3<11,"10 ou menos",SE(B3<51,"11 a 50",SE(B3<100,"51 a 100"))))

Pode levar algumas tentativas e erros, mas quando você pegar o jeito, as fórmulas SE se tornarão seu novo melhor amigo do Excel.

11. Use cifrões para manter a fórmula de uma célula independentemente de onde ela se mova.

Você já viu um cifrão em uma fórmula do Excel? Quando usado em uma fórmula, não representa um dólar americano; em vez disso, garante que a coluna e a linha exatas sejam mantidas iguais, mesmo que você copie a mesma fórmula em linhas adjacentes.

Veja, uma referência de célula — quando você se refere à célula A5 da célula C5, por exemplo — é relativa por padrão. Nesse caso, você está se referindo a uma célula que está cinco colunas à esquerda (C menos A) e na mesma linha (5). Isso é chamado de fórmula relativa. Quando você copia uma fórmula relativa de uma célula para outra, ela ajusta os valores na fórmula com base no local para onde foi movida. Mas, às vezes, queremos que esses valores permaneçam os mesmos, independentemente de serem movidos ou não - e podemos fazer isso transformando a fórmula em uma fórmula absoluta.

Para alterar a fórmula relativa (=A5+C5) para uma fórmula absoluta, precedemos os valores da linha e da coluna por cifrões, assim: (=$A$5+$C$5) . (Saiba mais na página de suporte do Microsoft Office aqui.)

12. Use a função PROCV para puxar dados de uma área de uma planilha para outra.

Você já teve dois conjuntos de dados em duas planilhas diferentes que deseja combinar em uma única planilha?

Por exemplo, você pode ter uma lista de nomes de pessoas ao lado de seus endereços de e-mail em uma planilha e uma lista dos endereços de e-mail dessas mesmas pessoas ao lado de seus nomes de empresas na outra, mas você quer que os nomes, endereços de e-mail e nomes de empresas dessas pessoas para aparecer em um só lugar.

Eu tenho que combinar muito conjuntos de dados como este - e quando o faço, o VLOOKUP é minha fórmula preferida.

Antes de usar a fórmula, porém, tenha certeza absoluta de que você tem pelo menos uma coluna que aparece de forma idêntica em ambos os lugares. Vasculhe seus conjuntos de dados para garantir que a coluna de dados que você está usando para combinar suas informações seja exatamente a mesma, incluindo sem espaços extras.

A fórmula: =VLOOKUP(valor de pesquisa, matriz da tabela, número da coluna, correspondência aproximada (VERDADEIRO) ou correspondência exata (FALSO))

A fórmula com as variáveis ​​do nosso exemplo abaixo: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

Nesta fórmula, existem várias variáveis. O seguinte é verdadeiro quando você deseja combinar informações na Planilha 1 e na Planilha 2 na Planilha 1.

• Valor de pesquisa : Este é o valor idêntico que você tem em ambas as planilhas. Escolha o primeiro valor em sua primeira planilha. No exemplo a seguir, isso significa o primeiro endereço de e-mail da lista ou célula 2 (C2).
• Matriz de tabela : a matriz de tabela é o intervalo de colunas na planilha 2 de onde você extrairá seus dados, incluindo a coluna de dados idêntica ao seu valor de pesquisa (no nosso exemplo, endereços de e-mail) na planilha 1, bem como a coluna de dados que você está tentando copiar para a Planilha 1. Em nosso exemplo, isso é "Planilha2!A:B". “A” significa Coluna A na Planilha 2, que é a coluna na Planilha 2 onde estão listados os dados idênticos ao nosso valor de pesquisa (e-mail) na Planilha 1. O “B” significa Coluna B, que contém as informações disponíveis apenas na Planilha 2 que você deseja traduzir para a Planilha 1.
• Número da coluna : informa ao Excel em qual coluna os novos dados que você deseja copiar para a Planilha 1 estão localizados. Em nosso exemplo, esta seria a coluna em que "Casa" está localizada. "Casa" é a segunda coluna em nosso intervalo de colunas (array de tabela), então nosso número de coluna é 2. [ Nota : Seu intervalo pode ter mais de duas colunas. Por exemplo, se houver três colunas na Planilha 2 — E-mail, Idade e Casa — e você ainda quiser trazer a Casa para a Planilha 1, ainda poderá usar um PROCV. Você só precisa alterar o “2” para um “3” para que ele recupere o valor na terceira coluna: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Correspondência Aproximada (TRUE) ou Correspondência Exata (FALSE) : Use FALSE para garantir que você obtenha apenas correspondências de valor exato. Se você usar TRUE, a função obterá correspondências aproximadas.

No exemplo abaixo, a Planilha 1 e a Planilha 2 contêm listas que descrevem informações diferentes sobre as mesmas pessoas, e o fio condutor entre as duas são seus endereços de e-mail. Digamos que queremos combinar os dois conjuntos de dados para que todas as informações da casa da Planilha 2 sejam traduzidas para a Planilha 1.

Então, quando digitamos a fórmula =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , trazemos todos os dados da casa para a Planilha 1.

Lembre-se de que o VLOOKUP apenas recuperará os valores da segunda planilha à direita da coluna que contém seus dados idênticos. Isso pode levar a algumas limitações, e é por isso que algumas pessoas preferem usar as funções INDEX e MATCH.

13. Use as fórmulas INDEX e MATCH para extrair dados das colunas horizontais.

Como VLOOKUP, as funções INDEX e MATCH extraem dados de outro conjunto de dados em um local central. Aqui estão as principais diferenças:

• PROCV é uma fórmula muito mais simples. Se você estiver trabalhando com grandes conjuntos de dados que exigiriam milhares de pesquisas, usar a função INDEX e MATCH diminuirá significativamente o tempo de carregamento no Excel.
• As fórmulas INDEX e MATCH funcionam da direita para a esquerda, enquanto as fórmulas VLOOKUP funcionam apenas como uma pesquisa da esquerda para a direita. Em outras palavras, se você precisar fazer uma pesquisa que tenha uma coluna de pesquisa à direita da coluna de resultados, precisará reorganizar essas colunas para fazer um PROCV. Isso pode ser tedioso com grandes conjuntos de dados e/ou levar a erros.

Portanto, se eu quiser combinar as informações da Planilha 1 e da Planilha 2 na Planilha 1, mas os valores das colunas nas Planilhas 1 e 2 não forem os mesmos, para fazer um PROCV, precisarei alternar entre minhas colunas. Nesse caso, eu optaria por fazer um INDEX e MATCH.

Vejamos um exemplo. Digamos que a Folha 1 contém uma lista de nomes de pessoas e seus endereços de e-mail de Hogwarts, e a Folha 2 contém uma lista de endereços de e-mail das pessoas e o Patrono que cada aluno tem. (Para os fãs que não são de Harry Potter, toda bruxa ou bruxo tem um guardião animal chamado “Patronus” associado a ele.) A informação que fica em ambas as folhas é a coluna contendo endereços de e-mail, mas esta coluna está em números de coluna diferentes em cada folha. Eu usaria as fórmulas INDEX e MATCH em vez de VLOOKUP para não precisar alternar nenhuma coluna.

Então, qual é a fórmula? A fórmula é, na verdade, a fórmula MATCH aninhada dentro da fórmula INDEX. Você verá que diferenciei a fórmula MATCH usando uma cor diferente aqui.

A fórmula: =ÍNDICE(matriz de tabela, fórmula CORRESP)

Isso se torna: =INDEX(table array, MATCH ( lookup_value, lookup_array))

A fórmula com as variáveis ​​do nosso exemplo abaixo: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0))))

Aqui estão as variáveis:

• Tabela Array : O intervalo de colunas na Planilha 2 contendo os novos dados que você deseja trazer para a Planilha 1. Em nosso exemplo, “A” significa Coluna A, que contém as informações de “Patrono” para cada pessoa.
• Valor de pesquisa : esta é a coluna na planilha 1 que contém valores idênticos em ambas as planilhas. No exemplo a seguir, isso significa a coluna “email” na Planilha 1, que é a Coluna C. Portanto: Planilha1!C:C.
• Matriz de Pesquisa : Esta é a coluna na Planilha 2 que contém valores idênticos em ambas as planilhas. No exemplo a seguir, isso se refere à coluna “email” na Planilha 2, que também é a Coluna C. Então: Planilha2!C:C.

Uma vez que você tenha suas variáveis ​​corretas, digite as fórmulas INDEX e MATCH na célula superior da coluna Patronus em branco na Planilha 1, onde você deseja que as informações combinadas fiquem.

14. Use a função CONT.SE para fazer o Excel contar palavras ou números em qualquer intervalo de células.

Em vez de contar manualmente com que frequência um determinado valor ou número aparece, deixe o Excel fazer o trabalho para você. Com a função CONT.SE, o Excel pode contar o número de vezes que uma palavra ou número aparece em qualquer intervalo de células.

Por exemplo, digamos que eu queira contar o número de vezes que a palavra “Gryffindor” aparece no meu conjunto de dados.

A fórmula: =CONT.SE(intervalo, critérios)

A fórmula com as variáveis ​​do nosso exemplo abaixo: =COUNTIF(D:D,"Gryffindor")

Nesta fórmula, existem várias variáveis:

• Intervalo : O intervalo que queremos que a fórmula cubra. Nesse caso, como estamos focando apenas em uma coluna, usamos “D:D” para indicar que a primeira e a última coluna são ambas D. Se eu estivesse olhando as colunas C e D, usaria “C:D .”
• Critérios : Qualquer número ou pedaço de texto que você deseja que o Excel conte. Use aspas apenas se desejar que o resultado seja um texto em vez de um número. Em nosso exemplo, o critério é “Gryffindor”.

Basta digitar a fórmula CONT.SE em qualquer célula e pressionar “Enter” para me mostrar quantas vezes a palavra “Gryffindor” aparece no conjunto de dados.

15. Combine as células usando &.

Os bancos de dados tendem a dividir os dados para torná-los o mais exatos possível. Por exemplo, em vez de ter uma coluna que mostra o nome completo de uma pessoa, um banco de dados pode ter os dados como nome e sobrenome em colunas separadas. Ou pode ter a localização de uma pessoa separada por cidade, estado e CEP. No Excel, você pode combinar células com dados diferentes em uma célula usando o sinal “&” em sua função.

A fórmula com as variáveis ​​do nosso exemplo abaixo: =A2&” “&B2

Vamos percorrer a fórmula juntos usando um exemplo. Imagine que queremos combinar nomes e sobrenomes em nomes completos em uma única coluna. Para fazer isso, primeiro colocamos nosso cursor na célula em branco onde queremos que o nome completo apareça. Em seguida, destacaríamos uma célula que contém um primeiro nome, digitaríamos um sinal de “&” e, em seguida, destacaríamos uma célula com o sobrenome correspondente.

Mas você não terminou - se tudo que você digitar for =A2&B2, não haverá um espaço entre o nome e o sobrenome da pessoa. Para adicionar esse espaço necessário, use a função =A2&” “&B2 . As aspas ao redor do espaço informam ao Excel para colocar um espaço entre o nome e o sobrenome.

Para tornar isso verdadeiro para várias linhas, basta arrastar o canto dessa primeira célula para baixo, conforme mostrado no exemplo.

16. Adicione caixas de seleção.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Aqui está como fazê-lo.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.