Excel guide · 4 min read
How to find dead stock in Excel
=MAXIFS(sale_dates, sale_skus, A2), subtract it from a report date to get days since the last sale, and flag anything past your cut-off (180 days here) as dead. A result of 0 from MAXIFS means the SKU never sold. In the sample, 3 of 8 SKUs are dead, holding 2,400 of 7,120 in stock value at cost, or 33.7%.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | On hand | Unit cost | Last sale | Days since | Age bucket | Stock value | Sale date | SKU | Report date | ||
| 2 | LMP-01 | 40 | 18 | 46317 | 9 | Active | 720 | Aug 19, 2025 | SHF-20 | Dead after (days) | ||
| 3 | LMP-02 | 25 | 22 | 46188 | 138 | 90-179 | 550 | Jan 12, 2026 | CHR-10 | Carrying cost % a year | ||
| 4 | CHR-10 | 12 | 85 | 46083 | 243 | 180-364 | 1020 | Mar 2, 2026 | CHR-10 | |||
| 5 | CHR-11 | 30 | 64 | 46300 | 26 | Active | 1920 | May 20, 2026 | LMP-02 | |||
| 6 | SHF-20 | 18 | 40 | 45888 | 438 | 365+ | 720 | Jun 15, 2026 | LMP-02 | |||
| 7 | SHF-21 | 55 | 12 | 0 | Never sold | 660 | Jul 30, 2026 | RUG-30 | ||||
| 8 | RUG-30 | 9 | 120 | 46233 | 93 | 90-179 | 1080 | Sep 14, 2026 | LMP-01 | |||
| 9 | RUG-31 | 60 | 7.5 | 46323 | 3 | Active | 450 | Oct 5, 2026 | CHR-11 | |||
| 10 | Oct 22, 2026 | LMP-01 | ||||||||||
| 11 | Oct 28, 2026 | RUG-31 |
Download this sheet as an Excel practice file (.xlsx)
Set up the stock list and the sales log
Two tables: the stock list in A:G with SKU, units on hand and unit cost, and the sales log in I:J with one row per sale, date and SKU, straight from your sales export. The log can be thousands of rows; the formulas don't change.
Put the settings in input cells: the report date in M1 (31 Oct 2026), the number of days without a sale that makes stock dead in M2 (180), and your yearly carrying cost % in M3 (25%). Using a fixed report date instead of TODAY() means a month-end report gives the same answer when you reopen it next week.
Last sale date per SKU with MAXIFS
In D2, =MAXIFS($I$2:$I$11,$J$2:$J$11,A2) returns the latest date in the log for that SKU. Dates are numbers in Excel, so the largest one is the most recent. Fill it down and format column D as a date. CHR-10 sold on 12 Jan and 2 Mar 2026; MAXIFS returns 2 Mar.
MAXIFS needs Excel 2019 or later (or Excel 365). In older versions, use =SUMPRODUCT(MAX(($J$2:$J$11=A2)*$I$2:$I$11)).
Result: 46083 shows as 2 Mar 2026 with the custom d mmm yyyy format
2 Mar 2026 for CHR-10: the later of its two sales.
Why does MAXIFS return 0?
When no row matches, MAXIFS returns 0, which formats as 0 Jan 1900. SHF-21 has no sales in the log at all.
Result: 0
0: SHF-21 never sold. Handle this on purpose instead of showing a 1900 date.
What if every SKU comes back as 0?
Check that the sale dates are real dates and not text. MAXIFS ignores text, so dates imported as text give 0 for every SKU. Fix them with convert text to date first.
Days since the last sale and aging buckets
In E2, =IF(D2=0,"",$M$1-D2) subtracts the last sale from the report date and leaves never-sold SKUs blank. In F2, IFS sorts each SKU into a bucket, testing the never-sold case first and then the oldest bucket down, because IFS stops at the first test that's TRUE:
=IFS(D2=0,"Never sold",E2>=365,"365+",E2>=180,"180-364",E2>=90,"90-179",TRUE,"Active")
The order matters. If E2>=90 came first, a SKU unsold for 438 days would land in 90-179. The final TRUE catches everything else as Active. Fill both columns down.
Result: 365+
365+ for SHF-20: its last sale was on 19 Aug 2025, 438 days before the report date.
What about a SKU that arrived last week?
It will show Never sold even though it's brand new. Add a received date column and measure from whichever is later, the last sale or the receipt, so new stock isn't flagged.
Dead stock value, its share and its carrying cost
Column G is the stock value at cost, =B2*C2. To total the dead stock, compare the report date minus the last sale with the cut-off in M2. Never-sold SKUs are caught automatically: their last sale is 0, so the gap is tens of thousands of days.
The result is 2,400: CHR-10 (1,020), SHF-20 (720) and SHF-21 (660). Divide by =SUM(G2:G9) for its share of all stock, 33.7%. Multiply by the carrying cost % in M3 for what holding it costs a year, 600 at 25%. See inventory carrying cost for building that percentage.
Result: 2400
2,400: the value at cost of SKUs with no sale in 180 days or more, including the one that never sold.
How do I list the dead SKUs, biggest value first?
In Excel 365, FILTER keeps the dead rows and SORTBY orders them by value.
Result: spills 3 rows × 7 columns
| CHR-10 | 12 | 85 | 46083 | 243 | 180-364 | 1020 |
| SHF-20 | 18 | 40 | 45888 | 438 | 365+ | 720 |
| SHF-21 | 55 | 12 | 0 | Never sold | 660 |
Spills three rows: CHR-10 (1,020), SHF-20 (720) and SHF-21 (660).
The watch list and what to do with it
The 90-179 bucket is your early warning: LMP-02 at 138 days and RUG-30 at 93 days hold 1,630 between them. They're not dead yet, which is the best time to act, with a promotion or a stop on reorders. =SUMIFS(G2:G9,F2:F9,"90-179") totals the bucket.
For stock that is dead, the usual options are a markdown, a bundle, returning it to the supplier or selling it off. Price the markdown with markdown percentage. How and when to write stock down in your books is a question for your accountant. To keep the rest of the range from drifting the same way, give each item min and max levels and order only up to the max: see Excel formulas for inventory.
Result: 1630
1,630: the value at cost of SKUs unsold for 90 to 179 days.
Common mistakes
Showing 0 Jan 1900 as a last sale date
MAXIFS returns 0 when a SKU never sold. Test for 0 and label it Never sold.
Putting IFS tests in the wrong order
Test the oldest bucket first. IFS returns the first TRUE, so a low threshold first swallows everything.
Using TODAY() in a month-end report
Put the report date in a cell so the result doesn't change every time you open the file.
Valuing dead stock at selling price
Use unit cost. The money tied up is what you paid, not what you hoped to sell it for.
FAQ
What counts as dead stock?
Stock with no sales for a set period, often somewhere between 6 and 12 months. The cut-off is your choice; keep it in an input cell.
How do I find the last sale date for each item in Excel?
=MAXIFS(sale_dates, sale_skus, A2) returns the most recent date for the SKU in A2. It returns 0 if the SKU has no sales.
How do I calculate days since last sale in Excel?
Subtract the last sale date from a report date: =$M$1-D2. Format the result as a number, not a date.
What's the difference between dead stock and slow-moving stock?
Slow-moving stock still sells, just slowly; dead stock hasn't sold at all in your cut-off period. The 90-179 bucket is a simple slow-moving list.
Functions used
MAXIFSLargest value that matchesIFPicks a result from a testIFSFirst true test winsSUMPRODUCTMultiply then addSUMAdds numbersSUMIFSAdds with several conditionsFILTERKeeps matching rows, spillsSORTBYSorts by other columns, spillsDATEBuilds a date
Related guides
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate inventory carrying cost in Excel
- How to calculate weeks of supply in Excel
- How to calculate markdown percentage in Excel
- How to calculate days of inventory on hand in Excel
- How to calculate backorder rate in Excel
- How to calculate debt-to-income ratio in Excel
Updated