Selecting All Formula Cells in a Worksheet
Sometimes when working in Excel, it can be helpful to quickly identify or select all of the cells in a worksheet which contain formulas. Luckily, there is an easy way to do this using the Go To functionality. If you want to check for formulas in a specific range, start by highlighting that range. Otherwise, just click on any single cell to check the entire worksheet.
Then, if you’re using an older version of Excel, go to Edit – Go To – Special. For newer versions of Excel, look on the Home ribbon for Find & Select, and choose the Go To Special option.
Next you’ll see a variety of options for what you’d like to select. Since you want to find the formula cells, select the radio button next to Formulas. There are options beneath Formulas that you can select if you’re only interested in formulas with a specific result. The four options, based on the output of the formula are:
- Number: The formula’s result is a number value
- Text: The result of the formula is text, such as in the case of a CONCATENATE function
- Logical: Formulas that result in a TRUE/FALSE value
- Errors: Formulas that return any type of error result, like #N/A, #DIV/0!, etc.
By default, all four options are selected, but you can uncheck any of the boxes to narrow your search down if you’d like. Once you’ve made your selection, click OK, and Excel will highlight all of the cells containing formulas that match your criteria. If no cells match your criteria, Excel will display a message explaining that there are no cells with formulas meeting your criteria.