Como criar uma tabela dinâmica no Excel
Atualizado em
Uma tabela dinâmica resume uma lista em poucos cliques, sem escrever fórmulas. Clique nos seus dados, vá em Inserir > Tabela Dinâmica, confirme o intervalo e o local, e arraste campos no painel Campos da Tabela Dinâmica: Região em Linhas e Vendas em Valores dá a Soma de Vendas de cada região, com um Total Geral embaixo. A tabela dinâmica não se atualiza sozinha: pressione Alt+F5 quando os dados mudarem.
Onde encontrar no Excel
Inserir > Tabela Dinâmica > Campos da Tabela Dinâmica (Filtros / Colunas / Linhas / Valores)
- Tabela/Intervalo
- Os dados de origem, com cabeçalhos, como
A1:F21. Toda coluna precisa de um cabeçalho: ele vira o nome do campo. Transforme a lista em tabela antes (Ctrl+T) e as novas linhas entram na próxima atualização. - Linhas
- O campo cujos itens viram os rótulos de linha, por exemplo cada região. Eles ficam em ordem de A a Z por padrão, sob o cabeçalho Rótulos de Linha.
- Colunas
- Um segundo campo opcional, distribuído no topo, como o trimestre. Linhas e Colunas juntas formam uma tabela cruzada, com um Total Geral para cada linha e cada coluna.
- Valores + Resumir campo de valor por
- O campo a calcular. O Excel escolhe Soma para uma coluna de números e Contagem assim que ela tem uma célula vazia ou com texto. Mude em Configurações do Campo de Valor: Soma, Contagem, Média, Máx, Mín.
- Filtros
- Um campo mostrado acima da tabela dinâmica com uma lista suspensa, por exemplo Canal. Escolha um item como Online e a tabela inteira passa a contar só essas linhas.
- Atualizar
- Análise de Tabela Dinâmica > Atualizar (Alt+F5) recalcula a tabela a partir da origem; Atualizar Tudo (Ctrl+Alt+F5) atualiza todas as tabelas dinâmicas da pasta de trabalho. Até lá, as mudanças nos dados não aparecem.
Pratique tabelas dinâmicas aqui
Selecione os dados com os cabeçalhos e clique em Tabela Dinâmica… acima da barra de fórmulas, como na guia Inserir do Excel, e escolha os campos. Ao contrário do Excel, a grade recalcula a tabela dinâmica assim que os dados mudam.
Total de vendas por região
Crie uma Tabela Dinâmica a partir de A1:C13 que mostre o total de vendas de cada região. Selecione a tabela com os cabeçalhos, clique em Tabela Dinâmica, mantenha o local E1 e coloque Região em Linhas e Vendas (R$) em Valores.
| Região | Produto | Vendas (R$) |
| Leste | Notebook | 1.200,00 |
| Oeste | Monitor | 450,00 |
| Norte | Notebook | 1.350,00 |
| Leste | Teclado | 80,00 |
| Sul | Monitor | 520,00 |
| Oeste | Notebook | 1.180,00 |
| Norte | Teclado | 95,00 |
| Leste | Monitor | 610,00 |
| Sul | Notebook | 1.250,00 |
| Oeste | Teclado | 110,00 |
| Norte | Monitor | 480,00 |
| Leste | Notebook | 1.300,00 |
Como criar uma tabela dinâmica, passo a passo
- Clique em qualquer célula dos dados, ou selecione-os com a linha de cabeçalhos, por exemplo
A1:F21. - Na guia Inserir, clique em Tabela Dinâmica. Na caixa de diálogo, confira Tabela/Intervalo e escolha Nova Planilha, ou Planilha Existente com um Local como
H1. - No painel Campos da Tabela Dinâmica, arraste um campo para Linhas (ou marque-o: texto vai para Linhas, números para Valores) e depois o campo a calcular para Valores.
- Se o cabeçalho disser Contagem de em vez de Soma de, abra Configurações do Campo de Valor e escolha o cálculo certo. Adicione um campo em Colunas ou Filtros para ir além.
Exemplos de tabela dinâmica
Vendas totais por região
Linhas: Região · Valores: Soma de Vendas
Uma linha por região, em ordem de A a Z, e um Total Geral embaixo. Clique com o botão direito num total e escolha Classificar > Classificar do Maior para o Menor para ordenar as regiões.
Número de pedidos por produto
Linhas: Produto · Valores: Contagem de Pedido
Os números de pedido são números, então o Excel propõe Soma de Pedido, que os soma e não significa nada. Troque para Contagem nas configurações do campo de valor.
Região por trimestre
Linhas: Região · Colunas: Trimestre · Valores: Soma de Vendas
Uma tabela cruzada: cada célula é o total de uma região em um trimestre, com totais para cada linha e coluna.
Participação no total
=I3/I7
O total de uma região dividido pelo Total Geral, formatado como porcentagem. Para a coluna inteira de uma vez, use Mostrar Valores como > % do Total Geral.
Uma fórmula que lê a tabela dinâmica
=INFODADOSTABELADINÂMICA("Vendas";$H$1;"Região";"Norte")
É o que o Excel escreve quando você clica numa célula da tabela dinâmica enquanto digita uma fórmula. Ela continua achando o total do Norte mesmo se a tabela mudar de lugar ou de ordem, mas não se desloca ao ser copiada.
Problemas comuns e como resolver
| Problema | Causa | Solução |
|---|---|---|
| Contagem de Vendas em vez de Soma de Vendas | A coluna Vendas tem uma célula vazia ou um número armazenado como texto, então o Excel usa Contagem. | Preencha ou corrija as células e defina Configurações do Campo de Valor > Soma. |
| Um total enorme e sem sentido dos números de pedido | A coluna de números de pedido é numérica, então o Excel a soma. | Resuma por Contagem para obter o número de pedidos. |
| A tabela dinâmica mostra números antigos | A tabela dinâmica não recalcula quando a origem muda, e linhas novas abaixo do intervalo ficam de fora. | Pressione Alt+F5. Para linhas novas, use Alterar Fonte de Dados, ou crie a tabela dinâmica a partir de uma tabela (Ctrl+T). |
| Aparece uma linha “(vazio)” | O intervalo de origem inclui linhas ou células vazias, por exemplo colunas inteiras A:F. | Selecione só os dados, use uma tabela, ou desmarque (vazio) no filtro dos rótulos de linha. |
| “O nome do campo da Tabela Dinâmica não é válido.” | Uma coluna da origem não tem cabeçalho, ou a seleção é uma célula fora dos dados. | Dê um cabeçalho a cada coluna e selecione os dados com a linha de cabeçalhos. |
| A fórmula ao lado da tabela não copia para baixo | Clicar numa célula da tabela dinâmica escreveu INFODADOSTABELADINÂMICA com itens fixos em vez de uma referência como I3. | Digite a referência à mão, ou desmarque Gerar GetPivotData em Análise de Tabela Dinâmica > Opções. |
Tabela dinâmica ou SOMASES?
Uma tabela dinâmica monta um resumo completo em segundos, e você o reorganiza arrastando campos, mas ela só muda quando é atualizada. Fórmulas com SOMASES, CONT.SES e MÉDIASES se atualizam na hora e colocam cada resultado exatamente onde você quer, ao custo de uma fórmula por célula. Use a tabela dinâmica para explorar e apresentar, e fórmulas para um layout fixo que precisa ficar sempre atualizado.
Perguntas frequentes
A tabela dinâmica se atualiza sozinha?
Não. Depois de mudar os dados, clique na tabela dinâmica e pressione Alt+F5, ou use Atualizar Tudo (Ctrl+Alt+F5). Você também pode marcar Atualizar dados ao abrir o arquivo nas opções da tabela dinâmica.
Como mostro porcentagens em vez de valores?
Clique com o botão direito num valor e escolha Mostrar Valores como > % do Total Geral. % do Total da Coluna e % do Total da Linha funcionam do mesmo jeito em tabelas cruzadas.
Como agrupo datas por mês ou trimestre?
Coloque um campo de data em Linhas: as versões recentes o agrupam sozinhas em anos, trimestres e meses. Se não, clique com o botão direito numa data, escolha Agrupar e marque Meses, Trimestres ou Anos.
Posso colocar o mesmo campo duas vezes em Valores?
Sim. Arraste Vendas duas vezes para Valores e defina um como Soma e o outro como Média ou Contagem para ver os dois lado a lado.
Como excluo uma tabela dinâmica?
Clique nela, vá em Análise de Tabela Dinâmica > Selecionar > Tabela Dinâmica Inteira e pressione Delete. Os dados de origem não mudam.
Continue praticando
Os 10 exercícios de tabela dinâmica, com explicações e acompanhamento do progresso.
Abrir os exercícios de tabela dinâmica