Excel Sales Tracking Template by Product: How to Build One

How-to basics · Updated November 22, 2026

The best way to track sales by product in Excel is to use a template that combines a data entry sheet (for each sale) with a pivot table or SUMIFS formula that automatically totals revenue and units per product. You can build this yourself in about 15 minutes using a simple layout: columns for Date, Product, Units, Unit Price, and Total, then a summary area that pulls totals with SUMIFS. This article explains exactly how to set that up, what to include, and how to avoid the most common mistakes.

Why a Product-Level Sales Tracker Matters

Tracking sales by product (rather than just total revenue) tells you which items actually drive your business. You might discover that a low-priced accessory has the highest margin, or that a top-selling product is barely profitable once shipping costs are added. A product-level tracker also helps with inventory planning—if you know Product A sells 200 units a month and Product B sells 20, you can order stock accordingly. Without this breakdown, you are essentially managing your business blind.

Core Components of a Sales Tracking Template

A good product-based sales tracker has three parts: a raw data log, a summary report, and a dashboard (optional but useful). Here is what each contains:

  • Data log sheet – one row per sale, with at least: Date, Product Name (or SKU), Quantity, Unit Price, and Total (calculated). Add Customer, Region, or Sales Rep if you want to slice the data later.
  • Summary sheet – uses formulas to total units and revenue per product. A pivot table is the easiest way, but SUMIFS works well if you prefer a static table.
  • Dashboard (optional) – charts showing top products, monthly trends, or product share. This is where you visually see which items are winning.

You can also add a drop-down list for product names (Data Validation) to keep entries consistent—typos like “Shirt” vs. “Shirt ” will break your totals.

How to Build the Summary with SUMIFS (Step-by-Step)

Assume your data log is in a sheet named “Sales”, with products in column A (A2:A1000), units in column B (B2:B1000), and revenue in column C (C2:C1000). On a separate “Summary” sheet, list each product name in A2, A3, etc. Then enter these formulas:

For total units (in cell B2):
=SUMIFS(Sales!B:B, Sales!A:A, A2)

For total revenue (in cell C2):
=SUMIFS(Sales!C:C, Sales!A:A, A2)

Copy the formulas down for each product. The result: if Product “Widget” appears 5 times with units 2, 3, 1, 4, 2, the units cell returns 12; if those rows have revenues 10, 15, 5, 20, 10, the revenue cell returns 60. SUMIFS works in Excel 2019, Excel 2021, and Microsoft 365. On Excel for the web, it works the same. If you have an older version (Excel 2016 or earlier), use SUMIF instead: =SUMIF(Sales!A:A, A2, Sales!B:B).

Using a Pivot Table Instead (Easier for Most People)

Pivot tables are the fastest way to get product totals without writing formulas. Select any cell in your data log, go to Insert > PivotTable, choose “New Worksheet”, then drag “Product” to the Rows area, and drag “Units” and “Revenue” to the Values area. Excel automatically sums them. Refresh the pivot table whenever you add new sales (right-click > Refresh). This method is ideal if you are not comfortable with formulas, and it updates with a single click. The only downside is that a pivot table does not auto-update when you add rows—you must refresh it, whereas SUMIFS formulas recalculate instantly if you reference entire columns.

Common Problems and How to Fix Them

Even a simple template can go wrong. Here are the frequent issues and their solutions:

  • Blank rows or merged cells in the data log – Avoid merging cells in the data log; it breaks sorting and filtering. Keep one row per sale.
  • Inconsistent product names – Use a drop-down list (Data > Data Validation > List) to force exact names. Alternatively, use a SKU column instead of names—SKUs are less likely to have typos.
  • Formulas not updating – Check that calculation is set to Automatic (Formulas > Calculation Options > Automatic). If you used SUMIFS with a specific range like A2:A100, new rows below 100 are ignored—use full columns (A:A) or a larger range.
  • Dates stored as text – If you want monthly or yearly summaries, ensure dates are real dates (format the column as Date). Text dates won’t sort or filter correctly.
  • Pivot table not showing new products – Refresh the pivot table. If you added rows beyond the original data range, change the source range or convert your data to a Table (Ctrl+T) so the pivot table picks up new rows automatically.

How to Choose a Pre-Made Template vs. Building Your Own

If you want a ready-made solution, Microsoft offers a free “Sales Tracking” template in Excel (File > New > search “sales tracking”). It includes a dashboard and charts. However, most pre-made templates are generic—they may not include a “Product” column or may track by customer instead. Before downloading, check that it has a column for product name or SKU. If you need custom fields (like “Order #” or “Channel”), building your own is often faster than adapting someone else’s. A simple custom template with the three sheets described above is usually better than a complex template you have to modify.

Costs and Safety Considerations

Building your own template costs nothing but time. Pre-made templates from Microsoft are free; third-party sites may charge $5–$50, but you can often find free ones with a quick search. When downloading any template from the internet, be cautious: open it in a copy of Excel with macros disabled (File > Options > Trust Center > Macro Settings) if you are unsure of the source, because malicious macros can harm your computer. For your own data, keep a backup copy of your workbook (save a second version each week) to avoid losing sales history if a file gets corrupted. If you share the file with others, use “Protect Sheet” (Review > Protect Sheet) to prevent accidental edits to formulas.

Alternatives to Excel for Product Sales Tracking

Excel is great for small to medium volumes, but if you have thousands of transactions or need real-time collaboration, consider Google Sheets (free, similar formulas, but SUMIFS works the same) or a dedicated CRM/inventory tool like Zoho Inventory, QuickBooks, or Airtable. These tools automate data entry and reporting, but they cost money and require a learning curve. Excel remains the most flexible and offline-friendly option for most small businesses.

Frequently Asked Questions

How do I add a new product to the tracker?
Simply type the product name in the data log (or add it to your drop-down list via Data Validation). The summary formulas or pivot table will pick it up automatically if you used full-column references or refreshed the pivot.

Can I track sales by month and product at the same time?
Yes. In a pivot table, drag “Date” to the Rows area (then group by month: right-click > Group > Months) and drag “Product” to the Columns area, or vice versa. With SUMIFS, you would add a date range criteria, for example =SUMIFS(Sales!C:C, Sales!A:A, A2, Sales!B:B, ">="&DATE(2025,1,1), Sales!B:B, "<="&DATE(2025,1,31)) assuming dates are in column B.

What if I have multiple regions or sales reps?
Add columns for Region or Rep to your data log, then include them as additional criteria in SUMIFS or drag them into the pivot table’s Filter or Rows area. For example, to see sales for a specific rep, add a filter on that column.

Is it better to use a pivot table or formulas?
Pivot tables are easier for ad-hoc analysis and require no formula writing. Formulas give you a fixed report that updates instantly and is easier to share with people who do not know how to refresh. Choose based on your comfort level.

How do I handle refunds or returns?
Add a “Type” column (Sale/Refund) and use negative quantities for refunds. Then your totals will net correctly. Alternatively, keep a separate “Returns” sheet and subtract with a formula.

Can I use this template on a Mac?
Yes. Excel for Mac has the same functions and menus (Insert > PivotTable, etc.). Keyboard shortcuts differ: use Cmd+T for table, Cmd+Z for undo, and Cmd+Option+P for pivot table creation (on some versions).

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.