Sample sales data in Excel for business analysis

Sample sales data in Excel lets you check revenue, gross profit and regional comparisons before applying the same method to a company export. The synthetic US order table below produces $6,860 in revenue and $2,386 in gross profit, a 34.8% gross margin. A second version changes one selling price so you can test whether the results move correctly.

These are invented business records for spreadsheet practice. They contain no customer information, and the table is the complete example rather than a promised download. For practice with a different business file, use the neighboring sample Excel data for analysis guide.

Start with a small order table

A fictional US office-supply distributor has the following completed orders. Each order contains one item, so each row is both an order and an order line. All sales are in USD; the example excludes sales tax and assumes no shipping charges, discounts or returns.

Put the headers in A1 through F1 and the records in rows 2 through 7. Enter dates as actual spreadsheet dates and display them in US month/day/year format. The numbers belong in numeric cells, without typed currency labels.

The unit cost is the cost assigned to the sold item for this example. A real export needs an agreed cost definition and the same period as the sale. The separate gross profit margin guide explains that cost boundary in more detail.

You can format these records as an Excel table. Microsoft's table documentation describes headers, filtering and calculated columns. Give the table clear field names, and keep grand totals outside its transaction records.

Calculate revenue and profit from the inputs

Add G for Revenue, H for COGS, I for Gross profit and J for Gross margin. Enter the following formulas in the indicated cells, then fill down through row 7. Type only the part beginning with the equals sign.

G2 =D2*E2
H2 =D2*F2
I2 =G2-H2
J2 =IF(G2=0,"",I2/G2)

Each result follows directly from the input table. Display currency to two decimals when needed and percentages to one decimal place; retain unrounded values in the calculations.

For S-101, 10 × 120 = $1,200 revenue and 10 × 78 = $780 COGS. Subtracting gives $420 gross profit. At company level, 6,860 - 4,474 = $2,386, and 2,386 / 6,860 = 34.8% after rounding.

The total margin formula is:

=SUM(I2:I7)/SUM(G2:G7)

Compare regions without losing the reconciliation

Make a separate summary with region names in L2 through L4. Put regional revenue in M, COGS in N, gross profit in O and margin in P. Microsoft documents SUMIF for adding values whose corresponding rows meet a specified condition:

M2 =SUMIF($C$2:$C$7,L2,$G$2:$G$7)
N2 =SUMIF($C$2:$C$7,L2,$H$2:$H$7)
O2 =M2-N2
P2 =IF(M2=0,"",O2/M2)

Fill the formulas down for the region labels. The pivot table guide covers another way to summarize the same sales rows.

West and East have equal revenue, but West contributes more gross profit because the underlying orders have different cost proportions. Central leads revenue. These are observations about the supplied rows; the table does not establish a territory's long-term performance.

Check that summed regional revenue minus summed order revenue equals zero. Repeat for COGS and gross profit. Finally, prove revenue equals COGS plus gross profit: 6,860 = 4,474 + 2,386. All checks should pass before you create a management chart.

Change one price and predict the result

Keep every input unchanged except S-106's unit selling price: reduce it from $250 to $225. Its quantity remains 6 and unit cost remains $175. This changes revenue by 6 × (225 - 250) = -$150, while COGS stays unchanged.

The revised company margin is 2,236 / 6,710 = 33.3%, rounded to one decimal place. If your workbook changes unit cost, order quantity or another region's revenue, inspect the references. The data comparison in Excel guide covers comparing two supplied versions of a business file.

Where it goes wrong

• Order totals repeated on item rows. A multi-item export can repeat an order-level amount. Confirm the row's meaning before summing it.
• Unit costs subtracted from line revenue. Multiply both unit price and unit cost by the sold quantity before calculating profit.
• Returns and discounts omitted. The practice table excludes them. A company export needs net amounts and a documented treatment of credits.
• Numbers stored as text. Inspect numeric fields after pasting. Resolve text values and blanks before trusting calculated totals.

Covirage is not affiliated with Microsoft.

Check your own sales export in Covirage

Bring a sample of your own order export with quantity, sales value, cost and region columns. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your file.

See Covirage for sales teams for the supporting team context.