https://docs.google.com/spreadsheets/d/1Y41xUwBQgN5hC5DQd0m7GE60OsfJsghTBZA2YZfxRNw/edit#gid=0

Sales Dashboard

If you remember from last week, I posted a Dashboard. This week, I wanted to walk through how you can make it in Google Sheets.

First, let’s start by laying it out.

Then we can add a logo of the brand to the left-hand side.

Next, I’ll create a title so when people open it up, they know what they’re looking at:

So far, we have developed this Dashboard. However, this could slowly change shape and colour as we start to add information in.

For instance, I have changed the colour on the top and left-side banner. I feel like it is already become more visually coherent:

Pivot Table

Insert > Pivot table:

Create another pivot table by copying this newly created one and adjusting the dimensions. So, I’ll copy that first pivot table then remove the values I had added. Next, I’ll add the Beverage Band dimension to the rows and Total Sales under the values:

I want to split this by year, so I am going to place the ‘Invoice Date’ dimension into the columns section:

This error says that I’m attempting to make too many columns. So, I will place the Invoice Date into the Rows section first:

Then I’ll right-click on a date > click ‘Create pivot date group’ > and select ‘Year’:

Now I can move them to the Columns section:

Now I’ll duplicate that pivot table so that I have dashboard access for both years. Next, I’m going to drill down by quarters, so I will duplicate it again:

For the last section of the dashboard, I want to have Sales and Operating Profit Margin, so I will duplicate it one last time and place those metrics in:

The ‘Operating Margin’ will automatically be a SUM calculation, so change that to AVERAGE. Beverage Supplier and Year are necessary for this, so I will remove the first and then add another Invoice Date to the Rows section > right-click on a date > ‘Create (another) pivot date group’ > ‘Quarter’:

Now that we have this, we can move the Year dimension from the Columns section into the Rows section, but placed above (order) the Quarter dimension:

We don’t want to have the yearly totals in there as we’re going to make a chart and it’s easier to deal with when they’re not in there. So, let’s click ‘Show Totals’ (on the right-hand side, under Invoice Date) and make sure it is off:

Speaking of making things easier, it is easier to not make this kind of chart using Pivot Tables, so I’m going to create a cell reference to this pivot table directly next to it:

Charts

First, let’s work on the ‘Sales by Beverage Brand and ‘Sales by Retailer’ sections. Since we have the tables in the Analyse sheet, I’m going to link it on the dashboard:

I will replace ‘Grand Total’ with ‘Total’, then format that bottom line with yellow highlight, top and bottom border lines, and bold the text.

Now, the same process for the Retailer. But first, I forgot to change it from Beverage Brand to Retailer:

That’s better. Now, to the dashboard:

Looking good. It’s not exactly what I want (as Google Sheets doesn’t have a Data Bars (Sparkline with in-cell text) equivalent), I will proceed with the following Conditional Format:

Then change to ‘Colour scale’ and change the min to white and max to green:

I will do the same for the other table too:

Now, for those scorecards at the top:

And to clean them up:

Now, for those charts. I’m going to select the quarterly table that I referenced in the Analyse sheet:

And ‘Insert’ a chart:

I want to turn this into a combo chart and use the Operating Profit Margin as a secondary axis (right):

I’ve created the combo chart, but you can’t see the Operating Profit Margin as it’s dealing with percentages (=<1). So, let’s engage the secondary axis. Under the Chart Editor, click Customise:

Then the ‘Series’ section:

Choose ‘Line 2’ under ‘Apply to all’:

Scroll down and change axis from ‘Left’ to ‘Right’:

And there we go:

Now I can name each series on the chart by clicking on each colour at the top and navigating through the Chart Editor on the right-hand side of the screen:

Then I shift that Legend to the bottom of the chart:

Then move it to the Dashboard:

I’ll also increase the point size on the line and select the data labels:

Slicers

Finally, I will add some slicers to the Dashboard. Since Google Sheets and Slicers don’t work well together, we have to do some adjustments. But, it’s really straightforward. The issue is that Slicers can’t read across different sheets; the pivot tables need to be located on the same ones. So, this was my approach:

Copy the Pivot Tables from the ‘Analyse’ sheet to the ‘Dashboard’ sheet then reconfigure the scorecards, tables, and graph.

This is now located underneath the dashboard:

I then started to update the scorecard cell references:

Then the tables:

And finally the graph. All that was required of me was to update the ‘Data range’ from the ‘Analyse’ sheet to the matrix now located in Dashboard:

You can see the ‘Series’ at the bottom right-hand side of the above screenshot. This updated automatically.

Now, I can insert a Slicer, based on Region. First, you need to select a copied Pivot Table, then click Data > Add a Slicer:

It will prompt you to select a column. I am picking ‘Region’:

I will move it up to the top left-hand side of the Dashboard:

And test by selecting two regions:

Looking good. I’ll add another slicer, for years. Due to the current limitations of Google Sheets and Slicers, we can only use dates, but we have to insert some conditions for the results we want:

If I am looking for 2023 data, I would create this condition:

If I am looking for 2022 data, I would create this condition:

This is not a fantastic solution, but this provides room to learn how to code this requirement in.

Conclusion

There we have an explanation from last week’s post of how to create these kinds of Dashboards in Google Sheets. Have you made your own? I’d love to see them. Post a link in a comment below!