Dead Stock % in Excel: Measure, Monitor, and Control Inventory That Isn’t Moving

Build the Dead Stock % Table and Chart

1. Confirm the columns you need in the dataset

• Month End (the month you are reporting on)
• On-Hand Value
• Last Movement Date

2. Create a Months Since Last Movement column

• Insert a new helper column to calculate how many months have passed since the item last moved.
• Use the hidden Excel function DATEDIF.
• Structure it as:
  • Start date: Last Movement Date
  • End date: Month End
  • Unit: “m” (months)
• This returns the number of months between the two dates (it counts full month boundaries, not exact days).

3. Create a Dead Stock Value column

• Add another helper column to flag dead stock based on a threshold.
• Use an IF statement:
  • If Months Since Last Movement is greater than or equal to 6 (or your chosen threshold), return the On-Hand Value.
  • Otherwise return 0.
• Result: you now have the on-hand dollars tied only to inventory that has not moved for 6+ months.

4. Create a unique Month End list for the summary table

• Build a separate summary section.
• Use UNIQUE on the Month End column to generate one row per month.
• Format the month values as dates.
• Copy and paste values if you want to “lock” the list.

5. Add summary table headers

Create columns like:

• Inventory Value
• Dead Stock Value
• Dead Stock %
• Target

6. Calculate total Inventory Value by month

• Use SUMIFS to sum On-Hand Value for each Month End.
• Criteria:
  • Month End equals the month in your summary table row.

7. Calculate total Dead Stock Value by month

• Use SUMIFS to sum your Dead Stock Value helper column by Month End.
• Criteria:
  • Month End equals the month in your summary table row.

8. Calculate Dead Stock %

• Divide Dead Stock Value by Inventory Value for each month.
• Format as a percentage.

9. Add a Target line

• Enter your target Dead Stock % in the first target cell (example: 4.0%).
• In the rows below, reference the cell above so the target stays consistent down the column.
• This makes the target easy to adjust later and keeps the chart dynamic.

10. Build the combo chart

• Select the Month End column plus:
  • Dead Stock Value
  • Dead Stock %
  • Target
• Do not include the total Inventory Value in the chart selection.
• Insert a Combo Chart.
• Set:
  • Dead Stock Value as columns
  • Dead Stock % as a line on the secondary axis
  • Target as a line on the secondary axis

11. Clean up the chart formatting

• Change the target line to:
  • thinner line weight
  • black color
  • dashed style
• Add data labels to the Dead Stock % line only (not everything).
• If labels look noisy:
  • format the label background fill
  • bold the label text if needed