Tracking Sales in Excel: A Step-by-Step Tutorial

How-to basics · Updated November 20, 2026

To track sales in Excel, make one row for each item sold, record its date, order ID, customer, product, salesperson, quantity, unit price, and discount, then calculate the line total. With quantity in F, unit price in G, discount rate in H, and net sales in I, enter =F2*G2*(1-H2) in I2; for a row with 2 units at $15 and no discount, the result is $30.

How a sales tracker works

A sales tracker is a structured list of transactions that you can filter, total, and summarize. Excel does not automatically know what counts as a sale for your business: you decide which transactions to record and how to define the sales amount. A useful tracker separates the underlying transaction records from the summaries used to review performance. Keeping those records as a tidy table makes it easier to update totals without rebuilding reports each time.

For this tutorial, the example records individual order lines. An order containing two products would have two rows with the same order ID, one for each product. This gives you more detail for product and quantity reporting. If you only need to track order totals and do not need product-level analysis, you can instead use one row per order. Choose one approach and use it consistently; mixing order-level and item-level rows can double-count sales.

Also decide whether your sales figure means the amount before or after discounts, refunds, tax, and shipping. The example calculates the amount after a line discount but before tax, shipping, or later refunds. That is a practical tracking convention, not a universal accounting definition. Label the figure clearly and use the rules your organization uses for its own reporting.

Set up the sales data table

In a blank worksheet, put these headers in row 1: Date, Order ID, Customer, Product, Salesperson, Quantity, Unit Price, Discount, and Net Sales. Enter one transaction line per row beneath the headers. Use a real Excel date in the Date column, a consistent order ID, and numbers for Quantity and Unit Price. Enter discounts as percentages, such as 10% for a ten-percent discount, rather than as the number 10.

Here is a small example. The first record is 5 January 2026, order 1001, customer North, Mug, salesperson Ana, 2 units, $15 each, and no discount. The next is 12 January, order 1002, customer West, Notebook, salesperson Ben, 3 units, $8 each, and a 10% discount. The third is 2 February, order 1003, customer North, Mug, salesperson Ana, 1 unit, $15, and a 20% discount.

Convert the range to an Excel table so filters and formulas are easier to maintain. Select a cell in the data, then choose Insert > Table, confirm the range, and check My table has headers. On Windows, Ctrl+T also opens the Create Table dialog; on Mac, use Command+T. With a table, new rows can inherit the formatting and calculated-column formula. Give the table a meaningful name in Table Design, such as SalesData, if you later want to use structured references.

  • Keep one header row, with no blank header cells or merged cells.
  • Do not put subtotals or explanatory notes inside the transaction list.
  • Use consistent spellings for products and salespeople; extra spaces or alternate spellings can split a report into separate categories.
  • Format dates and currency for readability, but remember that cell formatting does not turn text into a usable date or number.

Calculate net sales for each row

In the example layout, Quantity is in F, Unit Price is in G, Discount is in H, and Net Sales is in I. Enter =F2*G2*(1-H2) in I2 and fill it down the column. The example rows return $30.00, $21.60, and $12.00: 3 notebooks at $8 with a 10% discount equal $21.60, and one $15 mug with a 20% discount equals $12. If the data is an Excel table, entering the formula in the first row of the Net Sales column will usually fill the calculated column for the other table rows.

These results total $63.60 across all three example lines. With Net Sales in I2:I4, enter =SUM(I2:I4) in a separate summary cell; it returns $63.60. Keep summary cells outside the source table so that they are not mistaken for transaction records when filtering or creating a PivotTable.

Check how your source data represents discounts before using this calculation. If a discount is a fixed dollar amount rather than a percentage, the formula needs to subtract that amount instead. Likewise, if the source already contains the final line total, do not multiply it by quantity again. A formula can calculate consistently and still be wrong if its inputs or assumptions do not match the data.

Summarize sales by month or salesperson

A quick total is useful, but most tracking questions involve a category or time period. A PivotTable is a flexible way to summarize a clean table: select a cell in SalesData, choose Insert > PivotTable, and place the PivotTable on a new worksheet. Drag Net Sales to Values, Date to Rows, and Salesperson or Product to Columns or Filters. If Excel displays individual dates, right-click a date in the PivotTable and choose Group, then select Months and, where relevant, Years. Grouping by year as well as month avoids combining January from different years.

For a formula-based monthly total, assume dates are in A2:A4 and net sales are in I2:I4. This January 2026 example uses a start date inclusive and the first day of the next month exclusive: =SUMIFS($I$2:$I$4,$A$2:$A$4,">="&DATE(2026,1,1),$A$2:$A$4,"<"&DATE(2026,2,1)). It returns $51.60, the $30.00 and $21.60 January lines. The date boundaries include any time values recorded on those dates while excluding 1 February. For a reusable report, refer to cells containing the chosen month’s start and next month’s start instead of typing dates into the formula.

PivotTables are often easier when you need to change the view, while formulas can be convenient for a fixed dashboard layout. A PivotTable does not necessarily update its cached results the moment source data changes; use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If new rows are added outside the source table or range, confirm they are included in the PivotTable source.

Filter, format, and visualize the tracker

Excel tables add filter controls to the header row. Use them to display a particular month, salesperson, or product without deleting other records. Sorting can help inspect the largest or most recent transactions, but sort the whole table rather than a single column; sorting one column alone can separate values from their matching order IDs and dates.

Use number formats to make data easier to scan. Format Date as a date, Unit Price and Net Sales as currency, and Discount as a percentage. Formatting changes how a value appears, not its underlying value. For example, a cell containing the text “10%” may not behave like the numeric percentage 10% in a calculation. If a total seems wrong, check whether the source cells are numbers and dates rather than text.

A chart can show a trend or make comparisons easier to spot. Create a PivotChart from a PivotTable, or select a small summary range and use Insert > Recommended Charts. A line chart is generally suitable for sales by month, while a bar or column chart can compare products or salespeople. Avoid charting every transaction when the question is about a trend; summarize the data first so the visual remains readable. State the period and sales definition in the chart title or nearby labels.

Keep records accurate and useful

Most spreadsheet errors arise from inconsistent inputs or unclear rules rather than complicated formulas. Agree on whether a row represents an order or an order line, how returns are recorded, and whether a sale belongs to its order date, invoice date, or another date. If a return occurs later, you might record it as a separate negative transaction or maintain a separate returns field; choose a method that can be explained and applied consistently.

Useful checks include comparing a sample of calculated rows with the original orders, looking for blank dates or order IDs, and checking whether quantities or prices are unexpectedly negative or zero. Data > Data Validation can restrict entries, for example by requiring whole numbers for quantity or a date within an allowed range. Validation can reduce accidental errors, but it is not a substitute for reviewing imported data, and pasted values may not behave like manually entered values.

Keep an unchanged copy of the source data before making large edits, and restrict access if customer or commercial information should not be widely shared. Avoid collecting personal details the report does not need. Excel files can be copied, emailed, or stored in shared locations, so use the storage and access controls appropriate to your organization. A sales spreadsheet is a reporting tool, not automatically a secure customer database or an official accounting record.

When Excel is enough—and when to move on

Excel is often a sensible starting point when a small team needs a flexible tracker, a manageable number of records, and reports that someone can maintain. It supports custom columns, filters, formulas, PivotTables, and charts without requiring a dedicated sales system. The main cost is the time needed to enter, check, protect, and reconcile the information. Excel itself does not verify that an order was actually paid or synchronize every sales channel automatically.

As the process grows, consider whether a customer relationship management system, point-of-sale tool, or accounting platform better fits the work. These systems may provide shared records, permissions, workflow automation, or integrations, depending on the product and plan. They also require setup and ongoing administration, and their capabilities and prices vary. A spreadsheet can still be useful for exports and analysis, but avoid maintaining competing versions of the same record in several places without a clear source of truth.

Frequently Asked Questions

How do I track daily sales in Excel?
Enter each transaction or order line as a new row, use a real date in the Date column, and calculate its sales amount in a dedicated column. Filter by date or summarize the date field in a PivotTable to review daily totals.

Should each sale be one row or should each product be one row?
Use one row per order if you only need order-level totals. Use one row per order line if you need product, quantity, or item-level analysis. For orders with multiple products, the line-item approach repeats the order ID, so do not sum an order-level amount that has been repeated on every line.

How can I calculate monthly sales in Excel?
Use a PivotTable grouped by month and year, or use SUMIFS with a start date and the first date of the next month as boundaries. Check that the date column contains actual Excel dates and that the sales range aligns row for row with it.

Why is my Excel sales total wrong?
Check for numbers or dates stored as text, missing or duplicated rows, discounts entered in the wrong format, and a mismatch between order totals and line totals. Also confirm that the formula range includes every data row and that the tracker’s definition of sales matches the report you are comparing it with.

Can Excel automatically track sales from an online store?
Not by itself in a basic workbook. You can import or paste an export from a store, or use an integration available in your organization’s software, but the process depends on the store and tools involved. Check that imported columns, dates, refunds, and identifiers map correctly before relying on the totals.

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.