How about if that same hour led to crucial details about your sales trends and customers insights?
On top of all that, what if that same one-hour investment came at no additional cost to you?
Seems like a pretty great deal.
Welcome to the benefits of an Excel-based analytical dashboard.
Benefits of an Analytical Dashboard
You’ve most likely heard of the powers and benefits of data analytics. You’ve probably seen examples from things like POS systems, Tableau, cloud-based software, and many others.
Some of these benefits include the following:
Spending time looking over your dashboard saves hours of debating anecdotal or piecemeal results and helps guide you towards the best path forward
Campaigns based on hard data are more successful as you can target your intended customer more effectively
The high end services all have their merits, but in a small business where you might not have the budget for those services (which are also potentially far beyond the capabilities you actually need), plus a large learning curve, those services don’t always make the most sense.
By learning (on a much faster learning curve) to make a dashboard in Excel, you can get the benefits you want in a completely customized dashboard at no cost (since you already have Excel on your computer).
Why use Excel for this
Let’s start with some common business questions.
How did your sales do last month? How does that compare to the same month of the prior year? How about compared to the pace you’ve been running at this year?
Now how about separating those numbers by project, item, or client? How about by distribution channel? Sales rep? What about the specific days you had promotions?
The first set of questions is somewhat easy to have a gut feeling for, if nothing else. You might even have some quick analysis created for you in QuickBooks (or similar software).
However, the second set of questions represents another level deeper. You might feel a bit less confident about your gut feeling. They require you to dive into the data each time you want to look, and maybe even anecdotally piece it together from a few different sources. It’s kind of a hassle, but they’re important insights, so it’s worth the time.
Here’s the good news from Excel – you can answer all of those questions, from top line revenue down to whatever level of detail you need, and have them at your fingertips in one place!
With a simple export of data from whatever source you need, you can populate dozens of custom views that you’ve templated and turn it into your very own analytical dashboard!
The only time intensive piece is building the template and framework for all your custom views.
After you create that, you’re essentially just updating with data each week/month/etc. and looking it over for trends. Making changes to it? Duplicating views with variations? Actually, all pretty easy.
The Excel Dashboard
Here is a very quick sample dashboard that shows that topline sales numbers (black chart) followed by a few, slightly deeper, analytical pieces that help evaluate performance (gray charts).
When I say “very quick” … this dashboard took less than an hour to fully create. Real life dashboards of this level of depth take a similar amount of time. Getting significantly deeper into the data does not necessarily mean significantly more time to create though.
Only a few “next level” type questions are displayed (although these may only be half a level deeper), but they are meant to be representative, and the great feature about the “behind the scenes” of the dashboard is that the building blocks for all levels of analysis can be VERY easily duplicated to get at any question you need.
Take a quick look at the dashboard, follow it along the yellow markers, 1 to 5. Any trends jump out at you?
You can likely see the following in a quick pass:
-
Sales are up in 2019! (Callout #1) – by 22.1% if we want to be specific
-
There seemed to be a change in performance starting in February (Callout #2). Perhaps a new item was released? Or a new marketing campaign?
-
All Sales Reps are selling more this year (Callout #3). Shawn leading the pack with a 28.7% increase over 2018
-
In Store sales have grown by over 60%! (Callout #4). However, Online sales have shrunk a tiny bit.
-
Further diagnosing, it looks like Jennifer and Oliver have really improved their In Store sales, while Shawn has faltered there (Callout #5). Perhaps there was a training that stuck with Jennifer and Oliver? Maybe Shawn has just focused all of his attention on Online sales?
Those types of insights are crucial to running your business and can be seen quickly from the dashboard. On top of that, there are still tons of different ways to cut the data… by product, by product and distribution channel, by sales rep by month, etc. All of these examples are (almost) as easy as copying and pasting.
Creating the Backend of the Dashboard – a General Guide
We start with one of the building blocks of an Excel-based analytical dashboard. Simply, the Pivot Table.
Depending on your level of familiarity with Excel, Pivot Tables might seem like “that complicated next level” or “that tool we use every day”.
The truth is, everyone can easily be at the latter, and if you’re already there, you could probably be using them more even more effectively. Investing just a bit of time can have a very outsized benefit to your business.
Pivot Tables are fantastic, for all levels of users, because of a few main pieces of functionality:
Drag and drop methods, no complex formulas, no macros or coding
It deserves mentioning that the quality of your data is very important. If the data is wrong to start, then there is no point is doing any analysis.
First… a few tips on how to create a Pivot Table
Feel free to skip this section if you’re already familiar.
For a detailed “how to” build a Pivot Table, I recommend watching from ~2:30 to ~5:30 in this video. The rest of the video has some good explanations as well, but for a quick guide, the 3 minutes is really all you need.
One piece I would highly recommend changing though, is in the data selection piece.
Important – select the entire column in the data, making sure the headers are in row 1. This allows you to add data in the future and have it included in the pivot table without having to change the data range.
The boxed-in range is okay as “ Sample Data!$A$1:$E$13 ”, but would need to be changed if you add more data to the end of it. The better version’s range “ Sample Data!$A:$E” highlights the entire columns.
Onto Creating the Dashboard
Start with creating a Pivot Table, and from there it is really just about dragging and dropping fields, copying and pasting, and picking a layout!
-
Select the “Pivot Chart” option, and out pops a variety of options. Select your favorite chart option (bar chart, line graph, multiple types, etc.) and boom! You’ve got a working chart to help you analyze!
-
Any time you change what’s in the Pivot Table that created the Pivot Chart, the Pivot Chart will change accordingly.
Example, if you limited the data to just “Jennifer” (Sales Rep filter), you’ll see only her sales in both the Table and Chart.
Tip for the aesthetics of the Chart
To get rid of the “buttons” or ugly looking gray bubbles polluting your chart, you can right click on any of them and select “Hide All Field Buttons on Chart” and they’ll go away (as shown below).
If you don’t mind their appearance, each button acts as a filter for the Pivot Table. So instead of scrolling over to the Table, you can filter right from the Chart.
You can add a chart title, hide the legend, and a lot of other customization by clicking on the Chart, and then selecting the green “+” button that appears to the right of the Chart (shown below).
Once you’ve got your first chart, you can then start the duplication process!
Copying the Pivot Table to be replicated
The premise here is that each Table you create is referencing the same data. Each variation is just how you want to cut the data for that particular view.
Note – the formatting you select for one chart will not transfer to a new chart. The easiest method is to pick from Excel’s preset templates, which are normally visually appealing enough – both the black and the gray Charts above are in Excel’s preset templates. You can always customize the look of every chart individually to how you want though.
Best practice is probably to settle on a color scheme and layout after you’ve created all the charts you want, that way you can quickly go through each and select the same layout all at once.
After creating however many variations that get to your necessary level of depth, you’ve now just created yourself a dashboard! Congrats!
Best Way to Create Formulas Based on the Tables to get Additional Metrics in your Dashboard
You may notice that some pieces in the above dashboard (the tables with headers that have blue background and white text) are not Pivot Tables or Pivot Charts. These are created using the same information though.
Enter the GETPIVOTDATA formula. It is one of the most complicated looking formulas, but one of the most effective to use.
Note that it is simply complicated “looking”, but not actually that complicated to use.
Here’s an example. We’re going to walk through the Sales By Month table in the dashboard above (and referenced below).
Let’s say you wanted to get that 41.1% “Growth vs Prior Yr” for Feb from the information in the Pivot Table.
Naturally, you would do the following (referencing picture below) U10 / T10 – 1 = 41.1%
When you go to do that though, this scary looking formula comes out:
Let’s break it down and look at the first piece, before the “/”:
=GETPIVOTDATA(“Sale Amount”,$S$7,”Year”,2019,”Month”,”Feb”)
Translating, it is saying the following:
=GETPIVOTDATA(“Sale Amount”,$S$7,”Year”,2019,”Month”,”Feb”)
=GETPIVOTDATA(“Sale Amount”,$S$7,”Year”,2019,”Month”,”Feb”)
=GETPIVOTDATA(“Sale Amount”,$S$7,”Year”,2019,”Month”,”Feb”)
=GETPIVOTDATA(“Sale Amount”,$S$7,“Year”,2019,”Month”,”Feb”)
=GETPIVOTDATA(“Sale Amount”,$S$7,”Year”,2019,“Month”,”Feb”)
You’ll notice the second GETPIVOTDATA (after the “/”) is the same formula, just referencing Year = 2018 instead of 2019.
It is the same formula as the nice and easy U10 / T10 – 1 above, just bringing in the functionality of the Pivot Table.
So why on earth would you actually use the complicated version?
Reason #1: Let’s say you add in another filter and the bulk of the Pivot Table shifts down by one row. The U10 / T10 – 1 will remain but will now be looking at a different month.
Reason #2: What if you changed the Pivot Table to include each Sales Rep’s details in each month (like below)?
Now that “U10 / T10 – 1” formula would be referencing Jennifer’s sales in Jan… not even close to total Feb sales.
If you used the GETPIVOTDATA formula, you would still get the result you want (Total Feb Sales Amount, 2019 over 2018) because you’re telling it what criteria to look at, regardless of what cells the intended data ended up in. It would still do $26,070 / $18,470 – 1, or 41.1%.
Note – in a scenario like the above picture, make sure Subtotals are enabled in the “Design” tab that appears when you click in the Pivot Table
Reason #3: You can completely customize the look of your created table, whereas you have limited aesthetic flexibility in the Pivot Table itself. I choose a blue header with white text.
Reason #4: You retain the copy and paste functionality of the “U10 / T10 – 1” formula but increase the accuracy of the formula.
Notice in our summary chart with each month’s “Growth vs Prior Yr”, we have the month abbreviation in the left column, then the % growth in the right column.
To utilize the copy and paste functionality of formulas in Excel, we just have to reference the month in the formula.
It is the same formula as above in every way except for the P9 in place of “Feb”. This just tells the formula to take the value in cell P9 as the criteria needed to be found in the “Month” section.
You can then copy and paste this formula to each of the months, and the “Growth vs Prior Yr” will fill out for each month, regardless of how many other variables are in the Pivot Table, or where the numbers you want are located (cell-wise).
Summary
So now you’ve learned…
-
The benefits of a dashboard, specifically one in Excel
-
How to make a Pivot Table
-
How to make a Pivot Chart based off it
-
How to duplicate those efforts (for efficiency)
-
How to reference the information in it to get analytical metrics that aren’t directly called out in the Pivot Table
-
(Most importantly) How to build your own fully functional, completely customizable analytical dashboard!
Updating the dashboard is an exercise that takes mere minutes to download the data and add it into your data sheet.
Reasonable time investment to make the dashboard, small time investment to update it, big business benefits.
