Fix LET and COLUMN Formulas in Excel Conditional Formatting
Question details
The user needs to understand why a conditional formatting formula that uses LET, COLUMN, OFFSET, INDIRECT, and ADDRESS evaluates in worksheet cells but fails in Excel for the web conditional formatting, and how to create a reliable rule.
- Product
- Microsoft Excel for the web
- Device & OS
- not provided
- Scenario
- Creating a dynamic conditional formatting rule that uses LET with COLUMN, OFFSET, INDIRECT, and ADDRESS to decide which cells to highlight.
- Observed behavior
- The formula works when entered in worksheet cells, but the conditional formatting rule does not evaluate correctly or is ignored in Excel for the web.
Check whether the workbook is open in Excel for the web or desktop Excel, because conditional formatting function support differs by platform. Download a local copy so you can test the rule in WPS Office Spreadsheets or desktop Excel without changing the original.
Simplify the conditional formatting rule with direct references and helper cells
Use this when you need the same conditional formatting result in Excel for the web without relying on unsupported functions.
Excel for the web evaluates conditional formatting formulas with a smaller function set than desktop Excel. Complex formulas that combine LET, COLUMN, OFFSET, INDIRECT, and ADDRESS often work in worksheet cells but fail or are ignored in conditional formatting.
In Excel for the web, select the target range, go to Home > Conditional Formatting > Manage Rules, and open the rule that contains the LET formula.
Replace the LET/COLUMN/OFFSET/INDIRECT/ADDRESS formula with a simpler formula that uses direct cell references, such as =D8=COLUMN(D8), or with a formula that references a helper cell.
Click Apply and check whether the expected cells are highlighted. If the rule still does not work, create helper cells that calculate the required value and reference those cells in the rule.
Create helper cells to calculate the comparison result
Use this when the original formula must keep complex logic but the conditional formatting rule itself needs to remain simple.
Apply the Conditional Formatting Rule in WPS Office Spreadsheets
Open the same workbook in WPS Office Spreadsheets, use the desktop conditional formatting tools to build the rule with helper cells or supported functions, and keep full Excel compatibility.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the conditional formatting rule.
- 2. Select the target range: Click and drag to select the cells you want to format, or use the Name Box to enter the range.
- 3. Open the New Formatting Rule dialog: Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
- 4. Enter a supported formula or helper-cell reference: Enter the simplified formula or helper-cell reference, click Format to choose the highlight, and then click OK.
- 5. Save in Excel-compatible format: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the file.
Frequently Asked Questions
Why does my LET formula work in a worksheet cell but not in Excel for the web conditional formatting?
Excel for the web supports a limited set of functions in conditional formatting rules. LET, OFFSET, INDIRECT, and ADDRESS may be available in worksheet cells but are not evaluated the same way in conditional formatting, so the rule can fail or be ignored.
Can I use LET and COLUMN directly in a conditional formatting rule?
It depends on the Excel platform. Desktop Excel may support more functions, but Excel for the web often does not. For a reliable rule, simplify the formula or move the complex calculation to a helper cell and reference that cell in the rule.
How do I create a helper cell for a conditional formatting formula?
Insert a column or row near the data, enter the complex formula in the helper cell to return TRUE or FALSE or 1 or 0, then create a conditional formatting rule that references the helper cell, such as =$E8=TRUE.
Will WPS Office open my Excel workbook without breaking the conditional formatting?
WPS Office Spreadsheets opens and saves .xlsx files and supports conditional formatting rules. You can open the workbook, edit the rule, and save it back as an Excel file while keeping format compatibility.
What should I do if the simplified rule still does not work in Excel for the web?
Download the workbook and test the rule in desktop Excel or WPS Office Spreadsheets. If it works locally, the issue is likely the web version's function limitations; keep the rule simple or use helper cells for the online file.
