Create Report That Displays Quarterly Sales by Territory in Excel

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.

Create a report in Excel that displays the quarterly sales by territory

Create Report That Displays Quarterly Sales by Territory in Excel (With Easy Steps)

For the demonstration, we are going to use the following dataset.

It includes sales by dates, which we are going to rearrange in a quarterly fashion with the help of Excel’s Table and Pivot Table features.

Step 1: Convert Dataset into Table

If the data is not in table format, convert the range into a table. The Excel table is one of the best features of Excel and makes many jobs easier, like referring, filtering, sorting, and updating.

• Select a cell in the range that you want to convert to the table and press Ctrl+T on your keyboard. Or go to the Insert tab, and from the Tables group of commands, click on the Table option.

• As a result, the Create Table dialog box will appear. The range will be selected automatically with My table has headers checkbox selected. To create a table, just click OK.

As a result, the dataset will be converted into a table.

Step 2: Name Table Range

Let’s name the table at this point. This will help some of the later portion of the work easier.

You can change the name of your table from the Design tab or use the Name Box. We have named our table with Data.

Step 3: Create a Pivot Table with Given Data

We are going to use Excel’s most used tool for creating our report and it is Pivot Table. Follow these steps to create a pivot table with the table.

• First, select a cell in the table.
• Then go to the Insert tab and click on the PivotTable command from the Tables group.

• At this instant, the 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.
• Then click OK.

A new worksheet is created and the PivotTable Fields task pane is showing automatically in the worksheet.

Step 4: Prepare a Pivot Table by Category Report

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.

• Now we want to change the number format of the sales in percentage (%) of Grand Total. To do that, right-click on a cell in the column.
• Then select Show Values As from the context menu.
• After that, click on the command % of Grand Total.

Thus, the column values will show in the percentages of Grand Total.

Step 5: Create a Pie Chart for Category Report

To create a report on the data, let’s add a pie chart to it. Follow these steps to add a pie chart from the data.

• First, select a cell in the Pivot Table.
• Then go to the Insert tab and click on the Pie Chart icon from the Charts group.
• After that, select the Pie chart from the drop-down list.

We will have the pie chart pop up on our spreadsheet.

After some modifications, the chart will now look like this.

Showing Category Names and Data Labels on Pie Chart

You can add data labels by following these steps.

• First, select the Pie Chart.
• Then go to the Design tab and in the Chart Layouts group of commands, click on the Quick Layout.
• From the drop-down select the Layout 1 option from the drop-down.

Alternative Way:

Another creative way we can add data labels to the chart is to use the GETPIVOTDATA function. We shall use the 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.

Now, let’s look at 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:

This formula returns a value of 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 as 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 of 950.

To Show the Labels:

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&” ” part is simple to understand. A cell reference then makes a space in the output.
• Then we used Excel’s TEXT As the value argument of the TEXT function, we have passed the GETPIVOTDATA function and as the format_text argument, we have used this format: “0%”
• The 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.

We inserted a Text Box from the Insert tab => Illustrations group of commands => Shapes

Now we 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 to 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 the 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 creation of a pie chart that shows dynamically the category-wise sales.

I just change the name of this Pivot Table to PT_CategorySales.

Read More: How to Make Daily Sales Report in Excel

Step 6: Prepare a Pivot Table for Quarterly Sales

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. We have also added sparklines to show the trends in different quarters.

Follow these steps to prepare the pivot table for quarterly sales.

• First of all, select a cell from the data table.
• Then select PivotTable from the Tables group of the Insert tab.

• Next, select where you want to place the pivot table and click on OK. For this demonstration, we have selected a new worksheet.

• Now do the following: add the Order Date field in the Columns area, the State field in the Rows area, and the Sales field in the Values

• To show the quarterly report now, right-click on any cell in the Column Labels and select Group from the context menu.

• Then select Quarters under the By section of the Grouping

• After clicking on OK, the pivot table will now look something like this.

Step 7: Show Top 15 States from Sales

The result from the previous step contains a quarterly report of all the states from the dataset. If you want all of them, you can go ahead with this one. But in case of more detailed analysis where you need the top states, here are some handy steps.

• First, right-click on any cell in the State column(or Row Labels).
• Then hover your mouse over Filter from the context menu and then select the Top 10

• Next, select 15 in the Show option from the Top 10 Filter (State)

• Once you click on OK, the pivot table will now show the top 15 states according to sales.

Step 8: Add Sparklines to Table

Before adding the Sparklines, I want to remove both the Grand Totals. Follow these steps for the detailed guide on how to do that.

• First of all, select a cell from the pivot table.
• Then go to the Design tab on your ribbon.
• Now select Grand Totals from the Layout
• Then select Off for Rows and Columns from the drop-down list.

The Grand Total section will thus be removed.

• To add sparklines, select cell F5, then go to the Insert tab on your ribbon.
• Now select Line from the Sparklines

• In the Create Sparklines box, select the range B5:E19 as the Data Range and the range F5:F19 as the Location Range.

• Then click on OK. The pivot table will now look like this finally.

• Also, let’s add some markers to make it more appealing. To do that, go to the Sparkline tab on your ribbon (it will appear once you select a cell containing sparkline) then select Markers from the Show

This is the final output of our sparkline.

Read More: How to Make Sales Report in Excel

Step 9: Add Slicer to Filter Output

Follow these simple steps to add slicers to the pivot table.

• First, select the pivot table for which you want to create the slicers.
• Then go to Insert Tab and from the Filters group of commands, click on the Slicer

• Next, the Insert Slicers dialog box will appear with all the available fields of the Pivot Table. Select the fields for which you want to create the slicers. Here, we have selected the Customer Name, State, and Category fields for the demonstration.

• After clicking on OK, 3 slicers will appear on top of the spreadsheet.

Step 10: Prepare Final Report

With all the detached stuff created let’s finally combine them all into a single spreadsheet to create a final report.

Now if you select/deselect an option from the slicer, the result will change accordingly in real-time. For example, let’s select Arizona from the State slicers. It will only report that.

You can select multiple ones now too. For example, adding Alabama with it will look like this. And that’s how you can create a report that displays the quarterly sales by territory.

Download Practice Workbook

You can download the workbook used for the demonstration to try these steps yourself as you go along with the article.

Conclusion

These were all the steps required to create a report that displays quarterly sales by territory in Excel. Hopefully, you can make one on your own with ease now. I hope you find this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

Related Articles

• How to Prepare MIS Report in Excel
• How to Make MIS Report in Excel for Accounts
• How to Make MIS Report in Excel for Sales
• Create a Report That Displays Quarterly Sales in Excel
• How to Make Monthly Sales Report in Excel

<< Go Back to Report in Excel | Learn Excel