In this article, I will create a report that displays the quarterly sales by territory. You can call it also a dynamic and interactive Excel dashboard that will be updated automatically to reflect the latest updates with your data.
This is the report that you will create after the end of this article.
I enjoyed a lot when I was writing this article. I hope you will also like the whole process.
This is the data we are going to use to create the report.
You can also create the same report if you follow along with me the whole article. To follow me, you can download the data from this link.
Table of Contents
- Introducing with the data and creating the table
- Creating a Pivot Table with the Table
- Sales by Category Report in a Pie Chart
- Quarterly Sales by Territory
- Adding Slicer to filter the Pivot Table Output
- Final Dashboard
- Download the Working File
Introducing with the data and creating the table
This is the sales data of a company. The data was big when I downloaded it from its source. But I have sliced, diced and shaped the data to make it small in size.
If the data is not in Table format, convert the range into a table. Excel table is one of the best features of Excel that makes many jobs easier like referring, filtering, sorting, and updating.
Select a cell in the range that you want to convert to table and press CTRL + T on your keyboard. Or go to Insert tab and from the Tables group of commands, click on the Table command.
Create Table dialog box will appear. The range will be selected automatically with My table has headers checkbox is selected. To create a table, just click the OK button.
A table is created. And from the Design tab (shows in the tab list only when the table is selected), you can choose your preferred design.
You can also change the name of your table from the Design tab or using the Name Box. We have named our table with Data.
Using Name Box to change the name of an object is: two names will exist for a single object: the old one and the new one. So, I will suggest not to use the Name Box. Or if you use Name Box to create names, delete the old one from the Name Manager dialog box (Formulas tab => Defined Names group of commands => Name Manager command).
It is easy to delete, just select the name that you want to delete and choose the Delete button the above.
Note: When have named the table with Data, we have actually named the data part of the table excluding the heading part. Just check out the image below. When Data is selected in the Name Box, it refers only the data part of the table.
Creating a Pivot Table with the Table
We are going to use the Excel’s most used tools for creating our report and it is Pivot Table.
You’re creating a report and Pivot Table is not present in the process – that is actually old ages when there was no pivot table in the world 😊
1) Select a cell in the table => Insert tab => in the Tables group of commands => click on the PivotTable command.
2) Create PivotTable dialog box appears. As we had selected a cell of the table before clicking on the PivotTable command, our table name (Data) is automatically showing in the Table/Range field of the dialog box.
We want to create the pivot table in a new worksheet, so we keep the default choice New Worksheet under the heading ‘Choose where you want the PivotTable report to be placed’.
And finally, click OK.
3) A new worksheet (Sheet1) is created and the PivotTable Fields task pane is showing automatically in the worksheet.
Sales by Category Report in a Pie Chart
Let’s make a Sales report Category wise and then we shall create a pie chart. To make the report, we organize the Pivot Table Fields in this way.
Observe the following image carefully. We have placed the Sales field two times in the Values area. For this reason, in the Columns area, an additional Values field is showing. In the Rows area, we have placed the Category field.
On the left side of the image, you are seeing the output pivot table for the above field settings.
Change the Number format of the Pivot Table
Let’s change the number format for this pivot table. To change the number format of a pivot table values, you have to open the Value Field Settings dialog box. You can open the Value Field Settings dialog box in 3 ways:
A) Right-click on a number in the Pivot Table, a drop-down menu will appear and you find the Value Field Settings command in the menu.
B) Or click on a field in the Values You will find the Value Field Settings command in the menu.
C) Third way: when a value cell is selected in the Excel Pivot Table, go to Analyze tab (appears only when a pivot table is selected) => in the Active Field group of commands => click on the Field Settings
Whatever way you use to open the Value Field Settings dialog box, the following dialog box will appear. Click on the Number Format command in the dialog box.
The Format Cells dialog box appears. Select the Number under the Category list and on the right make two changes like the following image.
- Make the Decimal places field option to 0
- And select the checkbox of Use 1000 Separator (,)
And when done click on the OK button two times to disappear the Format Cells and Value Field Settings dialog box respectively.
And in this way, we have changed the number formatting of the first value column of the pivot table.
Showing the Category wise Sales in % of Grand Total
In the same (above) way, you can change number format of the 2nd value column of this pivot table. But we shall not do that. We shall show the values of this column in percentage (%) of Grand Total.
Right-click on a cell in the column. Hover your mouse over the option Show Values As and then click on the command % of Grand Total.
You see the column values are showing in the percentages of Grand Total.
Creating a Pie Chart from this data
Select a cell in the Pivot Table => go to Insert tab => Charts group of commands => click on the Pie Chart drop-down => under the 2-D Pie, we select the first option.
And we get a pie chart like the following image. It seems that it is a general Excel chart. It is actually not.
It is actually a Pivot Chart. In the chart, you see the Pivot Table Fields are showing, Category is showing as a drop-down list and on the bottom-left corner, you’re seeing the Values is showing as a field.
For our convenience, we remove the first Sum of Sales field from the Pivot Table (right click on the field and then choose Remove Field option)
The chart looks now like the following image.
You can make a or all the field names invisible from the chart. Just right-click on a field name on the Pivot Chart. On the appeared menu, you will find two options to disappear the field names from the chart:
- Hide Value Field Buttons on Chart: Will hide all the value field buttons from the chart.
- Hide All Field Buttons on Chart: This will hide all the field buttons from the chart.
I choose the second option.
The above actions will output the following chart.
Showing the Category Names and Data Labels on the Pie Chart
I will show a general Excel charting technique to add anything on a chart. If you know the process, you can skip this section.
We shall use the GETPIVOTDATA Excel function to pull data from the pivot table.
You’re seeing a pivot table below created from our data.
This pivot table is showing the Sum of Sales, State and Category wise.
We have placed the State field in the Rows area, the Category field in the Columns area and the Sales field in the Values area.
Excel GETPIVOTDATA Function
Now, let me introduce you to Excel’s GETPIVOTDATA function.
GETPIVOTDATA syntax: GETPIVOTDATA (data_field, pivot_table, [field1, item1], [field2, item2], …)
A pivot table has only one data_field but it can have any number of other fields.
For the above Pivot Table:
- The data_field is the Sales field
- The other two fields are State and Category.
In the following image, you see I have used a GETPIVOTDATA formula in cell H9: =GETPIVOTDATA(“Sales”, A3, “State”, H7, “Category”, H8)
This formula returns value 950 in cell H9.
How does this formula work?
- The data_field argument is the Sales No doubt.
- A3 is a cell reference within the pivot table. It can be any cell reference within a pivot table.
- field1, item1 = “State”, H7. You can translate it like Idaho (value of cell H7 is Idaho) item in the State
- field2, item2 = “Category”, H8. It can be translated like Office Supplies (the value of cell H8 is Office Supplies) item in the Category
- The cross-section of the Idaho values and Office Supplies values give us the value 950
I hope GETPIVOTDATA function is crystal clear to you now.
Showing the Category name and Data Label on the chart
Using the GETPIVOTDATA function, we show the category names and sales values (% of Total) in some cells (like the following image).
For your understanding, let me explain this formula in cell D4
=A4&” “&TEXT(GETPIVOTDATA(“Sales”, A3, “Category”, A4), “0%”)
- A4&” ” part is simple to understand. A cell reference and then makes a space in the output.
- Then we have used Excel’s TEXT As the value argument of TEXT function, we have passed the GETPIVOTDATA function and as the format_text argument, we have used this format: “0%”
- GETPIVOTDATA part is simple to understand. So, I will not explain how the GETPIVOTDATA function works here.
Now, we shall show these data on the chart.
I insert a Text Box from the Insert tab => Illustrations group of commands => Shapes
Now I insert the Text Box on the chart => Put an equal sign on the Formula Bar and then select cell D4.
If I press Enter, the Text Box will show the value of cell D4.
In the same way, I create other Text Boxes and refer the relevant cells.
Note: When one Text Box is created, you can make new Text Boxes from this one. This is how you can do it:
- Hover your mouse pointer over the border of the created Text Box and press CTRL key on your keyboard. A plus sign will appear.
- Now drag your mouse. You will see a new Text Box (object) is created, drop this newly created Text Box at your preferred place.
So, we are done with the creating of a pie chart that shows dynamically the category wise sales.
I just change the name of this Pivot Table to PT_CategorySales.
There is also an easy way to show both the Series Name and Data Label on the chart.
Create and select the Pie Chart => open the Design tab => in the Chart Layouts group of commands => click on the Quick Layout drop down => select the Layout 1 option from the drop-down.
Quarterly Sales by Territory
Sometimes, you might want to see the Sales changes in different quarters over the years.
We are going to create a report like the following image.
The image shows the top 15 US States according to Total Sales for different quarters. I have also added sparklines to show the trends in different quarters.
Let me show you the process.
Create a Pivot Table and make the following changes. I have added the Order Date field in the Columns area, State field in the Rows area and the Sales field in the Values area.
You see one thing: when I have added the Order Date field in the Columns area, two more fields (Quarters and Years) have been added automatically in the Columns area. I am using Excel version 2016. If you are using an older version of Excel, these two fields will not be created automatically.
What will you do to show the Quarters and Years if you’re using older Excel version?
1) Right-click on any cell in the Column Labels (Jan, Feb, Mar, …) => click on the Group… command in the drop-down menu
2) Grouping dialog box will appear => Starting at and Ending at fields are showing the first and last dates respectively. But you can change these dates and enter your preferred dates => In the By window, you see Months, Quarters and Years are selected automatically.
3) As we want to show the report only for different quarters, so I select only the Quarters option in the By field and click OK.
You see that the Pivot Table is showing Sales for different Quarters only.
Showing the Top 15 States according to Total Sales
1) Right-click on any cell in the State column (changed Row Labels to State) => in the drop-down menu, hover your mouse pointer over the Filters command => And then click on the ‘Top 10’ option.
2) Top 10 Filter (State) dialog box appears. Here, we choose 15 and then click OK.
3) We get a pivot table for the top 15 states. Now we sort this pivot table from Largest to Smallest value.
4) And this is the pivot table we get.
Adding the Sparklines
Before adding the Sparklines, I want to remove both the Grand Totals.
1) Go to PivotTables Tools => Design tab => Layout group of commands => Click on the Grand Totals drop down => Choose Off for Rows and Columns option.
2) This is the pivot table now.
3) In cell F5 (this cell is not part of the Pivot Table), let’s insert a sparkline.
Select the cell => go to Insert tab => in the Sparklines group of commands => I click on the Line sparkline command.
4) Create Sparklines dialog box appears. In the Data Range field, I select the range that will make the Sparklines and in the Location Range field, I select the cells where the Sparklines will be located.
When the selection is done, click on the OK button.
5) And we get these sparklines.
6) Make some changes with the Sparklines in the Sparklines Tools Design tab. Add the marker and change the theme of the sparklines.
7) And we are done. I have applied some formatting on the pivot table. And here is the result.
Adding Slicer to filter the Pivot Table Output
1) Adding slicer to a pivot table is very simple. Select the Pivot Table for which you want to create the slicers => go to Insert Tab => in the Filters group of commands => Click on the Slicer command.
2) Insert Slicers dialog box appears with all the available fields of the Pivot Table. Select the fields for which you want to create the slicers. I select Customer Name, State, and Category fields.
3) Click OK. And 3 slicers will appear.
This is my simplified final dashboard.
Let’s see how this dashboard works in real time.
Note: you are seeing one thing in the report: when we are filtering using one slicer, every slicer reacts on that update. This is because I have connected the slicers for the updates. How?
1) Right-click anywhere on a slicer => Choose Report Connections… on the drop down
2) Report Connections (Customer Name) dialog box appears. In the dialog box, select all the Pivot Tables that you want to connect. And then click OK.
3) Do this thing for all the Slicers. Otherwise, if you filter using one slicer, the other slicers will not react your filtering.
Download the Working File
So, that’s all. This is a very big article. So, I could have made many kinds of mistakes. It will be highly appreciated if you let me know in the comment box about the mistakes that you have encountered.
I have created this report that displays the quarterly sales by territory but if your requirement is different you could make it finer based on your purposes.