If Excel is not highlighting cells in the formula, read this post to know how to fix the issue. The default behavior of Excel is to highlight the cells that are part of a formula when a user double-clicks on the cell containing the formula.
For example, if cell C3 contains the formula =C2-C1, cells C2 and C1 will be highlighted when the user double-clicks on cell C3 (a wide border appears around the cells in different colors, such as blue and red, indicating the cell values are part of the formula). This is quite useful as it makes it easier to visually distinguish the cell references that are part of the formula. Also, users can move around the cells using their mouse to modify the cell references.
A few users have reported that the highlight feature is not working in their Excel data sheets. The references are no longer highlighted as they double-click on the cell containing the formula. If you’re one of the users who’s facing the same problem, keep reading further to know how to fix the issue.
Fix Excel not highlighting Cells in formula
If Excel is not highlighting cells in the formula, place the cursor in the cell containing the formula and press fn+f2. This should highlight all the cell references. If that doesn’t help, save your work and close Excel. Then reboot your PC and relaunch Excel. A few users have been able to fix the issue this way. If that doesn’t work for you, we suggest using the following fixes:
- Enable ‘Allow editing directly in cells’ in Excel options
- Turn on ‘Enable fill handle and cell drag-and-drop’ in Excel options
- Turn off ‘Disable hardware graphics acceleration’ in Excel options
- Reduce the number of arguments in the formula
- Use VBA code
Let us see these in detail.
1] Enable ‘Allow editing directly in cells’ in Excel options
There are a few Excel options that need to be turned on or off to make Excel highlight referenced cells in a formula. The first one is ‘Allow editing directly in cells’.
Open the Workbook that’s having the issue and go to File > More… > Options. Excel Options window will appear. Click on the Advanced tab in the left panel. Make sure the checkbox for Allow editing directly in cells is ticked. Click on the OK button to save changes. Now see if this fixes the issue.
2] Turn on ‘Enable fill handle and cell drag-and-drop’ in Excel options
The next option is ‘Enable fill handle and cell drag-and-drop’. Look for the option within the same Excel Options window (Advanced tab, under Editing options). Tick the checkbox for Enable fill handle and cell drag-and-drop. Save changes by clicking the OK button. Now retry highlighting formula references in Excel.
3] Turn off ‘Disable hardware graphics acceleration’ in Excel options
A few users have tried their luck on this setting and it worked. Open the Excel Options window and switch to the Advanced tab. Look for Disable hardware graphics acceleration under Display options. Uncheck the option, save changes to Excel, and then try highlighting the cells.
4] Reduce the number of arguments in the formula
If the above solutions won’t resolve the issue, try reducing the number of arguments in your formula. Excel may not highlight referenced cell values if the formula consists of a large number of arguments. For example, If you’re summing around 35 cell values (which are not in succession), try to reduce them to 30 or less (by doing a subtotal of values in batches and then doing a grand total). This has helped a few users fix the issue.
5] Use VBA code
If none of the above methods help, you may use a VBA code to force highlight cell references in a formula in Excel.
Open the workbook and go to the Developers tab. If you can’t see the tab, you can enable it from the Excel Options menu.
Click on the Visual Basic option in the extreme left end. VB Editor window will appear. Click on Insert > Module.
A new code module window will appear. Type or copy-paste the following code in the window:
Sub Highlight_Cells_in_Excel_Formula() Application.EditDirectlyInCell = True End Sub
Press F5 or the Run icon to run the code. The above code will enforce ‘allow editing in cell’ in Microsoft Excel.
I hope the above solutions will help fix the cell highlighting issue when a formula is selected in Excel. For any queries, please use the comments section below.
Read: Excel filter not working properly.
Why isn’t Excel highlighting cells in formula?
If Excel isn’t highlighting cells in the formula, you might have accidentally modified some default settings that are required to highlight the cell references in a selected formula. Check Advanced Options in Excel and change them back to defaults to fix the issue.
How do you highlight cells in Excel if it is a formula?
You may use Conditional Formatting to highlight all cells that are part of a formula in Excel. This will help you (or your viewers) keep track of the data that’s being used in the calculation. Another quick way to highlight cells in Excel is using the Go To Special dialogue box. You can select all the cells that contain the formulas and use the Fill Color option to manually highlight the cells.
Read Next: Excel not opening on Windows computer.
