Discover the Hidden Powers of Go To Special

What’s So Magical About Excel’s Special Button?

By Marie McCooey

You’re probably wondering what is the Special button in Excel?

And how can it help you?

Let’s explore this little known gem that can easily save you time when you need to:

  • Find and remove blank rows in your worksheets
  • Identify formulas with errors
  • Select visible cells only

And so much more.

Where can you find this hidden treasure?

You’ll find the Special button in Excel’s Go To window where you can use cell addresses or names to navigate to sections of enormous worksheets.

First, press CTRL + G or my favorite, F5, to display the Go To dialog box.

Go To Window

Click the Special button.

The Go To Special window displays numerous choices.

Go To Special window

Let’s look at some examples of how this time-saving tool can help you.

How to quickly remove blank rows

Imagine, you’ve downloaded a large file with hundreds or thousands of rows.

And many of them are blank.

You’re dreading the process of deleting each blank row individually.

Here’s where Go To Special comes to the rescue.

  1. Select the first column, including all the rows.
  2. Press F5 or CTRL + G, then click the Special button.
  3. Select Blanks, then click OK.

Go To Blanks
All the blank cells in the first column are selected.

Go To with Blanks Highlighted

  1. Point to one of the empty cells and using the right mouse button click one of the empty cells and choose Delete… from the shortcut menu.
  2. Select the Entire row option, then click OK.

Delete Entire Row
All the blank rows are instantly deleted.

How to easily locate formulas with errors

There’s nothing worse than having errors in your Excel formulas.

But, how can you easily find those pesky errors in a large file without clicking on each cell?

Go To Special can help.

  1. Click in any cell, press F5 or CTRL + G, then click the Special button.
  2. Select Formulas and uncheck all the formula types except Errors, then click OK.

Go To Special Errors

All the worksheet cells that contain formulas with errors display as selected.

Next, apply a Fill color to the cells to help you identify the errors easily.

Go To Special Errors with Fill

How to avoid unwanted hidden data

You have a large file with many columns and rows you need to send a colleague. You realize the worksheet’s order data should not be included. Instead of deleting the columns, you decide to hide the order data columns.

You select the Order data columns to hide, right click the selected columns and select Hide from the shortcut menu.

Hide Columns for Visible Cells Only
The order data columns appear hidden.

Next, select and copy the remaining data on the worksheet and paste the data into a new worksheet.

But, what happened?

Excel copied and pasted all the data, including the data in the hidden columns.

How can you avoid this?

The trick to copying your data without including the hidden data is to use Excel’s Go To Special, Visible Cells Only option to exclude hidden columns or rows.

  1. Select the data to copy (which includes the hidden data).
  2. Press F5 or CTRL + G, then click the Special button.
  3. Select the Visible Cells Only option and click OK.

Go To Special Visible Cells Only

Hint: You can skip steps 2 and 3 and press ALT + ;  (the Select Visible Cells Only keyboard shortcut) instead to select the Visible cells only.

Discover the magic of Excel’s Go To Special today.

If you have examples of how you use GO TO SPECIAL, share them in the comments!

Leave a Reply

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