Excel Go To Special: select blanks, formulas, errors or visible cells
Go To Special selects every cell of a certain type at once. Open it with F5 › Special or Ctrl+G › Special (or Home › Find & Select › Go To Special).
Select the range first
Select the area to search (or one cell for the whole sheet), then open Go To Special.
5 jobs it makes easy
1. Fill blanks with the value above (tidy a report with merged-style gaps):
- Select the column › F5 › Special › Blanks › OK.
- Type
=then press the up arrow. - Press Ctrl+Enter — every blank gets the value above it.
2. Delete empty rows: Blanks › OK › Home › Delete › Delete Sheet Rows (only if whole rows are empty). See delete blank rows.
3. Find every formula before sharing: Formulas › OK, then give them a colour. Or Constants to find typed numbers that should be formulas.
4. Find errors: Formulas › untick all except Errors › OK. Tab moves through them. See formula errors.
5. Copy only visible rows after filtering or hiding: Visible cells only (shortcut Alt+;) › Ctrl+C.
Other options
| Option | Selects |
|---|---|
| Row differences | cells that differ from the first column (compare columns) |
| Conditional formats | cells with conditional formatting rules |
| Data validation | cells with drop-downs |
| Last cell | the bottom-right used cell (Ctrl+End) |
| Objects | every picture, shape and chart |
Related: compare two columns · remove drop-downs
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
