Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.



The above picture is a budget worksheet, with text and numbers. The numbers in the Total row and Total column are produced by formulas in those cells.

Microsoft created the moniker “Special Cells” as an umbrella name for groups of different classifications of cells. There is nothing special about Special Cells except their condition of interest at the moment you care about them. For example, various cells on your worksheet might contain formulas, and/or conditional formatting, and/or data validation, and/or many other features. Some cells will be visible or hidden depending on their row or column. Some cells might contain nothing at all and even they can be referred to as Special Cells.

Let’s say you only care about cells on your worksheet that contain constants — that is, values be they text or numbers — that you’ve manually entered. If you want to format all your constant cells at the same time, such as to fill them with an identifying color or to bold them, sans VBA you’d first need to select all those cells at the same time so you can work with them as a group.

To do that, hit the F5 key on your keyboard and from the Go To Special dialog box, click the Special button as shown in this next picture.



In the Go To Special dialog box, select the option for Constants. Notice that all Constants possibilities (Text, Numbers, Logicals, and Errors) are also selected. Click the OK button as shown, and see that all cells with constants have been selected.



If you only want to select the cells with constants that are text, without including constants that hold numbers, then deselect the options for Numbers, Logicals, and Errors in the Go To Special dialog box as shown.



And as you can probably guess by now, to select only the formulas, here’s the image for doing that.



This technique for identifying Special Cells has valuable applications. You can select to identify only cells with formulas that contain errors. Or all cells with data validation or conditional formatting if you lose track of what cells are where on your worksheet.

In versions of Excel prior to 2007, the Special Cells selection facility is limited to 8192 cells at the same time. This limitation was removed in versions 2007 and after.