Inventory Accuracy % in Excel:
Monthly and Location Tracking Dashboard

Build an Inventory Accuracy % Report in Excel

1. Set up the helper columns in the dataset

• Confirm your dataset includes Count Date, Location, On Hand, and Physical Count.
• Add three helper fields to support the reporting view:
  • Variance between system on-hand and physical count using ABS
  • Accuracy flag using IF and a tolerance value
  • Month end date using EOMONTH

2. Build a month list for the reporting table

• Create a unique list of month-end dates using UNIQUE from the month field you created with EOMONTH.
• Format the month list as dates so it reads clearly in the report.

3. Build a location list across the top

• Create a unique list of locations using UNIQUE from the Location field.
• Sort the list alphabetically to keep the table consistent.
• Convert the vertical list into horizontal headers using TRANSPOSE.
• Copy and paste values to lock the headers in place.

4. Calculate inventory accuracy for each month-location intersection

• Use COUNTIFS to count the number of inaccurate rows based on:
  • Accuracy flag equals the “inaccurate” value
  • Month equals the row’s month
  • Location equals the column’s location
• Use COUNTIFS again to count the total number of rows for that same month and location, without filtering to only inaccurate rows.
• Convert the “inaccurate rate” into “accuracy” by adjusting the result as shown in the video.
• Format the result as a percentage.

5. Make the table copy-friendly using proper reference locking

• Apply relative and absolute reference locking so:
  • The month reference changes only when you copy down
  • The location reference changes only when you copy across
• Instead of dragging across (which can break structured table references), use copy and paste to fill the grid.

6. Add overall summaries

• Create an overall accuracy by location row:
  • Copy the main calculation and remove the month criteria so it summarizes all months for each location.
• Create an overall accuracy by month column:
  • Copy the main calculation and remove the location criteria so it summarizes all locations for each month.
• Use copy down for the monthly overall column where it is safe to do so.

7. Add a target and highlight underperformance

• Enter a target accuracy percentage (example shown: 96%).
• Apply Conditional Formatting:
  • Highlight Cells Rules
  • Less Than
  • Target value
• Select both the main matrix and the summary areas so the highlighting applies everywhere.

8. Display a labeled target without breaking calculations

• If you want the target cell to display text like “Target: 96%” but still behave like a number:
  • Open Format Cells using Ctrl + 1
  • Use Custom number formatting with quoted text so the cell remains numeric while showing a label.