How to Create a Report That Displays Quarterly Sales by Territory

Reporting is a critical part of the sales process. By analyzing your metrics, you gain understanding of how your sales teams are performing, and have the opportunity to identify areas for improvement.

While it’s important to understand sales performance as a whole, it can also be helpful to understand how specific segments of your pipeline are performing.

In this post, we’ll go over how to create a segment-specific report that you can use to understand your business’ quarterly sales by territory using Excel.

Create A Report That Displays Quarterly Sales By Territory

The first thing you’ll need to do is gather relevant sales data to include in your report. Since you’re hoping to understand territory sales performance, your data should be specific to those metrics.

Once you’ve collected the data, you’ll want to place it into an Excel spreadsheet. For this example, I’ve created a sample data set (snippet shown below) and created columns for territory, sale date, number of deals closed, and total deal revenue.

The first few steps of the process are prepwork for creating the final report.

1. Select your entire table with your cursor and navigate to the toolbar. Click the Insert tab, and then select PivotTable, as shown in the gif below. This table will display the end result after you complete the following steps.

After selecting PivotTable you should see a pop-up window on your screen, as pictured below.

2. Leave the Select a table or range field as is and simply select New worksheet and click OK.

You should then land on a blank spreadsheet with a PivotTable Fields box on the right side of your sheet, as shown below.

3. Within the PivotTable Field Name box you should see a tab that says Quarters. If not, you must manually change the values. To do this, select the checkbox next to the Date field, as shown below.

4. Navigate to the toolbar and select the PivotTable Analyze tab, and click Group Selection twice. The gif below demonstrates this process.

5. In the Grouping popup window (shown below), select Quarters from the menu options and click OK. You should still see an empty spreadsheet with the PivotTable Fields box on the right side of your page.

Now it’s time to begin creating the report.

6. In the PivotTable Fields box, select Quarters from the Field Name menu and drag it into the Columns box. The gif below demonstrates this process.

7. Repeat the same process and select the column name from your data set that contains revenue totals and drop it in the Values box.

8. Repeat this again and select the column name from your data set that contains territory names and drop it in the Rows box.

Your final PivotTable Fields box should have Quarters in the Columns box, Territory in the Rows box, and Sum of Revenue in the Values box. The image below is an example of what this should look like.

Note that your column header titles may be different depending on how you’ve named them.

You should now see a PivotTable in your spreadsheet that displays the total number of quarterly sales by territory. The image below is what my table looks like.

9. If you want, you can clean up the table and rename column titles that make the data set easier to interpret. My new table is displayed below.

While the table above shows quarterly revenue by territory, you can also create a report that displays the quarterly deals by territory.

10. Simply repeat step seven and replace Sum of Revenue in the Values box with the title of the column from your data set that includes the number of deals closed.

My final table that displays quarterly sales by territory is displayed in the image below.

Once you have your final table(s) you can make your own personal customizations for colors and fonts, and even create a chart using the PivotChart function for additional visualizations, like bar graphs or pie charts.

Additional Sales Reporting Tools

While Excel is an incredibly powerful tool, there are additional feature-rich services available that you can use to supplement your reporting process.

InsightSquared

InsightSquared’s dashboards display comprehensive, real-time performance reports. You can analyze your entire pipeline with pre-built reports and apply filters to monitor custom metrics, like territory data. The image below is a sample pipeline dashboard.

Image Source

Canopy

Canopy is a high-powered insights platform that you can use to monitor your entire sales funnel. While it includes the expected dashboard and forecasting tools, you can also get overviews of your entire sales funnel and filter by category to view specific territory sales data (as shown in the image below).

Image Source

If you’re a HubSpot user,  InsightSquared and Canopy can integrate with Sales Hub.

Understand All Aspects of Your Sales Performance

Whether you’re a small business or an enterprise level corporation, it’s important to understand all aspects of your sales performance, especially if you operate in multiple markets.

Use this guide to help you create a report that displays your business’ quarterly sales by territory to understand how your different regions are performing.