Let’s be honest, we all agree Excel is an old-school talk!
Yet, with over 1.1 Billion users globally, it still holds the ground when it comes to data management and visualizations. 🤯
But are you aware that you can create interactive dashboards in Excel? Yes, you heard us right!
In this article, we’ll help you understand how to create an interactive dashboard in Excel. And at the end, uncover a robust tool that will help you escape the hectic processes of Excel.
Can’t wait for that long? Here’s a quick summary of the article:
An interactive dashboard in Microsoft Excel is a one-pager (mostly, but not always) report that allows business teams to track and measure crucial business KPIs and metrics under one roof.
It consists of a combination of charts, figures, and tables that visualize complex and hard-to-understand data in an easy-to-read format.
Besides, creating an interactive dashboard in Excel is fun. You get to learn most of the Excel functionalities and meanwhile have an opportunity to create something stunning, which would make your stakeholders go wow for the job! 😮
So, are you ready?
Let’s rightaway jump to the section where we’ll see how to make an interactive dashboard in Excel.
Before starting the process, let us ask: Why are you creating this dashboard and for whom?
Different stakeholders or departments within the organization would want to analyze different facts and figures.
For instance, a Chief Financial Officer (CFO) would prefer looking at key financial metrics, while an investor would want a summarized dashboard of all the departments.
Keeping this in mind, try to attach a purpose to your dashboard and then collect data around it for accurate and effective decision-making.
Here’s a raw data set we will be using to create an interactive dashboard in Excel.
Let’s first convert this dataset into a table (you can skip this if you already have a table).
Select your data, go to Insert > Table. In the pop-up that opens, tick the box - ‘My table has headers,’ and click OK.
Your table will look something like this:
Next, convert the table into Pivot table; this will help you create interactive charts to put in your dashboard in Excel. Select the table, go to Insert Tab > Pivot Table. Click on OK and you will have a new sheet with Pivot Table.
For our interactive Excel dashboard we want 3 charts, meaning we need 3 pivot tables. To do so, we will just duplicate the pivot table sheet in the Excel workbook.
And just like that you have 3 pivot tables ready. Let’s move on to the next step to create charts for our interactive dashboard.
So our first chart would represent every product’s monthly-wise sale.
For that we need 3 data entries - Sales, Product, and Month.
In the Pivot table sheet, drag and drop the Month data in the rows area, product in the columns area, and Sales in the values area.
Here’s a GIF to explain it to you clearly:
If you want, arrange the values in decreasing or increasing order.
Next, click anywhere on the Pivot Table as soon as you do that, you will see a tab - PivotTable Analyze appears in the top menu.
Next, click on PivotChart inside the PivotTable Analyze menu and choose the chart you want to represent your data. In our case, we’re choosing Stacked columns from the chart drop-down.
The moment you press OK, a pivot chart will appear in front of you.
Volia! You have made your first chart.
Now, it’s time to style the chart via formatting options in Excel.
Let’s start by giving the chart a title:
Click on the + sign that you see besides the chart you just created, tick the ‘Chart Title’ box, and give your chart a suitable title.
Excel has some basic formatting options that you can use to make your chart look better:
Try them out and see how you want your pivot chart.
After you’re done formatting, you can then move your chart to the Interactive dashboard sheet.
Repeat the procedure to create such pivot table charts for your interactive dashboard, once you’re done, we’d start formatting and customizing our interactive dashboard.
When it comes to creating charts, Excel gives you a boatload of options:
And many more…
Here’s how our dashboard looks after creating 3 charts.
Select any chart you want on your interactive dashboard and click on PivotChart Analyze. Go ahead and click on Insert Timeline.
Note: For inserting a timeline to any pivot chart, you MUST have the Date column in your data.
Tick the Date checkbox and press OK.
This will give you a timeline of the chart.
In a similar fashion you can add slicer for all the charts you want to on the interactive dashboard.
Here’s the snapshot of our final interactive dashboard in Excel:
Doesn’t it look pretty?
Now that you’ve seen how to create an interactive dashboard in Excel, it’s time to explore some templates that you can use directly.
A financial dashboard lets you monitor the financial performance of an organization. It consists of key financial KPIs such as net profit, working capital, accounts receivable, and more.
In simple terms, these dashboards are meant for executives or business owners who aren’t aware of financial jargon but would like to have an overview of all the financial operations in theri business.
CFOs and finance teams use these dashboards to demonstrate the business's financial health to stakeholders and other relevant parties.
Download Link: Financial Dashboard
A marketing performance dashboard is generally a one-pager report in Excel that visualizes key marketing metrics to gauge progress toward marketing objectives and goals.
Modern-day marketing performance dashboards cover two aspects: Offline and online marketing metrics.
They allow you to collect relevant insights from your digital and physical marketing efforts. Thereby, helping you to target marketing campaigns to relevant audience groups.
Download Link: Marketing Performance Dashboard
This particular marketing performance dashboard has the following metrics:
A social media dashboard allows you to monitor all the activities going around your social media accounts under one dashboard. The dashboard typically includes metrics like engagement, traffic, leads, subscriber or follower count, and audience insights.
Download Link: Social Media Dashboard
The dashboard provides an overview of the key sales metrics C-level executives and managers are keen about. It includes annual sales, product-wise sales, region-wise sales, and more.
Besides, the dashboard provides granular insights into the performance of your sales team. Like how your sales representatives are performing, whether they are meeting their targets, and more.
Download Link: Sales Management Dashboard
This particular sales management dashboard focuses on the following:
A comprehensive dashboard template that streamlines all your project-related operations. It tracks your timeline, budget, task status, and more.
Having this data readily available under one roof can allow project managers to manage productivity as a whole efficiently.
Download Link: Project Management Dashboard
Which Key Performance Indicators can you track using this template?
Excel struggles with large amounts of data and may become slow and unresponsive. In some cases, you can encounter downtime when multiple users try to access a spreadsheet simultaneously.
Both ways, Excel is not the first choice for organizations willing to scale their operations from time to time.
Besides, Excel is a stand-alone application. It does not integrate with other platforms or data sources easily. This makes it challenging for companies with data spread across multiple social and analytical platforms to centralize them under an Excel sheet.
With Excel, you are limited to using a set of pre-built data visualization elements.
Yes, the platform does offer customization options, but that too is shrunk to basic editing of charts. 📊
Suppose we compare Excel's gallery of data visualization elements to Datapad (our tool) or any modern-day dashboard software. The other is more advanced in creating visually appealing and informative dashboards.
Excel doesn't have built-in collaboration features like Datapad. Meaning you cannot communicate on the go. You have to use a third-party app for real-time communication.
P.S. Datapad helps you eliminate this bottleneck. How? We'll talk about this feature in a later section.
For now, Excel has no control over what version of the application users work upon – 2013, 2016, or 2019. This makes it difficult for users using a different version to experience the exact same dashboard which others are using.
And when a team uses different versions to access the same dashboard, what they view entirely depends on what their version supports.
Our POV: You can’t do anything except circulating a disclaimer to your team asking them to use the same version.
Excel does not have built-in security options, making it easy for unauthorized users to access and modify data. This can often lead to data manipulation and inaccurate results.
Being from a 90's background, Excel comes with next-to-no automation. In fact, its users spend most of their time preparing data and double-checking it manually.
Simply put, Excel does not have the ability to automate the process of data collection and updating. This leads to a lot of manual work, thereby increasing the chances of human errors or unintentional data manipulation.
Datapad is the industry's leading mobile-only platform that allows you to create dynamic interactive dashboards and makes data analysis easy for you and your team.
We've ensured to fit in all the crucial features of creating a dashboard into a powerful mobile app (don't worry, it doesn't consume much space).
Moreover, we have fully automated the process of creating interactive dashboards, from data extraction to cleansing and presenting; you can do it all in a matter of minutes, provided you have a stable internet connection.
Moving forward, we'll show how our tool can help you create a stunning dashboard in easy-to-do steps. And then, as we said earlier, we'll discover some cool features of Datapad, like built-in chat features.
Get your hands on our mobile app. You can easily download it from the app store or play store.
Once into the app, signup with your preferred email address.
Note: Please provide an active email to receive verification mail and other updates from our team.
Soon after, you'll be prompted to create your personal workspace.
A workplace is your personal office where you can store all your data and dashboards, create new dashboards, and have complete control over access.
So, give a name to your space and press Create Workspace.
Next, click on the (+) sign in the top right corner to create a new dashboard. In this dashboard, you'll have all your charts under one roof.
Give your dashboard – name, description, and an emoji (optional) and hit the create button on the top right corner.
To import data to the dashboard, tap on Options at the top right corner.
Then click on Add Metric. You can manually enter data or import it automatically using our one-click integrations to popular marketing platforms.
To automatically import data:
Here's the process in action.
From your workplace, click on the Datapad icon > Manage Users.
Next, click on (+) in the top right corner.
Enter email addresses to send invite emails to as many people as you want onboard.
And finally, you're all set to change the look and feel of your dashboard.
Datapad offers plenty of customization options. You have a choice to set your chart type, change colors, font styles and size, and more.
Besides, our intuitive drag-and-drop feature allows you to perform granular customizations hassle-free.
And when you're happy with the look, you know you have the dashboard ready to be shared.
For the most part, we cherish these key features of our tool.
With Datapad, you eliminate the hustle of scrolling through lengthy sheets and big data graphs.
Our mobile-first interface allows you to work and navigate seamlessly across the app.
Besides, we tested Datapad for compatibility and resolution on all small-screen devices. And guess the results?
It's pixel-perfect for all. ✅
Do you remember how importing data to Excel is a nightmare?
Well! We got a cure.
Datapad allows you to import data both ways: automatically and manually.
Earlier, we explained how to import data via our one-click integrations. Here, you'll see how to do the process manually.
And you're done. Need help with the process?
No worries; we always have a demo for you. 😁
Datapad offers an in-built chat feature that makes team collaborations a piece of cake.
In Datapad, you can simply comment under a chart or metric to strike up a conversation with your teammates in real-time. For example, if you see something off with a metric, here's what you can do:
👇🏻 Comment your views right below the metric
👇🏻 Your team gets real-time alerts or push notifications of your comment
👇🏻 They reply under your comment (and then the conversion can go on)
Simply put, it works similarly to Twitter threads.
Here's the feature in action
Unlike Excel, you can enable role-based management in Datapad. It will allow you to control who can view/access/edit the dashboard.
Moreover, you can see user activities in history or in real time to be informed about who's active on the dashboard.
Astonished to go-through so many features for a free dashboarding tool? Why don’t you try it yourself to create interactive and dynamic dashboards, and test our tool for real!
Scan the QR code below and get started today.