How to automatically highlight cells containing formulas in Excel
How to automatically highlight cells containing formulas in Excel: Excel is a powerful tool for managing data and performing calculations, but with all its capabilities, it can be easy to overlook some of its useful features. One of these features is the ability to automatically highlight cells containing formulas. This can be helpful when you need to quickly identify which cells contain formulas and which do not. In this tutorial, we will show you how to do this in a few simple steps.
1. How to automatically highlight cells containing formulas in Excel
Step 1: To highlight cells containing formulas, first open the Excel file containing the data table to check the formulas. Then, select the Developer tab on the toolbar and select Visual Basic. Alternatively, you can use the shortcut Alt + F11 to open the VBA window.
How to automatically highlight cells containing formulas in Excel
Step 2: At this point, the Microsoft Visual Basic for Applications window will appear. Select the Insert tab on the toolbar. When the scroll bar appears, select the Module option.
How to automatically highlight cells containing formulas in Excel
Step 3: At this point, the Module dialog box will appear. Copy the code below into it. Note that in the line of code Rng.Interior.ColorIndex = 50, the number 50 is the color code, so we can change it to another code if we want to use a different color. Then, press Run to run the code.
Sub SelectFormulaCells()
'Updateby20140827
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = “KutoolsforExcel”
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas, 23)
Application.ScreenUpdating = False
For Each Rng In WorkRng
Rng.Interior.ColorIndex = 50
Next
Application.ScreenUpdating = True
End Sub
How to automatically highlight cells containing formulas in Excel
Step 4: At this point, the data range dialog box will appear. Select your data table and click OK.
How to automatically highlight cells containing formulas in Excel
That’s all you need to do. Cells containing formulas in the table will be highlighted to make it easier for you to identify them.
How to automatically highlight cells containing formulas in Excel
2. How to select formula cells in Excel
If you only need to quickly find cells containing formulas without highlighting them, you can select the formula cells. First, press F5 to open the Go To dialog box. Then, select the Special option.
How to automatically highlight cells containing formulas in Excel
Next, select the Formulas option and click OK to complete the process.
How to automatically highlight cells containing formulas in Excel
This will quickly select cells containing formulas in the worksheet.
How to automatically highlight cells containing formulas in Excel
Being able to quickly identify cells containing formulas is an important aspect of working with data in Excel. By using the methods outlined in this tutorial, you can streamline your workflow and save time when working with large data sets. So the next time you are working in Excel, give these techniques a try and see how they can improve your productivity.
