Office applications are designed to increase our productivity. You can access all your documents and create reports, tables, and graphs. The problem with this is that we don’t always use applications for their intended purpose. As a result, we waste time opening long documents or trying to find the best way to display information. Well, Excel has a built-in function called Pivot Table that calculates data for you and presents it in the form of a table or chart that you can easily filter and sort quickly based on different rules. This eliminates the need to go from one application to another just to get basic information out of them. Pivot Tables in Excel also allow us to present data in a dashboard-like view where all information is displayed right on your screen! Twisting and turning various filters allows you to get any kind of data instantly. It’s far easier than creating dashboards using PowerPoint, pie charts using Word, etc.
Having a hard time figuring out how to create an Excel Dashboard? I’ve seen Dashboards that are pie charts, line charts, and even multiple graphs on one page. But how do you take all of these different graphs, and put them together on one page? Do you just cut and paste them together, but they overlap? Is the background supposed to be transparent?
How to Bring Data into Excel
Before creating dashboards in Excel, you need to import the data into Excel. You can copy and paste the data, or if you use CommCare, you can create an Excel Connection to your export. But, the best way is to use ODBC (or Live Data Connector). ODBC can connect your apps to Excel, passing real-time data from your app to Excel. As data is updated in your app, your Excel dashboard will also be updated to reflect the latest information. This is a perfect option if you track and store data in another place, and prefer creating a dashboard in Excel. Data can be imported two different ways: in a flat file or a pivot table.
Figure out which charts best represent your data
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.
Organize your data
Before you begin your dashboard creation in Excel, it’s critical to have your data well organized. We always encourage analysts to use Excel modeling best practices, whether building financial models or preparing to design a dashboard.
The most important part is to have all the information you plan on graphing in one area. It can be very hard to link the graphs if data is spread out throughout the spreadsheet. In the image below you can see an example of how to neatly organize your data from the financial model to be ready for the Excel dashboard.
Set Up Your Excel Dashboard File
Once you have added your data, you need to structure your workbook. Open a new Excel Workbook and create two to three sheets (two to three tabs). You could have one sheet for your dashboard and one sheet for the raw data (so you can hide the raw data). This will keep your Excel workbook organized. In this example, we’ll have two tabs.
Set up your page
Once your data is organized, it’s time to set up the page. The orientation can be landscape or portrait, and the size will depend on the output you’re trying to create. The most common is to make it an 8.5” by 11” page so that it can easily print to paper or PDF in a standard size. Typically, dashboards are only one page, but you can easily create a multi-page dashboard as well. To learn how to do this, see our free Excel tutorial course.
Important things to consider when setting up your page include: will the dashboard be emailed or printed, will it go to executives or lay people, is it for internal use or external use, and should the information be mostly visual or contain numbers and words as well.
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 your “Chart Data” tab of your workbook. That’s the tab that you’ll pull data from 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.
It’s nearly impossible to design a dashboard right on the first try. You need to brainstorm, experiment, and tinker to finally come up with a great design. This step usually takes several iterations, and you may even find yourself continually changing and updating your Excel dashboard based on the feedback you receive over time.
Below is an example of some brainstorming that you can do before you actually begin the dashboard creation in Excel. Notice that it’s mostly just rough notes and ideas about how the page should be structured, and what type of data needs to be included. In the planning stage, we also recommend looking at other examples of dashboards to see what you like.
From Scratch? That’s right. Learning how to build an Excel dashboard doesn’t have to be difficult. Follow these simple steps and you’ll be creating a dashboard in no time!