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

by Avantix Learning Team | Updated September 14, 2023

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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.

Find and select 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 18 King Street East, Suite 1400, 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 instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix® Learning

You may also like

What is Power Query in Excel?

What is Power Query in Excel?

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at 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