How to Create a Quarterly Sales by Territory Report in Excel with AI

Building a quarterly sales by territory report in Excel used to mean a drawn-out battle with PivotTables, filters, and formulas. You would spend hours just getting the data organized, let alone finding any actual insights. This article will show you how to skip that entire process and build the same vital report in minutes using Excel's built-in AI features.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why a Sales by Territory Report Is a Game-Changer

Before jumping into the “how,” it’s worth a quick reminder of the “why.” A quarterly sales by territory report is more than just a collection of numbers, it’s a strategic map of your business. It tells you where you’re succeeding, where you’re struggling, and where to invest your resources next. With a clear view of performance by region, you can:

• Reward Top Performers: Easily identify the sales territories and reps that are consistently hitting their targets.
• Spot Underperforming Areas: Uncover regions that need more support, training, or strategic adjustments before they become a bigger problem.
• Allocate Resources Smarter: Make data-backed decisions about where to deploy your marketing budget, hire new salespeople, or focus your efforts for the next quarter.
• Identify Growth Opportunities: Spot emerging markets or territories showing unexpected growth, giving you a chance to double down on what’s working.

In short, it’s one of the most effective reports for turning raw sales data into a concrete action plan.

Step 1: Get Your Data Ready for Analysis

No matter how smart the AI is, it can’t make sense of messy, disorganized data. This is arguably the most important step in the entire process. Before you can ask any questions, your sales data needs to be in a clean, structured table format inside Excel.

Think of it like setting the dinner table before guests arrive. Each piece of information needs its own dedicated place. For a sales by territory report, your "table" should have columns for essential details like:

• Order ID or Transaction ID: A unique identifier for each sale.
• Sale Date: The full date the transaction occurred (e.g., 01/15/2024). This must be formatted as a date in Excel for quarterly grouping to work correctly.
• Territory or Region: The geographic area where the sale was made (e.g., North, South, West, Midwest).
• Salesperson: The name of the rep credited with the sale.
• Product Category: What was sold (e.g., Software, Hardware, Services).
• Sale Amount: The dollar value of the transaction.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Clean Data Table Example

Your data should look something like this, with each row representing a single sale and each column representing a specific piece of information. Notice there are no blank rows or columns interrupting the table.

Step 2: The Classic Way - A Quick Look at PivotTables

For decades, the go-to tool for this job has been the PivotTable. It’s powerful, but it comes with a learning curve and requires several manual steps. This involves:

1. Highlighting your data and going to Insert > PivotTable.
2. In the PivotTable Fields pane, you’d drag Territory to the Rows area.
3. Then, drag Sale Amount to the Values area to sum the sales figures.
4. Finally, you'd drag Sale Date to the Columns area. Excel would often group this by Days and Months, forcing you to right-click the dates, select Group, and then manually choose Quarters and Years.

It’s effective but requires you to know exactly which fields to drag where. Let’s look at a much faster method.

Step 3: The New Speed - Using Excel's "Analyze Data" AI

Many Excel users don’t even know this feature exists, but it’s a powerful shortcut hidden in plain sight. "Analyze Data" (formerly called "Ideas") uses AI to interpret your data and your questions, generating PivotTables and charts for you with a fraction of the effort.

How to Build Your Report with AI in Just a Few Clicks

This process is surprisingly simple. You just have to know where to find the magic button.

1. Select Your Data: Click anywhere inside your clean data table. You don’t need to highlight the entire thing, Excel is smart enough to detect the boundaries of a well-formatted table.
2. Locate and Click "Analyze Data": Go to the Home tab on the Ribbon. All the way on the right side, you'll see a button with a lightning bolt icon labeled Analyze Data. Click on it.
3. Talk to Your Data: A new pane will appear on the right side of your screen. You’ll see some AI-suggested analyses, but the real power is in the text box at the very top that says, "Ask a question about your data." This is where you can use plain English to tell Excel what you want to see.

For our goal, type in a clear prompt like:

Show total sale amount by territory by quarter

Or you could ask slightly differently:

quarterly sales by territory

Press Enter.

1. Get Your Answer Instantly: In seconds, the AI will process your request and generate the exact PivotTable and corresponding chart that answers your question. It understands words like "total," "quarterly," and "by territory" and automatically knows to sum the sales figures and group the dates without any manual intervention from you.
2. Insert the Report into Your Sheet: Below the generated report, you’ll see a button that says + Insert PivotChart or + Insert PivotTable. Click it, and Excel will create a new sheet in your workbook containing the fully built, formatted report. That’s it! You've successfully created your report in a few seconds instead of several minutes.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Drilling Deeper: Asking AI Follow-Up Questions

A good report often leads to more questions, and the "Analyze Data" tool is perfect for this kind of exploratory analysis. You don’t have to start from scratch. Just go back to the text box and ask something more specific.

Once you have your initial quarterly report, you might wonder:

• About top reps: "Who was the salesperson with the highest sales in the North territory in Q3?"
• About product performance: "Show me software sales in the West territory."
• About trends over time: "Which territory grew the most from Q1 to Q2?"

Each time you ask a question, the AI generates a fresh analysis you can add to your workbook. This transforms data analysis from a rigid, step-by-step process into a fluid conversation where you can follow your curiosity.

Making Your Report Shine: Simple Visualization Tips

The AI will give you an informative report, but a few small tweaks can make it much easier for others to understand at a glance.

Use Conditional Formatting

Once you’ve inserted your final PivotTable, select the sales data and go to Home > Conditional Formatting. Use Color Scales to create a "heat map," where your highest sales numbers are green and your lowest are red. This instantly draws the eye to areas of strength and weakness without anyone having to read a single number.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Choose the Right Chart

The "Analyze Data" feature usually defaults to a good chart (like a clustered column chart), but you can always change it. Right-click on your chart and select Change Chart Type. A line chart, for example, might be better for showing the performance trend of a single territory over the course of the year.

Common Pitfalls and How to Avoid Them

Even with helpful AI, a few issues can trip you up:

• Messy Data: As mentioned, this is the number one cause of failed analysis. Inconsistent spellings for territories (e.g., "North" vs. "N.") or extra spaces will be treated as different regions. Clean your data first.
• Incorrect Date Formatting: If the "Sale Date" column is formatted as text instead of a date, Excel’s AI won’t be able to group it into quarters. Fix this by selecting the column, going to the Data tab, and using the Text to Columns feature to convert it.
• Vague Prompts: While the AI is smart, being too vague will lead to ambiguous results. Instead of just "sales," be more specific, like "total sales" or "average sales."

Final Thoughts

The days of struggling with complex formulas and multi-step processes in Excel are fading. With features like "Analyze Data," Excel is making it possible for anyone, regardless of technical skill, to get fast answers from their data just by asking questions. This is a big step forward in transforming data from a simple spreadsheet into a valuable business asset.

The limitation, however, is that you're still working with static files that need to be manually updated - like downloading a CSV from your CRM each month. Tools are now emerging to completely automate this process. We designed Graphed for exactly this purpose: to have one central place to ask questions of your live sales and marketing data. By connecting directly to sources like Salesforce, HubSpot, and Google Analytics, we remove the manual export/import steps, ensuring you’re always looking at real-time dashboards so your weekly, monthly, and quarterly analysis is always up to date without any data wrangling required on your part.