Parts Availability Rate: Track Inventory Issues by
Month, Tech, and Part Category

Parts Availability Rate by Month and Technician

1. Create a Month field from the Service Date

• In your dataset, use the Service Date column to extract the month.
  
• In a new column, enter a TEXT formula to pull the month name from the date:
  
  • Use TEXT with the service date cell and the format for month name (four Ms).
    
• Fill the formula down for all rows so every job has a month label.
  

2. Set up the monthly summary table

• Create a small table with these columns:
  
  • Month
    
  • Jobs Requiring Parts
    
  • Parts Available on First Visit
    
  • Availability Rate
    
• In the Month column, type January and drag down to December.
  
• Center the headers and months to keep the table clean.
  

3. Count jobs requiring parts by month using COUNTIFS

• Use COUNTIFS to count jobs where:
  
  • Parts Required equals Yes
    
  • Month equals the month in your summary table
    
• Important setup detail:
  
  • When selecting criteria ranges, click the table column data range, not the Excel sheet letter column header, so the structured reference stays correct.
    
• Copy the COUNTIFS formula down for all months.
  

4. Count jobs with parts available on the first visit by month

• Use COUNTIFS again, this time counting jobs where:
  
  • Parts Available on First Visit equals Yes
    
  • Month equals the month in your summary table
    
• Copy the formula down through December.
  

5. Calculate the monthly availability rate and format it

• In Availability Rate, divide:
  
  • Parts Available on First Visit
    
  • By Jobs Requiring Parts
    
• Format as a percentage using Ctrl + Shift + 5.
  
• Fill the formula down to calculate each month’s rate.
  

6. Add dynamic benchmark lines for zones

• Add three rows (or columns) for benchmark zones:
  
  • Failure Zone
    
  • At Risk
    
  • Operationally Stable
    
• Enter your benchmark percentages once, then make the cells below reference the benchmark cell above so it stays dynamic.
  
• This makes the chart update automatically if you adjust the benchmark values later.
  

7. Create the monthly line chart with benchmark zones

• Highlight Month first.
  
• Hold Ctrl and select:
  
  • Availability Rate
    
  • The three benchmark zone ranges
    
• Insert a line chart using Recommended Charts.
  
• Rename the chart title to something like:
  
  • Parts Availability by Month
    

8. Clean up the chart formatting for visibility

• Adjust the vertical axis minimum to show more variation:
  
  • Set minimum to 0.55 so performance differences are easier to see.
    
• Format the benchmark lines:
  
  • Change line colors for the zone lines
    
  • Make them dashed
    
  • Increase line width slightly so they stand out
    
• Optional:
  
  • Add data labels to the Availability Rate line so the percentages show directly on the chart.
    

9. Build the technician list and parts category list using UNIQUE

• Create a unique list of technicians using UNIQUE on the Technician column.
  
• Copy and paste values (Ctrl + C, then Ctrl + Shift + V) to remove formulas.
  
• Create a unique list of parts categories using UNIQUE on the Parts Category column.
  
• Remove the zero entry if it appears due to blanks.
  
• Transpose the parts category list so categories run across columns:
  
  • Copy the category list
    
  • Paste using Transpose (Paste Special → Transpose or the quick transpose shortcut)
    

10. Build the technician-by-category availability heat map using COUNTIFS

• The goal is:
  
  • Numerator: count of jobs where Parts Available on First Visit equals Yes
    
  • Denominator: count of jobs where Parts Required equals Yes
    
  • Availability Rate: numerator divided by denominator
    
• Numerator COUNTIFS criteria:
  
  • Technician equals the row technician name
    
  • Parts Category equals the column category name
    
  • Parts Available on First Visit equals Yes
    
• Denominator COUNTIFS criteria:
  
  • Technician equals the row technician name
    
  • Parts Category equals the column category name
    
  • Parts Required equals Yes
    
• Divide numerator by denominator and format as a percentage.
  

11. Lock references correctly before copying across the grid

• When copying left to right and top to bottom, lock cell references so they do not drift:
  
  • Lock the technician column reference when dragging across
    
  • Lock the category row reference when dragging down
    
• Use dollar signs to control what stays fixed:
  
  • Dollar sign before the column letter locks the column
    
  • Dollar sign before the row number locks the row
    
• Copy and paste across the full matrix once the anchors are correct.
  

12. Handle divide-by-zero errors using IFERROR

• Wrap the availability calculation with IFERROR.
  
• Use a blank output so the heat map stays clean and readable.
  
• This removes distracting error values where there are no qualifying jobs.
  

13. Add a dynamic threshold and apply conditional formatting

• Add a threshold input cell (example: 85%).
  
• Apply Conditional Formatting to the heat map:
  
  • Highlight Cell Rules
    
  • Less Than
    
  • Instead of typing a number, click the threshold cell
    
• Now changing the threshold updates highlights automatically.
  

14. Improve the threshold label without breaking the number

• Keep the threshold cell as a number so conditional formatting still works.
  
• Use a custom number format to display a label like:
  
  • Threshold: 75%
    
• This keeps it readable while remaining numeric.
  

15. Create technician-level and category-level rollups

• Technician-level availability:
  
  • Copy the heat map formula
    
  • Remove the Parts Category criteria so it calculates availability for each technician across all categories
    
• Category-level availability:
  
  • Copy the formula again
    
  • Remove the Technician criteria so it calculates availability for each part category across all technicians
    
• Apply the same conditional formatting rules so low performance stands out.
  

16. Copy conditional formatting quickly across sections

• Use Format Painter to apply the same conditional formatting style to new summary blocks.
  
• This keeps your dashboard consistent and saves time.