How to Create a Quarterly Sales by Territory Report in Excel

Building a quarterly sales by territory report in Excel shows you exactly which regions are performing well and which ones need more attention. It's a foundational report for any sales manager or business owner. This guide will walk you through creating a dynamic and visual report from scratch using raw sales data, PivotTables, and charts.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First Things First: A Clean Data Structure is Essential

Before you can report on your data, you have to organize it properly. The best way to set up your sales data for analysis in Excel is in a simple, flat-file format. This means each row represents a single sales transaction, and each column represents a specific attribute of that transaction. Avoid pre-formatted reports that have totals or subtotals, as these will interfere with Excel's analytical tools.

Your raw data should look something like this:

• Transaction ID: A unique identifier for each sale.
• Order Date: The date the sale was made.
• Salesperson: The name of the person who closed the deal.
• Territory: The region where the sale occurred (e.g., North, South, East, West).
• Product: The name of the product or service sold.
• Units Sold: The quantity of the product sold.
• Revenue: The total monetary value of the sale.

This structured format is crucial because it gives tools like PivotTables the raw material needed to slice and organize your information in any way you can imagine. If your data is structured with months as columns or has merged cells, you'll need to clean it up into this format first.

Step 1: Format Your Data as an Excel Table

Once your data is in a clean, tabular format, the first action you should always take is to convert it into an official Excel Table. This is not the same as just having data in cells, a formal Table structure has several benefits that make analysis much easier.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use an Excel Table?

• Dynamic Range: When you add new rows of sales data, the Table automatically expands. Your report will include the new data the next time you refresh it, without you having to manually update cell ranges.
• Readability: Tables come with built-in formatting, like alternating row colors (banded rows), making them easy to read.
• Structured References: Formulas that reference Table data use clear column names (like [Revenue]) instead of cryptic cell references (like G2:G100), which makes them easier to understand.

How to Create a Table:

1. Click any single cell inside your data range.
2. Go to the Insert tab on the Ribbon.
3. Click Table.
4. A small dialog box will appear, confirming the range of your data. Ensure the "My table has headers" box is checked.
5. Click OK.

Your data will now be formatted as a dynamic Table. You'll also see a new "Table Design" tab appear when you have the table selected.

Step 2: Add 'Quarter' and 'Year' Helper Columns

To analyze our sales on a quarterly basis, we need a way to group dates into their respective quarters. While some versions of Excel can do this grouping automatically within a PivotTable, adding a dedicated "Quarter" column to your Table is a more reliable method that works every time. We'll also add a "Year" column for multi-year analysis.

Create the 'Year' Column:

1. Go to the first empty column to the right of your data. Click in the header cell and type "Year."
2. In the first cell directly below the header, enter the following formula. If your order date is in a column named "Order Date", the formula will be:

=YEAR([@[Order Date]])

Since you are working inside an Excel Table, it should automatically fill this formula all the way down the column. If not, click the small green box at the bottom-right corner of the cell and drag it down.

Create the 'Quarter' Column:

1. In the next empty column, create a new header called "Quarter."
2. In the first cell below the new header, enter this formula:

="Q"&ROUNDUP(MONTH([@[Order Date]])/3,0)

How This Formula Works:

• MONTH([@[Order Date]]) extracts the month number (1 for January, 2 for February, etc.) from the date.
• /3 divides that month number by 3. For example, March (3) becomes 1, April (4) becomes 1.33, and June (6) becomes 2.
• ROUNDUP(...,0) rounds that number up to the nearest whole number. This neatly groups months 1, 2, and 3 into Quarter 1, months 4, 5, and 6 into Quarter 2, and so on.
• "Q"& adds the letter "Q" to the beginning of the number, giving you "Q1", "Q2", etc.

Your table now includes two powerful new columns that will make the reporting process a breeze.

Step 3: Build a PivotTable to Summarize Your Sales Data

A PivotTable is Excel’s most powerful tool for summarizing data. It lets you aggregate thousands of rows of sales transactions into an easy-to-read report without writing a single complex formula.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Insert a PivotTable:

1. Click anywhere inside your Excel Table.
2. Go to the Insert tab and click PivotTable.
3. A dialog box will pop up. Your table name should already be selected in the range field.
4. Choose New Worksheet to keep your report clean and separate from your raw data.
5. Click OK.

A new worksheet will appear with a blank PivotTable placeholder on the left and a "PivotTable Fields" pane on the right. This pane is your control center for building the report.

Configuring your PivotTable Fields:

Now, we'll design our report by dragging and dropping fields into the four areas at the bottom of the Fields pane:

1. Rows: Drag the Territory field into the Rows area. This will list each of your sales territories down the side of your report.
2. Columns: Drag the Year field and then the Quarter field into the Columns area. This creates a logical grouping of quarters within each year across the top of your report.
3. Values: Drag the Revenue field into the Values area. Excel will automatically default to Sum of Revenue. If it shows Count of Revenue, click on it, select "Value Field Settings," and change the calculation to "Sum."

Instantly, you have a perfectly formatted summary table showing total sales for each territory, neatly broken out by year and quarter.

Step 4: Format and Polish Your Report

The raw PivotTable provides the right numbers, but a little formatting can make it much more professional and easier to understand.

Format Numbers as Currency

Instead of manually selecting cells, format numbers the "PivotTable way" so the formatting sticks even when the table re-sizes.

1. Right-click on any of the revenue numbers inside your PivotTable.
2. Go to Value Field Settings.
3. Click on the Number Format button in the bottom-left corner.
4. Choose Currency from the category list and adjust the decimal places as needed. Click OK.

Change the Report Design

The default PivotTable design can look a bit dull. To liven it up:

1. Click inside your PivotTable.
2. Go to the Design tab on the Ribbon.
3. Hover over the different styles in the "PivotTable Styles" gallery to find one that fits your brand or preference. Using a style with banded rows and columns can greatly improve readability.

Step 5: Visualize the Data with a PivotChart

While the table is great, a chart is often better for spotting trends and communicating results at a glance. A PivotChart is a chart that's directly linked to your PivotTable, so any changes or filters you apply to the table will automatically update the chart.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create a PivotChart:

1. Click anywhere inside your completed PivotTable.
2. Go to the PivotTable Analyze tab on the Ribbon.
3. Click PivotChart.
4. Excel will recommend some chart types. A Clustered Column Chart is an excellent choice for this report, as it allows you to compare the performance of different territories within the same quarter. Select it and click OK.

Clean Up Your Chart:

The default chart often needs a little tweaking to look its best:

• Add a Title: Click on "Chart Title" and give it a descriptive name like "Quarterly Sales Performance by Territory."
• Remove Field Buttons: The grey buttons (like "Sum of Revenue") on the chart are useful for filtering but can make the chart look cluttered in a presentation. To hide them, click on the PivotTable Analyze tab and then on the Field Buttons dropdown. Choose Hide All.
• Move the Legend: You can move the legend to the top or bottom for better spacing by clicking on the plus sign (+) next to the chart and selecting new options under "Legend."

Bonus Tip: Add Slicers for Interactive Filtering

Slicers are user-friendly filter buttons that make your report interactive. They allow anyone, even those unfamiliar with Excel, to easily drill down into the data.

1. Click on your PivotTable or PivotChart.
2. Go to the PivotTable Analyze tab.
3. Click Insert Slicer.
4. A dialog box will appear with a list of all your data fields. Check the boxes for fields you might want to filter by, such as Salesperson or Product.
5. Click OK.

You now have interactive filter boxes that control both your pivot table and your pivot chart. Click on a salesperson's name, and the entire report will instantly update to show their performance across all territories.

Final Thoughts

By following these steps, you can transform a raw list of sales transactions into a powerful, professional, and interactive quarterly report in Excel. Structuring your data correctly, using a PivotTable to summarize, and a PivotChart to visualize are fundamental skills for any type of business data analysis.

Of course, this process in Excel still relies on manually updating your raw data export and refreshing the report. We built Graphed to remove these manual steps entirely. Instead of creating formulas and PivotTables, you can simply connect your CRM or sales platform in a few clicks. Then ask, "Build a quarterly sales dashboard showing revenue by territory," and let our AI build a real-time, shareable dashboard for you instantly. This gives you back the time to focus on what the data means, not how to build the report.