Excel for retail and ecommerce

For shop owners, online sellers and the people who run their stock and pricing. Excel turns a product list with sales and stock counts into the answers you need every week: true margins, which channel pays, which products come back too often, what sells best, and what to reorder before it runs out. Every formula below runs on 30 days of real-looking sales you can edit.

Independent shop owners
Online store and marketplace sellers
Merchandisers and buyers
Inventory and operations leads

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	SKU	Product	Unit cost	Price	Shop units	Online units	Returns	On hand
2	TS-01	Organic crew tee	7.4	28	64	142	19	85
3	HD-02	Zip hoodie	18.9	64	21	58	9	40
4	CP-03	Canvas cap	4.1	22	38	47	3	120
5	TB-04	Tote bag	3.25	18	55	30	1	26
6	BN-05	Beanie	3.8	24	17	76	4	18
7	MG-06	Enamel mug	4.6	19	29	22	5	64
8	SK-07	Crew socks (3 pack)	5.2	21	12	88	6	35
9	PR-08	Art print A3	2.15	30	8	41	7	150
10	JK-09	Rain jacket	34	119	6	23	5	14
11
12	Lead time (days)	10	Safety stock (days)	5	Days in period	30

What Excel does for retail and ecommerce

Know the difference between margin and markup

Both compare price and cost, but they divide by different things. Margin is profit divided by price; markup is profit divided by cost. A tee that costs $7.40 and sells for $28 has a margin of 74% and a markup of 278%. Mixing them up is how shops price "at 50%" and end up with a third of what they planned.

Suppliers and wholesale terms often talk markup (or "keystone", doubling the cost, which is a 100% markup and a 50% margin). Your profit reports should use margin, because it is a share of the sales you actually report.

=(D2-C2)/D2

Result: 0.73571428571 shows as 73.6% with Percent format

The organic tee keeps 73.6% of its price as gross profit. The markup on the same tee, (D2-C2)/C2, is 278%.

Compare sales by channel

When you sell in a shop and online, the question is not just which channel sells more units but which brings in more revenue. Multiply each channel's units by price and add them up, then divide by total revenue to get the online share.

Remember each channel carries different costs: marketplace fees, shipping, card fees, staff. Put those in their own rows before you decide which channel is more profitable.

=SUMPRODUCT(F2:F10,D2:D10)/SUMPRODUCT(E2:E10+F2:F10,D2:D10)

Result: 0.70845946167 shows as 71% with Percent format

Online brings in 71% of revenue ($17,319 of $24,446). Before you move more effort online, take marketplace and shipping costs off that number.

Track the returns rate

Returns are a hidden cost: shipping both ways, staff time, and stock that cannot always be resold. Returns rate is units returned divided by units sold. Track it for the whole shop and per product.

A high rate on one product usually points to a fixable problem: sizing, a misleading photo, or a quality issue. Online sales almost always see more returns than in-store, so compare products within the same channel when you can.

=SUM(G2:G10)/SUM(E2:F10)

Result: 0.075933075933 shows as 7.6% with Percent format

7.6% of units came back overall (59 of 777). The rain jacket is the outlier at 17%: 5 returns from 29 sold.

Find your best seller

Best seller by units is one total across both channels. E2:E10+F2:F10 adds the two columns row by row; MAX finds the biggest total and XLOOKUP returns the product name on that row.

Rank by gross profit too, by replacing the sum with (E2:E10+F2:F10)*(D2:D10-C2:C10). The product that sells the most units is not always the one that earns the most.

=XLOOKUP(MAX(E2:E10+F2:F10),E2:E10+F2:F10,B2:B10)

Result: Organic crew tee

The organic crew tee: 206 sold across the shop and online, more than twice the next product.

Set a reorder point from sales speed and lead time

A reorder point is the stock level at which you order again, so new stock arrives before the old runs out. It is daily sales times the days you need to cover: the supplier's lead time plus a few days of safety stock for slow deliveries or a busy week.

Here the lead time (B12), safety days (D12) and length of the sales period (F12) sit in cells, so the formula updates when your supplier gets slower or a holiday rush starts.

=ROUNDUP((E5+F5)/F12*(B12+D12),0)

Result: 43

Tote bags sell 2.8 a day, so reorder at 43. There are only 26 on hand: order now.

List everything that needs reordering

Instead of checking rows one by one, FILTER keeps the products whose stock is at or below their reorder point, and TEXTJOIN turns them into a short list you can paste into an email to your supplier.

This needs Excel 365 or 2021 for FILTER. In older versions, add a Status column with IF and filter the table on "Reorder".

=TEXTJOIN(", ",TRUE,FILTER(B2:B10,H2:H10<=(E2:E10+F2:F10)/F12*(B12+D12)))

Result: Organic crew tee, Tote bag, Beanie, Crew socks (3 pack), Rain jacket

Five products are at or below their reorder points: Organic crew tee, Tote bag, Beanie, Crew socks (3 pack) and Rain jacket.

See how many days of stock you have left

Days of cover is stock on hand divided by daily sales. It is easier to act on than a raw count: 18 beanies sounds fine until you see they sell three a day.

Compare it with the lead time. If days of cover is shorter than the lead time, you will run out before a new order can arrive.

=ROUND(H6/((E6+F6)/F12),1)

Result: 5.8

Only 5.8 days of beanies left, and a new order takes 10 days to arrive. Expect to be out of stock for about four days.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many units were sold in total, shop and online?

Your formula for exercise 1

Show answer
=SUM(E2:F10) = 777. E2:F10 covers both channels, so one SUM adds them all.
2What is the margin on the zip hoodie (row 3)?

Your formula for exercise 2

Show answer
=(D3-C3)/D3 = 0.7046875. Margin divides by price: (64 - 18.90) / 64 is about 70.5%.
3What is the markup on the canvas cap (row 4)?

Your formula for exercise 3

Show answer
=(D4-C4)/C4 = 4.3658536585. Markup divides by cost, so it is always bigger than the margin: about 437% here.
4How much revenue came from online sales?

Your formula for exercise 4

Show answer
=SUMPRODUCT(F2:F10,D2:D10) = 17319. SUMPRODUCT multiplies each pair and adds them: $17,319.
5What is the returns rate for the rain jacket (row 10)?

Your formula for exercise 5

Show answer
=G10/(E10+F10) = 0.1724137931. The brackets add the two channels before dividing: 5 / 29 is about 17%.
6What is all the stock on hand worth at cost?

Your formula for exercise 6

Show answer
=SUMPRODUCT(H2:H10,C2:C10) = 3304.8. Stock is valued at what you paid for it, not the selling price.
7How many products have a returns rate above 10%?

Your formula for exercise 7

Show answer
=SUMPRODUCT(--(G2:G10/(E2:E10+F2:F10)>0.1)) = 3. Three products: the zip hoodie (11.4%), the art print (14.3%) and the rain jacket (17.2%). The tee just misses at 9.2%.
8What price gives the enamel mug (cost in C7) a 60% margin? Round to cents.

Your formula for exercise 8

Show answer
=ROUND(C7/(1-0.6),2) = 11.5. 4.60 / 0.4 = 11.50. The current $19 price is well above it.

Free templates for retail and ecommerce

Guided project: Sales by channel report

Split a month of orders by channel: revenue, share, average order value, returns, and the channel that pays most.

5 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate profit margin for products in Excel?

For one product, =(price-cost)/price, formatted as a percentage. For a whole range, =SUMPRODUCT(units,price-cost)/SUMPRODUCT(units,price) gives the margin weighted by what actually sold.

How do I calculate the returns rate in Excel?

Divide units returned by units sold: =returns/sold. Use units for product-level problems (sizing, quality) and orders for channel-level comparisons. Format as a percentage.

How do I find best-selling products in Excel?

Add the units across channels and use XLOOKUP(MAX(...)) for the top one, LARGE for the top 3, or SORT(range,column,-1) in Excel 365 to list every product from best to worst seller.

Can Excel manage inventory for an online store?

For a few hundred products, yes: a sheet with on hand, on order, sales and lead times gives you reorder points and days of cover. Once you sell across several channels with live stock sync, use your store or inventory software for counts and export to Excel for analysis.

Excel for other fields

Updated November 2, 2026