Mais de 100 truques ocultos de tabela dinâmica

Tabelas Dinâmicas é uma das habilidades intermediárias do Excel e este é um tutorial avançado de Tabelas Dinâmicas que mostra as 100 principais dicas e truques para dominar essa habilidade. O fato é que, quando se trata de análise de dados, criação de relatórios rápidos e eficientes ou apresentação de dados resumidos, nada se compara a uma tabela dinâmica.

Ele é dinâmico e flexível. Mesmo se você comparar fórmulas e tabelas dinâmicas, descobrirá que as tabelas dinâmicas são fáceis de usar e gerenciar. Se você quiser usar suas habilidades em tabelas dinâmicas, a melhor maneira é ter uma lista de dicas e truques que você pode aprender.

Ao longo desta lista usei as palavras “Analyze Tab” e “Design Tab”. Para obter essas duas guias na faixa do Excel, primeiro você deve selecionar uma Tabela Dinâmica. Além disso , certifique-se de baixar este arquivo de amostra aqui para tentar esses truques.

5 coisas a considerar antes de criar uma tabela dinâmica

Antes de criar uma tabela dinâmica, você deve dedicar alguns minutos trabalhando na fonte de dados que usará para verificar se há alguma correção que precise ser feita.

1. Sem colunas e linhas vazias nos dados de origem

Uma das coisas que você precisa controlar nos dados de origem é que não deve haver linhas ou colunas vazias.

Ao criar uma tabela dinâmica, se você tiver uma linha ou coluna em branco, o Excel levará apenas os dados até essa linha ou coluna.

tabelas dinâmicas Dicas e truques do Excel para remover linhas e colunas embaladas dos dados de origem

2. Nenhuma célula em branco na coluna de valor

Além da linha e coluna vazias, você não deve ter uma célula vazia na coluna onde estão os valores.

Dicas e truques de tabelas dinâmicas do Excel para que não haja células em branco na coluna de valor

O principal motivo para verificar isso é que se você tiver uma célula em branco na coluna do campo Valores: o Excel aplicará o número no pivô em vez da SOMA dos valores.

3. Os dados devem estar no formato correto

Ao usar dados de origem para uma tabela dinâmica, eles devem estar no formato correto.

Digamos que você tenha datas em uma coluna e essa coluna esteja formatada como texto. Nesse caso, não seria possível agrupar as datas na tabela dinâmica que você criou.

Dicas e truques de tabelas dinâmicas do Excel para formato de dados devem estar corretos

4. Use uma tabela para dados de origem

Antes de criar uma tabela dinâmica, você deve converter os dados de origem em uma tabela.

Dicas de tabela dinâmica do Excel, truques para inserir uma tabela dinâmica

Uma tabela se expande cada vez que você adiciona novos dados a ela e isso facilita a alteração da fonte de dados da tabela dinâmica (quase automática).

Aqui estão as etapas:

  1. Selecione todos os seus dados ou uma das células.
  2. Pressione a tecla de atalho Ctrl + T.
  3. Clique OK.

5. Remova totais de dados

Por fim, certifique-se de excluir o total da fonte de dados.

Dicas e truques de tabelas dinâmicas do Excel para remover o total dos dados de origem

Se você tiver dados de origem com totais gerais, o Excel considerará esses totais como valores e os valores da tabela dinâmica serão aumentados em dobro.

Dica: Se você aplicou uma tabela na fonte de dados, o Excel não incluirá esse total ao criar uma tabela dinâmica.

Dicas para ajudá-lo ao criar uma tabela dinâmica

Agora, essas dicas você pode usar quando os dados estiverem preparados e você estiver pronto para criar uma tabela dinâmica com eles.

Existe uma opção na aba “Inserir” para verificar as tabelas dinâmicas recomendadas. Quando você clica em “Tabelas Dinâmicas Recomendadas”, ele mostra um conjunto de tabelas dinâmicas que podem ser possíveis com os dados que você possui.

Dicas de tabela dinâmica do Excel Dicas para criar uma tabela dinâmica usando a opção dinâmica recomendada

Esta opção é muito útil quando você deseja ver todas as possibilidades que tem com os dados disponíveis.

Existe uma ferramenta no Excel chamada “Análise Rápida” que se parece com uma barra de ferramentas rápida que aparece toda vez que você seleciona o intervalo de dados.

E a partir desta ferramenta você também pode criar uma tabela dinâmica.

Ferramenta de análise rápida ➜ Tabelas ➜ Tabela dinâmica vazia.

Dicas de tabela dinâmica do Excel Dicas para criar uma tabela dinâmica usando a ferramenta de análise rápida

Esta é uma das dicas de tabela dinâmica mais úteis desta lista que quero que você comece a usar agora.

Digamos que você queira dinamizar a partir de uma pasta de trabalho que está em uma pasta diferente e não queira adicionar dados dessa pasta de trabalho à sua planilha atual.

Você pode vincular este arquivo como fonte sem adicionar nenhum dado no arquivo atual. Aqui estão as etapas.

  • Na caixa de diálogo Criar Tabela Dinâmica, selecione “Usar fonte de dados externa”.
Dicas de tabela dinâmica do Excel, truques para selecionar uma fonte de dados externa
  • Depois disso, vá até a aba Conexões e clique em “Procurar mais”.
Dicas de tabela dinâmica do Excel, truques para clicar mais
  • Localize o arquivo que deseja usar e selecione-o.
  • Clique OK.
  • Agora selecione a planilha na qual você possui dados.
dicas de tabela dinâmica do Excel dicas para selecionar uma planilha
  • Clique em OK (duas vezes).
dicas de tabela dinâmica do Excel dicas para arquivo está conectado

Agora você pode criar uma tabela dinâmica com todas as opções de campo do arquivo de origem externo.

Em vez de criar uma tabela dinâmica na guia Inserir, você também pode usar o “Assistente de tabela dinâmica e gráfico dinâmico clássico”.

A única coisa que gosto no assistente clássico é que existe uma opção para extrair dados de várias planilhas antes de criar uma tabela dinâmica.

Dicas de tabela dinâmica do Excel, truques para criar uma tabela dinâmica usando o assistente de tabela dinâmica

Uma maneira fácil de abrir este assistente é usar o atalho de teclado: Alt + D + P.

Nas configurações dos campos da tabela dinâmica, existe uma opção para pesquisar os campos. Você pode pesquisar o campo onde há centenas de colunas.

Dicas de tabela dinâmica do Excel, truques para adicionar campos por meio da barra de pesquisa

Quando você começa a digitar na caixa de pesquisa, ela começa a filtrar as colunas.

Existe uma opção que você pode usar para alterar o estilo da “janela de campo da tabela dinâmica”. Clique no ícone de engrenagem no canto superior direito e selecione o estilo que deseja aplicar.

tabela dinâmica, dicas do Excel, truques para alterar o estilo da janela do campo

Se você tiver um grande conjunto de dados, poderá classificar a lista de campos em ordem de A a Z para facilitar a localização dos campos obrigatórios.

Clique no ícone de engrenagem no canto superior direito e selecione “Classificar de A a Z”. Por padrão, os campos são classificados pelos dados de origem.

Excel-tabela dinâmica-conselhos-truques-para-classificar-lista de campos

Acontece comigo que quando crio uma tabela dinâmica e clico nela, aparece a “Lista de Campos” do lado direito e isso acontece toda vez que clico em uma tabela dinâmica.

Mas você pode desativá-lo e para isso basta clicar no “Botão Feild List” na aba “PivotTable Analyze”.

Dicas da tabela dinâmica do Excel, truques para ocultar o painel da lista de campos

Depois de criar uma tabela dinâmica, a próxima coisa que acho que você precisa fazer é nomear uma tabela dinâmica.

E para isso, você pode ir até a aba Analisar ➜ Tabela Dinâmica ➜ Opções da Tabela Dinâmica e inserir o novo nome.

Dicas de tabela dinâmica do Excel, truques para alterar o nome de uma tabela dinâmica

Recentemente, a opção de criar uma tabela dinâmica foi adicionada ao aplicativo online Excel (opções limitadas).

É tão simples quanto criar um pivô na aplicação web do Excel:

Na guia Inserir, clique no botão “Tabela Dinâmica” no grupo de tabelas…

Dicas de tabela dinâmica do Excel Dicas Crie uma tabela dinâmica no aplicativo Excel Online

…em seguida, selecione o intervalo de dados de origem…

Dicas de tabela dinâmica do Excel Dicas para selecionar dados para criar uma tabela dinâmica no aplicativo Excel Online

…e a planilha onde deseja inseri-la…

…e, no final, clique em OK.

Se quiser automatizar o processo de criação de tabela dinâmica, você pode usar o código VBA para isso.

Truques de dicas de tabela dinâmica do Excel para usar código VBA

Neste guia , mencionei um processo passo a passo simples para criar uma tabela dinâmica usando código de macro.

Formate uma tabela dinâmica como um PRO

Como você pode usar uma tabela dinâmica como relatório, é importante fazer algumas alterações na formatação padrão.

Existem vários estilos predefinidos no Excel para uma tabela dinâmica que você pode aplicar com apenas um clique.

Na aba projetada, você pode encontrar “Estilo de tabela dinâmica” e ao clicar em “Mais”, você pode simplesmente selecionar um estilo de sua preferência.

Você também pode criar um novo estilo personalizado, usando a opção “Novo estilo de tabela dinâmica”.

Dicas de tabela dinâmica do Excel Truques para estilizar uma tabela dinâmica

Quando terminar seu estilo personalizado, você pode simplesmente salvá-lo para usar na próxima vez, ele ainda estará lá.

Vá para Opções de Tabela Dinâmica (clique com o botão direito na Tabela Dinâmica e vá para Opções de Tabela Dinâmica) e marque a caixa “Preservar formatação de célula ao atualizar”.

A vantagem desta opção é que toda vez que você atualizar sua tabela dinâmica, você não perderá a formatação que possui.

Dicas de tabela dinâmica do Excel Dicas para manter a formatação de células ao atualizar uma tabela dinâmica

Além de formatar um, você também precisa mantê-lo, que é “Largura da Coluna”.

Para fazer isso, vá em “Opções da tabela dinâmica” e desmarque “Ajustar automaticamente a largura da coluna na atualização” e clique em OK.

Dicas de tabela dinâmica do Excel, truques para desativar a atualização automática de largura ao atualizar uma tabela dinâmica

Ao usar vários itens em uma tabela dinâmica, você pode simplesmente repetir os rótulos dos itens principais. Facilita a compreensão da estrutura da tabela dinâmica.

  • Selecione a tabela dinâmica e navegue até a guia “Design”.
  • Na guia Design, vá para Layout ➜ Layout do relatório ➜ Repetir todos os rótulos de item.
Dicas de tabela dinâmica do Excel, truques para repetir todos os rótulos de itens

Na maioria dos casos, você deve formatar os valores após criar uma tabela dinâmica.

Por exemplo, se você deseja alterar o número de casas decimais dos números. Basta selecionar a coluna de valores e abrir a opção “Formatar célula”.

Dicas de tabela dinâmica do Excel, truques para formatar valores

E a partir desta opção você pode alterar os números decimais. Na opção “Formato” você pode até alterar outras opções.

Uma das minhas coisas favoritas sobre formatação é alterar o “estilo da fonte” de uma tabela dinâmica.

Você pode usar a opção de formato, mas a maneira mais fácil é fazer isso na guia Página inicial. Selecione toda a tabela dinâmica e selecione o estilo da fonte.

Dicas da tabela dinâmica do Excel Dicas para alterar o estilo da fonte da tabela dinâmica

Ao adicionar uma tabela dinâmica com mais de um campo de item, você obtém subtotais para o campo principal.

Mas às vezes não há necessidade de exibir subtotais. Nessa situação, você pode ocultá-los fazendo o seguinte:

  • Clique na tabela dinâmica e vá para a guia Analisar.
  • Na guia Analisar, vá para Layout ➜ Subtotais ➜ Não mostrar subtotais.
Dicas de tabela dinâmica do Excel Dicas para ocultar subtotais

Assim como os subtotais, você também pode ocultar e mostrar os totais gerais. Abaixo estão as etapas simples para fazer isso.

  • Clique na tabela dinâmica e vá para a guia Analisar.
  • Na guia Analisar, vá para Layout ➜ Total Geral ➜ Desativado para Linhas e Colunas.
Dicas de tabela dinâmica do Excel Dicas para ocultar totais gerais

Em uma tabela dinâmica normal, temos apenas um formato de valores na coluna de valores.

Porém, existem algumas situações (raras) em que você precisa ter formatos diferentes em uma única tabela dinâmica, como abaixo. Para fazer isso, você precisa usar uma formatação personalizada .

No Excel, existem temas de cores predefinidos que você pode usar. Esses temas também podem ser aplicados a tabelas dinâmicas. Vá para a guia “Layout” e clique no menu suspenso “Temas”.

Dicas de tabela dinâmica do Excel, truques para aplicar o tema

Existem mais de 32 temas que você pode aplicar com apenas um clique ou pode salvar seu estilo de formatação atual como tema.

Para cada tabela dinâmica, você pode escolher um layout.

No Excel (se você estiver usando a versão 2007 ou superior) você pode ter três layouts diferentes. Na guia Design, vá para Relatório de layout ➜ Layout e selecione o layout que deseja aplicar.

Dicas de tabela dinâmica do Excel Dicas para alterar o layout de uma tabela dinâmica

Uma das primeiras coisas que faço ao criar uma tabela dinâmica é aplicar “Linha e coluna com marca”.

Você pode aplicá-lo na aba de design e marcar “Coluna com faixas” e “Linhas com faixas”.

Dicas de tabela dinâmica do Excel Dicas para aplicar colunas e linhas com faixas

Filtrar dados em uma tabela dinâmica

O que torna a Tabela Dinâmica uma das ferramentas de análise de dados mais poderosas são os “Filtros”.

Assim como um filtro normal, você pode ativar/desativar filtros em uma tabela dinâmica. Na “Guia Analisar”, você pode clicar no botão “Cabeçalho de campo” para ativar ou desativar filtros.

Dicas de tabela dinâmica do Excel, truques para desativar filtros

Você selecionou uma ou mais células em uma tabela dinâmica e deseja filtrar apenas essas células. Aqui está a opção que você pode usar.

Após selecionar as células, clique com o botão direito e vá em “Filtrar”, depois selecione “Manter apenas os itens selecionados”.

truques de dicas do Excel da tabela dinâmica para a seleção atual filtrar

Assim como filtrar células selecionadas, você também pode ocultá-las. Para fazer isso, vá em “Filtro” e em seguida selecione “Ocultar itens selecionados”.

Dicas de tabela dinâmica do Excel, truques para ocultar a seleção

Além dos filtros normais, você usa filtros de rótulo e filtros de valor para filtrar com um valor ou critério específico.

Filtro de rótulo:

Dicas de tabela dinâmica do Excel, truques para filtro de rótulos

Filtro de valor:

Dicas de tabela dinâmica do Excel, truques para filtrar valores

Como falei na dica acima, você pode ter o campo Label e Value, mas precisa habilitar uma opção para usar essas duas opções de filtro juntas.

  • Primeiro, abra as “Opções da Tabela Dinâmica” e vá para a aba “Total e Filtro”.
  • Na aba “Total e filtro”, marque a caixa “Permitir múltiplos filtros por campo” .
  • Depois disso, clique em OK.
Dicas de tabela dinâmica do Excel Dicas para permitir vários filtros por campo

Uma das minhas opções favoritas em filtros é filtrar por “Top 10 Values” . Esta opção de filtro é útil ao criar um relatório instantâneo.

Para isso, você deve ir até o “Filtro de Valores” e clicar em “Top 10” e depois clicar em OK.

Truques de dicas de tabela dinâmica do Excel para filtrar os 10 principais valores

Se quiser filtrar ao criar uma tabela dinâmica, você pode fazê-lo na janela “Campo Dinâmico”.

Para filtrar os valores em uma coluna, você pode clicar na seta para baixo à direita e filtrar os valores conforme necessário.

Dicas de tabela dinâmica do Excel, truques para filtrar campos na janela de campos da tabela dinâmica

Uma das melhores coisas que encontrei para filtrar dados em uma tabela dinâmica é usar um “Slicer”.

Para inserir um slicer, basta ir até “Aba Analisar” e no grupo “Filtro” clicar no botão “Inserir Slicer”, após selecionar o campo para o qual deseja inserir um slicer e clicar em OK.

Truques com dicas de tabela dinâmica do Excel para adicionar uma segmentação de dados

Relacionado: Excel SLICER – Um guia completo sobre como filtrar dados com ele

Depois de inserir um segmento, você poderá alterar seu estilo e formato.

  • Selecione o slicer e vá para a guia Opções.
  • Em “Slicer Styles”, clique no menu suspenso e selecione o estilo que deseja aplicar.
tabela dinâmica dicas do Excel dicas para formatar um slicer

Além dos estilos, você pode alterar a configuração na janela de configurações: clique no botão “Configurações do Slicer” para abrir a janela de configurações.

Dicas de tabela dinâmica do Excel Dicas para formatar uma segmentação de dados e outras opções

Às vezes, quando você tem várias tabelas dinâmicas, é difícil controlar todas elas. Mas se você conectar um único segmento a várias tabelas dinâmicas , poderá controlar todos os pivôs sem esforço.

  • Primeiro, insira um fatiador.
  • E, em seguida, clique com o botão direito no slicer e selecione “Report Connections”.
Dicas de tabela dinâmica do Excel Dicas de fatiador único para todas as conexões de relatório de tabela dinâmica
  • Na caixa de diálogo, selecione todos os pivôs e clique em OK.
Dicas de tabela dinâmica do Excel Dicas de fatiador único para todas as tabelas dinâmicas Selecionar

Agora você pode simplesmente filtrar todas as tabelas dinâmicas com uma única segmentação de dados.

Ao contrário de um segmento, uma linha do tempo é uma ferramenta de filtragem específica para filtrar datas e é muito mais poderosa que o filtro normal.

Para inserir um slicer, basta ir até “Aba Analisar” e no grupo “Filtro” clicar no botão “ Inserir Linha do Tempo ” e em seguida selecionar a coluna de data e clicar em OK.

Dicas de tabela dinâmica do Excel, truques para adicionar uma linha do tempo

Depois de inserir uma linha do tempo, você poderá alterar seu estilo e formato.

  • Selecione a linha do tempo e vá para a guia Opções.
  • Em “Estilos de linha do tempo”, clique no menu suspenso e selecione o estilo que deseja aplicar.
Dicas de tabela dinâmica do Excel, truques para formatar um filtro de linha do tempo

Além dos estilos, você também pode alterar as configurações.

Dicas de tabela dinâmica do Excel, truques para formatar um filtro de linha do tempo e outras opções

Você pode usar curingas do Excel em todas as opções de filtro onde for necessário inserir o valor a ser filtrado. Veja os exemplos abaixo onde usei um asterisco para filtrar valores começando com a letra A.

Dicas de tabela dinâmica do Excel Dicas curinga com filtros

Se você aplicou filtros em vários campos, poderá remover todos esses filtros na guia Analisar ➜ Ações ➜ Limpar ➜ Limpar filtro.

Dicas de tabela dinâmica do Excel, truques para limpar todos os filtros

Dicas para aproveitar ao máximo as tabelas dinâmicas

Trabalhar com uma tabela dinâmica pode ser mais fácil se você conhecer as dicas que mencionei acima.

Essas dicas ajudarão você a economizar mais de 2 horas por semana.

As tabelas dinâmicas são dinâmicas, portanto, quando você adiciona novos dados ou atualiza valores nos dados de origem, é necessário atualizá-los para que a tabela dinâmica obtenha todos os novos valores adicionados da origem. Atualizar uma tabela dinâmica é simples:

  • Primeiro, clique com o botão direito em um pivô e selecione “Atualizar”.
  • Em segundo lugar, vá para a guia “Analisar” e clique no botão “Atualizar”.
Dicas de tabela dinâmica do Excel, truques para atualizar manualmente uma tabela dinâmica

Existe uma opção simples no Excel que você pode ativar e fazer com que uma tabela dinâmica seja atualizada automaticamente sempre que abrir a pasta de trabalho. Para fazer isso, aqui estão as etapas simples:

  • Primeiro, clique com o botão direito em uma tabela dinâmica e vá para “Opções de tabela dinâmica”.
  • Depois disso, vá até a aba “Dados” e marque a caixa “Atualizar dados ao abrir arquivo”.
Dicas de tabela dinâmica do Excel Truques para marcar a caixa Atualizar tabela dinâmica ao abrir um arquivo
  • No final, clique em OK.

Agora, toda vez que você abrir a pasta de trabalho, esta tabela dinâmica será atualizada instantaneamente.

Se você deseja atualizar sua tabela dinâmica automaticamente após um intervalo específico, esta dica é para você.

… Veja como fazer isso.

  • Primeiramente, ao criar uma Tabela Dinâmica, na janela “Criar Tabela Dinâmica”, marque a caixa “Adicionar estes dados ao modelo de dados”.
Dicas de tabela dinâmica do Excel para adicionar esses dados ao modelo de dados
  • Depois disso, depois de criar uma tabela dinâmica, selecione uma das células e vá para “Aba Analisar”.
  • Na guia Analisar, Dados ➜ Editar fonte de dados ➜ Propriedades da conexão.
Dicas de tabela dinâmica do Excel, truques para clicar em propriedades de conexão
  • Agora, em “Propriedades da conexão”, na aba de uso, marque a caixa “Atualizar a cada” e insira os minutos.
Dicas de tabela dinâmica do Excel, truques para adicionar tempo para atualizar uma tabela dinâmica
  • No final, clique em OK.

Agora, após esse período específico inserido, seu pivô será atualizado automaticamente.

Às vezes, quando você tem erros nos dados de origem, eles refletem o mesmo no pivô e isso não é nada bom.

A melhor maneira é substituir esses erros por um valor significativo.

Abaixo estão as etapas a seguir:

  • Primeiro, clique com o botão direito em sua tabela dinâmica e abra Opções de tabela dinâmica.
  • Agora, em “Layout e Formato”, marque a caixa “Para mostrar valor de erro” e insira o valor na caixa de entrada.
Dicas de tabela dinâmica do Excel, truques para inserir erro de valor de célula
  • No final, clique em OK.

Agora, para todos os erros, você terá o valor especificado.

Digamos que você tenha um pivô para dados de vendas e algumas células estejam em branco.

Porque uma pessoa que não sabe por que essas células estão vazias pode perguntar sobre isso. Portanto, é melhor substituí-lo por uma palavra significativa.

…etapas simples que você precisa seguir para isso.

  • Primeiro, “clique com o botão direito” em sua tabela dinâmica e abra as opções da tabela dinâmica.
  • Agora, em “Layout e Formato”, marque a caixa “Para mostrar células vazias” e insira o valor na caixa de entrada.
truques de tabela dinâmica insira valor para células vazias
  • No final, clique em OK.

Agora, para todas as células vazias, você terá o valor especificado.

Digamos que você tenha um pivô para dados de vendas e algumas células estejam vazias.

Porque uma pessoa que não sabe por que essas células estão vazias pode perguntar sobre isso. Portanto, é melhor substituí-lo por uma palavra significativa.

…etapas simples que você precisa seguir para isso.

  • Primeiro, “clique com o botão direito” em sua tabela dinâmica e abra as opções da tabela dinâmica.
  • Agora, em “Layout e Formato”, marque a caixa “Para mostrar células vazias” e insira o valor na caixa de entrada.
truques de tabela dinâmica insira valor para células vazias
  • No final, clique em OK.

Agora, para todas as células vazias, você terá o valor especificado.

Agora suponha que você tenha uma grande tabela dinâmica com vários itens.

Aqui você pode inserir uma linha em branco após cada elemento para que não haja confusão no pivô.

Dicas de tabela dinâmica do Excel, truques para adicionar uma linha em branco após cada item

Confira estas etapas a seguir:

  • Selecione a tabela dinâmica e vá para a guia Design.
  • Na guia Design, vá para Layout ➜ Linhas em branco ➜ Inserir linha em branco após cada elemento.
Dicas de tabela dinâmica do Excel, truques para adicionar uma linha em branco após cada item na seleção da guia Design

A melhor coisa sobre essa opção é que ela oferece uma visão mais clara do seu relatório.

Quando você tem uma longa lista de itens em seu pivô, pode organizar todos esses itens em uma ordem personalizada simplesmente arrastando e soltando.

Truques da tabela dinâmica do Excel para arrastar e soltar itens

Digamos que você criou uma tabela dinâmica a partir de dados de vendas mensais e usou produtos como filtro de relatório.

Com a opção “Mostrar páginas de filtro de relatório”, você pode criar várias planilhas com uma tabela dinâmica para cada produto.

Digamos que se você tiver 10 produtos em um filtro dinâmico, poderá criar 10 planilhas diferentes com apenas um clique.

Siga esses passos:

  • Selecione seu pivô e vá para a aba de análise.
  • Na guia Analisar, navegue até Tabela Dinâmica ➜ Opções ➜ Mostrar páginas de filtro de relatório.
Dicas de tabela dinâmica do Excel, truques para criar uma planilha separada para cada item

Agora você tem quatro tabelas dinâmicas em quatro planilhas separadas.

Dicas de tabela dinâmica do Excel, truques para criar uma planilha separada para cada opção de item

Quando você adiciona uma coluna de valor no campo de valor, ela mostra SUM ou COUNT (às vezes), mas há algumas outras coisas que você pode calcular aqui:

Para abrir as opções “Configurações de valor”, selecione uma célula na coluna de valor e clique com o botão direito.

dicas de tabela dinâmica do Excel dicas para alterar as configurações de valores para o campo de valor

E no menu de contexto, abra “Configurações do campo de valor” e clique em

No campo “Resumir valor por”, selecione o tipo de cálculo que deseja exibir no pivô.

Digamos que você tenha uma venda de tabela dinâmica por mês.

Agora você deseja inserir um total acumulado em sua tabela dinâmica para mostrar o crescimento total das vendas durante todo o mês.

Aqui estão as etapas:

  • Clique com o botão direito nele e clique em “Configuração do campo de valor”.
  • Na lista suspensa “Mostrar valores como”, selecione “Total acumulado em”.
Dicas de tabela dinâmica do Excel, truques para adicionar parâmetros de campo de valor total em execução
  • No final, clique em OK.
Dicas de tabela dinâmica do Excel Dicas para adicionar uma coluna de total em execução

Saiba mais sobre como adicionar um total acumulado a uma tabela dinâmica .

A classificação oferece uma maneira melhor de comparar as coisas entre si…

…e para inserir uma coluna de classificação em uma tabela dinâmica, você pode seguir os seguintes passos:

  • Primeiro, insira o mesmo campo de dados duas vezes no pivô.
  • Depois disso, para o segundo campo, clique com o botão direito sobre ele e abra “Configurações do campo de valor”.
  • Vá para a guia “Mostrar valores como” e selecione “Ordenar do maior para o menor”.
Dicas de tabela dinâmica do Excel, truques para adicionar opções de linha
  • No final, clique em OK.
Dicas de tabela dinâmica do Excel, truques para adicionar uma coluna de classificação

…clique aqui para saber mais sobre classificação em uma tabela dinâmica .

Imagine que você tem uma tabela dinâmica para vendas de produtos.

E agora você deseja calcular a participação percentual de todos os produtos nas vendas totais.

Passos para usar:

  • Primeiro, insira o mesmo campo de dados duas vezes no pivô.
  • Depois disso, para o segundo campo, clique com o botão direito sobre ele e abra “Configurações do campo de valor”.
  • Vá para a guia “Mostrar valores como” e selecione “% do total geral”.
Dicas de tabela dinâmica do Excel, truques para criar opções de compartilhamento de porcentagem
  • No final, clique em OK.
Dicas de tabela dinâmica do Excel, truques para criar uma parcela percentual

Também é uma opção perfeita para criar um relatório rápido.

Quando você cria uma Tabela Dinâmica, o Excel solicita que você adicione uma nova planilha para a Tabela Dinâmica…

…mas também tem a opção de mover uma tabela dinâmica existente para uma nova planilha.

  • Para fazer isso, vá em “Aba Analisar” ➜ Ações ➜ Mover tabelas dinâmicas.
Dicas de tabela dinâmica do Excel Dicas para mover uma tabela dinâmica para uma nova planilha

Existe uma situação em que você precisa fazer referência a uma célula em um pivô.

Porém, pode haver um problema porque quando você se refere a uma célula em um pivô, o Excel usa automaticamente a função GetPivotData para referência.

O melhor é que você pode desativá-lo e aqui estão os passos:

  • Vá para a guia Arquivo ➜ Opções.
  • Nas opções, vá em Fórmulas ➜ Trabalhar com fórmulas ➜ desmarque “Usar funções GetPivotData para referência de tabela dinâmica”.
Dicas de tabela dinâmica do Excel, truques para desativar a obtenção de dados dinâmicos

Você também pode usar o código VBA para isso:

SubdisableGetPivotData()

Application.GenerateGetPivotData = Falso

Finalizando a legenda

Confira estes ➜ Os 100 principais códigos VBA do Excel + arquivo PDF

Imagine que você deseja criar uma tabela dinâmica por mês, mas tem datas em seus dados.

Nesta situação, você terá que adicionar uma coluna extra por meses.

Mas a melhor maneira é criar usando métodos de agrupamento de datas na tabela dinâmica. Usando este método, você não precisa adicionar uma coluna auxiliar.

Use as etapas abaixo:

  • Primeiro, você precisa inserir a data como um item de linha em sua tabela dinâmica.
Truques de dicas de tabela dinâmica do Excel para agrupar datas e adicionar uma coluna de data
  • Clique com o botão direito na tabela dinâmica e selecione “Grupo…”.
tabela dinâmica dicas do excel truques para agrupar datas clique no grupo
  • Selecione “Mês” na seção por e clique em OK.
Truques de dicas de tabela dinâmica do Excel para agrupar datas e selecionar mês

Irá agrupar todas as datas em meses e se quiser saber mais sobre esta opção aqui está o guia completo.

Assim como as datas, você também pode agrupar valores numéricos.

Os passos são simples.

  • Clique com o botão direito na tabela dinâmica e selecione “Grupo…”.
  • Insira o valor para criar um intervalo de grupo na caixa “por” e clique em OK.
tabela dinâmica, dicas do Excel, truques para agrupar dados numéricos

… clique aqui para saber como a opção de agrupamento de tabelas dinâmicas pode ajudá-lo a criar um histograma no Excel.

Para agrupar colunas como linhas, você pode seguir as mesmas etapas das linhas. Mas você precisa selecionar um cabeçalho de coluna antes disso.

Quando você não precisa de grupos em sua tabela dinâmica, você pode simplesmente desagrupar eles clicando com o botão direito e selecionando “Desagrupar”.

Para se tornar um usuário avançado de tabela dinâmica, você precisa aprender como criar um campo calculado e um item em uma tabela dinâmica.

Digamos que na tabela dinâmica abaixo você precise criar novos dados por campo multiplicando o campo de dados atual por 10.

Dicas de tabela dinâmica do Excel, truques para inserir um campo personalizado

Nessa situação, em vez de criar uma coluna separada em uma tabela dinâmica, você pode inserir um item calculado.

um guia completo para criar um item e campo calculado em uma tabela dinâmica

Depois de adicionar um cálculo a uma tabela dinâmica ou ter uma tabela dinâmica com um campo ou item calculado, você poderá ver a lista de fórmulas usadas.

Para isso, basta acessar “Aba Analisar” ➜ Cálculo ➜ Campos, Elementos e Conjuntos ➜ Listar Fórmulas.

Dicas de tabela dinâmica do Excel, truques para obter a lista de fórmulas usadas no item da tabela dinâmica

Você receberá instantaneamente uma nova planilha com uma lista de fórmulas usadas na tabela dinâmica.

Se você tiver valores duplicados em sua data, poderá usar uma tabela dinâmica para obter uma lista de valores exclusivos.

  • Primeiro, você precisa inserir uma tabela dinâmica e, em seguida, adicionar a coluna onde há valores duplicados como um campo de linha.
  • Depois disso, copie este campo de linha do pivô e cole-o como valores.
  • Agora, a lista que você tem como valores é uma lista de valores exclusivos.

O que eu gosto em usar uma tabela dinâmica para verificar valores exclusivos é que ela é uma configuração que serve para todos.

Você não precisa criá-lo repetidamente.

Digamos que você tenha entradas em seus dados de origem onde não há valores ou valores zero.

Você pode ativar a opção “Mostrar elementos sem dados” no campo.

  • Primeiro, clique com o botão direito no campo e abra “Configurações de campo”.
  • Agora vá em “Layout e Impressão” e marque “Mostrar itens sem dados” e clique em OK.
Dicas de tabela dinâmica do Excel, truques para mostrar itens sem dados

Estrondo! Todos os itens para os quais você não possui dados serão exibidos na tabela dinâmica.

Esta é uma das minhas opções favoritas de tabela dinâmica.

Com isso você pode criar uma coluna onde mostra a diferença dos valores atuais em relação ao valor anterior.

Digamos que você tenha um pivô com valores de mês,…

…então, com esta opção…

… você pode adicionar uma coluna de valor de diferença do mês anterior, como abaixo.

Aqui estão as etapas:

  • Primeiro, você precisa adicionar a coluna onde estão os valores, duas vezes no campo de valor.
tabela dinâmica dicas do Excel truques para inserir coluna de diferença adicionar coluna de valor duas vezes
  • Depois disso, para o segundo campo, abra “Configuração de valor” e “Mostrar valor como”.
  • Agora, na lista suspensa “Mostrar valores como”, selecione “Diferença de” e selecione “Mês” e “(Anterior)” em “Item base”.
  • No final, clique em OK.
Truques de dicas de tabela dinâmica do Excel para inserir coluna de diferença, selecione as opções anteriores

Isso converterá instantaneamente a coluna de valores em uma coluna diferente da anterior.

Dicas de tabela dinâmica do Excel, truques para inserir uma coluna de diferença

Quando você clica duas vezes em uma célula de valor em uma tabela dinâmica, os dados subjacentes a esse valor são exibidos.

Dicas da tabela dinâmica do Excel Dicas para desativar a exibição de detalhes

Isso é bom, mas não é todo o tempo que você precisa para que isso aconteça e é por isso que você pode desligá-lo se necessário.

Tudo o que você precisa fazer é abrir as opções da tabela dinâmica, ir até a “Guia Dados” e desmarcar “Ativar detalhes de exibição”.

E então clique em OK.

Aqui estão as etapas simples para colar uma tabela dinâmica em um slide do PowerPoint.

  • Primeiro, selecione uma tabela dinâmica e copie-a.
  • Depois disso, vá até o slide do PowerPoint e abra as opções especiais de colagem.
Dicas de tabela dinâmica do Excel Dicas para girar a tabela no Powerpoint Colar especial
  • Agora, na caixa de diálogo de colagem especial, selecione “Objeto gráfico do Microsoft Excel” e clique em OK.

Insira uma imagem

Para fazer alterações na tabela dinâmica, você deve clicar duas vezes no gráfico.

Para adicionar uma tabela dinâmica no Microsoft Word, você deve seguir os mesmos passos do PowerPoint.

Se você tiver vários campos de dimensão em uma linha ou coluna, poderá expandir ou recolher os campos externos.

Você precisa clicar no botão + para expandir e no botão – para recolher…

Dicas de tabela dinâmica do Excel, truques para expandir ou recolher cabeçalhos de campo

…e para expandir ou recolher todos os grupos de uma vez, você pode clicar com o botão direito e escolher a opção.

Se você tiver vários campos de dimensão em uma linha ou coluna, poderá expandir ou recolher os campos externos.

Dicas de tabela dinâmica do Excel Truques para ocultar botões de expansão ou recolhimento

Existe uma opção em uma tabela dinâmica onde você pode contar o número de células com o valor numérico.

Dicas de tabela dinâmica do Excel Dicas para usar a tabela dinâmica em uma planilha protegida

Para isso, basta abrir a “Opção de Valor” e selecionar “Número de Contagem” no “Campo de Valor Resumido por” e clicar em OK.

Sim, você pode classificar por valores correspondentes.

  • Basta abrir o filtro e selecionar “Mais opções de classificação”.
excel-dicas-de-tabela-dinâmica-truques-para-classificar-elementos-por-um-valor-correspondente-abrir-filtro
  • Em seguida, selecione “Acesso (A a Z) por:” e selecione a coluna para classificar e clique em OK.
tabela dinâmica, dicas do Excel, truques para classificar itens com base em um valor correspondente, abrir seleção

Observação:

Se você tiver várias colunas de valores, poderá usar apenas uma coluna para ordem de classificação.

Sim, você pode usar uma ordem de classificação personalizada para sua tabela dinâmica.

  • Para isso, ao abrir “Mais opções de classificação”, clique em “Mais opções” e desmarque a caixa “Classificar automaticamente quando o relatório for atualizado”.
  • Depois disso, selecione a ordem de classificação e clique em OK no final.

Você precisa criar uma nova ordem de classificação personalizada e, em seguida, criá-la na guia Arquivo ➜ Opções ➜ Avançado ➜ Geral ➜ Editar lista personalizada.

Se você ativar “Atualização atrasada de layout” e depois arrastar e soltar campos entre as áreas.

Dicas de tabela dinâmica do Excel Dicas de layout preguiçoso

Sua tabela dinâmica não será atualizada a menos que você clique no botão Atualizar abaixo, no canto dos campos da tabela dinâmica.

Isso torna mais fácil para você verificar a tabela dinâmica.

Quando você insere um campo de valor, o nome obtido para o campo se parece com “Soma do Valor” ou “Contagem de Unidades”.

Mas às vezes (bem, o tempo todo) você precisa alterar esse nome para um nome sem “Soma de” ou “Contagem de”.

Para fazer isso, basta remover “Contagem de” ou “Soma de” da célula e adicionar um espaço no final do nome.

Sim é isso.

Se você quiser selecionar uma tabela dinâmica inteira de uma vez:

Selecione uma das células da tabela dinâmica e use o atalho de teclado Control + A.

Ou…

Vá para a guia Analisar ➜ Selecione ➜ Tabela dinâmica inteira.

Dicas de tabela dinâmica do Excel, truques para selecionar a tabela dinâmica inteira de uma vez

Se você deseja converter uma tabela dinâmica em valores, basta selecionar a tabela dinâmica inteira e então:

Use Control + C para copiá-lo e cole especial ➜ Valores.

Ao proteger uma planilha que contém uma tabela dinâmica, certifique-se de verificar:

“Usar a tabela dinâmica e o gráfico dinâmico”

de “Permitir que todos os usuários desta planilha façam:”.

Dicas de tabela dinâmica do Excel Dicas para usar a tabela dinâmica em uma planilha protegida

Se você deseja abrir as “Configurações de valor” para uma coluna de valor específica…

…ENTÃO…

Dicas de tabela dinâmica do Excel, truques para clicar duas vezes para abrir as configurações do campo de valor

…a melhor maneira é clicar duas vezes no cabeçalho da coluna.

Torne suas tabelas dinâmicas um pouco mais perfeitas

As tabelas dinâmicas são uma das maneiras mais eficazes e fáceis de criar relatórios. E precisamos compartilhar relacionamentos com outras pessoas o tempo todo. Antes, compartilhei algumas dicas úteis que podem ajudá-lo a compartilhar uma tabela dinâmica facilmente.

Se você pensa assim: Quando você cria uma tabela dinâmica do zero, o Excel cria um cache dinâmico.

Portanto, quanto mais tabelas dinâmicas você criar do zero, mais cache dinâmico o Excel criará e seu arquivo terá que armazenar mais dados.

Então qual é o objetivo?

Certifique-se de que todas as tabelas dinâmicas provenientes de fontes de dados tenham o mesmo cache.

Mas Puneet, como eu poderia fazer isso?

Simples, sempre que precisar criar um segundo, terceiro ou quarto… basta copiar e colar o primeiro e fazer alterações nele.

Outra coisa que você pode fazer antes de enviar uma tabela dinâmica para alguém é excluir os dados de origem.

Sua tabela dinâmica ainda funcionará corretamente.

E, se alguém precisar dos dados de origem, poderá obtê-los clicando no total geral da tabela dinâmica.

Outra maneira de compartilhar uma tabela dinâmica com alguém é criar uma página da web.

Sim, um arquivo HTML simples com uma tabela dinâmica.

  • Para fazer isso, basta salvar a pasta de trabalho como uma página da web [html].
Dicas de tabela dinâmica do Excel Dicas para salvar uma tabela dinâmica como uma página da Web
  • Na página Publicar como Web, selecione a tabela dinâmica e clique em “Publicar”.
Dicas de tabela dinâmica do Excel Dicas para salvar uma tabela dinâmica como uma página da Web como uma postagem

Agora você pode enviar esta página HTML para qualquer pessoa e ela poderá ver a tabela dinâmica (não editável) até mesmo em seu celular.

Dicas de tabela dinâmica do Excel Dicas para salvar uma tabela dinâmica como um arquivo HTML de página da Web

Suponha que você tenha um link da web para um arquivo Excel, como abaixo:

 https://exceladvisor.org/book1.xlsx

Nesta pasta de trabalho você tem os dados e com esses dados você precisa criar uma tabela dinâmica.

Para isso, POWER QUERY é a chave.

Confira isto: Exemplos de Power Query + dicas e truques

  • Primeiro, vá para a guia Dados ➜ Obter e transformar dados ➜ Da Web.
Dicas de tabela dinâmica do Excel Dicas para criar uma tabela dinâmica usando o Power uery, obtendo dados de um link da web
  • Agora, na caixa de diálogo “Da Web”, insira o endereço da web da pasta de trabalho e clique em OK.
Dicas de tabela dinâmica do Excel Truques para criar uma tabela dinâmica usando o Power uery, obtendo dados de um link da Web Adicionar URL
  • Depois disso, selecione a planilha e clique em “Carregar para”.
tabela dinâmica, dicas do Excel, truques para criar uma tabela dinâmica usando o Power Uery, obtendo dados de um link da web, selecione uma planilha
  • Em seguida, selecione o relatório de tabela dinâmica e clique em OK.
excel-dicas-de-tabela-dinâmica-truques-paracriar-tabela-dinâmica-usando-power-uery-por-obter-dados-de-um-link-da-web-adicionar-url

Neste ponto, você tem uma tabela dinâmica em branco conectada à pasta de trabalho a partir do endereço da Web inserido.

Agora você pode criar uma tabela dinâmica como desejar.

O que você pode fazer em uma tabela dinâmica com CF

Para mim, a formatação condicional é uma formatação inteligente. Tenho certeza que você concorda com isso. Bem, quando se trata de tabela dinâmica, CF funciona perfeitamente.

Existem todas as opções de CF disponíveis para uso com uma tabela dinâmica.

➜ aqui está o guia que pode ajudá-lo a aprender todas as diferentes maneiras de usar CF em tabelas dinâmicas .

Em vez de filtrar, você pode destacar os 10 primeiros valores em uma tabela dinâmica.

Para fazer isso, você deve usar a formatação condicional.

As etapas estão abaixo:

  • Selecione uma das células na coluna de valor da sua tabela dinâmica.
  • Vá para a guia Início ➜ Estilos ➜ Formatação Condicional.
  • Agora em Formatação Condicional, vá para Regras Superiores/Inferiores ➜ 10 Principais Elementos.
Dicas de tabela dinâmica do Excel, truques para aplicar as 10 principais opções de formatação condicional
  • Selecione a cor na janela que você possui.
tabela dinâmica, dicas do Excel, truques para aplicar as 10 principais opções de formatação condicional, selecione a cor
  • E no final clique em OK.

Esta opção é muito útil ao criar relatórios rápidos com uma tabela dinâmica e uma vez que você.

Você pode simplesmente remover a formatação condicional de uma tabela dinâmica seguindo as etapas abaixo:

  • Primeiro, selecione uma das células da tabela dinâmica.
  • Depois disso, vá para a aba Home ➜ Estilos ➜ Formatação Condicional ➜ Limpar Regras ➜ “Limpar as regras para esta tabela dinâmica”.
Dicas de tabela dinâmica do Excel, truques para limpar toda a formatação condicional

Se você tiver várias tabelas dinâmicas, deverá excluir os CFs um por um.

Use gráficos dinâmicos com tabelas dinâmicas para visualizar seus relatórios

Sou um grande fã da tabela dinâmica.

Se você souber usar uma tabela dinâmica corretamente, poderá aproveitar ao máximo uma das melhores ferramentas do Excel.

Aqui estão algumas dicas que você pode usar para se tornar um PRO de tabela dinâmica rapidamente e se quiser saber tudo sobre uma tabela dinâmica , você pode aprender com este guia .

Compartilhei um atalho de teclado simples para inserir uma tabela dinâmica , mas você também usa as etapas abaixo:

  • Selecione uma célula na tabela dinâmica e vá para “Aba Analisar” .
  • Na aba “Analisar”, clique em “Gráfico Dinâmico”.
Dicas de tabela dinâmica do Excel, truques para inserir uma tabela dinâmica

Ele criará instantaneamente uma tabela dinâmica a partir da tabela dinâmica que você possui.

Uma tabela dinâmica e um gráfico dinâmico são minha forma favorita de criar um histograma no Excel.

Dicas de tabela dinâmica do Excel Dicas para criar um histograma usando uma tabela dinâmica

Aqui está um guia passo a passo para isso.

Quando você insere uma nova tabela dinâmica, ela vem com botões para filtrá-la que às vezes não são muito úteis.

E se você pensa assim, você pode esconder todos ou alguns deles.

dicas de tabela dinâmica dicas do Excel uma tabela dinâmica com botões de filtro

Clique com o botão direito no botão e selecione “Ocultar botão de campo de valor no gráfico” para ocultar o botão selecionado ou clique em “Ocultar todos os botões de campo no gráfico” para ocultar todos os botões.

Truques de dicas de tabela dinâmica do Excel Ocultar botões de filtro de tabela dinâmica

Quando você oculta todos os botões em uma Tabela Dinâmica, ele também oculta o botão de filtro na parte inferior do gráfico , mas você ainda pode filtrá-lo usando o filtro de Tabela Dinâmica, Slicer ou Linha do Tempo.

Aqui estão as etapas simples para colar uma tabela dinâmica em um slide do PowerPoint.

  • Primeiro, selecione uma tabela dinâmica e copie-a.
  • Depois disso, vá para o slide do PowerPoint e abra Opções de colagem especiais .
tabela dinâmica dicas do Excel truques para copiar uma tabela dinâmica para uma apresentação de slides em power point abrir colar especial
  • Agora, na caixa de diálogo de colagem especial, selecione “Objeto gráfico do Microsoft Excel” e clique em OK.
Dicas de tabela dinâmica do Excel Dicas para copiar uma tabela dinâmica para um slide do Power Point Selecione um objeto gráfico do Microsoft Excel

Para fazer alterações na tabela dinâmica, você precisa clicar duas vezes nela.

Atalhos de teclado para aumentar seu trabalho na tabela dinâmica

Todos nós amamos atalhos de teclado. CERTO? Listei aqui alguns dos atalhos de teclado comuns, mas úteis, que você pode usar para acelerar o trabalho da tabela dinâmica.

Alt + N + V

Para usar esta tecla de atalho, certifique-se de ter selecionado os dados de origem ou de que a célula ativa seja dos dados de origem.

Alt + Shift + Seta para a direita

Digamos que você tenha uma tabela dinâmica com meses e queira agrupar os primeiros seis ou os últimos seis meses.

Tudo que você precisa fazer é selecionar essas seis células e usar esta tecla de atalho.

Alt + Shift + Seta para a esquerda

Assim como você pode criar um grupo de itens, este atalho ajuda a desagrupar esses itens do grupo.

Ctrl + –

Esta tecla de atalho simplesmente ocultará as células selecionadas.

Na verdade, ele não oculta as células, mas as filtra, o que você pode limpar nas opções de filtros.

Ctrl + –

Para usar esta tecla de atalho, você deve selecionar uma célula na coluna do campo de valor.

E ao pressionar esta tecla de atalho, abre a janela Campo Calculado .

Alt + D e P

Neste atalho de teclado, você deve pressionar as teclas posteriormente.

Alt + seta para baixo

Esta chave abre a lista de campos.

Alt+F1

Para usar este atalho de teclado, você deve selecionar uma célula na tabela dinâmica. Esta chave insere uma tabela dinâmica na planilha existente.

F11

E, se quiser inserir um pivô em uma nova planilha, basta usar a chave acima.

No final

Como eu disse, as tabelas dinâmicas são uma daquelas ferramentas que podem ajudá-lo a melhorar a geração de relatórios e a análise de dados rapidamente.

E com essas dicas e truques você pode economizar ainda mais tempo. Se você me perguntar, quero que comece a usar pelo menos 10 dicas primeiro, depois passe para as próximas 10 e assim por diante.

Mas agora você tem que me dizer uma coisa: qual é a sua dica favorita de tabela dinâmica?

Não deixe de ler estes

Adicione um comentário

O seu endereço de email não será publicado. Campos obrigatórios marcados com *