Go To Special

The ‘Go To’ and ‘Go To Special’ functions can help you audit your worksheet and also act as a tool to update values and make changes to your spreadsheet with the minimum fuss.

 

From the Home tab, they can be accessed under ‘Find & Select’ from the Editing group.

 

GoToSpecial1

 

There are also two shortcuts to get to ‘Go To’. Either Ctrl + G, or simply by pressing F5.

 

GoToSpecial2

 

In this case we have four named ranges, the Go To menu will also show anything else you have named such as tables and pivot tables.

 

You will also notice in the bottom left of the Go To menu is the ‘Special…’ option. This is where the magic starts.

 

Our dataset here is tennis player Andy Murray’s career statistics. We’re going to open the Go To menu, click on the Special button and run through some examples of what it can do.

 

GoToSpecial3

 

After using the Ctrl + G shortcut and clicking Special, these are the options you are confronted with.

 

GoToSpecial4

 

If you select Constants, and only leave Numbers ticked…

 

GoToSpecial5

 

All cells containing a number are highlighted…

 

GoToSpecial6

 

You can also choose Formulas…

 

GoToSpecial7

 

Firstly select only the range of cells containing data. Then choose Blanks from the Go To Special menu…

 

GoToSpecial8

 

A little trick you might find useful, having selected all the blank cells click zero and then hit Ctrl + Enter and you will populate all the blank cells with a zero in one go.

 

GoToSpecial9

 

Other useful options allow you to select a cell’s Precedents or Dependents, all cells containing Comments, all Objects on a worksheet and all cells containing Data Validation amongst others.

 

Another interesting option within the Go To Special function is Row Differences. Firstly you highlight your range of cells (in this case B3:F5) and all cell values in the same row which do not match the first column will be highlighted. Column Differences works in much the same way, should you wish to quickly select all values not matching the first row in the column.

 

GoToSpecial10

Leave a Reply

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