Go To Special in Excel: Select Blanks, Formulas and More

Go To Special in Excel selects every cell of one kind in a single step: all the blanks, all the formulas, all the numbers you typed, all the errors, or only the visible cells. Once they're selected, you can fill, clear, format or copy them together. This guide shows how to open Go To Special and the uses that save the most time.

How to open Go To Special#

There are three ways to open the dialog:

The first dialog, Go To, lets you jump to any cell address or named range, much like the Name Box. The Special button opens the options this guide is about.

Select a range first#

What you select before opening the dialog decides where Excel looks:

• One cell selected: Excel searches the whole used area of the sheet.
• A range selected: Excel searches only that range.

So to find blanks in one column, select that column's data first. If you don't, you may select blank cells all over the sheet.

What each Go To Special option selects#

The ones below are worth learning first.

Use 1: Select blanks and fill them#

Imported reports often leave a value only on the first row of each group, with blanks below. Go To Special fixes this in seconds:

1. Select the column, for example B2:B8.
2. Open Go To Special, choose Blanks and click OK.
3. Type = and press ↑ (up arrow) to point at the cell above.
4. Press Ctrl+Enter (Mac: Control+Return) to put that formula in every selected blank.

Each blank now copies the value above it. Our step-by-step guide to filling blank cells with the value above also shows how to turn those formulas into plain values.

To fill blanks with a fixed value such as 0 or Unknown, type the value at step 3 instead, then press Ctrl+Enter.

Use 2: Clear inputs but keep the formulas#

To reuse a template, such as a timesheet or budget, you need to empty the numbers people typed without deleting the formulas.

1. Select the area.
2. Open Go To Special, choose Constants, untick Text (so labels stay) and leave Numbers ticked. Click OK.
3. Press Delete (Mac: delete).

Only typed numbers are cleared. Formulas stay and show zero or blank results. If you make a mistake, Ctrl+Z (Mac: ⌘Z) brings everything back; the Edit, clear and undo lesson practises exactly that, and our guide on editing and undoing without the mouse has more shortcuts.

Use 3: Find every formula or every error#

Choose Formulas to select every formula cell, then give them a fill colour. It's a quick way to see which numbers are calculated and which were typed in, and a good first step before you lock formulas on a protected sheet.

To find errors, choose Formulas and untick everything except Errors. Excel selects every cell showing #N/A, #DIV/0!, #REF! and so on. Press Tab (Shift+Tab to go back) to move through them one by one without losing the selection.

Use 4: Copy only the visible cells#

When rows are hidden or filtered, a normal copy can include the hidden rows. To copy only what you see:

1. Select the range.
2. Open Go To Special, choose Visible cells only and click OK. On Windows, Alt+; does the same in one keystroke.
3. Copy and paste as usual.

Use 5: Jump to the last cell#

Last cell selects the bottom-right corner of the used area, the same as Ctrl+End. If it lands far below your data, old formatting or deleted content is making the sheet bigger than it looks. Deleting those empty rows and columns and saving the file fixes it. The Move around the sheet lesson practises Ctrl+End and Ctrl+Home, and our guide to moving around Excel with the keyboard covers the other jumps.

Use 6: Compare rows quickly#

Row differences compares each row of your selection against the first column you selected (the column of the active cell). Select two columns, such as an expected and an actual value, and choose Row differences: Excel selects every cell in the second column that doesn't match. Give them a colour and you have a quick check list.

Things to watch out for#

• "No cells were found." Nothing in the selection matches. Check that you selected the right range.
• Cells that look blank but aren't. A cell with a space, or a formula returning "", isn't blank to Go To Special. Clean spaces with TRIM first.
• A single cell selected by mistake. Go To Special then searches the whole sheet. Undo and reselect the range.

When the selected blanks are part of a series, such as dates or numbers that should increase, use AutoFill instead of copying the value above; see our guide to AutoFill series.

Practise it#

Try the COUNTBLANK exercise on this page, then practise jumping with Ctrl+End, Ctrl+Home and the Name Box in a live spreadsheet. The Edit, clear and undo lesson helps you clear selected cells safely and undo when needed.