12 Examples || How to Use Excel Go To Special?

When editing a worksheet, Excel Go To Special command and Go To selection command are very helpful to a certain extend. Maximum Excel users either avoid or fail to avail these smart options.

For example, if we know the cell reference of the cell or range we want to move to, it is faster and more efficient to use Excel Go To command. Once we have used Go To, a list of the previously visited cells and ranges will also appear in the list box. We can easily return to these locations by clicking these references and then clicking OK, or simply by double-clicking the reference. 

We may also notice that once we have used the Go To command, the Reference box at the bottom of the dialog box shows our previous location. To return to this location, simply press Enter or OK. This feature allows us to toggle between two locations by simply pressing the F5 Key and Enter. 

The Special… button on the Go To box provides a mechanism for selecting specific ranges in our worksheet based upon the contents of the cell. When we choose this button, Excel Go To Special dialog box appears. Many of these options can be helpful when we are reviewing a worksheet or when we are auditing a worksheet. For example, we may want to select all of the formulas in our worksheet that display error values. Simply choose Formulas and check the box labelled Errors. Be sure to uncheck the other Formula choices. 

(A) HOW TO ACTIVATE EXCEL GO TO SPECIAL?

We can activate the Excel Go To Special dialog box either in 03 ways:  

(01) Using the Excel Shortcut: Press Ctrl+G ➪ Go To dialog box opens ➪ Either click Special button or press Alt+S ➪ Go To Special dialog box opens.

(02) Using the Function Key: Press F5 ➪ Go To ➪ Click Special or Alt+S ➪ Go To Special

(03) Using the Ribbon: Go to the Home tab ➪ Find & Select dropdown in the Editing section ➪ Either select Go To or Go To Special (both options are available).

Note: We had detail explained the Excel Shortcut in two separate tutorials and requested you to read these tutorials:

80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts

90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |

(B) HOW TO USE EXCEL GO TO COMMAND?

01) EXCEL ‘GO TO’ COMMAND HELPS TO MOVE TO A CELL

Suppose we want to move to Cell J2 of the current worksheet:

Place cursor anywhere in the worksheet ➪ Press Ctrl+G / F5 ➪ simply enter a cell reference (e.g., J2) in the Reference box at the bottom of the dialog box ➪ choose OK or press Enter. Excel will activate and display the cell.

Note: Upper- or lower case does not matter at all.

02) EXCEL ‘GO TO’ COMMAND HELPS TO SELECT BIG RANGE OF CELLS

Suppose we want to select the range A2:J11 of the current worksheet:

Place the cursor anywhere in the worksheet ➪ Press Ctrl+G / F5 which will launch the Excel ‘Go To’ dialog box ➪ Simply enter a cell range we want to select (for example, A2:J11) in the Reference box at the bottom of the dialog box ➪ choose OK or press Enter.

Excel will activate and display the cell range.

03) EXCEL ‘GO TO’ COMMAND HELPS TO MOVE ANOTHER WORKSHEET OF THE SAME WORKBOOK

Suppose we want to move from the current worksheet to a Cell J2 in Replace tab/worksheet.

First, launch the ‘Go To’ dialog box and type the name of the worksheet wants to move to, followed by an exclamation mark and then type Cell reference. For example, Replace!J2.

04) EXCEL ‘GO TO’ COMMAND HELPS TO MOVE ANOTHER WORKSHEET OF DIFFERENT WORKBOOK

Suppose we want to move from the current workbook to Cell J2 in Replace worksheet.

First, launch the ‘Go To’ dialog box and type the name of the worksheet wants to move to, followed by an exclamation mark and then type Cell reference. For example, Replace!J2.

If we want to go from one workbook to another worksheet in any other workbook, open the Go To dialog box and type in the following format:

‘[name of the workbook] worksheet’!Cell name

Note: Remember than both workbooks should be opened.

For example, to move to cell A2 in sheet 1 of the workbook named Sales Master Data, open the

‘Go To’ dialog box and type in the following command.

‘[Sales Master Data]sheet1’!A2

(C) HOW TO USE EXCEL GO TO SPECIAL COMMAND?

The Excel Go To Special dialog box contains many options to select cells, according to the type of contents they contain.

➢ Example-1: HOW TO USE EXCEL ‘GO TO SPECIAL’ COMMENT OPTION?

Select any cell in a range ➪ Press Ctrl+G or the F5 key which will open the Excel ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special‘ dialog box ➪ Choose ‘Comments’ radio button ➪ Click OK or press Enter which will select all cells that contain a comment.

➢ Example-2: HOW TO USE EXCEL ‘GO TO SPECIAL’ CONSTANTS OPTION?

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Constants’ radio button; additionally, Excel by default selects the Numbers, Text, Logicals and Error checkboxes ➪ Click OK or press Enter which will select all cells that contain numbers, texts, errors, but it does not select the cells with formulas. 

➢ Example-3: HOW TO USE EXCEL ‘GO TO SPECIAL’ FORMULAS OPTION?

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Formulas’ radio button; additionally, Excel by default selects the Numbers, Text, Logicals and Error checkboxes ➪ Click OK or press Enter which will select all the cells with formulas (starting with an equal sign).

➢ Example-4: HOW TO USE EXCEL ‘GO TO SPECIAL’ ERROR OPTION?

If we select the Constants radio button and Errors checkbox, it will select all the errors except in the formula.

Similarly, when we select the Formulas radio button and Errors checkbox, which will select all the errors in the formula.

➢ Example-5: HOW TO USE EXCEL ‘GO TO SPECIAL’ BLANKS OPTION?

⇒ Fill Blank Cells with Zero Value

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Blanks’ radio ➪ Click OK or press Enter which will select all the cells that are blank.

After selecting cells, we can fill the color, put any values (e.g., zero ‘0’). As selected, put 0 in one cell and then press Alt+Enter. As a result, all cells are filled with the value 0.

⇒ Fill Blank Cells from the Respective Above Cell

In the second example, we put the Country name in blank cells based on the Country name from the respective above cell. 

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Blanks’ radio ➪ Click OK or press Enter which will select all the cells that are blank.

Then place an equal sign and select the above cell reference.

Then press Ctrl+Enter which will apply the respective Country name in the blank cells.

Then convert the formula into text with the help of Excel Paste Special. Copy the range ➪ Press Alt+E+S+V (sequentially press Alt, E, S, V) or Alt+Ctrl+V+V  (sequentially press Alt+Ctrl+V, V) which will select the Value option in the Paste Special dialog box ➪ Click OK or press Enter which will convert all the formulas into value.

➢ Example-6: HOW TO USE EXCEL ‘GO TO SPECIAL’ OBJECTS OPTION?

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Object’ radio ➪ Click OK or press Enter which will select all the objects including the text boxes.

➢ Example-7: HOW TO USE EXCEL ‘GO TO SPECIAL’ CONDITIONAL FORMATS OPTION?

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box ➪ Choose ‘Conditional Formats’ radio ➪ Click OK or press Enter which will select only cells that contain conditional formatting.

➢ Example-8: HOW TO USE EXCEL ‘GO TO SPECIAL’ DATA VALIDATION OPTION?

In the given example, validation rules applied to the Country column (i.e., column B).

Select any cell in a range ➪ Press Ctrl+G or F5 which will open the ‘Go To’ dialog box ➪ Click ‘Special’ button or press Alt+S which will open the ‘Go To Special’ dialog box➪ Choose ‘Data Validation‘ radio ➪ Click OK or press Enter which will select cells that contain data validation rules.

• All – Selects all cells that contain the data validation rule (Excel by default select this option).
• Same – Selects all cells that contain a similar validation rule as the current cell.