Unlock the secrets to mastering interactive Excel dashboards with our ultimate step-by-step guide. Discover essential tips and tricks to transform your data visualization skills and enhance your reporting.
by Mihir Kamdar / Last Updated:
This comprehensive guide on building a interactive Excel dashboard. By the end of this article, you’ll be able to:
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently! In this tutorial, we’ll use a zipped folder that contains data related to yearly sales data. customer, item type, location, order priority, and sales channel.
In today’s fast-paced business world, data visualization is crucial for making informed decisions quickly. Excel dashboards are a powerful tool that can help professionals across various industries analyze and present data effectively. In this comprehensive guide, we’ll walk you through the process of creating an interactive Excel dashboard in under 30 minutes, using a real-world example that demonstrates each step along the way.
Data visualization has become increasingly important as businesses generate and collect vast amounts of data. By presenting data in a visual format, dashboards make it easier for decision-makers to identify trends, patterns, and anomalies that might otherwise go unnoticed in raw data. Interactive dashboards take this a step further by allowing users to explore data dynamically, asking questions and uncovering insights in real-time.
Interactive dashboards in Excel are a type of dashboard that allows you to track and measure key performance indicators (KPIs) and metrics. They provide a visual representation of complex data, making it easier to understand and analyze. With interactive dashboards, you can change data inputs, filter data, and drill down into specific details to gain insights and make data-driven decisions.
An interactive dashboard is a visual representation of key data points that allows users to engage with the information dynamically. It typically includes features like filters, slicers, and drill-down capabilities, enabling users to explore data at various levels of detail. Interactive dashboards provide a user-friendly interface for analyzing complex data sets and uncovering valuable insights.
Key features and elements of an effective interactive dashboard include:
Clear and concise data visualizations (charts, graphs, tables)
Interactive filters and slicers for data exploration
Consistent layout and design that aligns with brand guidelines
Accessible and user-friendly interface
Excel is an ideal platform for creating dashboards due to its accessibility, powerful built-in tools, and ease of sharing within an organization. Most professionals are familiar with Excel, making it a convenient choice for building and distributing dashboards. Excel’s wide range of functions, formulas, and charting options allow users to create sophisticated and dynamic dashboards without requiring advanced programming skills.
Some of the key benefits of using Excel for dashboards include:
Wide availability and familiarity among professionals
Powerful data analysis tools (PivotTables, formulas, functions)
Extensive charting and visualization options
Ability to create interactive elements (slicers, timelines, drop-down lists)
Ease of sharing and collaboration within an organization
Before diving into creating your Excel dashboard, it’s important to plan and define the objectives and audience for your dashboard. This will help you ensure that your dashboard is focused, relevant, and effective in providing the necessary information to the right stakeholders.
Before creating your dashboard, it’s essential to understand the needs and expectations of your target audience. Consider who will be using the dashboard and what information they need to make informed decisions. Clearly define the objectives of your dashboard and ensure they align with your organization’s goals.
Some questions to consider when identifying your audience and objectives:
Who will be using the dashboard (executives, managers, analysts, etc.)?
What key decisions will the dashboard inform?
What metrics and data points are most important to the target audience?
How often will the dashboard be used and updated?
What actions should users take based on the insights provided?
For this tutorial, we’ll create a revenue dashboard for a small e-commerce business, focusing on key metrics like revenue, orders, and customer acquisition. The target audience for this dashboard will be the sales and marketing teams, who will use the insights to optimize product performance, customer acquisition strategies, and overall business growth.
KPIs are measurable values that demonstrate how effectively a company is achieving its objectives. They help organizations track progress, identify areas for improvement, and make data-driven decisions. When selecting KPIs for your dashboard, consider the following:
Relevance: Choose KPIs that directly relate to your dashboard objectives and audience needs.
Actionability: Select KPIs that provide insights users can act upon to drive improvements.
Simplicity: Limit the number of KPIs to avoid overwhelming users and maintain focus on key metrics.
Examples of common KPIs across different industries include:
Sales and Revenue: Total revenue, revenue growth, average order value, conversion rate
Marketing: Customer acquisition cost, lead generation, click-through rate, return on ad spend
Customer Service: Customer satisfaction score, first response time, resolution rate
Operations: Inventory turnover, cycle time, on-time delivery, production yield
For our e-commerce revenue dashboard, we’ll use the following KPIs:
Revenue
Total Orders
Average Revenue Per Order
These KPIs will help us track the overall health of the business, identify top-performing products and acquisition channels, and monitor customer behavior.
To create an interactive Excel dashboard, you need to collect and prepare your data your data. This involves identifying the data sources, gathering the necessary data, and organizing it in a format that can be easily analyzed and visualized.
Identify the necessary data sources for your dashboard. These may include:
Internal databases and systems (e.g., CRM, ERP, marketing automation)
External data sources (e.g., Google Analytics, social media platforms, industry benchmarks)
Spreadsheets and CSV files
In this tutorial, we’ll use a zipped folder that contains data related to yearly sales data. customer, item type, location, order priority, and sales channel. You can download the zipped folder.
Here’s a step-by-step process for importing data cleaning and organizing the “E-commerce Revenue Data” in Excel:
1. Open a blank Excel Workbook → Go to Data → Get Data → From File → From Excel Workbook → Select 2014 Sales Data → Click Import → Select Sheet1 → Click Transform Data → Rename the Query to 2014 Sales Data → Select Close & Load To → Select Only Create Connection & Select Add to Data Model → Click OK.
2. Do the same as shown in step 1 for all the yearly sales data from 2015 – 2021 and rename the below queries.
3. Do the same as shown in step 1 for all the customer, item type, location, order priority, and sales channel files. Rename all the queries.
4. Go to Data → Get Data → Launch Power Query Editor → Select all the Yearly Sales Data → Click Append Queries as New → Select all the Queries → Click OK → Rename the Query to FactSalesTransaction.
5. Go to FactSalesTransaction query → Go to Choose Columns → Choose Columns → Deselect all the Columns → Click OK.
6. Select all the data → Remove Duplicates.
PivotTables are powerful data analysis tools in Excel that allow you to summarize reports and analyze large amounts of data. They enable you to organize, manipulate, and visualize your data in a pivot table format, providing insights and trends that are not immediately apparent in the raw data.
A PivotTable is a powerful Excel tool that allows you to summarize, analyze, and explore large datasets quickly and easily. It enables you to dynamically restructure and aggregate your data, revealing insights and trends that might be difficult to spot in raw data.
PivotTables work by allowing you to drag and drop fields from your dataset into rows, columns, and values. Excel then automatically aggregates the data based on your field selections, creating a summary table that you can easily manipulate and explore.
First, you need to enable Power Pivot. Go to File → Options → Add-ins → Select COM Add-ins → Select Microsoft Power Pivot for Excel → Power Pivot tab will appear on the ribbon.
Go to Power Pivot → Go to Manage → Organize all the tables → Create a relationship between all the fields as shown below by dragging the field from Dim Table to FactSalesTransaction table.
Go to Data View → Go to FactSalesTransaction → Click any cell on the Calculation Area → Go to the Formula Bar & type in the formula → Format it to currency & remove decimal.
This is the first formula to calculate Revenue.
Add a second formula called Number of Orders.
Click any cell on the Calculation Area → Go to the Formula Bar & type in the formula → Format it to whole number.
Add a third formula called Average Revenue Per Order.
Click any cell on the Calculation Area →. Go to the Formula Bar & type in the formula → Format it to currency & remove decimal.
To create a PivotTable in Excel, follow these steps:
Go to PivotTable → Go to PivotTable → Click on Existing Worksheet → Select Location → Press OK → Drag all the measures in Values area.
For our e-commerce sales dashboard, let’s create PivotTables summarizing:
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell E1 → Press OK.
Revenue by sales channel.
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell I1 → Press OK.
Revenue by order priority
Go to PowerPivot → Click on Manage → Go to PivotTable → Click on Existing Sheet → Click on cell A4 → Press OK.
Revenue by item type
Visualizing data with charts and graphs is a key component of creating an interactive Excel dashboard. Charts and graphs provide a visual representation of data, making it easier to understand and to analyze data for trends, patterns, and relationships.
Selecting the appropriate chart type is crucial for effectively communicating your data insights. Different chart types are suited for different purposes and data structures. Some common chart types and their uses include:
Column and Bar Charts: Compare values across categories or show trends over time.
Line Charts: Display continuous data or show changes over time.
Pie Charts: Show the composition or proportion of a whole.
Scatter Plots: Visualize relationships between two variables.
Area Charts: Demonstrate the magnitude of change over time or across categories.
When choosing a chart type, consider the following:
The type of data you’re visualizing (categorical, continuous, discrete)
The relationship between variables (comparison, distribution, composition, trend)
The message you want to convey (change over time, proportion, correlation)
For our e-commerce sales dashboard, we’ll create:
A pie chart showing revenue by sales channel. This will help us identify which sales channel is performing better.
1. Change the values to the percentage of the Grand total.
2. Go to Insert → PivotChart → Pie → Click OK.
A column chart showing revenue by order priority. This will help us identify which order priority is performing better.
Go to Insert → PivotChart → Column → Cluster Column → Click OK.
A bar chart showing revenue by item type. This will help us identify which item type is performing better.
Go to Insert → PivotChart → Bar → Cluster Bar → Click OK.
Once you’ve selected the appropriate bar chart and types, customize your charts to enhance readability, visual appeal, and impact. Here are some tips for customizing your charts:
1. Add descriptive titles and axis labels
2. Adjust chart colors and styles
3. Format data labels and markers
4. Customize chart layout and background
5. Add trendlines and annotations
To customize your pie charts in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Sales Channel Distribution”.
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill. Change the online to HEX code f0a500 and offline to HEX code to 191919.
To customize your column chart in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Revenue by Order Priority,” the Y-axis as “Revenue,” and the X-axis as “Order Priority.”
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill and changing the color to HEX code to 191919.
To customize your bar chart in Excel:
1. Click on the plus sign of the chart and enable all the elements.
2. Enter the chart title as “Revenue by Item Type,” the Y-axis as “Revenue,” and the X-axis as “Item Type.”
3. Go to the Format tab to customize the chart colors by clicking on the Shape Fill and changing the color to HEX code to F0A500.
We need to build 3 KPIs based on the PivotTable we created earlier.
1. Create 3 Rectangle Rounded Corners by going to Insert → Shapes → Select Rectangle Rounded Corners → Draw the rectangle. Copy and paste that shape two more times.
2. Change the color for each shape to below HEX code.
3. Add the cell reference within each shape by clicking on the formula bar. First shape should be referencing cell A2, second shape should be referencing cell A3, and last shape should be referencing cell A4. Make all the text center aligned and font size to 12.
4. Insert a text box and enter below text for each.
5. Group the each text box and shape.
In this section, we will explore how to enhance interactivity in your Excel dashboard using features such as slicers and timelines. These features allow users to interact with the dashboard, filter data, and explore different aspects of the data in real-time.
Slicers and timelines are powerful Excel features that allow users to interact with and filter PivotTable data quickly and intuitively. They provide a visual way to select and deselect data points, instantly updating connected charts and tables.
Slicers are used to filter categorical data, such as product categories or customer segments. Timelines, on the other hand, are used to filter continuous date ranges, allowing users to focus on specific time periods.
Incorporating slicers and timelines in your Excel dashboard offers several benefits:
Improved user experience: Slicers and timelines provide an intuitive, visual way for users to interact with data, making it easier to explore and analyze information.
Increased data flexibility: Users can quickly filter data to focus on specific subsets, uncovering insights that might be hidden in aggregate data.
Dynamic updates: Connected charts and tables automatically update when slicer or timeline selections change, providing real-time insights.
Enhanced visual appeal: Well-designed slicers and timelines add a professional, polished look to your dashboard.
To add timeline to your Excel dashboard, follow these steps:
1. Select any cell within a PivotTable.
2. Go to the PivotTable Analyze tab.
3. Click on “Insert Timeline ” and choose Order date.
4. Format your timeline to grey color.
To add slicer to your Excel dashboard, follow these steps:
1. Select any cell within a PivotTable.
2. Go to the PivotTable Analyze tab.
3. Click on “Insert Slicer” and choose Country.
4. Format your slicers to grey color and make the column as 2.
One of the most powerful features of slicers is the ability to connect them to multiple PivotTables and charts, allowing users to filter data across the entire dashboard with a single click. To connect a slicer to multiple elements:
1. Right-click on the slicer and choose “Report Connections.”
2. In the Report Connections dialog box, select all the PivotTables.
3. Click “OK” to apply the connections.
Now, when a user interacts with the slicer, all connected PivotTables and charts will update automatically, providing a seamless, interactive experience.
Now that you have created your pivot tables, pivot charts, and added interactivity with slicers and timelines, it’s time to assemble all the components into a cohesive Excel dashboard.
An effective dashboard layout should be intuitive, visually appealing, and optimized for the user’s needs. When assembling your dashboard components, consider the following design principles:
1. Prioritize information hierarchy
2. Maintain visual consistency
3. Maximize whitespace
For our e-commerce revenue dashboard, we’ll arrange the components as follows:
Copy and paste all the charts, slicers, and KPIs from the Pivot tab to the Dashboard tab. Organize all the visuals as shown below and also insert a text box with the text Revenue Dashboard.
Change the reference for each KPI in the formula bar.
1. Revenue: =Pivot!A2
2. Total Order: =Pivot!B2.
3. Avg $ / Order: =Pivot!C2
Disable Gridlines, Formula Bar, and Headings in the View tab.
Dashboard is finally ready.
To create a dashboard in Excel, follow these steps:
Yes, Excel has a range of features that allow you to create interactive dashboards, including:
While Excel doesn’t have a dedicated “dashboard” feature, these tools collectively enable you to create powerful, interactive dashboards.
An interactive dashboard in Excel is a visual representation of key data points that allows users to engage with the information dynamically. Interactive dashboards typically include features like:
Interactive dashboards provide a user-friendly interface for analyzing complex data sets, uncovering insights, and making data-driven decisions.
To create a dynamic dashboard in Excel, follow these steps:
By following these steps and leveraging Excel’s powerful features, you can create a dynamic, interactive dashboard that enables users to explore and analyze data efficiently.
Yes, you can create a dashboard in Excel. Excel provides a wide range of tools and features that make it possible to design and build professional-looking dashboards. With Excel, you can create interactive and dynamic dashboards that allow users to explore and analyze data in a user-friendly way.
In this comprehensive guide, we’ve walked through the process of creating an interactive Excel dashboard from start to finish. By following the step-by-step instructions and best practices outlined, you can create a professional, user-friendly dashboard that effectively communicates key insights and drives data-driven decision making.
You’ve learned how to:
Remember, the key to a successful dashboard is understanding your audience, selecting the right KPIs, and presenting information in a clear, concise, and visually appealing way. By leveraging Excel’s powerful features, such as PivotTables, charts, slicers, and timelines, you can create a dynamic and interactive dashboard that allows users to explore data and uncover valuable insights.
Office Tech Skill is an educational platform that focuses on teaching courses in the Microsoft Office suite, equipping professionals with the skills needed to excel in the evolving tech landscape.
Legal
Company
Courses
Free Resources
