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.
Enable you to do smarter, faster and better
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.
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.
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.