Excel 2010: How to Find Those Special Cells

Have you ever wondered how to find specific cells in a worksheet, such as blank cells or those cells that contain formula errors? Recently someone wanted to know if there is a way to find all cells in a worksheet that had validation rules applied. One of Excel’s “best kept secrets” is the command on the Home tab of the Ribbon under Find & Select called Go to Special…

As you can see, this dialog box offers many options for finding cells in a data range on a worksheet.  For example, if you want to find the data validation rules that are applied in a worksheet, all you have to do is Click the radio button for Data validation and Click OK.  By choosing “All,” all cells will be highlighted that have validation rules applied. If you Click “Same,” then only those cells that have the same validation rules applied as the currently selected cell will be highlighted.

Other commonly used options you may want to find and highlight in a worksheet are: various types of formulas (including errors), blanks, Current region (or entire list), Current array (active cell must be contained in the array), Conditional Formats, or Visible cells only. Note that the Visible cells only option is useful if you have hidden rows or columns in the worksheet and you want to copy and paste only visible cells.  The option of Last cell will find the last cell in the worksheet that contains data or formatting. To find out more about using Precedents and Dependents, you may want to read the blog article entitled: Excel 2010: Using Formula Auditing Tools.

Leave a Reply

Your email address will not be published. Required fields are marked *