Excel for Ecommerce: Orders, Margins and Stock

Excel for ecommerce is about answering the same questions every day: how much did each channel sell, what is the average order worth, which products make money and what needs reordering. Store and marketplace platforms export the raw orders, and Excel turns them into answers with a few functions. This guide walks through each task on a small online shop, so you can use it at work or prepare for an ecommerce or online retail Excel test.

What do ecommerce teams use Excel for?#

Ecommerce teams use Excel to combine and summarise exports that the selling platforms don't join up for them. Typical jobs:

• Sales reporting: revenue and orders by channel, product, day or week.
• Unit economics: average order value, gross margin and profit per order.
• Growth tracking: week-over-week and year-over-year percent change.
• Inventory: stock levels, reorder flags and days of cover.
• Cleaning exports: trimming spaces, fixing numbers stored as text, matching SKUs between files.

Everything below uses two tables. The orders table has one row per order:

The Products sheet has one row per SKU:

Revenue by channel with SUMIF and SUMIFS#

SUMIF adds the revenue for one channel, and SUMIFS adds it with several conditions. Website revenue:

=SUMIF(B2:B8,"Website",E2:E8)

The result is 241 (50 + 45 + 56 + 90). For Website sales of one product, add a second condition with SUMIFS, which puts the range to add first:

=SUMIFS(E2:E8,B2:B8,"Website",C2:C8,"HD-03")

The result is 135 (45 + 90). With a date column, the same pattern gives revenue for one week: add a pair like A2:A500,">="&H1 for the start date. The SUMIF lesson covers the basics, and the SUMIFS function guide covers every argument. Revenue by channel is the backbone of Excel for sales operations too.

Average order value#

Average order value (AOV) is total revenue divided by the number of orders. For the Website channel:

=SUMIF(B2:B8,"Website",E2:E8)/COUNTIF(B2:B8,"Website")

The result is 60.25 (241 ÷ 4 orders). For the whole shop, =SUM(E2:E8)/COUNTA(A2:A8) gives 355 ÷ 7 = 50.71.

If your export has one row per line item rather than per order, an order with three products appears three times. Count distinct orders first, for example with =COUNTA(UNIQUE(A2:A500)) in Excel 2021 or Microsoft 365, or the AOV will be too low.

Margins: pull unit costs with XLOOKUP#

Gross margin needs the unit cost, which lives in the Products table, so join the two tables with XLOOKUP. The cost of the goods in order 5001:

=D2*XLOOKUP(C2,Products!$A$2:$A$4,Products!$D$2:$D$4)

XLOOKUP finds TS-01 in the SKU column and returns its unit cost, 9. Times 2 units, the result is 18, so the order's gross profit is 50 - 18 = 32. Fill the formula down to cost every order. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365; in older versions, use VLOOKUP with FALSE. The XLOOKUP lesson and the XLOOKUP function guide show the full syntax, including a fourth argument like "Unknown SKU" for SKUs that aren't in the product list.

Gross margin % per product is (price - cost) / price:

On the Products sheet, =(C2-D2)/C2 gives 0.64 for the T-shirt. Format it as a percentage. Don't confuse margin with markup, which divides by cost: the T-shirt's markup is (25 - 9) / 9 = 178%.

Week-over-week growth with percent change#

Percent change compares two periods: (new - old) / old. If last week's revenue in B2 was 4,200 and this week's in C2 is 4,830:

=(C2-B2)/B2

The result is 0.15, or 15%. Always divide by the older period. When last week was 0, the formula returns #DIV/0!, so guard it with =IF(B2=0,"",(C2-B2)/B2). The percentages and % change lesson practises these formulas, including growing a number by a percent for a forecast.

Stock: reorder flags and days of cover#

For a small catalogue, two formulas keep stock under control. On the Products sheet, add a reorder point in column F. For the hoodie in row 4, flag whether to order:

=IF(E4<=F4,"Reorder","OK")

If the hoodie's reorder point in F4 is 10, its stock of 6 returns Reorder. Days of cover estimates how long stock lasts at the current sales rate. If the hoodie sells about 0.5 units a day:

=E4/0.5

That gives 12 days. In a real sheet, put the daily sales rate in its own column, for example units sold in the last 28 days divided by 28, and point the formula at it. Units sold per SKU come straight from the orders table: =SUMIF(C2:C8,"TS-01",D2:D8) returns 6 T-shirts.

Clean marketplace and store exports#

Exports from different platforms rarely match perfectly, so clean them before you join them. Watch for:

• Extra spaces in SKUs and channel names. =TRIM(C2) removes them, so "TS-01 " matches "TS-01".
• Numbers stored as text, which SUMIF skips. Convert them with VALUE or Data > Text to Columns.
• Different SKU formats between files, such as "ts-01" and "TS01". XLOOKUP isn't case-sensitive, but it does see the missing hyphen.
• Duplicate orders after combining files. Use Data > Remove Duplicates on the order ID.

These cleaning and joining skills overlap with analyst roles; see Excel skills for data analysts and Excel skills for business analysts. For the next level, such as dynamic arrays and PivotTables, read advanced Excel skills for jobs, and see how the same toolkit works for client projects in Excel for consultants.

Practise it#

Start with the exercise on this page, then practise SUMIF in a live spreadsheet, where you type each formula and it is checked instantly. Next, join tables with the XLOOKUP lesson and measure growth with percentages and % change.