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!
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.
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.
Antes da criação de qualquer dashboard é importante fazer uma análise de dados para verificarmos o que de fato será mostrado.
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
É 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
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
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
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
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
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
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
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
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
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
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
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
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
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:
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
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.
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?
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.
Quer sair do zero no Power BI e impressionar no mercado de trabalho? Se inscreva agora no Intensivão de Power BI!