Build a Simple Sales Report in Excel

A simple sales report in Excel turns a list of orders into a few clear answers: how much you sold, where, when and through whom. You need a Revenue column, SUM and SUMIFS for the totals, MAX for the top order and, if you like, a pivot table. This guide builds one from eight sample orders. It is the kind of task you'll meet in sales and operations roles and in Excel job tests.

Start with clean order data#

A good report starts with one row per order and one column per detail. Don't put totals, blank rows or notes inside the data, because formulas and pivot tables read every row.

Revenue is units times price. In G2, type =E2*F2 and fill it down to G9 with Ctrl+D (Mac: ⌘D).

Turning the range into a table with Ctrl+T (Mac: ⌘T) is a good habit: new orders join the table, and formulas and pivots that use it pick them up.

Headline numbers: total revenue, orders and average order#

Put the headline numbers at the top of the summary, where a reader looks first.

COUNTA counts filled cells, so it counts orders whatever is in them. AVERAGE adds the revenue and divides by the number of orders.

Revenue by region with SUMIFS#

SUMIFS adds the numbers in rows that match one or more conditions. List the regions in J2:J4, then in K2 write:

=SUMIFS($G$2:$G$9,$B$2:$B$9,J2)

The first range is what to add (Revenue). Then comes a pair: the range to check (Region) and what it must match (the name in J2). The $ signs lock the data ranges, so you can fill the formula down to K4.

The share column divides each region by the grand total: =K2/$K$5, where K5 holds =SUM(K2:K4). Lock K5 so every row divides by the same cell, and format the column as a percentage. A quick check: the region totals must add up to the total revenue in the headline. The SUMIFS function guide lists more criteria you can use.

With one condition, SUMIF works too: =SUMIF($B$2:$B$9,J2,$G$2:$G$9). Note the different argument order: SUMIF puts the numbers to add last.

Revenue by month without a helper column#

To total a month, give SUMIFS two conditions on the same Date column: on or after the first day of the month, and before the first day of the next month.

=SUMIFS(G2:G9,A2:A9,">="&DATE(2025,1,1),A2:A9,"<"&DATE(2025,2,1))

The & joins the comparison sign to the date, so the criteria reads "on or after 1/1/2025". January gives 10,080 and February gives 9,000. Because the formula reads the dates themselves, it stays right if someone sorts the orders differently.

The same pattern works for any period: a quarter, a week or a custom date range typed into two cells.

Top performers: the biggest order and who made it#

MAX finds the largest revenue. To find the rep who made it, look that amount up in the Revenue column and return the Rep column:

=XLOOKUP(MAX(G2:G9),G2:G9,C2:C9)

The result is Chloe, with 4,500. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. In older versions, use =INDEX(C2:C9,MATCH(MAX(G2:G9),G2:G9,0)).

Totals by rep use the same SUMIFS shape with the Rep column: Chloe 6,660, Dev 5,040, Ana 3,780, Ben 3,600. Two conditions answer narrower questions. North laptops, for example, are =SUMIFS(G2:G9,B2:B9,"North",D2:D9,"Laptop"), which gives 6,300. That's the task in the exercise on this page.

Build the same report with a pivot table#

A pivot table summarizes the orders without any formulas. It is the faster option when you want to explore the data.

1. Click any cell in the order data.
2. Choose Insert > PivotTable, keep New Worksheet and click OK.
3. In the PivotTable Fields panel, drag Region to Rows and Revenue to Values.
4. Drag Product to Columns to split each region by product.
5. To show shares, click the ▾ on Sum of Revenue, choose Value Field Settings, open Show Values As and pick % of Grand Total.

Pivot tables don't update by themselves: after you add orders, click PivotTable Analyze > Refresh (on a Mac, the tab is PivotTable Analyze or PivotTable). See pivot table vs SUMIFS for when each is the better tool.

Make the report easy to read#

A report is for someone else, so make the answer obvious:

• Put the summary on its own sheet, with the headline numbers at the top and the raw data on another sheet.
• Format money consistently. Select the revenue cells and press Ctrl+Shift+$ (Mac: Control+Shift+$).
• Add one chart. Select the region table and choose Insert > Recommended Charts, then a clustered column chart. Our guide to Excel charts covers which chart fits which question.
• Label every number with what it is and the period it covers ("Revenue, Jan–Feb 2025").

These are exactly the skills listed in Excel skills for sales and operations roles, and a sales summary like this is a common format in Excel tests for job interviews. Finance teams build the same totals for budgets and forecasts, as described in Excel skills for finance jobs. For more tracking projects, try the inventory tracker.

Practise it#

Do the exercise on this page to total one region and product with SUMIFS. Then take the sales report interview drill, which asks the questions a manager would ask about 15 orders. Brush up on the basics with the SUMIF lesson, and build the pivot version in the first pivot tables lesson.