Excel Sales Tracking Template by Customer: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can track sales by customer in Excel using a template that combines a data entry sheet, a customer master list, and pivot tables or SUMIFS formulas to summarize totals per customer. The simplest ready-made approach is to use Excel’s built-in “Customer Sales” template (File > New > search “customer sales”), but you can also build a custom one in about 10 minutes with three sheets: Sales Log, Customers, and a Summary sheet.

Why Track Sales by Customer in Excel?

Businesses track sales by customer to answer questions like: who buys most often, which customers generate the highest revenue, and who has stopped ordering recently. Excel is a common choice because it is already installed on most office computers, requires no subscription beyond the software itself, and lets you control the exact layout. Unlike a CRM like Salesforce or HubSpot, Excel gives you full ownership of your data—no per-seat fees, no cloud dependency, and no forced workflow. The trade-off is that you must maintain the data yourself and learn a few formulas or pivot table steps.

Core Components of a Customer Sales Template

A functional template has three logical parts. The first is a transaction log where each row is one sale. The second is a customer reference table with unique IDs and names. The third is a summary area that aggregates the log by customer. Here is what each contains:

  • Sales Log sheet: columns for Date, Customer ID, Customer Name, Product, Quantity, Unit Price, and Total. Use a table (Ctrl+T) so new rows are automatically included in formulas and pivot tables.
  • Customers sheet: columns for Customer ID, Company Name, Contact Person, Email, and maybe Region or Segment. This avoids retyping the name and enables lookups.
  • Summary sheet: either a pivot table or formulas that total revenue, order count, and average order value per customer.

You can also add a dashboard with charts showing top 10 customers by revenue, monthly trends, and payment status, but those are enhancements, not core requirements.

How to Build a Simple Version with SUMIFS

Assume your Sales Log has headers in row 1, with Customer Name in column B and Total in column G, and data in rows 2 to 100. On a Summary sheet, put customer names in A2:A10 and enter this formula in B2:

=SUMIFS('Sales Log'!$G$2:$G$100,'Sales Log'!$B$2:$B$100,A2)

This sums the Total column for every row where the Customer Name matches A2. Copy the formula down for each customer. The result, for example, if “Acme Corp” appears in three rows with totals 100, 250, and 150, returns 500. For order count, use =COUNTIFS('Sales Log'!$B$2:$B$100,A2). For average order value, divide total by count: =B2/C2.

If you prefer a pivot table, select any cell in the Sales Log table, go to Insert > PivotTable, drag Customer Name to Rows and Total to Values. Change the value field setting to Sum if it defaults to Count. A pivot table automatically updates when you refresh (right-click > Refresh), but you must add new data inside the table range or extend it manually.

Choosing a Template: Built-in vs. Downloadable vs. Custom

You have three main paths. First, Excel’s built-in template: open Excel, click File > New, type “customer sales” in the search box, and pick one. These are free and already formatted, but they often include extra columns you may not need and may not match your exact product list. Second, downloadable templates from sites like Vertex42 or Smartsheet (as Excel files). These are usually more polished but may contain macros or complex formulas; check that they work in your Excel version. Third, building your own as described above. This takes the most time initially but gives you exactly what you need and teaches you the structure so you can modify it later. For most small businesses, a custom template with a table and a pivot table is the most sustainable because you understand every part of it.

Common Problems and How to Fix Them

Several issues come up repeatedly when people use such templates. One is duplicate customer names—if you type “Acme” in one row and “Acme Corp” in another, SUMIFS treats them as different customers. Fix by using a Customer ID column and a data validation dropdown on the Sales Log that pulls from the Customers sheet. Another issue is blank rows or text in numeric columns, which cause formula errors. Use Excel tables and set data types (Home > Number) to avoid this. A third problem is that the template does not update automatically when you add new sales. If you use SUMIFS with a fixed range like $G$2:$G$100, new rows beyond row 100 are ignored. Convert the range to a table reference, for example =SUMIFS(Table1[Total],Table1[Customer Name],A2), which expands automatically. Lastly, users often forget to refresh pivot tables after adding data; set the pivot table to refresh on file open (right-click > PivotTable Options > Data > Refresh data when opening the file).

Comparing Excel to Dedicated CRM Tools

Excel is a good fit for solo entrepreneurs, freelancers, and small teams with under a few hundred customers and simple sales processes. It is free (if you already have Office), offline, and flexible. However, it lacks automation: you must manually enter each sale, there is no automatic email integration, and sharing a file with multiple users often leads to version conflicts. Dedicated tools like HubSpot CRM (free tier) or Zoho CRM offer contact history, deal pipelines, and email tracking, but they have a learning curve and may require paid plans for reporting beyond basic metrics. A middle ground is using Excel for reporting and a simple form (like Microsoft Forms) to capture sales data into the log, but that still requires manual setup. For most people asking this question, Excel is sufficient until they need multi-user real-time collaboration or automated follow-ups.

Cost and Safety Considerations

Excel itself is not free unless you have a Microsoft 365 subscription (about $10 per month for personal, $12.50 per user per month for business) or a one-time purchase of Office Home & Business (around $250). The built-in templates are included. Downloadable templates may be free or cost a few dollars; be cautious with free sites that ask for excessive permissions or bundle macros—only download from reputable sources. When working with customer data, remember that Excel files are not encrypted by default. If you store customer names, emails, or purchase history, protect the file with a password (File > Info > Protect Workbook > Encrypt with Password) and avoid sending it via unencrypted email. Also, if you use macros (VBA) in a downloaded template, save a copy of the original before running anything, because macros cannot be undone with Ctrl+Z. And never share a workbook with sensitive data without removing personal information via File > Info > Check for Issues > Inspect Document.

Frequently Asked Questions

Can I track sales by customer without a pivot table? Yes, use SUMIFS or COUNTIFS as shown above. Pivot tables are convenient for quick analysis, but formulas give you more control over formatting and allow you to place results anywhere.

How do I add a new customer to the dropdown list? Go to the Customers sheet, add the new customer’s details in a new row, then select the cell with the dropdown on the Sales Log, go to Data > Data Validation, and change the Source range to include the new row. If you use a table for Customers, the range updates automatically when you add rows.

What if I have multiple sales to the same customer on the same day? That is fine—each row is a separate transaction. Your summary will show the total for that day. If you want a single row per order, you need an Order ID column and use SUMIFS with that ID.

Can I track sales by customer and product at the same time? Yes, add Product to the Rows area of a pivot table (Customer Name then Product) or use SUMIFS with multiple criteria, e.g., =SUMIFS(TotalRange, CustomerRange, A2, ProductRange, B2).

Why is my SUMIFS formula returning 0 even though there are matching sales? The most common cause is that the Customer Name in the Sales Log has extra spaces or different text case. Use TRIM and PROPER functions to clean data, or check with =EXACT(A2,'Sales Log'!B2).

Is there a way to automatically email a monthly report to each customer? Not with Excel alone. You can use a macro to generate PDFs and send emails via Outlook, but that requires VBA and is beyond a basic template. For that, consider a CRM or a mail merge with Word.

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.