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

Excel Go To Dialog

 

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.

Excel Go To Dialog with Named References

 

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.

Excel Go To Special Dialog

 

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.

Excel Data Validation Circle Invalid Data

 

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!

 

This article was written by Jeff Lenning

9 comments:

  1. Denise Weber
    Reply

    I really like the Go To\Special\Visible Cells Only; I can definitely use that. I have learned that you can’t copy & paste filtered spreadsheets without doing something special.
    Now I know what to do!
    Thank you Jeff!

  2. Denise Weber
    Reply

    I really like the Go To\Special\Visible Cells Only; I can definitely use that. I have learned that you can’t copy & paste filtered spreadsheets without doing something special.
    Now I know what to do!
    Thank you Jeff!

  3. amy zhang
    Reply

    i really like select formula error function. I use massive excel workbook to automate my clients work, now it’s easier to update and review all the formula!

  4. CHAO JIN
    Reply

    I like the Go To\Special\Visible Cells.

    Thank you.

  5. Ben Equitan
    Reply

    I have been using name references for years but shortcut to navigate to the box will be extremely helpful to save significant amoutn of time

  6. Marner Harris
    Reply

    The crowd goes “WOW”! The go to special feature will quickly help in detecting worksheet errors.

    1. jefflenning Post author
      Reply

      🙂

  7. Marner Harris
    Reply

    The crowd goes “WOW”! The go to special feature will quickly help in detecting worksheet errors.

  8. Sharon McAlister
    Reply

    I like being able to identify formulas, great way to error check! Thanks

Leave a Reply

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

By submitting this form, you accept the Mollom privacy policy.