Blog

Postado em em 28 de julho de 2020

Dashboard em Excel – Fácil e Dinâmico

O Dashboard em Excel mais fácil da história!

Nessa publicação você vai aprender a construir um dashboard em Excel bem dinâmico seguindo um passo a passo bem simples e fácil!

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 é um Dashboard em Excel?

Dashboard nada mais é do que um relatório dinâmico com um resumo gerencial de algumas informações, ou seja, ele é uma representação visual com gráficos e/ou tabelas utilizados para facilitar a visualização do usuário, para que ele possa tomar decisões a partir dessas informações mais bem apresentadas.

 

Quando utilizar um Dashboard?

Um dashboard será utilizado sempre que o usuário quiser mostrar de forma mais visual e mais direta os dados que possui em diversas tabelas. Assim conseguirá resumir e facilitar a visualização e análise desses dados.

 

Como criar um Dashboard em Excel?

Antes da criação de qualquer dashboard é importante fazer uma análise de dados para verificarmos o que de fato será mostrado.

 

Tabela inicial

Tabela inicial

 

É possível observar que temos o valor de vendas de algumas empresas durante 7 anos, ou seja, de 2014 até 2020. Com isso já podemos verificar a possibilidade de criar um dashboard dinâmico para mostrar ao usuário o comportamento de cada uma dessas empresas ao longo dos anos.

Nesta aula vamos aprender a criar um dashboard onde o usuário poderá selecionar com um “botão” a empresa que quer analisar e vamos mostrar um gráfico de barras com essas informações.

O primeiro passo será a inserção desses botões dentro da tabela, para isso basta ir em Controles de Formulário e em seguida selecionar o Botão de Opção.

Ensinamos a habilitar nesse vídeo sobre caixa de seleção, assim o usuário poderá acessar os Controles de Formulário: https://youtu.be/HlDNT_i0d74

 

Caixa de controles de formulário - Dashboard em Excel

Caixa de controles de formulário

 

É a opção que tem 2 círculos um dentro do outro como se estivesse selecionado. Ao selecionar essa opção o usuário poderá segurar a tecla ALT do teclado e arrastar para dimensionar em qual célula vai criar esse botão. Neste caso vamos colocar somente em uma célula que será após o último ano analisado.

 

Inserindo o botão de opção dentro da célula

Inserindo o botão de opção dentro da célula

 

Com isso teremos o botão dimensionado para o tamanho da célula para que o usuário não tenha que fazer manualmente. Podemos então apagar o texto, pois nesse caso já será auto explicativo que esse botão se refere a primeira empresa, pois vamos repetir o procedimento para todas elas.

Para repetir o procedimento o usuário poderá selecionar o botão, copiá-lo, selecionar a célula logo abaixo e colar, assim o formato será mantido e o botão será colado no espaço da célula.

 

Copiando e colando o botão de opção para as demais células

Copiando e colando o botão de opção para as demais células

 

Feito isso teremos 1 botão para cada uma das empresas. O interessante desse botão é que ele é interligado com os outros, ou seja, como é um botão de seleção o usuário só consegue selecionar um por vez, portanto isso vai ser útil para nossa análise para que o usuário veja uma informação de cada vez.

O próximo passo é inserir um vínculo para esse botão para sabermos qual informação está sendo selecionada. Para isso basta clicar com o botão direito no primeiro botão e ir até a opção Formatar Controle.

 

Opção para formatar o botão

Opção para formatar o botão

 

Com isso o Excel irá abrir uma janela para que possamos selecionar a célula onde será mostrado o vínculo (o número referente a essa seleção).

 

Selecionando a célula de vínculo

Selecionando a célula de vínculo

 

Neste caso vamos selecionar a célula B11 para o vínculo, então toda vez que o usuário selecionar um dos botões essa célula irá mostrar o número da seleção.

 

Resultado do vínculo no Dashboard em Excel

Resultado do vínculo no Dashboard em Excel

 

Isso vai servir para que possamos fazer a seleção da empresa a ser analisada. Então para fazer essa análise vamos utilizar uma fórmula que pode ser o fim do PROCV que é a fórmula DESLOC.

 

Fórmula DESLOC

Fórmula DESLOC

 

Essa fórmula pede 3 argumentos obrigatórios e 2 argumentos opcionais. O primeiro argumento é a referência, que será a célula B2.

O segundo argumento é a quantidade de linhas que vamos deslocar para obter a informação, como a célula B2 está selecionada e marcamos a Empresa D, vamos deslocar a seleção em 4 linhas que iria retornar exatamente o nome da empresa selecionada.

O terceiro argumento é similar ao anterior, mas faz o deslocamento em colunas, neste caso vamos colocar 0, pois não queremos esse deslocamento em colunas.

Para a altura vamos manter o valor 1 que é de uma célula, ou seja, vamos ter 1 linha.

Para largura vamos colocar o valor 8, pois são quantas informações nós temos na tabela, ou seja, temos 8 colunas com informações. Uma coluna com o nome da empresa e as outras 7 com os valores.

 

Fórmula DESLOC para o Dashboard em Excel

Fórmula DESLOC para o Dashboard em Excel

 

Ao pressionar Enter o Excel vai automaticamente preencher as próximas 7 colunas com essas informações (caso o usuário esteja utilizando a versão mais recente do Excel, 2019 ou 365).

OBS:  Para as versões anteriores utilizar a fórmula matricial ensinada no vídeo:

https://www.youtube.com/watch?v=JtuOCTeUDFY

 

Resultado da fórmula DESLOC

Resultado da fórmula DESLOC

 

Com isso teremos todas as informações da empresa sendo mostradas de forma automática utilizando apenas uma fórmula na célula B12.

Para o próximo passo vamos selecionar as informações de empresa e dos valores de cada uma delas e vamos até a Formatação Condicional que se encontra na guia Página Inicial e em seguida selecionar a opção Nova Regra.

 

Inserindo uma Formatação Condicional

Inserindo uma Formatação Condicional

 

Feito isso será aberta uma janela para escolher a formatação, vamos escolher a última opção para que possamos inserir uma fórmula e vamos colocar uma fórmula para verificar se a empresa da tabela é igual a empresa selecionada.

Em seguida o usuário poderá escolher como vai formatar as células formatadas caso elas sejam iguais a empresa selecionada.

 

Inserindo a fórmula e a formatação desejada

Inserindo a fórmula e a formatação desejada

 

Feito isso a empresa que foi selecionada ficará toda da cor selecionada e a medida em que o usuário seleciona outra empresa a anterior fica sem preenchimento e a nova passa a ter o preenchimento.

 

Resultado da formatação condicional para linha

Resultado da formatação condicional para linha

 

Para finalizar o nosso Dashboard vamos criar um gráfico de colunas com as informações das informações que foram obtidas através da fórmula DESLOC. Para criar o gráfico basta selecionar essas informações ir até a guia Inserir e por fim selecionar o gráfico de Coluna Agrupada.

 

Inserindo um gráfico com as informações da fórmula DESLOC

Inserindo um gráfico com as informações da fórmula DESLOC

 

Feito isso o gráfico será criado e o usuário poderá fazer suas alterações para melhorar a visualização.

 

Gráfico criado para o Dashboard em Excel

Gráfico criado para o Dashboard em Excel

 

Depois basta alterar as informações que deseja no gráfico, inserir as informações no eixo horizontal e apagar as linhas de grade da planilha.

Neste vídeo falamos tudo sobre gráficos inclusive sobre formatação:

https://youtu.be/P_SnCOqMYqQ

Após todas as formatações o usuário terá um dashboard dinâmico no qual o gráfico será alterado a medida em que o usuário seleciona a empresa que deseja analisar.

 

Dashboard em Excel completo e funcional

Dashboard em Excel completo e funcional

 

Nesta aula foi possível aprender a como criar um dashboard dinâmico onde o usuário pode selecionar as empresas através de botões e assim analisar os dados da empresa que foi selecionada.

 

Hashtag Treinamentos

Nosso curso de Excel funciona para qualquer um, desde a pessoa que não sabe absolutamente nada de Excel até aquela pessoa que quer aquele algo a mais para impressionar!


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