[Fixed] Excel Not Highlighting Cells in Formula

While working with Excel, sometimes we face this particular problem where Excel isn’t highlighting cells in a formula. This usually happens when we are editing formulas and expect Excel to visually mark cells with colours for easier identification. We can easily fix this issue with some simple troubleshooting steps.

When Excel does not highlight cell references while editing formulas, one common cause is that in-cell editing is disabled. Enabling this option allows you to edit formulas directly in cells and ensures that Excel highlights cells in the formula.

Steps:

➤ Open your Excel workbook and click on the File tab in the top-left corner of Excel.

➤ From the left-hand menu, select Options.

➤ A new window named “Excel Options” will appear.
➤ Click on Advanced from the left panel.
➤ Scroll down to the section called Editing options. Look for the checkbox labeled Allow editing directly in cells and enable it.
➤ Click OK to save your changes and close the Options window.

Now, Excel should highlight cells in the Formula.

If Excel does not highlight cell references after enabling the cell editing feature from Excel settings, we can also force it using a simple VBA Macro.

Steps:

➤ Open your Excel workbook that contains formulas. Then press  Alt  +  F11  to open the VBA editor.
➤ In the VBA editor, click on Insert > Module. A blank code window will appear.

➤ Copy and paste the following code into the module window:

Sub EnableInCellEditing()
    Application.EditDirectlyInCell = True
    MsgBox "In-cell editing has been enabled successfully!"
End Sub

➤ Press  Alt  +  Q  to close the VBA editor and return to Excel.
➤ Now press the keyboard shortcut  Alt  +  F8  . Then select EnableInCellEditing, and click Run.

➤ Now, your formula containing Excel cells should be highlighted in different colors.

When the above two troubleshooting steps don’t work and Excel still stops highlighting the referenced cells in formulas, it may be caused by corrupted program files. Repairing or reinstalling Excel can restore missing or broken components.

Steps:

➤ Close all Excel files, then press  Windows  +  S  , type Control Panel in the search box, and press Enter. You will see the Control Panel at the top of the search results. Click on it.

➤ Inside the Control Panel, click on Programs.

➤ Click on Programs and Features.

➤ Scroll through the list of installed programs until you find Microsoft Office Application.
➤ Right-click on the name and then choose Change from the pop-up.

➤ Select Quick Repair and click Repair. This will fix the issues.

When Excel doesn’t automatically highlight the referenced cells in a formula, pressing  F2  or  Fn  +  F2 puts the cell highlighting with the formula in Excel into edit mode. This makes Excel highlight the cells used in the formula with different colors.

Steps:

➤ Open your Excel sheet with the dataset. In our example, cell C2 contains the formula =A2*B2. You will see the formula displayed in the formula bar, but the referenced cells (A2 and B2) may not be highlighted yet.

➤ Press  F2  on your keyboard. This switches the formula cell into edit mode. Now Excel highlights the referenced cells. For example, A2 might appear in blue and B2 in red.

Frequently Asked Questions

Why are cells not highlighting in Excel formulas?

Cells may not highlight if Formula Editing Mode is disabled, if the worksheet is protected, or if Excel is running in compatibility mode.

How to get Excel to highlight cells in a formula?

Enable Formula Editing Mode in Excel Options under Advanced settings, press  F2  to enter edit mode, and make sure the sheet is unprotected.

Why is  F2  not highlighting cells in the formula?

If pressing  F2  doesn’t highlight cells, it could be due to disabled editing features, outdated Excel versions, or certain add-ins interfering with formula editing.

Concluding Words

When Excel is not highlighting cells in a formula, the most reliable method is to enable Formula Editing Mode and ensure the sheet allows editing. With these fixes, Excel should return to its normal highlighting behavior. If you face any other issues, let us know in the comments section below.