Go To Special Places in Excel

Have you ever called up the Go To dialog in Excel? There is a little button in it that enables you to go to special places in Excel. This post explores some of these special destinations.

For starters, let’s call up the Go To dialog within an empty workbook. There are a few different ways to display the dialog, I personally like to use the F5 key. If you are more of a ribbon person, then you could use the following command icon to display the Go To dialog shown below:

• Home > Find & Select > Go To

Typically, the Go To dialog box is used to quickly navigate to named references. For example, if our workbook had two named references co_name and commission_rate,  then calling up the dialog box would allow you to select a name, as shown below.

So far so good? Good, now, let’s investigate that little Special button in the lower left.  Clicking it brings up the Go To Special dialog, shown below.

This dialog has changed a bit through the different versions of Excel, so depending on which version of Excel you have, your dialog may have slightly different options.

There are a bunch of fun options to play with. I’ll highlight just a few of them in this post.

Constants

When you Go To Constants, Excel selects cells that contain stored constants. Basically, this includes cells that have entered values and excludes empty cells and formula cells. When you select the Constants radio button, the dialog allows you to be more specific and pick and choose numbers, text strings, logical values, and errors.

This can be handy when setting up worksheet protection.  When you apply worksheet protection, by default, all cells are locked. If you want a way to quickly unlock input cells, you can go to constants, and then unlock them at one time.

Formulas

When you Go To Formulas, Excel selects cells that contain formulas. When you select the Formulas radio button, the dialog allows you to be more specific and pick and choose formulas that return numbers, text strings, logical values, and errors.

This can be handy when trying to quickly identify all cells that contain formula errors. Simply go to formulas, select errors, and bam, Excel selects any formula cells that return an error.

Visible Cells Only

When you Go To Visible Cells Only, Excel re-selects the cells within the selected range that are in rows and columns that are visible. This basically includes cells within the selected range that are visible, and excludes cells within the range that are in rows or columns that are hidden, either with a filter, or manually.

This can be handy when you have a range of data, and you have applied a filter. Let’s say that when you try to copy and paste the filtered range, Excel actually pastes all cells in the range, whether or not they show in the filtered results. That is, you copy the entire filtered range expecting that a paste will only paste the visible cells. But, when you paste, Excel pastes all cells, including cells in both the visible and hidden rows. If this happens, simply use the Go To dialog. Select the entire filtered range, and then before doing your copy, Go To Visible Cells Only. Excel will update the selected range to include visible cells, and to exclude hidden cells. Now, go ahead and do your copy and paste, and rejoice that Excel pasted the visible cells only.

Data Validation

When you Go To Data Validation, Excel selects those cells that have been set up with Data Validation.

This can be handy when you are reviewing a workbook, and want to quickly figure out which cells have Data Validation applied. On a related note, if you want to quickly see which cells that have Data Validation applied violate the validation rule, simply use the circle invalid data command:

•  Home > Data Validation > Circle Invalid Data

This will place a red circle around all Data Validation cells that contain a value that violates the validation setting, as shown below.

We’ve only covered a few of the Special places you can Go To in Excel, feel free to explore others. For more information on what each option does, simply open up the Go To Special dialog, and click the little question mark in the upper right. This should bring up the Excel help system, and explain the details of each option.

And remember, Excel rules!