You need to quickly find and select all empty cells or every cell containing a formula in a large Excel worksheet. Manually searching is slow and error-prone. Excel’s Go To Special feature provides a direct way to select cells based on specific criteria. This article explains how to use Go To Special to instantly select all blank cells or formula cells in one action.
Key Takeaways: Using Go To Special in Excel
- Home > Find & Select > Go To Special: Opens the dialog to select cells by type, such as blanks, formulas, or constants.
- F5 > Special: A keyboard shortcut alternative to open the Go To Special dialog directly.
- Selecting Blanks or Formulas: Choose the appropriate option in the dialog to highlight all matching cells in your selected range instantly.
What the Go To Special Feature Does
Go To Special is a selection tool within Excel. It allows you to isolate cells that share a common attribute. You can select all cells that are blank, contain formulas, constants, comments, or have data validation rules. This is useful for data cleaning, auditing, and formatting. For example, you can find all blank cells to fill them with a default value. You can also find all formula cells to check for errors or apply protection.
The feature works on the currently selected range. If you select a single cell, Excel searches the entire used range of the worksheet. It is better to select a specific range first for more precise control. The selection made by Go To Special is often a non-contiguous range. You can then format, delete, or edit all the selected cells at once.
Steps to Select Blank or Formula Cells
Follow these steps to use the Go To Special command. The process is the same whether you are targeting blanks, formulas, or other cell types.
- Select your target range
Click and drag to highlight the cells you want to search. To search the entire worksheet, click on any single cell. - Open the Go To Special dialog
Go to the Home tab on the ribbon. In the Editing group, click Find & Select. Then choose Go To Special from the dropdown menu. Alternatively, press the F5 key and click the Special button. - Choose your selection criteria
In the Go To Special dialog, you will see a list of options. To select all empty cells, click the Blanks radio button. To select all cells containing formulas, click the Formulas radio button. You can also refine formula selection by checking boxes for Numbers, Text, Logicals, or Errors. - Apply the selection
Click OK. Excel will immediately select all cells within your initial range that match the chosen criteria. The selected cells will be highlighted. - Perform your action
With the cells selected, you can now perform a batch action. Type a value and press Ctrl+Enter to fill all blank cells. Or apply a format, like a border or fill color, to all formula cells.
Using the Keyboard Shortcut Method
You can access the feature faster using keyboard shortcuts. First, select your range. Press F5 to open the Go To dialog. Then press Alt+S to open the Go To Special dialog. Use the arrow keys to navigate the options and press Enter to select.
Common Mistakes and Limitations
Go To Special Selects Hidden or Filtered Cells
Go To Special will select cells that are hidden by rows or columns or filtered out. If you delete the selected cells, you may accidentally delete hidden data. Always check if your sheet has filters or hidden rows before using the feature for deletions.
Formulas Returning Empty Strings Appear as Blanks
A cell with a formula like =”” appears empty but is not selected by the Blanks option. Go To Special Blanks only selects truly empty cells. To find formula-driven “blanks”, use the Formulas option and check all sub-types.
Cannot Select Both Blanks and Formulas Simultaneously
The Go To Special dialog only allows one primary selection per operation. You cannot select both blank cells and formula cells in one click. You must run the command twice and use Ctrl to add to the selection, or use a more advanced filter.
Go To Special Options: Blanks vs Formulas
| Item | Select Blanks | Select Formulas |
|---|---|---|
| Primary Use | Find empty cells for data entry or cleanup | Audit, protect, or format cells with calculations |
| Cells Selected | Cells with no content, formula, or space | Cells beginning with an equals sign (=) |
| Sub-options Available | None | Numbers, Text, Logicals, Errors |
| Common Action After Selection | Fill with value using Ctrl+Enter | Apply a colored border or check for errors |
| Keyboard Navigation in Dialog | Press B | Press F |
You can now quickly select all blank or formula cells in your Excel worksheets. Use the Go To Special command from the Home tab or the F5 shortcut. Remember that cells with formulas showing empty text are not selected as blanks. For advanced auditing, try using Go To Special to select cells with data validation or comments next.