Excel Go To Special Tool

In this section you will learn how to activate and use the Excel Go To Special Tool which allows you to highlight cells based on what you are looking for e.g.

• Input cells, formula cells, inputs that are only numerics
• Differences in formulas in a row or column or
• Cells that contain conditional formatting or data validation behind the

Related Items: External Links, Auditing Toolbar, Excel Find Replace

The GO TO SPECIAL command is one of Excel’s best features, and probably the one that people don’t know about the most. Just to show you where it sits

• if you go to Edit,
• GO TO
• SPECIAL, you’ll see a little box pops up what it gives you, it’s basically saying “where must it go to” and you can say
• select all Comments or
• Constants or
• Formulas
• Blanks
• Row differences
• Column differences etc,

So it’s a very quick way of traveling around your spreadsheet, and you can imagine with a very big spreadsheet this can be very useful.

So for example what I want it to do is

• I want it to go to all the Constants,
• now I can specify I just want to see numbers and not worry about any of these others,
• and when I click Ok
• you’ll see it highlights all the cells that contain constants which are numbers
• and you can imagine what that makes it very easy to do is to colour it, so I can now change all those to maybe a light yellow
• and so you can Continue.

Again remember if you’ve got a section highlighted it will only look within that section, if you have one cell clicked, it’ll look through the whole section, so

• we’ll click on GO TO
• SPECIAL,
• and I can say now I’m looking for Formulas
• that have Numbers in them ,
• we say Ok
• you’ll see it highlights all the formulas,

So you can see that this is the manual way of doing what Spreadsheet Professional does in its painter, you can navigate across the sheets

There are a number of other useful features however in this feature, so let’s say we’ve got a big spreadsheet, and all over you can see these little red flags which are comments whcih are included. Now this is an old spreadsheet we want to get rid of those because they are relevant to our prior projects, you can work through the entire spreadsheet to try and find them, or

• you can click on the Comments and
• say Ok and
• you’ll see it’ll just highlights all the cells that have the comments in them
• and you can go to Edit,
• Clear comments and you can do it extremely quickly

One of the other very useful features is the ability to actually see what is different about a row or a column. So if

• you highlight for example this row here,
• and you go Edit,
• GO TO
• SPECIAL,
• you can look up here and say tell me “what in this row is different” and the way it works is it says
• that’s the first formula in which cells are different to that first formula and
• we click Ok
• and it points out that every single cell after that is different, so we can look at the formula and say ok there’s that formula we know what that’s doing,
• the next formula and presumably all the others after that does something different and you’ll see yes there’s a new formula there,
• but lets now look at that section there and see which of the formulas are different,
• so Edit,
• GO TO
• SPECIAL
• we again looking at rows differences, and what it’s going to say is “this formula is here, which of these are not an exact copy across”,
• and you click Ok
• and it points out that cell there is not the same as all the other cells,
• just to prove it, this cell would look there,
• the cell pointed out, is over there and its got a different calculation in it,

As you can see this is the manual process of doing the maps in Spreadsheet Professional, where you’d need to go through every single row or column to identify differences.

Another useful feature of the GO TO SPECIAL, is to find cells that have got programming sitting behind them, so if we say

• GO TO
• SPECIAL

We can look for two areas, conditional formats and data validation and it’s not obvious to spot in the spreadsheet itself so

• lets look for all data validation cells,
• if you click Ok,
• it points out that there are two cells that appear to have data validation in it,
• for review purposes what you might want to do is just change the colour,
• then go look there and say ok “what validation is sitting here”,
• it’s the tax rate,
• Ok,
• validation has put in it can’t be more than 100% so that might not concern you,
• you can go there and see the same thing, this the inflation rate, cannot be more than 20% and that may or may not concern you,

But what is important to understand, is the ability to navigate through your spreadsheet quickly and easily

Just to run through all the options that exist with GO TO SPECIAL.

• I’ve shown you how to use a Comments one
• if you click on Constants you can specify what you are looking for in particular,
• Similarly with Formula.
• Blanks will highlight all the blanks in a particular area
• Current region will bring out the current region and the current array
• If you ask for Objects it will highlight all objects which are the buttons pictures etc, it will enable you to delete them quite quickly
• Row differences we have been through and
• Column differences is exactly the same, you just highlight the column and specify you’re looking for differences

Precedents and Dependents are quite useful, what it allows you to do is look at all the precedents or all the dependents. So for example we are currently clicked on inflation rate 5%,

• we want to know the dependents, what other cells are relying on it,
• lets first look at the Direct only
• and if you click Ok,
• it’ll highlight the cells that rely on that cell and you can see immediately that one is missing

Alternatively, you can look for

• Dependents
• to All Levels, so it tell me all the cells that directly or indirectly refer to that cell,
• click Ok and
• you’ll see it highlights all the cells, which is quite a useful feature

The problem with this tool is that is only looks at the current sheet. So unlike the Auditing toolbar where it’ll warn you if it’s being used in other sheets, this one doesn’t do that. So that’s the Dependents and you can do exactly the same with the Precedents

• Last cell will tell you what is the actual last cell in a spreadsheet, which is useful to find stray spreadsheets.
• Visible cells show only the unhidden cells
• and I’ve shown you Conditional formatting
• and Data validation

In general this is an extremely useful tool to get to know and it’ll make your life significantly easier especially if you don’t have Spreadsheet Professional loaded.