Go To Special

What is the Go To Special Function in Excel?

The Go-To Special tool in Excel allows you to select a specific group of cells based on defined criteria.

Working on Excel models means many different types of cell values bind the model together. It can include text strings, numbers, logical values, or even errors if the values are missing.

The comments can be added wherever the user might feel it necessary, or cells can even be left blank for future use.

If you are to work on such huge Excel models on a weekly or monthly basis, it is not easy to remember which cells were left blank and which cells have logical formulae.

The option left to you is to check all the individual cells using the F2 key, and if you find the desired value by God's grace, you can make the necessary changes.

However, what if we say there is a loophole or tool you can use to select the desired group of cells quickly?

This is where the 'Go-to Special' tool comes in for our rescue, potentially saving us time while working on such complicated Excel models. Well, more on that in the subsequent sections and how to use this wonderful tool.

Understanding the Go To Special Tool in Excel

It is a special breed of tool that will allow you to select a cell with a particular value in the selected spreadsheet.

For example, assume you must select all the cells with hardcoded numerical values in your financial model. Then, you must open the Go To Special tool > select the radio box for Constants and click on Ok.

You will see that all the constant numerical values get selected upon which further manipulations or analysis can be done.

This tool is a great way to select cells that contain:

• Numbers, text strings, logical values, errors as a result of the formula in the cell

• Numbers, text strings, logical values, errors that are constants or hardcoded

• Blank cells

• Objects

• Last cells

• Precedents and Dependents 

So how to use this special tool?

In general, you can follow three methods to use the tool in Excel. They are

• By pressing the F5 key on the keyboard

• Using the combination of Ctrl + G keys

• Clicking on Home > Find & Select > Go To Special

In the first two methods, you will initially find that the dialog box opens, as illustrated below:

This simple Go To tool allows you to jump directly to the cell reference you input in the dialog box. However, to open the Go To Special tool, we need to click on the 'Special,' which will open up the tool as

We are good to go ahead and see how the tool works in action, which we will cover in our next section.

Example of Go To Special in Excel

What else could be better than a financial model to understand how the tool works? 

A financial model consists of all the different components you might see in the Go To Special tool - constants, formulas that return the result either as text strings, numbers, or even comments about the various assumptions in the financial model.

By the way, if you are looking for a Financial Modelling course, we have you covered! Learn how to build an in-depth financial model using Nike's financial statement and further estimate the stock price using the DCF method.

Returning to the Go To Special, let's see some of the scenarios on how you can select a specific group of cells using the tool:

1. Selecting all the constant numbers/hardcoded numbers in the spreadsheet

Suppose you have finally forecasted the Income Statement for Nike three statement model, as illustrated below:

We know that blue font color is usually used for historical numbers or any numerical value hardcoded in the financial model. Here, the hardcoded numbers are not in the blue font color.

What would you do here?

Here, we will open the tool using the F5 key > select the radio button for 'Constants' and tick the check box only for 'Numbers.'

After you click on Ok, you will find that all the constant numbers get selected, as illustrated below:

Now all you need to do is select the blue font to give you the result in the spreadsheet:

What once was a tedious task becomes a walk in a park.

2. Errors due to formulae

If you are working on financial models, chances are you will be getting a lot of different types of errors. The various errors you will see are #VALUE!, #N/A!, #DIV/0!, #NUM!, #REF! etc., which can affect the integrity of the financial model.

Suppose we want to capture all the errors arising from the formula used in the spreadsheet. In that case, we can use the Go To Special tool by following the steps below:

• Press the F5 key or Ctrl + G key

• Click on Special

• Select the radio button for Formulas and then tick the checkbox for Errors.

• Finally, click on Ok, which will select all the cells consisting of errors from formulae.

Now, you can take further action on those error values or highlight them using a color fill to make them easily accessible in case you want to review them the next time.

3. Selecting the blank cells

The Go-To Special tool doesn't just identify and select cells containing a particular value. It can also identify blank cells and select those cells from the spreadsheet.

Suppose you have the test scores for the students, as illustrated below:

If a person who isn't aware of the Go To Special tool's capabilities would individually select all the blank cells and then highlight them using cell fill to help in better differentiation.

On the other hand, using the Go To Special, you only need to select the radio button for 'Blanks' and click on Ok. 

This will select all the empty cells on the spreadsheet, as illustrated below:

You can ignore the cells outside the table, but this is how capturing the blanks generally works. Do you notice that although the rest of the spreadsheet is empty, it still doesn't get selected using the Go To Special?

What happens is that only the range up to which the data exists is evaluated for the blank cells, i.e., column E and row 12. Since data does not exist beyond those points, the Go To Special tool selects no other blank cells. 

4. Selecting the objects on the spreadsheet

Objects refer to all the shapes, images, charts, etc., on the spreadsheet that aid in the visual representation of the given data.

There could be instances where you do not find those images and shapes necessary any longer and want to remove them altogether.

Select the radio button for objects in Go To Tool >click on Ok, which will select all the things on the spreadsheet, as illustrated below:

One was an icon we used to represent the findings of the pie chart, while the other was a graph. You can directly remove those objects from the spreadsheet or re-align them accordingly.

Other uses for Go To Special

All the examples we saw before this section are something you would need daily.

We also saw other options: Precedents, Dependents, Last cell, Current region, Data Validation, etc.

This section will cover those options, and you can then decide in what situations you might need to use them accordingly.

1. Precedents

What do we mean by precedents here? Again, an example could help to understand it better.

Suppose you have the data as illustrated below:

We know that the values in column D result from multiplication between the values in columns B and C. So, for example, $2 price x 21 items sold equals $42.

Let's assume we know the cells referenced to derive the sales. In this case, we will select the range D3:D8, select the radio button for Precedents, and click on OK. This will give us the result:

Precedents will help you determine data sources that feed into our selected cells.

2. Dependents

If you understand what precedents are, you need to know that dependents work exactly opposite their counterparts.

We know that the arithmetic calculations between price and items sold give us the sales in column D.

If we select the values in either the B3:B8 or C3:C8 range and select the radio button for dependents, we get the selected cells as range D3:D8 since it is the range into which the selected cells feed the data.

3. Current region

This option will allow you to select the current region in the spreadsheet. For example, suppose you have the data illustrated below:

Notice that the current cell position is in cell G3. If you use the current region, you see that the table in range G3:H8 gets selected. Since a blank column separates the tables, the adjoining table is not specified in the 'current region.'

4. Conditional formats

Let's say you have the conditional formatting tool in action, highlighting criteria-based cells in the dataset.

To identify the range on which the conditional formatting tool works, we will select the radio box for Conditional formats > All and click on Ok.

The selected cells on the spreadsheet will be:

Conclusion

Excel's "Go To Special" function is a flexible and effective tool that greatly increases efficiency and productivity when handling data. The capability to identify particular cell types—for example, formulas, constants, blanks, or errors—saves time during the data analysis, cleanup, and manipulation phases.

"Go To Special" gives users focused selection options so they may quickly find and fix abnormalities, irregularities, or certain data patterns in their spreadsheets. This feature offers an extensive set of selection criteria that meets a variety of user needs, whether they are related to formatting, data validation, auditing, or other uses.

As more and more businesses use Excel to make data-driven decisions, knowing how to use the "Go To Special" feature is crucial to increasing output and guaranteeing data accuracy.

Adopting it as a core Excel function gives users the capacity to effectively handle and work with data, which in turn promotes informed decision-making and organizational success.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Advanced Excel Formulas
• COLUMN Function in Excel
• Documenting Excel Models
• Excel Shortcuts Overview
• Group in Excel