A dashboard is a visual representation of key metrics that allow you to view and analyze your data in one place quickly. Dashboards not only provide consolidated data views but also a self-service business intelligence opportunity, where users are able to filter the data to display just what’s important to them. In the past, Excel reporting often required you to generate multiple reports for different people or departments depending on their needs.

Overview

In this topic, we’ll discuss how to use multiple PivotTables, Pivot Charts, and PivotTable tools to create a dynamic dashboard. Then we'll give users the ability to quickly filter the data the way they want with Slicers and a Timeline, which allow your PivotTables and charts to automatically expand and contract to display only the information that users want to see. In addition, you can quickly refresh your dashboard when you add or update data. This makes it very handy because you only need to create the dashboard report once.

For this example, we're going to create four Pivot Tables and charts from a single data source.

Once your dashboard is created, we’ll show you how to share it with people by creating a Microsoft Group. We also have an interactive Excel workbook that you can download and follow these steps on your own.

What is an Excel Dashboard?

A dashboard is a compact visual representation of data. Dashboards are designed to be eye-catching, easy to understand, and concise so that users can quickly extract insights.

Dashboards can contain raw numbers, tables, and charts. However, dashboards become powerful tools when they incorporate visual elements to enrich the information they convey, such as through colors, charts, and conditional formatting.

Why are Excel Dashboards Useful?

Dashboards can be used in nearly every industry and business use case.

They are a tool for improving the business's decision-making process and can perform complex data analyses. You can learn more about how to do data analysis in Excel or, more generally, in spreadsheets with one of our courses.

Here are a few reasons why dashboards are so useful in organizations. They allow you to:

• Track KPIs.
• Perform complex calculations and analyses.
• Promote data-driven decision-making.
• Diagnose, investigate, and solve business problems.
• Explore scenarios for business forecasting and planning.

Before Creating an Excel Dashboard

To build high-value dashboards that people in your organization actually use, first start with a strategy. You need to know beforehand why you're building this dashboard, who you're building it for, how it will be built and how you will distribute it to your users.

The dashboard strategy process can be summarized into these five main components:

Purpose

Be clear on why you need this dashboard - what are the goals? Is this dashboard answering a specific business question or solving a particular problem?

Sometimes, the departments in an organization may already have one or more goals in mind for the dashboard. They may have a task to complete and know that a dashboard would help them do their job better.

However, other times business users don't know that a data-driven approach is even possible or useful to them. In these cases, you or your manager may need to look out for possible areas where a dashboard can add value to the business and propose it as a solution. Here it is even more important to clearly define the purpose and goals of the dashboard before you start building it.

Audience

In this step, you identify who this dashboard is intended for. Find out who your targeted audience is and what their goals are when they look at the dashboard. Is it for detail-oriented finance managers who want to see a lot of detail or for C-level executives who just want a high-level overview of the business KPIs?

Knowing your audience is an important prerequisite for answering the questions in the next few sections of your dashboard strategy.

Data

What data will you be using? Is the data already available in a database or a flat file that you can simply import into Excel for your dashboard? Or do you need first to extract and prepare the data?

Also, understand how much data is needed. Do you need large amounts of historical data, or are you dealing with more recent events?

Does the data need to be cleaned and transformed before you can build the dashboard? Do you need to check for errors or remove duplicates? This is usually the case for newly extracted data, and data obtained from a database would typically already be transformed and ready to use.

How often do the data and the dashboard need to be updated? Flat files tend to lead to static dashboards, whereas database connections using ODBC can be updated when needed and lead to dynamic dashboards.

Layout

You can identify the best layout for your dashboard at this stage. The previous stages of the dashboard strategy will inform this step. You already know why you're creating the dashboard, who it's for, and the details about the data that will be used.

• Purpose - based on the goals you identified in this step, you know what you should focus on as you design the dashboard - these are the metrics, KPIs, and core pieces of information that should be shown on the dashboard.
• Audience - since you know who the dashboard is for, you know the best layout for them. For example, you can incorporate a lot of tables and extra sheets for the detail-oriented folks or quick, eye-catching visuals for the C-level executives.
• Data - since you know more about the data needed for the dashboard, you can determine the best method for importing the data and whether you need to make special considerations for displaying large volumes of data on the dashboard.

Also, determine if the dashboard needs interactive elements so users can explore the data and diagnose and investigate possible business problems themselves. Or, if the dashboard will be used to create PowerPoint presentations, it can be more static.

Distribution

How will you be sharing the Excel dashboard with your intended audience? There are a few options for sharing Excel files, such as attaching it to an email, saving it to a shared OneDrive folder, or exporting it to a PDF.

Keep in mind that these methods (except the export to PDF) give users the ability to edit the dashboard and the data. If you don't want anyone to be able to make changes, you can protect the workbook and lock the cells using a password. Or, you force the workbook to open in read-only mode.

Excel Dashboard Design Best Practices

Follow these simple best practices as you design and create your dashboard:

Keep it simple - choose simple charts and simple design elements for your dashboard. This will help to maintain focus on what is important.

Don't overcrowd the dashboard - don't try to fill up every available space with visuals, data, or color. The more overcrowded the dashboard becomes, the less information it conveys because it becomes a confusing mess. Give each element on your dashboard room to breathe.

The five-second rule - this is a general design rule. In the context of Excel dashboards, you should be able to convey the most high-value, high-impact information within five seconds. This requires regular communication with business users to determine what types of information are most relevant and important to them and to ensure it aligns with the purpose described above.

Use colors wisely - colors should be used sparingly and should also ideally convey some meaning.

• For example, using a specific set of colors to denote different categories across all visuals in the dashboard
• Or, 'robot' colors can be used to denote good and bad values.
• Always use colorblind-friendly color palettes - red and green are the two most problematic colors, especially when used together
• Use freeze panes for large tables to enhance readability
• Stay consistent (colors, layout, formatting, etc.) - if you use a particular color in one dashboard to denote a 'good' value then make sure you use this color for the same purpose in other dashboards. Doing so will make future dashboards more intuitive and easier to understand.

How to create an Excel Dashboard

1. Figure out which charts best represent your data

Do you know how we just said you’ll be faced with tons of options to represent your data? We weren’t kidding—there are bar charts, column charts, pie charts, line charts, scatter plots, waterfall charts, and so many more.

However, not all of them will be the best fit for the data that you want to represent. For example, a line chart is excellent for analyzing trends while a pie chart is effective for looking at a snapshot in time.

Not sure which chart is the best choice for you? Your best bet is to play around. Create a few different charts, look at the results, and see which ones make the most sense for displaying your data in an easily digestible manner.

With so many options, we couldn’t possibly dive into every chart type in detail here. So, instead, we’re going to dip our toes into the dashboard waters by focusing on the step-by-step process for creating one specific type of chart: a column chart.

We’ll stick with the same budget data set that we used above. Our goal is to create column charts that will display how much we spend on each individual budget line item per month in the first quarter. This means that we’ll end up with separate charts for electricity, gas, phone, etc.

2. Filter your data

When creating a chart, you’re not going to need to use all of your data at once—you’ll need to filter through it to focus on only the pieces you need at that given time.

The easiest way to do this is by using the “Filter” option within Excel. For example, we want to filter by item type and only see numbers related to our electricity expenses.

To do that, we’ll highlight the entire data set, click the “Data” ribbon in the toolbar, and then click the “Filter” button. When doing so, you’ll see that little arrows appear next to your column headers. If you click one of those arrows, you’ll be presented with a drop-down menu that you can use to filter your data.

When you’ve filtered down to only the data that you want, highlight all of the cells of data, hit “copy,” and then paste only those rows into the “Chart Data” tab of your workbook. You’ll pull data from that tab when building your charts.

Why can’t you just select data from your regular “Data” tab? Well, put simply, because even though you’ve filtered the data, those other irrelevant rows are still included there (albeit hidden)—meaning they’ll throw things off in your chart.

3. Build your chart

Now that you have only the data that you need, you’re ready to begin building your chart.

Click on the “Dashboard” tab of your worksheet, click the “Insert” button in the toolbar, and then select the type of chart you want from the menu. In this case, we’re going to use a clustered column chart.

When you insert the chart, you’ll see a blank box. Don’t worry—you haven’t screwed anything up. We’ll cover how to get your data to appear there in the next step.

TIP: Still aren’t sure which chart option is the best fit for your data? Highlight all of your rows of data in your “Chart Data” tab and then click “Recommended Charts” within the “Insert” ribbon. Excel will suggest some charts for you to use.

4. Select your data

Now that you have that blank box inserted in your “Dashboard” tab of your workbook, it’s time to pull some data in.

To do so, right-click on that box and then choose “Select Data.” After that, navigate over to your “Chart Data” tab (where your filtered data is live) and highlight all of the data that you want to display—minus the column headers. In this case, we’re selecting the “Item” and “Cost” data, since the purpose of my chart is to show how much we spend on electricity each month.

The data that you just selected is for the vertical axis. But, you still need to select your data for the horizontal axis of your chart.

To do that, click the button within the “Horizontal Axis” field of the “Select Data” popup and then highlight the information you need for the horizontal axis—in this case, the months.

After doing so, hit your “Enter” key, click “OK,” and then head over to your “Dashboard” tab to ensure that your chart is populated with that data.

5. Double-check your data

At this point, it’s wise to take a quick look at your chart and make sure that nothing looks off. Mistakes can happen. So, it’s worth it to take the time to ensure that your chart is pulling in your data correctly.

Does something look off?

Do some troubleshooting to figure out where you went wrong.

6. Polish your chart

Here’s the fun part—when you get to put the finishing touches on your chart.

From changing the colors to match your brand to adding labels, titles, units, or any other information that is required, you can polish up your chart by double-clicking on the chart area and then using the options in the toolbar (there are buttons up there for everything from “Add Chart Element” to “Change Colors) to make your changes.

7. Repeat the process for other data

A true Excel dashboard includes several different charts and gauges to display data. So, to complete our dashboard in this example scenario, we’ll go back and repeat all of those steps for our other budget line items—such as phone, vehicle, gas, etc.

One important thing to note: You don’t want to delete any data in your “Chart Data” tab—since the data there, is what’s feeding your charts.

So, when going through the process of making your other charts, make sure to paste the new data sets below each other, rather than deleting and replacing them within that tab:

After we’ve done that? We’ll end up with a tab that shows how much we spend on each item each month.

Before Building the Dashboard: Research, Questions, & Things to Consider

Before you start building your dashboard, first take some time to reflect on why you need a dashboard, what purpose it will serve, where the data will come from, and what capabilities you do and don’t need.

It might also be helpful to mock up your Excel dashboard on a piece of paper. Draw boxes for each data type to get a sense of the layout and add quick sketches of the graphs you want to include. This mockup will help get everyone on the same page and let you get approval from stakeholders before you start spending time and money on the actual dashboard.

Things to Consider: How to Design the Dashboard

Dashboard elements: What do you want to include on your dashboard? You can choose from static tables, pivot tables, dynamic charts, Excel gauge widgets, or non-charting objects, like auto-shape objects. Do you want to add a lot of small charts or a couple of big charts? Identifying the elements you want to add to your dashboard will help you group similar data together and give you an idea of the layout.

Dashboard background color: How much color do you want to incorporate in your dashboard? Do you want to add a dashboard background color to make the dashboard elements pop? Do you want to color-code similar charts?

Enhancing the dashboard UI: How important is the ease of use? Do you want to spend time enhancing the dashboard UI? You could add hierarchy to the layout for easy navigation, add drop-down lists, add labels to each graph with auto-shape objects, or use freeze panes to prevent users from scrolling.

Using PowerPoint and Excel

You could also make an interactive dashboard with PowerPoint. Adding interactivity to a pure Excel dashboard can be challenging, and usually requires Macros (or VBAs), the programming language used within Excel. However, if you add the charts and dashboard components you create in Excel to PowerPoint, you can easily add an interactive element.

For example, you could create five pie charts showing the exact same data over the course of five years. If you add one pie chart to each PowerPoint slide, you can move through these slides and the chart will look like it’s in motion.

Using PowerPoint and Excel together also makes it easy to share your dashboard. You can simply save the dashboard as a PowerPoint Show and email it to colleagues.

FAQ’s

Why are you creating this dashboard?

Are you trying to prove or disprove a hypothesis? Is this dashboard for a specific task, like showing the status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will help guide the design and data.

Do you need to track certain KPIs?

Your dashboard should only highlight data that adds value. Make sure you understand the most important KPIs and create a dashboard around those metrics. Anything outside those main KPIs isn’t necessary.

Who needs to see the dashboard?

Is this for a colleague, manager, stakeholder, external vendor, or C-level executive? How do these people prefer to digest information? How much time do they have to look at this dashboard? Think about who you’re making the dashboard for and keep their preferences in mind.

Where will the data come from?

Do you need to manually import data into your dashboard or will you use an integration or connector to automatically sync and refresh data? What other tools do you use to gather data?

How up-to-date does the dashboard need to be?

Can you update your dashboard weekly or monthly, or does it always need to show real-time, updated information? Depending on what you choose, this will change the way you build your dashboard.

What format does the dashboard need to be in?

Are you emailing a static dashboard or providing a link to a dynamic one? Does the dashboard need to be embedded in presentations or decks? Do you want to share read-only access or do you want to provide editing capabilities to certain people?

What is the dashboard format in Excel?

The Excel Dashboard is used to display overviews of large data tracks. Excel Dashboards use dashboard elements like tables, charts, and gauges to show the overviews. The dashboards ease the decision-making process by showing the vital parts of the data in the same window.

Is the Excel dashboard free?

This Excel dashboard template for visualizing value added is available to you for free. All the source data for the visualization example are traditionally located on the "Data" sheet.

Can you have a dashboard in Excel?

To make sense of these numbers, it's much more practical to use Excel Dashboards. Excel dashboards are one of the best ways to track KPIs and key metrics, compare data points, and provide shareholders with an overview of data-backed information that will help them make everyday decisions.

Where can I create a dashboard?

Whether you're a small business, a manager of a team, or looking to share important numbers across your whole organization, Oas36ty's online dashboard creator lets you create stunning dashboards from live data in a matter of minutes. Sign up for a free Oas36ty account and start making dashboards right away.