Labor Efficiency Ratio (LER) in Excel:
Identify Labor Waste and Improve Project Margins

How to Analyze Labor Efficiency Ratio by PM and System Type

1. Start with the Key Data Columns

Begin with a structured dataset that includes the fields used in the video:

• Project
• Start date
• Market
• System type
• PM name
• Revenue
• Quoted hours
• Standard hours
• Actual labor hours

For this walkthrough, the main fields used are:

• System type
• PM name
• Standard hours
• Actual labor hours

There is also an optional row-level calculation in the dataset to spot individual jobs that fall below target, but the main analysis focuses on summary results by PM and system type.

2. Create a Unique List of System Types

The first step is to build a clean list of system types.

• Use the UNIQUE function on the System Type column
• Use the SORT function to alphabetize the results
• Copy the list and paste it as values using Ctrl + Shift + V

This gives you a static, clean list of system categories to use in the summary table.

3. Create a Unique List of Project Managers

Next, repeat the same process for project managers.

• Use UNIQUE on the PM Name column
• Wrap it with SORT to organize the names alphabetically
• Copy and paste the results as values

This creates the PM list needed for the columns of the matrix.

4. Transpose the PM Names Across the Top

Once the PM names are listed vertically, move them into row format.

• Copy the PM list
• Paste it using Transpose

Now you have:

• System types down the left side
• PM names across the top

This creates the structure for your LER matrix.

5. Build the Overall Efficiency View by PM

Before building the full matrix, the video starts with an overall efficiency view for each PM.

• Use SUMIFS to total standard hours by PM
• Use SUMIFS again to total actual labor hours by PM
• Divide the aggregated values to get the efficiency percentage
• Copy and paste the formula results across instead of dragging

This gives you a high-level view of overall labor efficiency by project manager before adding system-level detail.

6. Add System Type as a Second Criteria

To make the analysis more useful, expand the calculation so it evaluates each PM by each system type.

• Start with the existing SUMIFS structure
• Add System Type as an additional criteria range
• Reference the system type labels on the left
• Keep the PM reference from the header row

At this point, the table is set up to return labor efficiency values for each PM and system type combination.

7. Lock the Cell References Correctly

Before copying the formula through the full matrix, lock the references properly.

• Lock the column reference for the system type field so it stays fixed when copying across
• Lock the row reference for the PM name so it stays fixed when copying down
• Use dollar signs where needed to hold those references in place

This is the step that allows the matrix to fill correctly without breaking the logic.

8. Copy the Formula Across the Full Matrix

After locking the references:

• Copy the formula across the PM columns
• Paste it down through all system types
• Repeat the same structure for the full matrix

Now the table gives a much clearer picture of where labor efficiency is strong and where it is falling short. This is where patterns start to stand out by system type and by project manager.

9. Create an Overall Row by System Type

The next step is to calculate an overall result for each system type, without filtering by PM.

• Copy the existing logic
• Remove the PM criteria from the calculation
• Keep the system type criteria
• Copy the formula across the overall row
• Format the results as percentages using Ctrl + Shift + 5

This gives you an overall benchmark for each system type across the whole dataset.

10. Apply Conditional Formatting as a Heat Map

Once the matrix is complete, turn it into a visual heat map.

• Highlight the efficiency values
• Go to Conditional Formatting
• Choose Color Scales
• Apply a scale where lower values appear in red and higher values appear in green

This makes it much easier to spot weak areas, especially recurring issues across certain system types.

11. Add a Threshold-Based Highlight Rule

The video also shows how to flag values below a specific target, such as 85%.

• Enter a target percentage in a separate cell
• Remove the original color scale from the selected range if needed
• Re-select the matrix
• Use Conditional Formatting with a Highlight Cells Less Than rule
• Use the target threshold to flag underperforming cells

This creates a more direct exception-based view, which is useful when you want to focus only on values below target.

12. Format the Target Cell So It Is Easy to Understand

To make the threshold obvious for other users:

• Keep the target cell numeric so Excel can compare it properly
• Use Ctrl + 1 to open Format Cells
• Apply a Custom number format
• Add the word “Target” into the display formatting while preserving the underlying number

This allows the cell to display something like a labeled target percentage without breaking the conditional formatting rule.

13. Use the Matrix to Spot Patterns

With the completed matrix and formatting in place, you can quickly see:

• Which PMs are consistently below target in certain system types
• Which system types are dragging down overall efficiency
• Where performance issues appear isolated versus recurring

This structure gives a much more useful view than a simple overall average because it shows exactly where efficiency problems are happening.

14. Keep the Dataset Structured for Easier Reporting

The video closes with an important practical point: this kind of analysis depends on having clean, structured data.

A properly organized dataset makes it much easier to:

• Build the matrix
• apply conditional formatting
• update the analysis as new jobs are added
• identify operational issues with more confidence