Nessa publicação vou te mostrar 10 dicas absurdas sobre a ferramenta de tabela dinâmica no Excel! Tenho certeza que você vai aprender muita coisa maneira!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
O que é a Tabela Dinâmica no Excel?
Tabela dinâmica como o próprio nome sugere é uma tabela em que podemos fazer algumas alterações de forma dinâmica, ou seja, podemos alterá-la de forma fácil e rápida para analisar somente os dados que queremos naquele determinado momento. É importante ressaltar que para criar uma tabela dinâmica será necessária uma tabela base, pois todas as informações que poderemos modificar serão baseados nessa tabela base.
Quando utilizar a Tabela Dinâmica no Excel?
A tabela dinâmica sempre será utilizada quando o usuário precisa de uma análise mais detalhada e mais focado em certos dados, pois o usuário tem a possibilidade de alterar de forma fácil os dados que serão mostrados para se adequar a análise desejada no momento. Essa tabela possui diversas ferramentas que nem todos que tem conhecimento de Excel sabem e nessa aula vamos aprender 10 dicas sobre a tabela dinâmica.
Quais são as 10 dicas sobre essa ferramenta?
Nesta aula vamos ver algumas dicas que poucas pessoas sabem em relação a tabela dinâmica, lembrando que essas dicas são para as pessoas que já tem conhecimento em tabela dinâmica.
No arquivo que foi baixado temos 3 tabelas dinâmicas que serão utilizadas para mostrar as essas dicas, como já sabemos que cada tabela dinâmica precisa de uma tabela base para ser criada, elas também se encontram dentro do arquivo.
Dica 1 – Subtotais
É possível verificar que na primeira tabela dinâmica temos os dados de uma empresa e logo ao lado de cada um dos setores temos o total das vendas de todos os 3 anos analisados.

Tabela Dinâmica no excel 1 – Tabela inicial
O que pouca gente sabe é que temos a possibilidade de alterar como esse subtotal aparece dentro da tabela dinâmica. Para fazer essa alteração vamos clicar em qualquer célula dentro da tabela dinâmica para habilitar duas novas guias, vamos acessar a guia Design e em seguida a opção Subtotais.

Opções para alterar os Subtotais
Aqui temos 3 opções, uma para não mostrar esses subtotais e as outras duas para alterar entre o subtotal no início ou no final do agrupamento. O padrão do Excel como já vimos é mostrando esse subtotal no início do grupo.
Dica 2 – Total Geral
Da mesma forma que podemos fazer alterações com os subtotais, podemos fazer também com os totais gerais, pois como vimos na imagem da tabela temos um total geral para os valores em colunas e um total geral para os valores nas linhas. Então na coluna F temos o total por ano e por setor enquanto na linha 23 temos o total por trimestre (os números na coluna 2 indicam os 4 trimestres do ano).

Tabela Dinâmica no excel 1 – Tabela inicial
Então para fazer as alterações nesses totais gerais vamos até a guia Design e acessar a opção Totais Gerais, que fica logo ao lado da opção Subtotais.

Opções para alterar os Totais Gerais
Aqui temos 4 opções, uma é para desabilitar tudo, uma para habilitar somente para colunas, uma para habilitar somente para linhas e uma que habilita para linhas e colunas que é o padrão do Excel. Essas modificações são úteis para que o usuário deixe apenas a visualização que seja mais importante para diminuir a quantidade de dados e facilitar a análise deles.
Dica 3 – Campo Calculado
Vamos utilizar agora a Tabela 2 para as próximas dicas. Note que nesta tabela temos as datas, o faturamento e o custo de uma determinada empresa, no entanto está faltando uma informação importante que é o lucro da empresa.

Tabela 2 – Segunda tabela dinâmica no Excel
Como sabemos para inserir uma coluna a mais dentro da tabela dinâmica temos que ter essa coluna dentro da nossa tabela base, ou seja, teríamos que ir até a tabela base, fazer os cálculos e atualizar a tabela dinâmica, pois essa coluna não existe na tabela.

Tabela base para a segunda tabela dinâmica no excel
No entanto podemos fazer esse procedimento sem que tenhamos que mexer na nossa base de dados, e faremos isso utilizando a ferramenta de Campo Calculado. Essa ferramenta se encontra na guia Analisar dentro da opção Campos, Itens e Conjuntos.

Acessando a opção de Campo Calculado
Ao selecionar essa opção teremos uma nova janela dentro do ambiente Excel.

Janela do campo calculado já com os dados
Dentro dessa janela podemos altera o nome do nosso campo, quer será Lucro e logo abaixo podemos colocar a fórmula, lembrando que só poderemos utilizar as operações básicas com essa ferramenta. Para a fórmula podemos utilizar a caixa de Campos logo abaixo e inseri-lo para compor a fórmula, neste caso será o Faturamento menos o Custo.
Ao pressionar OK o Excel irá criar a coluna Soma de Lucro, com o somatório do lucro para todas as datas que temos na planilha.

Resultado do campo calculado na tabela dinâmica no excel
Dica 4 – Agrupamento
Utilizando ainda a mesma tabela caso o usuário queira agrupar as datas, ou separá-las com o objetivo de ter uma melhor visualização. Isso será feito porque normalmente não temos uma análise de dados diária, a melhor forma de visualizar pode ser em meses, trimestres e até anos.
Então para agrupar esses dados de datas que temos para melhorar a análise desses dados vamos clicar em uma das datas e em seguida ir até a guia Dados. Nesta guia vamos selecionar a opção Agrupar.

Opção para agrupar dados
Ao clicar nessa opção o Excel irá abrir uma nova janela que nos permite escolher o tipo de agrupamento que será feito.

Opções para o agrupamento de dados
Neste caso vamos selecionar as opções de Meses, Trimestres e Anos. Ao pressionar OK o Excel irá dividir todas as datas que temos dentro das opções que selecionamos.

Resultado do agrupamento de dados na tabela dinâmica no excel
OBS: Veja que ao agruparmos as datas o Excel retirou os subtotais da tabela, caso o usuário queira colocá-los de volta basta fazer o procedimento que já vimos.
Desta forma temos uma análise mais detalhada por períodos dentro da tabela, o que facilita ao invés de olharmos data por data. Esse tipo de análise pode variar de acordo com a necessidade do usuário e dos dados que estão sendo analisados, nada impede que o usuário faça uma análise diária caso esteja trabalhando com dados que variem diariamente como é o caso das ações.
Dica 5 – Análise detalhada de dados da tabela dinâmica
Na última tabela que fizemos o agrupamento de dados foi possível notar que temos os dados no último grupo, que é o de meses, contém o somatório de todas as datas daquele mês, mas olhando somente para a tabela não é possível verificar de quais dias ou os outros detalhes que compõem esse valor analisado.
Para isso temos uma forma muito útil que poucas pessoas conhecem para fazer essa análise. Vamos supor que queremos verificar as informações de janeiro de 2018, que se encontra na célula B4, para isso basta dar um duplo clique nessa célula que o Excel irá gerar uma nova aba com os dados detalhados.

Resultado após o duplo clique em um dado da tabela dinâmica no excel
É possível observar que o Excel cria uma nova aba com todos os dados que compõem os dados da célula selecionada. O Excel puxa o mesmo formato que temos na tabela base que foi utilizada para criar a tabela dinâmica. Então essa é uma opção muito útil para mostrar de onde estão vindo aqueles valores da célula selecionada de forma isolada e sem alterar a tabela dinâmica.
Dica 6 – Classificação
Assim como em uma tabela comum dentro da tabela dinâmica podemos fazer a classificação dos dados, para isso basta clicar com o botão direito no dado que será classificado e ir até a opção Classificar.

Opções de classificação de dados
IMPORTANTE: O usuário poderá fazer a classificação para qualquer tipo de dado, como nessa tabela temos valores e datas o Excel permite a classificação dos valores e também das datas, basta selecionar o dado que deseja classificar.
Dica 7 – Lista Personalizada
Para as próximas dicas vamos utilizar a Tabela 3.

Tabela 3 – Terceira tabela dinâmica no excel
É possível observar que temos uma tabela de regiões e do faturamento de cada cliente para cada uma dessas regiões. No entanto as vezes o usuário pode querer modificar essa ordem em que as regiões aparecem, e talvez a classificação em ordem alfabética não é a mais adequada para o caso. Então vamos supor que o usuário queira uma ordem personalizada da seguinte maneira:
Sudeste
Sul
Centro-Oeste
Nordeste
Norte
Então para que seja possível inserir essa ordem que o usuário deseja teremos que construir uma lista personalizada para atribuir a nossa tabela dinâmica. Para a criação dessa lista personalizada vamos até a guia Arquivo e em seguida em Opções. Feito isso será aberta uma janela de opções do Excel. Nesta janela vamos até a opção Avançado e dentro desse ambiente vamos descer a barra de rolagem até encontrar a opção Editar Listas Personalizadas.

Acessando as opções do Excel para criar uma lista personalizada
Ao selecionar essa opção o Excel irá abrir a janela de listas personalizadas já na opção NOVA LISTA, então basta colocar a sequência desejada pelo usuário nesse espaço e clicar em Adicionar para colocarmos essa lista juntamente com as outras.
OBS: Veja que o Excel já possui algumas listas personalizadas como padrão, então se o usuário escrever janeiro em uma célula é arrastar o Excel irá completar automaticamente até a célula desejada com os meses do ano. O mesmo vale para qualquer lista personalizada.

Inserindo os dados da nova lista personalizada
Feito isso podemos clicar em OK e em OK novamente na janela de opções do Excel. Para inserir essa lista dentro da tabela dinâmica vamos clicar com o botão direito em qualquer uma das regiões e acessar a opção Mais Opções de Classificação dentro de Classificar.

Mais opções de classificação dos dados
Ao selecionar essa opção o Excel irá abrir uma nova janela.

Janela para mais opções de classificação
Aqui vamos marcar a opção Crescente para que nossos dados fiquem na ordem que colocamos, caso queira a ordem inversa basta selecionar a opção Decrescente, mas como não inserimos nossa lista ainda vamos clicar em Mais Opções.

Selecionando a lista personalizada que foi criada
Nesta janela vamos desmarcar a caixa que por padrão está marcada e logo abaixo vamos selecionar a nossa lista que acabamos de criar, em seguida basta pressionar OK e OK novamente para a janela anterior. Feito isso teremos nossa tabela dinâmica com a ordem da lista personalizada.

Resultado da classificação com lista personalizada
Dica 8 – Segmentação de Dados
Dentro da tabela dinâmica podemos inserir um “filtro inteligente” que é muito utilizado na criação de Dashboards dentro do Excel, isso serve para facilitar na hora de filtrar e possibilitar que o usuário faça isso de maneira fácil.
Para acessar essa opção vamos clicar em qualquer célula da tabela dinâmica (para habilitar as duas guias), vamos até a guia Analisar e em seguida em Inserir Segmentação de Dados.

Opção de segmentação de dados
Ao selecionar essa opção o Excel irá abrir uma janela com os dados que temos na tabela, desta forma podemos escolher qual deles irá ser utilizado como filtro.

Selecionando a opção de Clientes para a segmentação de dados
Neste caso vamos escolher a segmentação de dados para os Clientes, desta forma o Excel vai gerar uma janela com cada um dos clientes em forma de botões.

Resultado da segmentação de dados
Ao clicar em qualquer um deles o filtro já será aplicado, ou seja, se clicarmos no Cliente B a tabela dinâmica só irá mostrar esse cliente, pois a tabela foi filtrada apenas para ele.

Selecionando apenas um cliente dentro da segmentação
Desta forma o usuário poderá filtrar de forma mais fácil seus dados e facilitar sua análise apenas com os dados que deseja. Caso queira filtra mais de um cliente por vez podemos segurar a tecla CTRL e selecionar outro cliente, ou marcar a opção logo no cabeçalho da segmentação de dados que é a Seleção Múltipla.

Seleção múltipla de cliente na segmentação de dados
Caso o usuário queira fazer algumas modificações nessa segmentação de dados ao selecionar essa caixa poderá ir até a guia Opções e fazer as alterações conforme a necessidade. Poderá alterar a quantidade de colunas em que os dados serão mostrados, o tamanho da caixa e até mesmo os estilos dessa segmentação.

Opções para formatar a segmentação de dados
Dica 9 – Expandir e Recolher Campos
Caso o usuário queira fazer uma análise mais detalhada desses dados inserindo as datas basta selecionar algum dado da tabela e nos campos da tabela dinâmica vamos colocar a Data de Operação na parte de linhas logo abaixo da Região do Brasil.

Inserindo o campo de Data de Operação em linhas
Feito isso é possível observar que o Excel expande essa data em trimestres e anos.

Resultado da inserção de datas no campo de linhas
Dentro da tabela dinâmica temos a opção de expandir as informações clicando no botão de +, desta forma a tabela irá mostrar mais detalhes daquele item.

Expandindo as datas para maior detalhamento de dados
Então é possível “navegar” dentro da tabela dinâmica para ter mais detalhes desses dados. Caso o usuário não queira ficar abrindo item por item para verificar todas as informações temos duas opções que são: Expandir Campo e Recolher Campo.

Opção de expandir e recolher campos
Essas duas opções facilitam, pois, o usuário pode expandir todos os anos (tendo uma célula de ano selecionada) ou todos os trimestres (selecionando a opção de expandir novamente). Isso facilita principalmente se a tabela é muito grande e possui muitos campos para expandir, então evita o trabalho de ter que fazer de um a um.
Caso o usuário queira inserir um filtro de Ano por exemplo basta puxar o campo de ano que está dentro da parte de linhas e remover os campos de Data da Operação e Trimestres.

Inserindo o filtro de Anos
Feito isso a tabela será modificada e ficará com uma opção logo acima para que o usuário possa filtrar de acordo com o ano desejado.

Resultado da inserção de filtros
Dica 10 – Formatação de dados
Caso seja necessário formatar os dados dessa tabela para que possamos inserir o formato contábil para indicar que os dados se tratam de dinheiro a opção que muitos iriam fazer seria selecionar os dados, ir até a guia Página Inicial e em seguida formatar como desejado. No entanto essa formatação será limitada as células que foram selecionadas, então se a quantidade de dados aumentar ou diminuir somente aquelas células serão formatadas.
Para formatar de forma correta vamos pressionar com o botão direito em uma das células da tabela dinâmica e selecionar a opção Formato de Número.

Opção de formato de número da tabela dinâmica no excel
Na janela que será aberta vamos selecionar a opção Contábil e pressionar OK.

Escolhendo o formato Contábil
Feito isso todos os dados serão formatados de forma correta e agora temos a tabela com os dados em formato contábil que mostra que os dados são referentes a dinheiro.

Resultado da formação de número com a opção Contábil
Dica 11 – Formatação Condicional
Assim como em tabelas comuns dentro da tabela dinâmica é possível inserir uma formatação condicional nos dados para formatar da forma que o usuário precisar para determinado problema. Para isso vamos selecionar qualquer uma das células da tabela, em seguida iremos acessar a guia Página Inicial. Aqui vamos fazer o procedimento normal da formatação, ir até a opção Formatação Condicional, Regras de Primeiros/Últimos e então a opção Acima da Média.

Opção de formatação condicional para valores acima da média da tabela dinâmica no excel
Feito isso podemos modificar como será o preenchimento da formatação, neste caso vamos escolher a cor verde. Ao pressionar OK teremos o seguinte resultado.

Resultado para a célula selecionada
Temos apenas a célula selecionada formatada, mas temos uma opção logo ao lado que nos permite expandir essa formatação para toda a tabela.

Opção para expandir a formatação condicional na tabela dinâmica no excel
Ao selecionar a última opção a formatação condicional será expandida para todos os dados da tabela.

Resultado da formatação condicional para toda a tabela dinâmica no excel
Finalizamos então todas as dicas sobre tabela dinâmica que boa parte delas poucas pessoas sabem, mesmo as que tem Excel avançado, portanto são dicas muito úteis que podem ser utilizadas em diversas situações.
Para acessar outra publicação sobre tabela dinâmica, clique no link: https://www.hashtagtreinamentos.com/5-passos-para-aprender-como-fazer-tabela-dinamica-no-excel/
Você conhece as modalidades de curso que a Hashtag Treinamentos oferece? Possuímos uma ampla variedade de cursos, tanto online quanto presenciais! Clique para saber mais!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.