Dashboard Sem Fórmula no Excel

Nessa publicação vou te mostrar como você pode impressionar qualquer um criando um Dashboard sem fórmula no Excel!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que você aprende neste vídeo

Resposta rápida: A aula monta um dashboard interativo no Excel sem usar nenhuma fórmula: transforma a base de vendas em tabelas dinâmicas, cria gráficos a partir delas e liga tudo com segmentação de dados e linha do tempo, de forma que clicar num filtro atualiza tabelas e gráficos ao mesmo tempo.

Neste vídeo (35 min):

• 2:00 - A base de dados usada no exemplo está disponível para download na descrição, com cada linha representando uma venda
• 4:00 - A base tem uma linha por venda, com colunas de data, fábrica, produto, tamanho e valor do pedido, cobrindo de janeiro de 2017 a dezembro de 2019
• 6:00 - Primeiro passo: selecionar toda a base e criar uma tabela dinâmica numa planilha nova
• 7:00 - Arrastar o campo fábrica para a área de linhas cria uma linha para cada fábrica, sem repetição
• 8:00 - Arrastar o campo valor do pedido para a área de valores soma automaticamente o total de vendas por fábrica
• 11:00 - Arrastar o campo produto para a área de colunas cruza fábrica com produto, mostrando o total de vendas de cada combinação
• 13:00 - A ferramenta segmentação de dados cria um filtro visual: clicar numa fábrica atualiza a tabela dinâmica na hora
• 15:00 - Copiar a segmentação de dados para a aba do dashboard e conectá-la à tabela dinâmica faz o filtro controlar o painel visual
• 18:00 - Um gráfico de colunas criado na aba do dashboard, ligado aos valores da tabela dinâmica, muda junto com a segmentação
• 22:00 - A ferramenta linha do tempo cria outro filtro, dessa vez por data, também conectado à tabela dinâmica
• 27:00 - Uma segunda tabela dinâmica, cruzando fábrica com tamanho do produto, alimenta um segundo gráfico
• 29:00 - A conexão de relatório liga a mesma segmentação de dados e a mesma linha do tempo a mais de uma tabela dinâmica, para os filtros controlarem todos os gráficos juntos

Trechos do vídeo:

• O dashboard inteiro funciona sem nenhuma fórmula digitada: as tabelas dinâmicas fazem as somas, e os gráficos e filtros ficam conectados a elas
• A segmentação de dados funciona como um filtro visual que pode ser conectado a mais de uma tabela dinâmica ao mesmo tempo, por meio das conexões de relatório
• A linha do tempo só aparece quando a base de dados tem uma coluna de data, e filtra por período do mesmo jeito que a segmentação filtra por categoria
• Arrastar um campo para colunas ou linhas de uma tabela dinâmica cruza as categorias automaticamente, sem repetir valores

Para criar um dashboard sem fórmula no Excel, transforme a base em tabela dinâmica (guia Inserir, opção Tabela Dinâmica), crie os gráficos a partir dela e controle tudo com Segmentação de Dados e Linha do Tempo. Ao clicar nos filtros, tabelas e gráficos se atualizam juntos, sem nenhuma fórmula digitada.

O que é e quando utilizar um Dashboard?

Dashboard é um relatório dinâmico que facilita a visualização e manipulação de informações de forma mais fácil, ou seja, o usuário consegue visualizar e escolher as informações que deseja analisar de forma mais rápida e intuitiva.

Vamos utilizar um Dashboard no Excel sempre que quisermos facilitar, melhorar a visualização e análise de dados e principalmente quando o usuário quiser impressionar com seus relatórios, pois com dashboard o Excel acaba não ficando com aquela cara de planilha e fica com um aspecto profissional.

Como Impressionar com um Dashboard sem fórmula?

Nesta aula vamos aprender a como impressionar criando um Dashboard sem fórmula interativo. Isso mesmo não vamos utilizar fórmulas para a criação desse dashboard.

Base de dados

Para iniciar a construção do dashboard vamos precisar de uma base de dados contendo as informações que serão analisadas.

É possível observar que temos informações relacionadas a vendas, então temos a data, fábrica, produto, tamanho, valor do pedido e o cliente que comprou. Com isso temos um banco de dados completo das vendas que foram feitas.

Para o próximo passo vamos selecionar toda essa tabela e criar uma tabela dinâmica. Para isso é possível pressionar as teclas CTRL + T para selecionar toda a base de dados e em seguida ir até a guia Inserir e selecionar a opção Tabela Dinâmica.

Criando a tabela dinâmica

Feito isso o programa irá abrir uma janela para confirmar o intervalo selecionado e se o usuário deseja criar a tabela em uma Nova Planilha. Geralmente vamos criar uma tabela dinâmica em uma nova planilha para facilitar a manipulação dos dados.

Campos da tabela dinâmica

Com isso uma nova aba será criada, onde o usuário já poderá renomear através de um clique duplo no nome. A direita do Excel será possível observar esse menu com os Campos da Tabela Dinâmica, que serão responsáveis pela criação da tabela em si, ou seja, podemos arrastar apenas as informações desejadas para determinados campos para criar a tabela da forma que quisermos.

Inserindo as informações nos campos da tabela dinâmica

Para essa aba Analise1 vamos inserir no campo de linhas a informação de Fábrica e no campo de valores a informação de Valor do Pedido. Com isso a tabela dinâmica já toma sua forma e podemos formatar os valores para facilitar a visualização.

Tabela dinâmica criada

Outra alteração interessante que podemos fazer é remover essa informação de Total Geral, visto que vamos criar um relatório para mostrar todas as informações. Para isso basta selecionar qualquer célula dentro da tabela dinâmica, ir até a guia Design, em seguida em Totais Gerais e por fim selecionar a primeira opção, para desabilitar os totais em linhas e colunas.

Desabilitando os totais de linhas e colunas

OBS: Essas informações que a tabela dinâmica está mostrando é um resumo do valor de venda por cada fábrica em todo o período que temos dentro da base de dados, ou seja, se o usuário filtrar por uma fábrica e somar os valores terá exatamente esses mesmos valores.

Outra alteração importante que vamos fazer é com o nome da tabela dinâmica para facilitar posteriormente. Para alterar esse nome é necessário selecionar alguma célula da tabela, ir até a guia Análise de Tabela Dinâmica, selecionar Tabela Dinâmica e por fim alterar o nome dela.

Alterando o nome da tabela dinâmica

Neste caso vamos alterar o nome dessa tabela para o mesmo nome da aba para facilitar a identificação. Outro ponto importante é que não vamos utilizar espaço entre o nome, será tudo junto.

Excel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Começar agora

Para detalhar um pouco mais a questão dos valores vamos inserir a informação de Produto no campo de colunas, assim teremos o valor de cada um dos produtos para cada uma das fábricas facilitando a análise desses valores.

Tabela dinâmica formatada

OBS: O usuário poderá fazer suas formatações para melhoras a visualização da tabela dinâmica, alterando o formato dos números, alinhamento, entre outras formatações.

O próximo passo já é para criarmos uma ferramenta que será utilizada dentro do Dashboard, que é a segmentação de dados. Para criar essa ferramenta basta selecionar alguma célula da tabela dinâmica, ir até a guia Análise de Tabela Dinâmica e selecionar a opção Inserir Segmentação de Dados.

Inserindo a segmentação de dados

Ao selecionar essa opção o Excel vai abrir uma janela para que o usuário informe qual a coluna que essa segmentação será feita. Neste caso vamos escolher Fábrica.

Selecionando a informação que será segmentada

Depois de selecionar a informação desejada o Excel vai criar uma lista com alguns botões, onde esses botões vão funcionar como filtro, ou seja, quando o usuário clicar em uma fábrica em específico a tabela dinâmica irá filtrar as informações somente para aquela fábrica e irá ocultar as outras informações.

Segmentação de dados concluída

Desta forma conseguimos atualizar de forma bem dinâmica essa análise de dados.

IMPORTANTE: Essa segmentação de dados do nosso dashboard sem fórmula vai funcionar para essa tabela dinâmica em qualquer aba em que esteja.

Sabendo disso podemos criar uma outra aba que será aonde vamos de fato ter o nosso dashboard. Para isso basta clicar no símbolo de + ao lado da última aba e dar o nome de Dashboard.

Como é nessa aba que vamos de fato construir o nosso dashboard podemos copiar a segmentação de dados que acabamos de fazer, colar nessa nova aba e excluir da aba em que foi criada, assim teremos essa segmentação em uma única aba.

OBS: Como foi informado anteriormente, ao copiar e colar essa segmentação de dados ela continua funcionando normalmente independente da aba em que esteja, então se fizermos alguma seleção as informações da aba Analise1 serão modificadas para aquela seleção.

Ainda dentro da aba Dashboard vamos criar um gráfico de Coluna Agrupada, como ainda não temos informações, vamos de fato criar um gráfico vazio para que possamos puxar as informações da nossa aba Analise1.

Criando gráfico de Coluna Agrupada

Com o gráfico em branco criado vamos clicar nele com o botão direito e ir até a opção Selecionar Dados.

Opção para inserir dados no gráfico

Com isso será aberta uma janela para que o usuário consiga inserir as informações que serão mostradas dentro do gráfico.

Janela para seleção de dados

Nesta parte temos 2 campos. O campo da esquerda é onde vamos inserir os valores enquanto no campo da direita vamos inserir as informações do eixo horizontal.

Para iniciar vamos clicar em Adicionar no canto esquerdo para selecionar o que será mostrado no gráfico.

Selecionando os valores

Então as informações que vamos inserir no gráfico são as informações da aba Analise1, então o nome vamos manter o que está na célula A5 que vai ser exatamente o que o usuário vai selecionar na segmentação de dados.

E para os valores vamos selecionar o intervalo de B5 até G5 que é exatamente o intervalo que contém as informações dessa fábrica em específico. Feito isso basta pressionar OK que parte do gráfico já será criada.

Agora basta ir em Editar no campo direito e selecionar o nome dos produtos, também na aba Analise1.

Inserindo as informações do eixo

Finalizada essa parte é possível pressionar OK duas vezes para observar que o gráfico se encontra pronto.

Gráfico concluído

O gráfico está pronto e a medida em que o usuário altera a seleção na segmentação de dados o gráfico já é alterado automaticamente.

O próximo passo do nosso dashboard sem fórmula é voltar até a tabela dinâmica e criar uma linha do tempo, vamos fazer isso através da opção Inserir Linha do Tempo que se encontra logo abaixo da opção de segmentação de dados.

Inserindo linha do tempo

Essa linha do tempo o Excel busca na base de dados uma coluna que tenham informações formatadas como data para habilitar essa ferramenta, desta forma podemos criar de fato uma linha do tempo para que o usuário seleciona os períodos em que deseja analisar as informações.

Isso é útil porque até o momento estamos analisando as informações de todos os meses de todos os anos da base de dados, no entanto é muito útil uma análise temporal dessas informações para verificar o comportamento desses dados.

Prévia do dashboard

Da mesma forma como fizemos com a segmentação de dados podemos fazer com a linha do tempo, ou seja, vamos copiar e colar na aba de Dashboard. Veja que é possível escolher um mês ou um intervalo contínuo dos meses desejados para fazer as análises.

Para dar uma cara mais de dashboard para essa aba podemos remover as linhas de grade, formatar o gráfico para remover algumas informações que não são necessárias, inserir os rótulos de dados. Claro que o usuário pode ir fazendo essas modificações para deixar o dashboard de acordo com sua necessidade.

Dashboard após algumas formatações

Essas modificações podem ser feitas ao longo da construção do dashboard para ir posicionando e adaptando a medida em que vai inserindo novas informações.

A ideia agora é ir inserindo mais informações para melhorar ainda mais a análise desses dados dentro do relatório, neste caso podemos inserir um gráfico de rosca para analisar o percentual em relação ao tamanho dos produtos.

Para isso vamos repetir o procedimento para a criação da tabela dinâmica, no entanto para esse caso vamos mudar a informação que temos em coluna para Tamanho e no campo de valores, vamos mudar de soma para contagem.

Desta forma teremos uma tabela com a quantidade de produtos vendidos por fábrica ao invés de termos o valor de venda de cada um dos tamanhos desses produtos.

Nova tabela dinâmica

OBS: Para alterar de soma para contagem basta clicar na seta em Soma de Valores, ir até a opção de Configurações do Campo de Valor e alterar de soma para contagem.

IMPORTANTE: Um passo muito importante na construção do dashboard sem fórmula é fazer a conexão das segmentações de dados que já temos a nova tabela dinâmica, ou seja, fazer com que a seleção daquela segmentação funcione tanto para a Analise1 quanto para Analise2.

Para fazer essa conexão é necessário clicar com o botão direito na segmentação de dados e selecionar a opção Conexões de Relatório.

Opção de conexão de relatórios

Feito isso será aberta uma janela onde o usuário poderá visualizar todas as tabelas dinâmicas que possui em seu arquivo e visualizar as que estão conectadas a essa segmentação de dados.

Conectando a segunda tabela

Neste caso vamos selecionar também a Analise2 para que ela também seja conectada a essa segmentação de dados. Lembrando que o mesmo procedimento terá que ser feito para a linha do tempo, assim as informações ficarão corretas.

Com as conexões feitas podemos partir para a criação do gráfico de rosca, que terá o mesmo procedimento do gráfico de barras.

Inserindo o gráfico de rosca ao dashboard

Desta forma teremos mais um gráfico que será baseado nas informações de segmentação de dados e linha do tempo selecionados pelo usuário, então ao modificar essa seleção os dois gráficos serão alterados automaticamente de acordo com o que foi selecionado. Ah, e mais uma vez uma ferramenta para o Dashboard sem fórmula.

Assim o usuário pode inserir mais gráficos ou informações para complementar seu dashboard de acordo com o que precisa mostrar para o usuário final, então pode adaptar o seu dashboard a sua necessidade.

Nesta aula foi possível aprender a como criar um dashboard sem fórmula que impressiona através da utilização de tabela dinâmica no Excel, podendo utilizar uma ou mais tabelas para construir e complementar esse dashboard.

Perguntas frequentes

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!

Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?