The quickest way to track sales by region in Excel is to use a PivotTable on raw sales data (columns: Date, Region, Rep, Product, Amount) and then add a Slicer for Region. For a ready-made solution, Microsoft offers a free “Sales Tracking” template in File > New that includes a Region field, but a custom PivotTable gives you full control.
Why a Region-Specific Template Matters
Sales data without a regional breakdown hides where growth is coming from and where it stalls. A region-specific template lets you compare performance across territories, spot underperforming areas, and allocate resources. Most generic sales trackers only show totals; the region filter turns raw numbers into actionable intelligence. You can answer questions like “Which region grew 20% last quarter?” or “Why is the West Coast below target?” without manually sorting through hundreds of rows.
Core Components of a Region-Based Sales Tracker
A solid template should contain these elements, each in its own sheet or clearly labeled section:
- Data Entry Sheet – raw rows with columns: Date, Region, Sales Rep, Product, Units, Unit Price, Revenue. Use an Excel Table (Ctrl+T) so formulas and PivotTables auto-expand.
- Region Lookup Table – a small mapping table if your raw data uses city or state, not region. For example, map “NY” and “NJ” to “East”.
- Summary Dashboard – PivotTable or SUMIFS formulas showing revenue, units, and % of total by region.
- Charts – a bar chart for regional revenue and a pie chart for market share.
- Target vs. Actual – a column for regional targets and a formula comparing actuals.
How to Build a Basic Region Tracker from Scratch
If you prefer to build your own, follow these steps in Excel for Microsoft 365 on Windows (Mac and web versions support the same functions):
- Enter your sales data in columns A to F: Date, Region, Rep, Product, Units, Unit Price.
- In column G, add a Revenue formula. With data in row 2, in G2 enter:
=E2*F2and drag down. - Select the data range (A1:G100) and press Ctrl+T to create a table. On Mac, press Cmd+T.
- Go to Insert > PivotTable. Choose “New Worksheet”.
- Drag “Region” to Rows, “Revenue” to Values. Excel will sum revenue by region.
- To add a Slicer, click anywhere in the PivotTable, go to PivotTable Analyze > Insert Slicer, and check “Region”. This gives you interactive buttons to filter.
For a formula-based approach, use SUMIFS. With regions in A2:A20 and revenue in G2:G20, in a summary cell enter: =SUMIFS(G2:G20,A2:A20,"East"). This returns the total revenue for the East region.
Using SUMIFS for Region Totals Without a PivotTable
SUMIFS is useful when you want a simple, non-interactive summary. Assume your data is in a table named SalesTable. In a summary sheet, list regions in A2:A5 (East, West, North, South). In B2, enter: =SUMIFS(SalesTable[Revenue],SalesTable[Region],A2). Drag down to fill. This formula sums revenue where the region matches the cell in column A. If you also want to count orders per region, use COUNTIFS: =COUNTIFS(SalesTable[Region],A2). The result for East, for example, would be the number of rows with “East” in the Region column.
How to Choose a Pre-Made Template
When picking a template, check these features:
- Does it use a PivotTable or formulas? PivotTables are more flexible for slicing by region, but formulas are easier for beginners.
- Is the Region field already included? Some templates only have state/city; you’ll need to add a lookup column.
- Does it include a dashboard with charts? A good template visualizes regional performance.
- Is it compatible with your Excel version? Templates using newer functions like FILTER or XLOOKUP require Excel 2021 or Microsoft 365.
Microsoft’s built-in template (File > New, search “sales tracking”) includes a Region column and a simple dashboard. It’s a safe starting point, but you may need to customize the region names to match your data.
Common Problems and Fixes
One frequent issue is that region names are inconsistent (e.g., “East” vs “EAST” vs “East Coast”). Use a lookup table to standardize. Another problem is that the PivotTable doesn’t update when you add new rows. Ensure your source data is an Excel Table, then go to PivotTable Analyze > Refresh (or press Alt+F5). If you see #REF! errors in SUMIFS, check that your ranges are the same size. Also, remember that SUMIFS is case-insensitive, so “east” and “EAST” are treated the same.
Comparing Excel Templates to Google Sheets
Google Sheets offers similar functionality with its own templates (File > New > Template gallery > Sales tracking). The functions differ slightly: Google Sheets uses SUMIFS with the same syntax, but the menu paths are different (e.g., Data > Pivot table instead of Insert > PivotTable). Also, Google Sheets has a built-in QUERY function that can group by region: =QUERY(A:G,"select B, sum(G) where A is not null group by B label sum(G) 'Total'"). Excel does not have QUERY; you’d use PivotTables or SUMIFS. If you need real-time collaboration, Google Sheets is better; if you need advanced charting and offline power, Excel is stronger.
Frequently Asked Questions
How do I add a region column to existing sales data?
If your data has state or city, create a lookup table (e.g., State in column A, Region in column B). Then use VLOOKUP or XLOOKUP. With states in D2:D100 and lookup table in H2:I20, in E2 enter: =XLOOKUP(D2,$H$2:$H$20,$I$2:$I$20). For Excel 2019 or earlier, use VLOOKUP: =VLOOKUP(D2,$H$2:$I$20,2,FALSE). This returns the region for each state.
Can I track multiple years in one template?
Yes. Add a Year column to your data. In the PivotTable, drag Year to the Filters area or use a Slicer for Year. For SUMIFS, add a condition: =SUMIFS(G2:G100,A2:A100,"East",H2:H100,2025) where H is the Year column.
Why does my PivotTable show blank rows for regions?
Blank rows usually mean there are blank cells in the Region column. Remove blanks by filtering the source table, or replace them with a placeholder like “Unassigned”. Also check for trailing spaces; use TRIM function to clean: =TRIM(A2).
Is there a way to automatically email a regional report?
Excel itself cannot send emails, but you can use VBA to create a macro that saves a PDF and sends it via Outlook. However, this requires coding and Outlook setup. For a simpler approach, use Power Automate to schedule a report export. Always save a copy of your workbook before running any macro.
What is the best chart type for regional comparison?
A clustered bar chart works well because it shows revenue for each region side-by-side. For market share, use a pie chart. To create a bar chart, select the PivotTable, go to Insert > Bar Chart. For a dynamic chart that updates with the Slicer, ensure the PivotTable is the chart’s source.
How do I protect the formulas so others don’t break them?
Go to Review > Protect Sheet. Before protecting, unlock the cells where users input data (select those cells, right-click > Format Cells > Protection > uncheck Locked). Then protect the sheet with a password. This prevents accidental edits to formulas.
Related
- Excel Instrument Repair Vernon CT: Full Guide
- Excel Interpolate Between Numbers: How To
- Excel Income and Expense Tracking Spreadsheet: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Text Excel File: How to Open, Import, and Save DataTo open a text file in Excel for Microsoft 365 on Windows, use Data > Get Data >…
- Split by Position in Power Query: A Practical GuideYes. In Excel for Microsoft 365, open the Power Query Editor, select the column, then choose Home >…
- Jump to a Row Number in Excel: Go To, Name Box, and MoreTo jump to a specific row in Excel for Microsoft 365 on Windows, press Ctrl+G, type a cell…
- How to Mail Merge in Word: A Complete Step-by-Step GuideTo mail merge in Word, prepare a recipient list—usually an Excel workbook—then use the Mailings tab to connect…
- LibreOffice: How to Merge Table Cells in Writer and CalcYes. In LibreOffice Writer, select adjacent cells in a table and choose Table > Merge Cells; in Calc,…
- How to Make One Page in Word Have Two Text ColumnsTo make just one page in Word have two columns, isolate it with section breaks, click inside that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.