Use Go to Special in Excel to Find, Select, Replace and Format Cells

by Avantix Learning Team | Updated May 1, 2021

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows)

You can use Go To Special in Excel to quickly find and select cells of a specific type within your worksheet. It only selects cells in the current worksheet, not the entire workbook. Go To Special also searches within the selected range if a range has been selected. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting. After you select multiple cells with Go To Special, you can enter data in the selected cells by pressing Ctrl + Enter or by apply formatting.

Recommended article: 10 Excel Data Entry Tricks and Shortcuts Every User Should Know

Do you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses >

You can use Go To Special to select cells that meet specific criteria including:

  • Formulas (numbers, text, logicals, errors)
  • Constants (numbers, text, logicals, errors)
  • Blank cells
  • Precedents and dependents
  • Visible cells

You can access Go To Special in several ways:

  • Press Ctrl + G and then select Special in the dialog box
  • Press F5 and then select Special in the dialog box
  • Use the Find & Select drop-down menu on the Home tab in the Ribbon

The Go to Special command  appears on the Home tab in the Editing group. Click Find & Select and choose Go to Special from the drop-down menu.

The Go To Special dialog box appears as follows:

Go to Special dialog box in Excel.

Finding and selecting constants

In the Go to Special dialog box, if you select Constants and press Enter or click OK, Excel selects all cells with constants. Once you have selected constants, under Formulas, you will be able to check numbers, text, logicals or errors.

If you select all constants and then choose a fill color or other formatting, you can see all the inputs in your worksheet.

Finding and selecting formulas

In the Go to Special dialog box, if you select:

  • Formulas and press Enter or click OK, Excel selects all cells with formulas.
  • Formulas (with only Numbers checked) and press Enter or click OK, Excel selects all cells that return a number.
  • Formulas (with only Text checked) and press Enter or click OK, Excel selects all cells that return text
  • Formulas (with only Logicals checked) and press Enter or click OK, Excel selects all cells that return logicals (true or false).
  • Formulas (with only Errors checked) and press Enter or click OK, Excel selects all cells that return errors. That can be a great way to find and then fix errors.

Selecting all of the formulas in a worksheet using Go to Special can be a great way of checking structure and consistency.

Finding and selecting blanks

In the Go to Special dialog box, if you select Blanks and press Enter or click OK, Excel selects all blank cells where it perceives data ranges (it won’t select all blank cells in the worksheet). If you have selected a range first, it will find and select blanks within that range.

This can be very useful if you want to quickly format or fill blank cells.

Finding and selecting the current region or array

In the Go to Special dialog box, if you select:

  • Current Region and press Enter or click OK, Excel selects all cells in the current region. It is more common to click in the desired data range and then to press Ctrl + * or Ctrl + A.
  • Current Array and press Enter or click OK, Excel selects the entire array if the active cell is within the array.

This is helpful if you want to delete an array, because the entire array must be selected to delete it.

Finding and selecting row and column differences

In the Go to Special dialog box, if you select:

  • Row differences and press Enter or click OK, Excel selects all cells that are different from the active cell in the selected row. To use this, first select the cells you want to compare, tab to the cell you want to use as the basis for comparison and then select Go to Special. If more than one row is selected, the comparison is performed for each row based on the cell in the same column as the first active cell.
  • Column differences and press Enter or click OK, Excel selects all cells that are different from the active cell in the selected column. To use this, first select the cells you want to compare, tab to the cell you want to use as the basis for comparison and then select Go to Special. If more than one column is selected, the comparison is performed for each column based on the cell in the same row as the first active cell.

This can be useful as an auditing tool to highlight inconsistent formulas in a row or column and to spot differences across multiple columns.

Finding and selecting precedents and dependents

In the Go to Special dialog box, if you select:

  • Precedents and press Enter or click OK, Excel selects the cells that are referenced by the formula in the active cell. Under Dependents, if you selected Direct only, Excel finds only cells that are directly referenced by formulas. If you clicked All levels, Excel would find all cells that are directly or indirectly referenced by the cells in the selection. You could then apply a fill color or other formatting to the precedent cells.
  • Dependents and press Enter or click OK, Excel selects the cells that are affected by changes in the active cell. Under Dependents, if you selected Direct only, Excel finds only cells that are directly affected by changes in the active cell. If you clicked All levels, Excel would find all cells that are directly or indirectly affected by changes in the active cell. You could then apply a fill color or other formatting to the dependent cells.

This also provides an alternative to the Trace Dependents or Trace Precedents commands in Excel.

Finding and selecting the last cell

In the Go to Special dialog box, if you select Last cell and press Enter or click OK, Excel selects the last cell that contains data or formatting.

Finding and selecting visible cells

In the Go to Special dialog box, if you select Visible cells only and press Enter or click OK, Excel selects only the visible cells in a selection where there are hidden cells.

This can be useful for formatting only visible cells or copying only visible cells.

Finding and selecting conditional formats

In the Go to Special dialog box, if you select Conditional Formats and press Enter or click OK, Excel selects only the cells that have conditional formats applied. Under Data validation, if you select All, Excel finds all cells with conditional formatting. Under Data validation, if you had selected Same, Excel finds cells that have the same conditional formats as the active cell.

This can be helpful for finding, editing and removing conditional formatting. The Manage Rules command in the Conditional Formatting drop-down menu can also be used to find cells with conditional formats.

Finding and selecting data validations

In the Go to Special dialog box, if you select Data Validation and press Enter or click OK, Excel selects all cells that have data validation applied. If you click All under Data Validation, Excel finds all cells that have data validation applied. If you click Same, Excel finds all cells that have the same data validation as the active cell.

This can be very helpful if you want to find and remove data validation.

Finding and selecting comments

In the Go to Special dialog box, if you select Comments and then press Enter or click OK, Excel selects all cells with comments.

This can be very useful if you want to clear all comments from your worksheet.

Finding and selecting objects

In the Go to Special dialog box, if you select Objects and press Enter or click OK, Excel selects all objects such as shapes, images and charts. This would be a good way to select all objects and delete them.

Check out Go to Special. It’s a great tool in Excel that can save you lots of time.

This article was first published on October 15, 2014 and has been updated for clarity and content.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

More resources

How to Quickly Delete Blank Rows in Excel (5 Ways)

10 Great Excel Keyboard Shortcuts for Filtering Data

How to Replace Blank Cells in Excel with a Value from a Cell Above

How to Use Flash Fill in Excel to Clean or Extract Data (Beginner’s Guide)

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom onsite training or an online course on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

How to Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)

How to Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)

You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Use Go to Special in Excel to Find, Select, Replace and Format Data
Article Name
How to Use Go to Special in Excel to Find, Select, Replace and Format Data
Description
You can use Go To Special in Excel to quickly find and select cells of a specific type within your worksheet. It only selects cells in the current worksheet, not the entire workbook. Go To Special also searches within the selected range if a range has been selected. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting. After you select multiple cells with Go To Special, you can enter data in the selected cells by pressing Ctrl + Enter or by apply formatting.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This