How to select all formula cells in a worksheet?

If there are multiple types of cells including formulas, text, number and so on in your worksheet, and you just want to select only formula cells in your worksheet or a selection, how can you solve it in Excel? Now I introduce some quick ways which can do a favor for you.

Select all formula cells with Go To function

Highlight all formula cells with VBA

---

Select all formula cells with Go To function

---

In Excel, you can use Go To function to quickly select formula cells only from a worksheet or selection.

1. Press Ctrl + G together to open Go To dialog, and then click Special button. See screenshot:

2. Check Formulas option, then click OK to close the dialog. See screenshot:

Now all the formula cells are selected in the worksheet.

Tip:

1. If you just want to select formula cells in a selection, select a selection before you apply Go To function.

2. You can specify the options in step 2 to select formulas type, such as only error formula.

---

Highlight all formula cells with VBA

There is a VBA code also can highlight all formula cells in a range of the whole worksheet.

1. Press Alt + F11 keys to open the Microsoft Visual Basic For Applications window.

2. Click Module > Insert to insert a Module window, and copy the below VBA into the window.

VBA: Highlight all formula cells.

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 = 36
Next
Application.ScreenUpdating = True
End Sub

3. Click Run button, a KutoolsforExcel dialog pops out for you to select a range. See screenshot:

4. Click OK. And all formula cells are highlighted.

Tip: In the above VBA code, you can change the number to adjust the highlight color you want in Rng.Interior.ColorIndex = 36.

---

---

Excel Productivity Tools