Excel for Retail Jobs: Sales, Stock and Margins
By ExcelReady TeamUpdated 6 min read
The short answer
The Excel skills used most in retail are totalling sales by category or store with SUMIF, comparing this week with last week using percent change, working out margin and markup from price and cost, and flagging low stock with conditional formatting. Add COUNTIF for counting items below a reorder level and you can handle most store reports.
Key takeaways
- SUMIF totals sales for one category, store or department from a long list.
- Percent change is (this week - last week) / last week, formatted as a percentage.
- Margin divides profit by price; markup divides profit by cost, so the same item has two different percentages.
- Conditional formatting colours low-stock items so they can't be missed.
On this page+
Excel for retail work comes down to three questions a store manager asks every week: what sold, how does it compare with last week, and what do we need to reorder? SUMIF, percent change and conditional formatting answer all three. This guide is for retail assistants, supervisors, store managers and merchandising staff, and every example uses one small sales sheet you can type in yourself.
What retail teams use Excel for#
Point-of-sale and stock systems can usually export to Excel, and that's where a lot of retail reporting happens. Common files include:
- Weekly sales reports: units and revenue by item, category or store.
- Stock lists: quantity on hand, reorder level and items on order. Excel skills for supply chain roles covers reordering in more depth.
- Price lists: cost, selling price, margin and discounts.
- Staff rotas and sales targets by person or shift.
The examples use this weekly sheet in A1:E7:
| SKU | Category | Units | Revenue | Stock |
|---|---|---|---|---|
| S-01 | Shoes | 12 | 960 | 4 |
| S-02 | Shirts | 30 | 750 | 25 |
| S-03 | Shoes | 8 | 720 | 15 |
| S-04 | Bags | 5 | 400 | 2 |
| S-05 | Shirts | 22 | 572 | 30 |
| S-06 | Bags | 9 | 630 | 6 |
Sales by category with SUMIF#
SUMIF adds the numbers in rows that match one condition. It turns an item list into a category summary.
=SUMIF(B2:B7,"Shoes",D2:D7)returns 1680 (960 + 720). The three parts are the range to check (Category), the condition ("Shoes") and the range to add (Revenue). Change the condition to "Shirts" and you get 1322; "Bags" gives 1030. All three add up to the store total, =SUM(D2:D7), which is 4032.
To build a category table, list the categories in G2:G4 and type this in H2:
=SUMIF($B$2:$B$7,G2,$D$2:$D$7)Fill it down (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the data ranges fixed while G2 changes to G3 and G4. For two conditions, such as category and store, use SUMIFS. The SUMIF function guide has more examples.
If you need every category at once and don't want to type the list, a pivot table does it in a few clicks. Select the data, choose Insert > PivotTable, drag Category to Rows and Revenue to Values. You get Bags 1030, Shirts 1322 and Shoes 1680, with a grand total of 4032. Add Units to Values as well to see both measures side by side. Refresh it (Data > Refresh All) when next week's sales arrive.
Week-over-week change with percent change#
Percent change shows how much sales grew or fell compared with an earlier period. The formula is:
=(this_week-last_week)/last_weekIf last week's store total was 3600 and this week's is 4032, then =(4032-3600)/3600 returns 0.12. Format it as a percentage (Ctrl+Shift+%; Mac: Control+Shift+%) to show 12%. A negative result means sales fell.
Two common mistakes: dividing by this week instead of last week, and multiplying by 100 and then also applying percentage format, which shows 1200%. The same formula works for comparing a store with a target or this year with last year. See Excel skills for sales operations for target tracking.
Margin and markup#
Margin and markup both measure profit on an item, but they divide by different numbers. Mixing them up is one of the most common retail maths errors.
| Measure | Formula | Item costs 48, sells for 80 |
|---|---|---|
| Profit | =Price-Cost | 32 |
| Margin | =(Price-Cost)/Price | 40% |
| Markup | =(Price-Cost)/Cost | 66.7% |
To set a price from a target margin, divide the cost by one minus the margin: =Cost/(1-Margin). A 48 item at a 40% margin gives =48/(1-40%), which is 80. Keep the target margin in its own cell and lock it with $ when you fill a price column down.
Discounts work the same way: a 25% discount on 80 is =80*(1-25%), which is 60. At that price, the margin falls to (60 - 48) ÷ 60 = 20%.
Counting and flagging low stock#
COUNTIF counts the items that meet a condition, and conditional formatting colours them.
=COUNTIF(E2:E7,"<5")returns 2: S-01 (4 left) and S-04 (2 left). To colour them:
- Select E2:E7.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than.
- Type 5 and choose a red fill.
When stock changes, the colours update by themselves. To colour the whole row instead, use a formula rule on A2:E7: =$E2<5. You can also add a reorder flag column with =IF(E2<5,"Reorder","OK"). Better still, keep each item's reorder level in its own column, say F, and compare against it: =IF(E2<F2,"Reorder","OK"). Fast sellers can then have a higher reorder level than slow ones, and the same formula works for every row.
Sell-through and best sellers#
Sell-through shows what share of the available stock sold. It's units sold divided by units sold plus units left:
=C2/(C2+E2)For S-01 that's 12 ÷ (12 + 4) = 75%, a strong seller that's nearly out. S-05 sold 22 with 30 left, a sell-through of about 42%. To find the best seller by units, =INDEX(A2:A7,MATCH(MAX(C2:C7),C2:C7,0)) returns S-02 (30 units). Sorting the table by Units (Data > Sort, largest to smallest) shows the full ranking.
How to prepare for a retail Excel test#
Retail Excel tests, when they're used, usually focus on a sales sheet: totals by category, a percent change, a margin and a low-stock count. Practise each until it takes a minute or two. Many of the same skills apply to supplier invoices and payments, covered in Excel skills for accounts payable, and to day-to-day record keeping in Excel for bookkeeping. Marketing teams in retail use them too, as Excel skills for marketing jobs shows.
Practise it#
Retail maths is quick to learn when you do it yourself. Practise SUMIF in a live spreadsheet: you type the formula, and it checks it instantly. Then practise percentages and % change, and flag low stock with conditional formatting. To check your basics against the clock, try the basic Excel mock test.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the total revenue for Shoes? Click H2 and use SUMIF to add the Revenue column (D2:D7) where the Category (B2:B7) is Shoes.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Stock | ||||||||
| 2 | Shoes | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 |
Tip: SUMIF checks the Category column, matches Shoes, and adds the Revenue column.
Practise this in a lesson
Frequently asked questions
What Excel skills are needed for retail jobs?
Retail roles use SUM and SUMIF for sales totals, percent change for comparing periods, margin and markup formulas, COUNTIF for stock counts, sorting and filtering, and conditional formatting to flag low stock or top sellers.
How do I calculate margin in Excel?
Margin is profit divided by selling price: =(price-cost)/price. An item that sells for 80 and costs 48 has a margin of (80-48)/80 = 40%. Format the result as a percentage.
What is the difference between margin and markup?
Margin divides profit by the selling price; markup divides profit by the cost. For an item costing 48 and selling for 80, the margin is 40% and the markup is about 66.7%.
How do I highlight low stock in Excel?
Select the stock column, go to Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter your reorder level and choose a fill colour. Cells below that level change colour automatically.