Backorder Rate % Heat Map in Excel
(By Month and Supplier)

Backorder Rate % Heat Map in Excel

1. Confirm the Required Columns

Make sure your dataset includes:

• Order Date
• Category
• Supplier
• Order Quantity
• Quantity Shipped

These are the only fields needed to calculate Backorder Rate %.

2. Convert the Data into a Table

• Click inside the dataset.
• Press Ctrl + T to convert it into a structured Excel Table.
• Confirm headers are included.

This allows structured references and dynamic expansion.

3. Create a Month-End Column

To group orders by month:

• Insert a new column called Month.
• Use the EOMONTH function to convert each order date into the end of its month.

This allows you to bucket all transactions into January, February, etc.

4. Create a Backorder Quantity Column

Backorder Quantity is calculated at the row level:

• Insert a new column called Backorder Quantity.
• Subtract Quantity Shipped from Order Quantity.

Fill the calculation down the table.

5. Create a Unique List of Months

• Use the UNIQUE function on the Month column.
• Copy and paste values to remove the formula.
• Format as a date.

These months will form the rows of your matrix.

6. Create a Unique List of Suppliers Across the Top

• Use UNIQUE on the Supplier column.
• Wrap it with SORT to alphabetize.
• Use TRANSPOSE to move the list horizontally across the top.
• Paste as values.

Now you have:

• Months down the left
• Suppliers across the top

7. Calculate Backorder Rate % by Month and Supplier

To build the matrix:

• Use SUMIFS to total Backorder Quantity by Month and Supplier.
• Use SUMIFS again to total Order Quantity by Month and Supplier.
• Divide the two totals to get Backorder Rate %.
• Format the result as a percentage using Ctrl + Shift + 5.

Copy and paste across the entire matrix after locking the proper references.

8. Add Overall Backorder Rate % by Supplier

• Copy the existing logic.
• Remove the Month condition from both SUMIFS calculations.
• Format as percentage.

This gives total Backorder Rate % per supplier.

9. Add Overall Backorder Rate % by Month

• Copy the logic again.
• Remove the Supplier condition from both SUMIFS calculations.
• Format as percentage and fill down.

This gives total Backorder Rate % per month.

10. Calculate the Grand Total Backorder Rate %

• Use the SUM function on the Backorder Quantity column.
• Divide by the SUM of the Order Quantity column.
• Format as percentage.

This gives your dataset-wide Backorder Rate %.

11. Create the Target Cell for the Heat Map

• Add a cell with your desired threshold (example: 4.0%).
• Format as a percentage.

   

This will serve as your changing target value.

12. Apply Conditional Formatting for the Heat Map

• Highlight the Backorder Rate % matrix.
• Use Conditional Formatting → Highlight Cells Rules → Greater Than.
• Reference the target cell.
• Choose a red fill format.

Now anything above your target automatically highlights.

13. Format the Target Cell Without Breaking It

If you want the cell to display something like “Target: 4.0%”:

• Open Format Cells (Ctrl + 1).
• Use Custom Formatting to add the word “Target” before the percentage format.

This keeps the cell numeric while displaying a clear label.