Financial analysts are constantly challenged to transform complex data into clear, actionable insights for the rest of the company. Key performance indicator (KPI) dashboards are a powerful solution that help you keep your finger on the pulse of your business.
By presenting key metrics in a visual, easy-to-understand format, you can effectively communicate insights to departments outside of finance, enhancing cross-functional collaboration and decision making.
Meg Bear, Controller at Turner Enterprises, is well-versed in sharing technical insights built from Excel models to non-technical users—and the benefits it brings. “By allowing people to have real-time access to their data, they have uncovered things,” she says. “Those ‘wows’ are happening on the ground, not just at my level. Now all of a sudden, we see where we’re spending.”
But Excel can pose some challenges even to the most advanced users. How can you create impactful KPI dashboards while incorporating all Excel has to offer?
In this article, we’ll guide you through a step-by-step process to create your own KPI dashboards in Excel. We'll explore best practices for selecting and visualizing the most relevant data points and offer practical tips to maximize your dashboard's impact.
To help you hit the ground running, we’ll also provide customizable templates that you can adapt to your specific reporting needs. But first, let’s explore why creating KPI dashboards in Excel is a good idea.
Excel remains a go-to for KPI dashboards in this era of sophisticated data visualization tools.
Here are three reasons why:
Excel is everywhere in the business world and this is a significant advantage when sharing KPI dashboards. Nearly everyone in your organization, from entry-level employees to C-suite executives, is familiar with Excel's interface and basic functionality.
Bart Vittori, Chief Financial Officer at Meats by Linz, highlights that cross-functional collaboration is critical to driving a business forward and offers an example of this within his company.
“Our sales reps have their own dashboards too so they can see what’s performing and give us some context about what’s happening in the market,” he says. “All of this just makes it so much easier to sit down as a team and say, ‘This is where we’re headed, and this is what we need to do.’”
Excel’s universal understanding eliminates the learning curve associated with specialized data visualization tools, making it easier to distribute and interpret insights across departments. You can ensure that insights are accessible and actionable for all stakeholders, regardless of their technical expertise.
As a finance professional, Excel is likely your primary workspace for data analysis and reporting. Creating KPI dashboards directly in Excel eliminates the need to switch between different applications or platforms.
This means you can work with your data, create visualizations and update dashboards within the same environment. By keeping your workflow contained within Excel, you save time, reduce the risk of data transfer errors and maintain a more efficient and focused analysis process.
Excel’s robust data analysis features provide financial analysts with the power to slice and dice data with remarkable precision. From pivot tables and power queries to advanced formulas and macros, Excel helps you dig beyond surface-level KPIs.
“Operational metrics are important, but the story is not complete until you can see how they impact your costs, revenue and EBITDA margins,” says Paolo Mari, VP of Business Analytics and Commercial Management at Metro Supply Chain Group (MSCG).
Excel’s capabilities allow you to drill down into granular details, uncover hidden trends and perform complex calculations with ease. You can create dynamic KPI dashboards that not only display high-level metrics, but also offer the flexibility to dive deep into the underlying data.
This analytical depth ensures your dashboards offer both broad insights and detailed breakdowns, meeting different inquiry needs within your organization.
Creating an effective KPI dashboard in Excel doesn't have to be a complicated task. In this section, we’ll walk you through the process of building a KPI dashboard, from data preparation to final presentation.
Whether you’re a seasoned Excel user or just getting started with dashboard creation, these steps will help you craft insightful, impactful visualizations that resonate with your stakeholders.
Before creating your KPI dashboard in Excel, it’s crucial to lay a solid foundation. This step involves gathering and organizing your data, as well as clearly defining the purpose and audience for your dashboard.
List your data sources and verify their integrity. You can check for the following:
Data Reliability: Make sure you can rely on the data source.
Data Formats: Check if you need to transform source data and whether each source formats data the same way.
Import Timelines: Verify how long data imports will take. End-of-day imports can mean less flexibility in reporting.
Next, focus on defining the purpose of your dashboard and identifying your audience. Consider the key metrics your stakeholders need to see and how they will use this information.
The most effective dashboards are tailored to their end users, providing useful analysis and actionable insights unique to your audience’s goals.
Every analysis begins by organizing raw data. In the example screenshots below, we’re using GL (General Ledger) and deal data. Our GL dataset mimics what you’d extract from an ERP or accounting database, containing accounts, account names, locations, departments, year periods and values.
Deal data includes information on deals won, projects completed and customers churned.
Deal data in our example.
Transform your raw data into a format suitable for dashboard creation. Highlight your entire data set, then press Ctrl + T to create a table. Here’s what our example datasets look like once transformed.
Highlight your dataset.
Creating a new table and naming it.
Name your tables appropriately (e.g., “GL_Data” and “Deal_Data” in our example) for easy reference later.
Naming our table GL_Data.
While organizing raw data is helpful, these tables don’t help your users dynamically filter and summarize data. To ensure this functionality, you need a pivot table. To do this, click anywhere within a table, then go to Insert > Pivot Table.
How to insert a pivot table.
It’s best to place pivot tables in a new worksheet for easier analysis. Importantly, select the option “Add this data to the Data Model.” This step allows you to create a data model within Excel, which will be crucial for joining different data sets later on.
Adding table data to the data model.
Select the fields that are most relevant to your dashboard’s needs and your pivot table is ready.
The next step is to establish relationships between your tables. This helps you filter data across all of them easily. In our example, we use periods and years to join our data.
To begin, create separate tables for the columns you want to use to join your tables.
In our example, we copied the “Period” column, removed duplicates and converted it to a table.
We did the same for “Years” and named these tables “Periods” and “Years” respectively.
Removing duplicates in data.
Naming the table “Period” in the top-left corner.
To manipulate and summarize data, you need to create a relationship between your tables. To join tables in Excel, you need a common column across both, called a key.
In our example, we're creating a relationship between GL and Deal data tables. We created these from the “Analyze” tab in Excel and used the “Periods” column as a key to join both tables.
Create a relationship between tables.
Matching columns to create a relationship.
Now that you can manipulate all your data in a single view, how can you easily filter it by criteria? This is where slicers come in. Slicers help you quickly filter data at the click of a button.
For instance, in our example, we've created a slicer to filter data in our pivot tables.
Inserting a slicer.
Our Period slicer.
The pivot table automatically updates with relevant data once we click the numbers in the “Period” slicer.
By setting up these relationships and slicers, you've created a powerful filtering system that allows you to manipulate all your data simultaneously.
This interconnected structure is the backbone of your dynamic KPI dashboard, helping you to analyze data across different dimensions with ease.
Now it’s time to pay attention to your dashboard’s visual elements. Create a new tab for your dashboard and use Excel’s formatting tools to create a header per your organization's branding or preferences.
The initial format of our KPI dashboard.
Dynamic tiles are a great way of displaying your data and offer an interactive element in your dashboards. Create a tile by selecting Insert → Shape. In our example, we’ve selected a rectangle.
Selecting a shape.
To make these tiles dynamic, reference cells from the tab containing pivot table data that you created in the previous step. Use the formula bar to create these references (type “=” and click the cell you want to reference), ensuring they pull data from the correct pivot table cells.
Pulling data into our dashboard using the formula bar.
Data displayed in our KPI dashboard.
Enhance your tiles with visual elements like icons that represent each metric. You can find appropriate icons using Excel's insert feature and customize their colors to match your dashboard's theme.
Finally, bring your slicers onto the dashboard to help users filter data dynamically. You can cut and paste them from the tab you created in the previous step. In our example, here's what our dashboard looks like once we've added Period and Year slicers to it.
Our dashboard with the cut-pasted Period and Year slicers.
While dynamic filters help your users filter relevant data, charts bring a dynamic visual element to them.
Just as you can create pivot tables, you can create pivot charts. To do this, click Insert → Pivot Chart. Select relevant data series for this chart, just as you would with a pivot table. In our example, we've created a pivot chart around revenue.
Creating a pivot chart.
Copy and paste your initial chart onto your dashboard, then duplicate it to create multiple charts as needed. Customize each chart by selecting specific data points (we've chosen subscription revenue, professional services and expenses in our example).
Our updated dashboard with KPIs, slicers and pivot charts.
Add headers, adjust colors and format these axes to make your charts visually appealing. To make your charts even more powerful, you can connect them to your slicers.
Right-click on each slicer, select “Report Connections,” and ensure all your charts are linked. This step ensures that your charts update dynamically when users interact with the slicers.
Linking charts using Report Connections.
And with that, your dynamic KPI dashboard is ready.
When defining your KPIs, there are a few more best practices to keep in mind:
Choose the right KPIs. Focus on metrics that align with your strategic goals and provide actionable insights. Remember, it’s not about tracking everything, but rather identifying the key indicators that will guide your business towards success. Here are a few popular financial and operational KPIs you can track:
| Popular Financial KPIs | Popular Operational KPIs |
| Operating cash flow | Units sold/customer count |
| Operating expenses | New and recurring revenue |
| Current ratio | Transactions (quantity, average value, frequency) |
| Net profit margin | Product line profitability |
| Net profit or EBITDA | Employee satisfaction |
| Working capital | Employee turnover |
| Receivables and payables aging | Revenue growth |
| Inventory health | Sales by region/team member |
| Sales growth | Brand exposure/impressions |
| Return on investment | Return on ad spend |
Understand your audience. Different stakeholders may require different levels of detail or focus on specific aspects of performance. Tailor the granularity and presentation of your KPIs to match the expectations and needs of your audience. “A lot of our customers want to know about their costs per order, average wage rates and how they’re tracking against the budget in their contract,” MSCG’s Paolo says, highlighting how KPIs change with the audience and what information you’re presenting to them. Changes in your business can require different KPI measurements too. For instance, Saint Mary’s University switched from measuring the dollar value of a registered student to a single course registration due to COVID-19 induced uncertainty.
Always include a comparison point. You need something you can measure against—whether it’s past performance or something else. If there's no comparison available, it’s not going to work as a KPI. After all, if you can’t measure it against something, what’s the use of following it in the first place?
Have some variety. Try to assemble a mix of strategic and tactical KPIs, operational and financial KPIs and present- and future-facing KPIs—sales orders placed versus pipeline performance, for instance—to help you get a full view of your organizational health and performance.
Don't go overboard. Choosing too many KPIs makes it difficult to keep an eye on anything too closely—making your KPI dashboards less effective. While there’s no “right” number of KPIs to keep up with, between five and 20 is considered the sweet spot by many CFOs.
Creating your own KPI dashboard from scratch can be time consuming. Here are a few popular KPI dashboard templates you can customize to fit your business’s needs.
Free Executive Dashboard Template: Track financial, operational, people and customer KPIs.
Free Marketing Dashboard Template for Excel: Analyze your funnel, conversions and marketing budget effectiveness.
Free Workforce & Headcount Planning Templates: Track workforce capacity and plan headcount with these templates.
Free Resource Allocation Planning Dashboard for Excel: Track employee account salaries, account benefits and total labor expenses.
Free SaaS Revenue Planning Dashboard: Track ARR for existing, churned, pipeline and future opportunities for your SaaS company.
Free Incentive Compensation Management Dashboard: Track employee quota, progress and compensation.
Free Product Dashboard Template: Track insights and snapshots of KPIs related to a specific product or product line.
While Excel is a great foundation for KPI dashboards, Vena offers advanced capabilities to elevate your financial reporting and analysis. By integrating Vena into your workflow, you can transform your Excel dashboards into dynamic, real-time insight engines.
Vena seamlessly connects to your source data, ensuring that your dashboards are always up to date and accurate. This real-time connectivity removes the need for manual data updates, reducing the risk of errors and saving valuable time for your team.
Vena Insights, powered by Power BI Embedded technology, takes your visualizations to the next level. You can easily drill down into complex data sets, uncover hidden trends, improve forecasts with machine learning and gain deeper insights into your business performance.
With Vena, you're not just creating dashboards—you're building a single source of truth. Upgrade your Excel-based KPI dashboards with Vena to unlock the full potential of your financial data and drive your business forward.
And if you want to dive deeper into creating engaging Excel-based KPI dashboards, check out our course in the Vena Academy.
Join Mitchel McCabe, Senior Consultant at Vena, as he guides you through the process of creating KPI dashboards in Excel.
Start Learning
