21 Técnicas Dicas e Truques de Excel Avançado

Neste artigo você aprenderá 21 técnicas, dicas e truques de Excel Avançado no Excel.

Diferenças de Dados

Nesta dica você aprenderá como encontrar diferenças de dados no Excel.

Veja abaixo que temos duas listas, Financeiro e Contábil e queremos encontrar as diferenças entre os valores.

excel avançado 1

Uma forma de fazer isso é usando a formatação condicional

Clique em Página Inicial->Formatação Condicional->Regra de Realce das Células->Valores Duplicados

excel avançado 2

Em valores Duplicados altere para Exclusivos.

excel avançado 3

O resultado será em então uma lista com as diferenças destacadas entre eles, pois ele exibirá apenas os valores exclusivos.

excel avançado 4

Juntar Arquivos no Excel

Nesta dica você pode juntar arquivos no Excel à partir de pastas de trabalho e planilhas.

excel avançado 5

No nosso exemplo temos uma lista de arquivos em uma pasta com várias pastas de trabalho que tem várias planilhas com os mesmos cabeçalhos.

excel avançado 6

Esta é a estrutura das planilhas.

excel avançado 7

Clique em Dados->De Arquivo->Da Pasta e selecione a pasta com os arquivos.

excel avançado 8

Na tela em seguida clique em Transformar Dados.

excel avançado 9

Clique em Content e selecione no botão com as setas.

excel avançado 10

Em Combinar os arquivos selecione a pasta principal para trazer todos os dados das planilhas.

excel avançado 11

Remova as outras colunas e clique nas setinhas na coluna para extrair os dados.

excel avançado 13

A tela seguinte ficará desta forma e clique nas setas para extrair os dados.

excel avançado 14

Na tela seguinte você tem os dados desta forma, já extraídos

excel avançado 15

Clique em primeira linha como cabeçalho.

excel avançado 16

Clique na coluna e em excluir erros, pois temos linhas com valores incorretos na coluna por conta da conversão de texto como valor por conta dos cabeçalhos.

excel avançado 17

Clique em Fechar e Carregar e selecione Apenas Criar Conexão.

excel avançado 18

Clique em Tabela e clique em OK para extrair os dados da planilha.

excel avançado 19

O resultado que temos é a exportação dos dados das planilha combinadas conforme temos abaixo.

excel avançado 20

Preenchimento Relâmpago

O preenchimento relâmpago permite que você realize extração de dados automaticamente à partir de exemplos.

excel avançado 21

Na lista acima temos dados na qual gostaríamos de extrair o CPF, DATA E NOTA.

Para isso digite um exemplo dos dados e pressione CTRL+E.

excel avançado 22

Por este atalho o Excel irá usar como exemplo este campo e irá preencher o campo de CPF.

excel avançado 23

Faça o mesmo com a data e a nota e você terá os dados extraídos conforme abaixo.

excel avançado 24

Outro exemplo que pode ser feito é criar um texto usando dados de várias colunas, veja abaixo.

No exemplo usamos uma função para demonstrar que não é tão simples criar com fórmulas.

Mas você pode digitar o exemplo diretamente e pressionar CTRL+E que os dados serão extraídos automaticamente conforme abaixo.

Lista Personalizada no Excel

Você também pode criar listas personalizadas no Excel, como temos para os dias da semana ou meses por exemplo.

Digamos que tenha uma lista de filiais e gostaria de digitar uma e arrastar para preencher as outras.

Para isso digite em Arquivo->Opções e selecione Avançado.

Rode a barra até o final e clique em Editar Listas Personalizadas…

Pode digitar as listas sendo cada item em uma linha diferente ou importar diretamente á partir do Excel.

Feito isso cliique em Adicionar e pronto.

Digite qualquer nome da sua lista e clique no canto para arrastar.

Ao arrastar você terá sua lista preenchida automaticamente.

Usando Janelas no Excel

Algo também interessante é a utilização de janelas no Excel para poder ver os dados das planilhas e navegar entre elas simultaneamente.

Para isso clique em Exibir e Exibir Lado a Lado.

Na tela seguinte selecione a forma como deseja Organizar as janelas, se lado a lado, na horizontal, na vertical ou em cascata.

No exemplo temos então Lado a Lado.

E com isso, você pode ver os dados da mesma planilha ou de planilhas diferentes ao mesmo tempo para verificar ou comparar dados.

Segmentação de Dados em Tabelas

A segmentação de dados permite que façamos filtros automáticos e visuais de dados.

Selecione a tabela e clique em Inserir->Segmentação de dados.

Na segmentação de dados você selecione quais os itens que irá desejar como filtro na tabela.

Na tela seguinte você terá então os filtros visuais criados automaticamente e também poderá alterar configurações de cores, tamanho e bloqueio para que ela não se movimente ao alterar a coluna por exemplo.

Analisar Dados – Ideas Excel

O Ideas do Excel realiza análises rápidas de dados usando inteligência artificial.

Veja os dados abaixo das vendas. Vamos ligar o recurso do Ideas para analisar estes dados.

Clique em Arquivo e mude o idioma para inglês, pois este recurso só existe no Excel em inglês.

Em seguida clique em Ideas e veja que automaticamente irá aparecer a guia de análise de dados na direita.

Serão exibidas sugestões e você também pode escrever uma pergunta sobre os seus dados.

Para aplicar clique no botão abaixo da sugestão e ela será aplicada imediatamente em uma nova planilha.

Veja mais sobre o Ideas no vídeo abaixo:

Atalhos no Excel

Uma forma de aumentar a sua produtividade é trabalhar com atalhos no Excel. Esta simples ação é capaz de aumentar consideravelmente a velocidade com que você alimenta e produz suas planilhas.

O motivo para aumentar esta velocidade é o fato de evitar o uso do mouse, que acaba sendo um grande vilão para a velocidade.

Veja mais detalhes e um e-book com 220 atalhos no Excel: https://www.guiadoexcel.com.br/220-atalhos-do-excel-para-aumentar-a-sua-produtividade/

Matrizes Dinâmicas no Excel

As matrizes dinâmicas vieram para revolucionar o Excel, mas o que muda?

Desde a forma como se referencias as células, até o modo como resolvemos problemas complexos de matriciais podem ser muitas resolvidos agora com funções que contém um único parâmetro.

Veja no exemplo abaixo, nesta temos uma seleção de vários dados à partir de uma única célula.

Isto geraria um erro de #Valor em outras versões do Excel, mas no 365 nós temos o retorno destes dados.

Veja que temos então os dados retornados à partir de uma única célula e isso muda tudo quanto ao uso do Excel.

No exemplo abaixo selecionamos várias linhas e colunas e temos os dados retornados automaticamente.

Vieram também outras funções como a função Classificar, que permite retornar a classificação dos dados automaticamente.

No exemplo abaixo usamos a função ÚNICO aonde temos o retorno de valores únicos automaticamente.

Outra função poderosa que veio com o 365 é a função Filtro, aonde temos o retorno de dados conforme filtragem dos dados.

Veja nesta função como usar o filtro de forma avançada no Excel.

Procv com PowerQuery no Excel

O Power Query permite que você faça a diversos tratamentos de dados, inclusive a combinação de dados.

No nosso exemplo abaixo temos a Fonte e de dados e um De, Para aonde temos a chave e o destino desejado.

O objetivo é que os dados sejam substituídos da coluna Comunicação pelo De Para.

Clique então na tabela de dados e em Dados->Importar Tabela para a origem.

Faça o mesmo clicando na tabela De->Para para que elas sejam importadas para o Power Query.

Clique então em Mesclar Consultas e selecione a coluna de Comunicação e a tabela de comunicação do De Para para que ela seja ligada.

Com isso temos então uma ligação dos dados pela chave e o retorno desta coluna que pode ser selecionada como Para.

Após isso pode exportar os dados da coluna tDados clicando na consulta e selecionando a Tabela e em OK para exportar os dados na planilha.

Veja que temos então o resultado na planilha abaixo.

Minigráficos no Excel

Os minigráficos tem como finalidade permitir uma rápida análise de dados de uma linha, demonstrando visualmente aonde os valores se sobressaem de forma positiva ou negativa.

Para usar, crie uma coluna aonde irão estar os minigráficos. Clique em Inserir->minigráficos.

Selecione o intervalo de dados com a entrada de dados e o intervalo de local aonde serão criados.

Com isso temos então o mini gráfico criado conforme abaixo.

No relatório temos então a planilha pronta com os minigráficos conforme temos e também com várias configurações.

Estes minigráficos podem ser feitos também com outro formato que é o de linha.

Emojis no Excel

Também é possível que sejam inclusos emojis no Excel à partir do Windows.

Basta clicar a tecla Windows + .

Irá aparecer a lista de emojis disponíveis no Excel. Veja um exemplo.

Na tabela acima temos uma tabela com os dados dos meses. Pode aplicar o emoji na formatação da célula.

Clique na coluna em Gerenciador de Regras de Formatação Condicional.

Adicione a regra clicando em nova regra e adicione a regra que deseja, no nosso caso aplicamos uma regra aonde verificamos se o valor é menor do que a média.

Aplique então clicando em Windows + . para retornar uma lista de emojis e colocar na formatação conforme desejar.

O resultado que temos então é a aplicação de emojis conforme abaixo, lembrando que pode ser também alicado diretamente nas células.

Formatar Células Personalizado

Com a formatação de células personalizada você conseguirá realizar o conteúdo de células com textos com visuais diferentes, textos, cores, formatação diferente para números positivos, negativos, zeros e textos, pode aplicar inclusive fórmulas condicionais diretamente na formatação personalizada.

Veja mais neste link: https://www.guiadoexcel.com.br/como-usar-formatar-celulas-personalizado-no-excel/

teste

Atalhos de Formatação Personalizado

O Excel possui muitos atalhos e alguns deles são para formatação personalizada.

Isso facilita bastante as coisas, evitando que tenha que usar o mouse.

Os que principalmente recomendo são os abaixo que são os mais comuns:

  1. CTRL+~ : Número Geral
  2. CTRL+$ : Formato de Moeda
  3. CTRL+% : Percentual
  4. CTRL+^ : Número Científico
  5. CTRL+# : Formato de data
  6. CTRL+@ : Formato de hora
  7. CTRL+1 : Abre o formato personalizado de células no Excel
Formatar personalizado excel 1 - atalhos

Atingir Meta no Excel

O atingir meta é um recurso do Excel que permite que você simule dados de forma automática.

Vejamos um exemplo. Nela nós temos um cálculo de ponto de equilíbrio aonde há

Para isso clique na guia Dados e Em Atingir Meta.

Selecione a célula aonde há a fórmula, nela temos a função que irá definir a célula e em Para valor digite 0 e clique em Alternando célula e selecione a célula Qtde.

Temos abaixo então o resultado, veja que o valor da quantidade foi alterado para alcançar o valor 0 no resultado, chegando então ao ponto de equilíbrio.

No final temos então os valores calculados automaticamente, sem a necessidade de digitar manualmente até chegar no valor 0.

Download Planilha Técnicas Dicas e Truques de Excel Avançado

Clique no botão abaixo para realizar o  download da planilha de técnicas e truques de Excel Avançado com exemplo de dados:

Baixe a planilha

Curso Excel Completo - Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel