How to use the Excel Go To Special Tool to find constants, formulas, blanks, row and column differences, conditional formatting and data validation.
Excel Goto special tool
A much underutilized tool is the Excel GO TO special tool and particularly the button marked SPECIAL. You can find it under HOME/ EDITING/ FIND % SELECT as shown below (For Excel 2003 use EDIT/ GOTO/ SPECIAL)
Below is a description of each function:
- Comments- will highlight all cells containing comments (useful to delete unwanted comments)
- Constants- will highlight all cells containing constants of the kind required (number, text, logical, error)
- Formulas- will highlight all cells containing formulae of the kind required (number, text, logical, error).
- Blanks- will highlight all blank cells
- Current region- will highlight all cells in the current region
- Current Array- will highlight all cells in the current array.
- Objects- highlights all objects on the sheet (e.g. buttons, pictures etc)
- Row/ Column Differences- will highlight which cells in a selection contain different formulae.
- Precedents/ Dependants- will highlight the precedence or dependents of a cell, either to one level or all levels.
- Last cell- will identify the last cell in a sheet (useful to find stray cells).
- Visible cells only- highlight only cells that are not hidden (useful to make changes to what you can see without affecting hidden cells)
- Conditional formats- highlights cells that have conditional formatting on them (either any conditional format or the same format of the current cell).
- Data Validation- highlights cells that have data validation on them (either any validation or the same validation as the current cell).
Watch the Excel video tutorial to see more.
Excel Video Tutorial Transcripts
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
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
- 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
- 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
- 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
- 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
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,
- 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
- 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.