Go To Special

Sometimes terrific features within Excel are only accessible a couple layers deep, so people may never learn of them.

The Special option in the Go To features is one of them.

You may already be aware how the Go To feature can help you navigate:

• You can access it by clicking the F5 key or

• From the Edit menu, select Go To

• You can then type in the cell reference of where you want to go to, such as L58

• Even more powerful is when you have named ranges.  You can type in the name of the range and the Go To feature will take you there.

Here is the small worksheet we will work with in our lesson.  The exercise and result are in the Go To Special file.

One thing that can be helpful to do in a worksheet is to document which numbers are inputs by color coding them.

You may not realize it, but the Go To feature has a Special button that will help you here. 

But first, here is a tip.  Highlight the number section of the worksheet first.

Click F5 or Go To from the Edit menu to bring up the Go To dialog box.

Notice the Special button.  Click on it.

Select Constants as we have done and click OK.

You now have only the input numbers highlighted.  While this is a small example and we could have highlighted these cells easily without using Go To, for a larger spreadsheet or one where formulas and inputs are more intertwined, this feature is a nice time saver.

You can now select a color via the formatting toolbar.

We chose the color Blue.  Here is the result.  Input numbers are now shown in blue.  Our reader can know easily tell what numbers were input rather than calculated.

Let's go back to the Special dialog box.

Play around with some of the other options on a workbook of yours, particularly a larger one.  Here are some ways that you could use these.  You might have other ideas also:

• Formulas.  You could do the reverse and color code formulas
• Comments.  Use this to quickly highlight all cells with comments, for making changes.
• Current Region.  Quickly highlight the entire current region for copying, formatting or editing.
• Current Array.  Ditto.  Also use it to show all the calculations that are part of the array.
• Precedents.  Suppose you have a calculated number, but want to change an input or formula that leads to it.  Use precedents to take you there quickly.
• Dependents.  Suppose you just changed a number or formula.  Use dependents to see all the places that are impacted by the change.
• Last Cell.  Go to the last cell in the worksheet.
• Conditional Formats.  Find all the cells that use conditional formatting, a feature we covered in an earlier lesson.
• Data Validation.  Find all cells where you have tests to validate the data.

The Special button in the Go To dialog can be a handy navigational resource.  Use it to help with your QA, formatting or just plain navigation.

Return To Excel Tips Index