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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.