Next Live Training Date:

17-21 Aug, 17-18 Sept (VBA)

Not sure which of these videos to watch? Try out our 'Which Excel Formula to Use' App to get guidance. NOTE: All these free Excel video tutorials are streamed via YouTube. If your network does not allow YouTube videos, you can access the video via your smartphone or from a different network.

Excel Go To Special Tool

Excel Go To Special Tool
Watch this video

How to use the Excel Go To Special Tool to find constants, formulas, blanks, row and column differences, conditional formatting and data validation.

NEW: Do you know about Excel’s new PowerPivot tool?

  • 7:15
  • Skill Level:
  • What's my skill level
    Flying screens

    All of our videos are listed by user Excel Skill Level.

    To find out your personal skill level, download our quick assessment.

    Once you've completed the test, send it to us and we'll evaluate for you.

    Download Test

  • Watch this Video

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)

Related Items: External LinksAuditing ToolbarExcel Find Replace

Excel Go To special tool

By clicking on the desired option and clicking OK, Excel will highlight all the cells on the sheet (or the selection) that meet the specified criteria.Excel Go To Special tool

Below is a description of each function:

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.

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

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

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

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

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

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

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

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.

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%,

Alternatively, you can look for

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

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.

Free Tips & Tricks Newsletter

Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!

Handy Tips & Tricks

Excel not recognising formula

Excel not recognising formula Occasionally when you are working with files (typically involving imported data) it appears as if Excel is not recognising the formula as a formula. Instead of seeing the answer, you see the actual formula. Below is a … Continue reading

Public VBA Course, Rugby WC, BODMAS Rule, Excel and non Excel windows side by side

In this issue: Reward- Quick Reference Guide – See below Next Excel course dates 14-18 Sep Excel VBA Public Course- 17-18 Sep BODMAS rule and Excel NB In houses Courses- See below Replacing quotes in Excel How to use our … Continue reading

“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”

– Absa Capital

Video Library & Tutorials //

View Entire Video Gallery ››

Tables in Excel for PowerPivot use

Tables in Excel for PowerPivot use

Excel see formula instead of value 2013

Excel see formula instead of value 2013

Excel Quick Sum 2013

Excel Quick Sum 2013

Excel Group Rows 2013

Excel Group Rows 2013

Some of our Featured Clients