Create a Report That Displays the Quarterly Sales by Territory in Excel – 9 Steps

This is an overview.


This is the sample dataset.

data to create report that displays quarterly sales by territory in excel

 

Step 1- Convert the Dataset into a Table

  • Select a cell in the range.
  • Press Ctrl+T, or go to the Insert tab, in Tables, click Table.

creating table

  • In the Create Table dialog box, the range will automatically be selected automatically and My table has headers checked.
  • Click OK.

The dataset will be converted into a table.


Step 2 – Name the Table Range

  • Change the name of your table in the Design tab or use the Name Box. Here, Data.

naming table range to create report that displays quarterly sales by territory in excel


Step 3 – Create a Pivot Table with the Given Data

  • Select a cell in the table.
  • Go to the Insert tab and click PivotTable in Tables.

  • In the Create PivotTable dialog box, the table: Data is automatically displayed in Table/Range.
  • To create a pivot table in a new worksheet, we keep the default choice New Worksheet in Choose where you want the PivotTable report to be placed.
  • Click OK.

creating pivot to create report that displays quarterly sales by territory in excel

A new worksheet is created and the PivotTable Fields task pane is displayed.


Step 4 – Prepare a Pivot Table by Category Report

To create the report, organize the Pivot Table Fields:

  • Sales is placed two times in the Values area (in the Columns area, an additional Values field is showing).
  • In the Rows area, enter Category.

 

pivot table fields to create report that displays quarterly sales by territory in excel

  • To change the number format of sales to percentage: right-click a cell in the column (%) of Grand Total.
  • Select Show Values As.
  • Click % of Grand Total.

determining formats

This is the output.


Step 5 – Create a Pie Chart for the Category Report

  • Select a cell in the Pivot Table.
  • Go to the Insert tab and click the Pie Chart icon in Charts.
  • Select Pie.

inserting pie chart to create report that displays quarterly sales by territory in excel

The pie chart is displayed.

Format the the chart. This is the output.

pie chart report of quarterly sales by territory

Showing Category Names and Data Labels on a Pie Chart

Add data labels:

  • Select the Pie Chart.
  • Go to the Design tab and in Chart Layouts, click Quick Layout.
  • Select Layout 1.

Alternative Way:

You can also add data labels to the chartusing the GETPIVOTDATA function.

The pivot table is showing the Sum of Sales, State, and Category.

State was placed in RowsCategory in Columns, and Sales in Values.

 

GETPIVOTDATA syntax: GETPIVOTDATA (data_field, pivot_table, [field1, item1], [field2, item2], …)

A pivot table has only one data_field but it can have other fields.

For the above Pivot Table:

  • The data_field is Sales
  • The other two fields are State and Category.

Use a GETPIVOTDATA formula in H9:

=GETPIVOTDATA("Sales", A3, "State", H7, "Category", H8)

The formula returns 950 in H9.

getpivotdata exaMPLE

Formula Breakdown

  • The data_field argument is Sales.
  • A3 is a cell reference within the pivot table.
  • field1, item1 = “State”, H7Idaho (the value of H7 is Idaho) item in State
  • field2, item2 = “Category”, H8Office Supplies (the value of H8) item in Category
  • The cross-section of the Idaho values and Office Supplies values returns 950.

To Show the Labels:

Using the GETPIVOTDATA function, the category names and sales values (% of Total) are displayed as shown below.

A formula is used in D4:

=A4&" "&TEXT(GETPIVOTDATA("Sales", A3, "Category", A4), "0%")
  • A4&” ” is a cell reference and creates a space in the output.
  • The TEXT as the value argument of the TEXT function passes the GETPIVOTDATA function. The format_text argument is:“0%”
  • The GETPIVOTDATA function:

 

insers a Text Box from the Insert tab => Illustrations  => Shapes

inserts the Text Box on the chart =>enters an equal sign in the Formula Bar and selects D4.

adding text box

If you press Enter, the Text Box will show the value of D4.

naming text box to create report that displays quarterly sales by territory in excel

Other Text Boxes were created.

Note: You can create new Text Boxes from an existing one:

  • Hover your mouse pointer over the border of the Text Box and press Ctrl. A plus sign will be displayed.
  • Drag your mouse to create a new Text Box. Choose a place to drop it.

 

The name of the Pivot Table was changed to PT_CategorySales.

Read More: How to Make Daily Sales Report in Excel


Step 6 – Prepare a Pivot Table for Quarterly Sales

 

The image above shows the top 15 US States according to Total Sales in different quarters. Sparklines were added to show the trends.

Prepare the pivot table for quarterly sales:

  • Select a cell.
  • Select PivotTable in Tables, in the Insert tab.

adding pivot table to create report that displays quarterly sales by territory in excel

  • Choose a destination for the pivot table and click OK. Here, a new worksheet.

creating pivot to create report that displays quarterly sales by territory in excel

  • Add the Order Date in Columns, the State in Rows, and the Sales in Values

  • To show the quarterly report, right-click any cell in Column Labels and select Group.

grouping data to create report that displays quarterly sales by territory in excel

  • In Grouping, choose By and select Quarters.

selecting quarter time periods to create report that displays quarterly sales by territory in excel

  • Click OK.
  • This is the output.


Step 7 –  Show the Top 15 States in Sales

  • Right-click any cell in State.
  • Select Filter and then choose Top 10

selecting top states to create report that displays quarterly sales by territory in excel

  • Select 15 in Show.

selecting top states

  • Click OK.

This is the output.


Step 8 – Add Sparklines to Table

Remove both Grand Totals:

  • Select a cell in the pivot table.
  • Go to the Design tab.
  • Select Grand Totals in Layout
  • Select Off for Rows and Columns.

removing grand total option

The Grand Total section will be removed.

grand total removed to create report that displays quarterly sales by territory in excel

  • To add sparklines, select F5 and go to the Insert tab.
  • Select Line in Sparklines

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

  • Click OK. This is the output.

  • Add markers:  go to the Sparkline tab and select Markers in Show

This is the final output.

sparkline final

Read More: How to Make Sales Report in Excel


Step 9 – Add a Slicer to Filter the Output

  • Select the pivot table.
  • Go to Insert and in Filters, click Slicer.

adding slicer to the report

  • In the Insert Slicers dialog box, select the fields to create slicers. Here, Customer Name, State, and Category.

slicer categories to create report that displays quarterly sales by territory in excel

  • Click OK.

3 slicers will be displayed.

all slicers


Step 10 – Prepare the Final Report

create report that displays quarterly sales by territory in excel

If you select/deselect an option in the slicer, the result will change accordingly. For example, select Arizona in State slicers. Only that report is displayed.

You can select multiple slicers. Add Alabama to see the result:


Download Practice Workbook

Download the workbook.


Related Articles


<< Go Back to Report in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

10 Comments
  1. That was a Udemy class without audio! Thank you, sir. I find your posts to be specific and accurate without any “I’m the Greatest” bullbucky. My compliments to your motivations and gracious habit of giving-away wonderful tutorials! -GuyR8s

  2. Thank you very much Kawser:

    Best regards,

    Sohail Rizki
    713 459 1340

  3. Really helped me in learning something new.

  4. Excellent Tutorial. Thank you for sharing Kawser

  5. Very glad to be your free online student. This is very great post.
    It teaches me more than the ttle especially the use of pivot table for sales reporting.
    Thanks

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo