Criando um painel do Excel (explicado com exemplos e modelos)

Um painel do Excel pode ser uma ferramenta incrível quando se trata de rastrear KPIs, comparar pontos de dados, e obter visualizações com base em dados que podem ajudar o gerenciamento a tomar decisões.

Neste tutorial, você aprenderá como criar um painel do Excel, práticas recomendadas a serem seguidas ao criar um, recursos e ferramentas que você pode usar no Excel , coisas a evitar a todo custo e material de treinamento recomendado.

O que é um painel do Excel e como ele difere de um relatório?

Vamos primeiro entender o que é um painel do Excel.

Um painel do Excel é de uma página (principalmente, mas nem sempre necessário) que ajuda os gerentes e líderes de negócios a rastrear os principais KPIs ou métricas e tomar uma decisão com base nisso. Ele contém gráficos / tabelas / visualizações que são apoiadas por dados.

Um painel é geralmente chamado de relatório, no entanto, nem todos os relatórios são painéis.

Aqui está a diferença:

• Um relatório coletaria e mostraria dados apenas em um único lugar. Por exemplo, se um gerente deseja saber como as vendas cresceram no último período e qual região foi mais lucrativa, um relatório não seria capaz de responder. Ele simplesmente relataria todos os dados de vendas relevantes. Esses relatórios são então usados para criar painéis (no Excel ou PowerPoint) que ajudarão na tomada de decisões.
• Um painel, por outro lado, responderia instantaneamente a questões importantes, como quais regiões têm melhor desempenho e quais produtos deve a gestão focar. Esses painéis podem ser estáticos ou interativos (onde o usuário pode fazer seleções e alterar visualizações e os dados seriam atualizados dinamicamente).

Agora que entendemos o que é um painel, vamos mergulhar em e aprenda como criar um painel no Excel.

Como criar um painel do Excel?

Criar um painel do Excel é um processo de várias etapas e há algumas coisas importantes que você precisa ter em mente ao criá-lo.

Mesmo antes de iniciar o Excel, você precisa ser claro sobre os objetivos do painel.

Por exemplo, se você estiver criando um painel de KPIs para rastrear KPIs financeiros de uma empresa, seu objetivo seria mostrar a comparação do período atual com o período anterior (s).

Da mesma forma, se você estiver criando um painel para o departamento de Recursos Humanos para rastrear o treinamento do funcionário, o objetivo seria mostrar quantos funcionários foram treinados e quantos precisam ser treinados para atingir a meta.

Coisas a fazer antes mesmo de começar a criar um painel do Excel

Muitas pessoas começam a trabalhar no painel assim que colocam as mãos nos dados.

E na maioria dos casos, eles trazem sobre eles a miséria de retrabalhar no painel, pois os objetivos do cliente / parte interessada não são atendidos.

Abaixo estão alguns dos que questões que você deve ter respondido antes de começar a construir um painel do Excel:

P: Qual é o objetivo do painel?

A primeira coisa a fazer assim que você obter os dados ( ou mesmo antes de obter os dados), é para obter clareza sobre o que seu stakeholder deseja. Seja claro sobre a finalidade do painel.

É para rastrear os KPIs apenas uma vez ou regularmente? Ele precisa rastrear os KPIs de toda a empresa ou divisão? Fazer as perguntas certas ajudaria você a entender de quais dados você precisa e como projetar o painel.

P: Quais são as fontes de dados?

Sempre saiba de onde vêm os dados e em qual formato. Em um de meus projetos, os dados foram fornecidos como arquivos PDF no idioma espanhol. Isso mudou completamente o escopo e a maior parte do nosso tempo foi sugado para selecionar manualmente os dados. Aqui estão as perguntas que você deve fazer: Quem é o proprietário dos dados? Em que formato você obterá os dados? Com que frequência os dados são atualizados?

P: Quem usará este painel do Excel?

Um gerente provavelmente estaria interessado apenas nos insights que seu painel fornece, no entanto, alguns analistas de dados em sua equipe pode precisar de uma visão mais detalhada. Com base em quem usa seu painel, você precisa estruturar os dados e a saída final.

P: Com que frequência o painel do Excel precisa ser atualizado?

Se seus painéis forem ser atualizado semanal ou mensalmente, é melhor criar um modelo plug-and-play (onde você simplesmente copia e cola os dados e eles são atualizados automaticamente). Se for um exercício único, você pode deixar de fora alguma automação e fazer isso manualmente.

P: Qual versão do Office o cliente / parte interessada usa?

É melhor não presuma que o cliente / parte interessada tenha a versão mais recente do MS Office. Certa vez, criei um painel apenas para saber que minha parte interessada estava usando o Excel 2003.Isso levou a algum retrabalho, pois a função IFERROR não funciona na versão do Excel 2003 (que usei extensivamente ao criar o painel).

Obtendo os dados no Excel

Assim que você tenha uma boa ideia do que você precisa criar, as próximas etapas são colocar as mãos nos dados e obtê-los no Excel.

Sua vida fica fácil quando seu cliente fornece dados no Excel, no entanto, se não for o caso, você precisa descobrir uma maneira eficiente de obtê-lo no Excel.

Se você tiver arquivos CSV ou de texto, poderá convertê-los facilmente no Excel. Se você tiver acesso a um banco de dados que armazena os dados, pode criar uma conexão e atualizá-lo indiretamente.

Depois de ter os dados, você precisa limpá-los e padronizá-los.

Por exemplo, você pode precisar se livrar de espaços à esquerda, à direita ou duplos, encontrar e remover duplicatas, remover espaços em branco e erros e assim por diante.

Em alguns casos, você pode até precisar reestruturar dados (por exemplo, digamos que você precise criar uma tabela dinâmica).

Essas etapas dependem do projeto e da aparência dos seus dados no Excel.

Delineando a estrutura do painel

Depois de ter os dados no Excel, você saberá exatamente o que pode ou não usar no painel do Excel.

Nesta fase, é uma boa ideia voltar com o seu partes interessadas com um esboço do painel do Excel.

Como prática recomendada, eu crio um esboço simples no PowerPoint junto com notas adicionais. O objetivo desta etapa é garantir que o seu stakeholder compreenda que tipo de painel ele pode esperar com os dados disponíveis.

Também ajuda, pois o stakeholder pode sugerir mudanças que agregariam mais valor a ele.

Aqui está um exemplo de esboço de amostra que criei para um dos painéis de KPI:

Depois de definir o esboço, é hora de começar a criar o painel do Excel.

Como prática recomendada, divida sua pasta de trabalho do Excel em três partes (estas são as planilhas que eu crio com o mesmo nome):

• Dados – pode ser uma ou mais planilhas que contêm os dados brutos.
• Cálculos – Este é onde você faz todos os cálculos. Novamente, você pode ter uma ou mais planilhas para cálculos.
• Painel – esta é a planilha que contém o painel. Na maioria dos casos, é uma visualização de página única que mostra análises / percepções apoiadas por dados.

Tabela do Excel – O molho secreto de um painel eficiente do Excel

A primeira coisa que faço com os dados brutos é convertê-los em uma tabela do Excel.

A tabela do Excel oferece muitas vantagens que são cruciais ao criar um painel do Excel.

Para converter dados tabulares em uma tabela do Excel, selecione os dados e vá para a guia Inserir e clique no ícone Tabela.

Aqui estão os benefícios de usar uma tabela do Excel para o seu painel:

• Quando você converte um conjunto de dados tabulares em uma tabela do Excel , você não precisa se preocupar com a alteração dos dados posteriormente. Mesmo que você obtenha dados adicionais, pode simplesmente adicioná-los à tabela sem se preocupar com a confusão das fórmulas. Isso é realmente útil quando eu crio painéis plug-and-play.
• Com uma Tabela do Excel, você pode usar os nomes das colunas em vez da referência. Por exemplo, em vez de C2: C1000, você pode usar Vendas.

Funções importantes do Excel para painéis

Você pode criar muitos bons painéis interativos do Excel, apenas usando fórmulas do Excel.

Quando você faz uma seleção em uma lista suspensa, ou usa uma barra de rolagem ou marca uma caixa de seleção, existem fórmulas que são atualizadas com base nos resultados e fornecem os dados atualizados / visualizar no painel.

Aqui estão minhas cinco principais funções do Excel para painéis do Excel:

• Função SUMPRODUCT: É minha função favorita ao criar um painel interativo do Excel. Isso me permite fazer cálculos complexos quando há muitas variáveis. Por exemplo, suponha que eu tenha um painel de vendas e desejo saber quais foram as vendas feitas pelo representante Bob no terceiro trimestre na região leste. Posso simplesmente criar uma fórmula de SUMPRODUCT para isso.
• Função INDEX / MATCH: Sou um grande defensor do uso da combinação da fórmula INDEX e MATCH para pesquisar dados em painéis do Excel. Você também pode usar a função PROCV, mas acho ÍNDICE / CORRESPONDÊNCIA a melhor escolha.
• Função IFERROR: Ao fazer cálculos nos dados brutos, muitas vezes você terminará com erros. Eu uso o IFERROR extensivamente para ocultar erros no painel (e muitas vezes nos dados brutos também).
• Função TEXT: Se você deseja criar manchetes ou títulos dinâmicos, você precisa usar a função TEXT para .
• Função ROWS / COLUMNS: Eu uso esses frequentemente quando tenho que copiar uma fórmula e um dos argumentos precisa ser incrementado conforme vamos para baixo / direita da célula.

Coisas a se ter em mente ao trabalhar com fórmulas em um painel do Excel:

• Evite usar fórmulas voláteis (como OFFSET, AGORA, HOJE e assim sobre..). Isso tornará sua pasta de trabalho mais lenta.
• Remova fórmulas desnecessárias. Se você tiver algumas fórmulas adicionais na planilha de cálculo, remova-as ao finalizar o painel.
• Use colunas auxiliares, pois podem ajudá-lo a evitar fórmulas de matriz longa.

Interativo Ferramentas para tornar seu painel do Excel incrível

Existem muitas ferramentas interativas que você pode usar para tornar seu painel do Excel dinâmico e fácil de usar.

Aqui estão algumas que eu uso regularmente:

• Barras de rolagem: use as barras de rolagem para salvar o estado real da pasta de trabalho. Por exemplo, se você tiver 100 linhas de dados, poderá usar uma barra de rolagem com apenas 10 linhas no painel. Um usuário pode usar a barra de rolagem se desejar dar uma olhada em todo o conjunto de dados.
• Caixas de seleção: as caixas de seleção permitem que você faça seleções e atualize o painel. Por exemplo, suponha que eu tenha um painel de treinamento e sou o CEO da empresa, gostaria de dar uma olhada no painel geral da empresa. Mas se eu fosse o chefe de vendas, gostaria apenas de observar o desempenho do meu departamento. Nesse caso, você pode criar um painel com caixas de seleção para diferentes divisões da empresa.
• Lista suspensa: uma lista suspensa permite que um usuário faça uma seleção exclusiva. Você pode usar essas seleções suspensas para atualizar o painel. Por exemplo, se estiver mostrando dados por departamento, você pode colocar os nomes dos departamentos no menu suspenso.

Usando gráficos do Excel para visualizar dados em um painel do Excel

Os gráficos não apenas tornam seu painel do Excel visualmente atraente, mas também facilitam o consumo e a interpretação.

Aqui estão algumas dicas ao usar os gráficos em um painel do Excel:

• Selecione o gráfico certo: o Excel oferece muitas opções de gráficos e você precisa usar o gráfico certo. Por exemplo, se você precisa mostrar uma tendência, você precisa usar um gráfico de linha, mas se quiser destacar os valores reais, um gráfico de barras / colunas pode ser a escolha certa. Embora muitos especialistas desaconselhem o uso de gráficos de pizza, eu sugiro que você use o seu critério. Se o seu público está acostumado a ver gráficos de pizza, você também pode usá-los.
• Use gráficos de combinação: eu recomendo o uso de gráficos de combinação, pois eles permitem que o usuário compare valores e extraia percepções de significado. Por exemplo, você pode mostrar o valor das vendas como um gráfico de colunas e o crescimento como um gráfico de linhas.

• Use gráficos dinâmicos: se você deseja permitir que o usuário faça seleções e deseja que o gráfico seja atualizado com ele, use gráficos dinâmicos. Agora, um gráfico dinâmico nada mais é que um gráfico regular cujos dados são atualizados no back-end quando você faz as seleções.

• Use Minigráficos para tornar seus dados mais significativos: se você tiver muitos dados em seu painel / relatório, pode considerar o uso de Minigráficos para torná-los visuais. Um minigráfico é um pequeno gráfico que reside em uma célula e pode ser criado usando um conjunto de dados. Eles são úteis quando você deseja mostrar uma tendência ao longo do tempo e, ao mesmo tempo, economizar espaço no painel. Você pode aprender mais sobre os Minigráficos do Excel aqui.
• Use cores contrastantes para destacar os dados: esta é uma dica de gráfico genérica onde você deve destacar os dados em um gráfico para que seja fácil de entender. Por exemplo, se você tiver dados de vendas, poderá destacar o ano com o menor valor de vendas em vermelho.

Você pode navegar por alguns de meus tutoriais de gráficos aqui.

Além disso, se você quiser se aprofundar nos gráficos do Excel, recomendo que você visite o blog do especialista em gráficos do Excel Jon Peltier.

O que fazer e o que não fazer nos painéis do Excel

Vamos primeiro começar com o que não fazer!

Aqui estão algumas das coisas que recomendo que você evite ao criar um painel do Excel. Novamente, eles variam de acordo com seu projeto e parte interessada, mas são válidos na maioria dos casos.

• Não bagunce seus painéis: só porque você tem dados e gráficos, não significa que deva ir em seu painel. Lembre-se de que o objetivo do painel é ajudar a identificar um problema ou auxiliar na tomada de decisões. Portanto, mantenha-o relevante e remova tudo o que não pertence a ele. Muitas vezes me pergunto se algo é apenas bom e absolutamente indispensável. Então, eu sigo em frente e removo todos os itens úteis.
• Não use fórmulas voláteis: pois isso tornará os cálculos mais lentos.
• Não guarde dados extras em sua pasta de trabalho: se você precisar desses dados, crie uma cópia do painel e mantenha-a como backup.

Agora, vamos dar uma olhada em alguns procedimentos (ou práticas recomendadas)

• Numerando seus gráficos / seção: seu painel não é apenas um conjunto aleatório de gráficos e pontos de dados. Em vez disso, é uma história em que uma coisa leva à outra.Você precisa ter certeza de que seu público segue as etapas na ordem certa e, portanto, é melhor numerá-las. Embora você possa orientá-los durante uma apresentação ao vivo, é uma grande ajuda quando alguém usa seu painel em um estágio posterior ou o imprime.
• Restringir o movimento na área do painel: Oculte todas as linhas / colunas para garantir que o usuário não role para fora acidentalmente.
• Congelar linhas / colunas importantes: use congelar painéis quando quiser que algumas linhas / colunas estejam sempre visíveis no painel.
• Torne as formas / gráficos fixos: certifique-se de que suas formas / gráficos ou controles interativos não sejam ocultados ou redimensionados quando alguém ocultar / redimensionar as células. Você também pode usar a ferramenta Câmera do Excel para tirar um instantâneo de gráficos / tabelas e colocá-lo no painel do Excel (essas imagens são dinâmicas e atualizam quando o gráfico / tabela de back-end é atualizado).
• Forneça um Guia do usuário: Se você tiver um painel complexo, é uma boa ideia criar uma planilha separada e destacar as etapas. Isso ajudará as pessoas a usarem seu painel, mesmo quando você não estiver lá.
• Economize espaço com gráficos de combinação: use gráficos de combinação (como gráficos de marcadores, gráficos de termômetro e gráficos reais x alvo) para salvar sua planilha espaço.
• Use símbolos & Formatação condicional: use símbolos (como setas para cima / para baixo ou marca de seleção / cruz) e formatação condicional para adicionar um camada de análise para seu painel (mas também não exagere).

Deseja criar painéis profissionais no Excel? Confira o curso Online Excel Dashboard, onde mostro tudo sobre a criação de um painel Excel de classe mundial.

Exemplos de painel

Aqui estão alguns exemplos legais de painel Excel que você pode baixar e jogar com.

Painel KPI do Excel

Você pode usar este painel para rastrear KPIs de várias empresas e em seguida, use gráficos de marcadores para mergulhar profundamente no desempenho individual da empresa.

Clique aqui para baixar este painel de KPI.

Se você estiver interessado em aprender como criar este painel de KPI – clique aqui.

Painel de desempenho do Call Center

Você pode usar este painel para rastrear os principais KPIs de um Central de Atendimento. Neste painel, você pode aprender como criar gráficos de combinação, como destacar pontos de dados específicos em gráficos, como classificar usando botões de opção, etc.

Clique aqui para baixar o painel.

Você pode ler mais sobre este painel aqui.

EPL Season visualizado em um painel do Excel

Neste painel, você aprenderá como usar o VBA em painéis do Excel. Neste painel, os detalhes dos jogos são atualizados quando você clica duas vezes nas células à esquerda. Ele também usa o VBA para mostrar um menu de ajuda para guiar o usuário no uso deste painel.

Clique aqui para baixar o painel EPL.

Você pode ler mais sobre este painel aqui.

Treinamento de painel recomendado

Se quiser aprender como criar painéis profissionais de classe mundial no Excel, confira meu curso online GRATUITO de painel do Excel.