3 years ago 3 years ago

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

by Sudipta Mondal 3 years ago3 years ago

8.3kviews

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.

TABLE OF CONTENTS:

(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.

Table: Options for Selecting a Cell by Type

Option	Description
Comments	Selects all cells that contain a comment.
Constants	Selects all cells that contain constants (never the formula) of the types specified in one or more of the checkboxes listed under the Formulas option.
Formulas	Selects all cells containing formulas that produce results of the types specified in one or more of the following four checkboxes. • Numbers – Selects all cells that contain numbers • Text – Selects all cells that contain text • Logicals – Selects all cells that contain logical values • Errors – Selects all cells that contain errors
Blanks	Select the range ➪ Then apply the option, which will select all the cells that are blank.
Current region	Select any cell in a range ➪ Then apply the option, which will select the entire dataset. • Equivalently, we press Ctrl+A to select the current region. • Equivalently, we press Ctrl+* (present in number keypad) or Ctrl+Shift+* (above the letter keypad) to select the current region. • Equivalently, Select the heading row by Shift+Spacebar ➪ Move downward with Ctrl+Shift+⬇
Current array	Select any cell in a range ➪ Then apply the option, which will select all the arrays in the data range
Objects	Select any cell in a range ➪ Then apply the option, which will select all the objects including the text box
Last Cell	Selects the last cell in the worksheet (that is, the lower-right corner) that contains data or formatting. • Equivalently, we press Ctrl+End to move to the last active cell (either contains data or formatting).
Visible cells only	Selects only cells that are unhidden. It is helpful to copy the cell references in the filtered cells.
Conditional Formats	Selects only cells that contain conditional formatting. • All – Selects all cells that contain conditional formats • Same – Selects all cells that contain similar conditional formats as the current cell
Data validation	Selects cells that contain data validation rules. • All – Selects all cells that contain a data validation rule • Same – Selects all cells that contain a similar validation rule as the current cell.

➢ 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

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.

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.

If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.