Formatting Issues

Highlight the Lowest Value Across Multiple Excel Worksheets

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to find and visually highlight the lowest data value among matching cell ranges located across several different worksheets (such as Sheet A, Sheet B, and Sheet C).

Product: Excel
Device & OS: not provided
Scenario: Comparing datasets spanning across multiple worksheets and applying conditional formatting to dynamically identify the absolute minimum value among all sheets.
Observed behavior: The user wants to set up a conditional formatting rule that correctly references and evaluates data ranges on other worksheets simultaneously.

Before you start

Ensure that the data ranges you plan to compare across your different worksheets are exactly the same size and located in corresponding rows and columns for the formula to work accurately.

Solution 1Recommended

Use a MIN Formula in Conditional Formatting

Create a new conditional formatting rule using the MIN function to compare values across all sheets and apply a fill color to the lowest one.

To highlight a value based on data from other sheets, you must use a formula-based conditional formatting rule. The formula will check if the current cell is equal to the absolute minimum value across all specified sheet ranges.

Select the target range on the first sheet

Navigate to your first worksheet (e.g., Sheet A). Select the matching data range (such as A1:F20), ensuring that the top-left cell (A1) is the active cell in your selection.

Open the New Formatting Rule dialog

Go to the Home tab on the Excel ribbon, click on Conditional Formatting in the Styles group, and select New Rule from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.

Enter the MIN formula

In the 'Format values where this formula is true' box, type the following formula: =A1=MIN('Sheet A'!$F$1:$F$20,'Sheet B'!$F$1:$F$20,'Sheet C'!$F$1:$F$20). Adjust the sheet names and absolute ranges ($F$1:$F$20) to match your specific workbook data.

Set the highlight format

Click the Format button, go to the Fill tab, select a highlight color (e.g., yellow or red), and click OK twice to apply the formatting rule.

Apply the rule to remaining sheets

Navigate to Sheet B and Sheet C, select the exact same data range (A1:F20), and repeat steps 2 through 5 using the exact same formula to ensure the lowest value is highlighted regardless of the sheet it resides on.

Understanding Cell References: It is crucial to use absolute references (like $F$1:$F$20) for the ranges inside the MIN function, but a relative reference (like A1) for the cell being evaluated at the beginning of the formula.

Effortless Spreadsheet Formatting

Easily Highlight Data Across Worksheets with WPS Office

WPS Spreadsheet provides robust conditional formatting tools and formula capabilities that are completely compatible with Excel. You can quickly highlight lowest or highest values across multiple sheets using a familiar, easy-to-use interface.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the multiple worksheets you want to compare.
2. Select the data range: Highlight the data range on your first sheet where you want the conditional formatting to apply.
3. Navigate to Conditional Formatting: Click the Home tab on the top ribbon, select Conditional Formatting, and choose New Rule.
4. Apply the MIN formula: Choose 'Use a formula to determine which cells to format', enter your cross-sheet MIN formula, set your preferred fill color, and click OK.

Free, lightweight, and fast spreadsheet applicationFully compatible with Microsoft Excel (.xlsx) formulas and conditional formattingIntuitive interface for advanced data analysis and visualizationSeamless cross-sheet calculations without lag

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong cell?

This often occurs if the active cell during range selection doesn't match the relative reference at the start of your formula. For example, if you highlighted the range starting from B2 but your formula begins with =A1, the formatting will be offset. Always ensure your formula starts with the top-left cell of your selected range.

Can I use this same method to highlight the highest value?

Yes, you can easily adapt this formatting rule to find the maximum value. Simply replace the MIN function with the MAX function in your conditional formatting formula, like so: =A1=MAX('Sheet A'!$F$1:$F$20,'Sheet B'!$F$1:$F$20,'Sheet C'!$F$1:$F$20).

Will the highlight update automatically if I change the data?

Yes. Conditional formatting is dynamic. If you change a number on any of the referenced sheets and it becomes the new lowest value, the highlight will automatically move from the old cell to the newly updated cell.

Document & File Issues