Select special types of cells

Excel 365
While working in Excel, you may need to find certain types of cells in your worksheets. For example, cells that contain comments, formulas, or only formulas with errors. See how to select all the embedded charts on the worksheet and how to select a large data range in one click in Excel.

To find special cell types in Excel, do the following:

   1.   Select the range of cells where you want to search for a specific cell type.

Don't select anything if you want to search for it throughout the workbook.

   2.   Do one of the following:

  • On the Home tab, in the Editing group, click the Find & Select button:
    Find & Select in Excel 365

    From the Find & Select dropdown list, select Go To...:

    Go To in Excel 365
  • Click Ctrl+G.

   3.   In the Go To dialog box, click the Special button:

Go To dilog box in Excel 365

   4.   In the Go To Special dialog box:

Go To Special in Excel 365
  • The Notes option selects the cells with notes. See the differences between comments and notes in Excel for Microsoft 365.
  • The Constants option selects all nonempty cells that don't contain formulas.

    Use the checkboxes under Formulas to specify types of non-formula cells to include.

  • The Formulas option selects cells with formulas.

    Use checkboxes to specify the type of formulas results:

    • Numbers
    • Text
    • Logical values (TRUE or FALSE)
    • Errors.
  • The Blanks option selects all empty cells.

    Note: If the cell contains space, a formula that returns nothing, etc., it is not empty.

  • The Current region option selects a rectangular range of cells around the active cell, determined by surrounding blank rows and columns.

    The effect is the same if you press Ctrl+Shift+* to select a range of data in columns and rows around the active cell.

    For example, for the active cell B3, Excel selects the entire data range:

    Current region in Excel 365

    See also how to select the entire column and row.

  • The Current array option selects the cells that contain a multicell array formula.

    For example, for the active cell C2, Excel selects:

    Current array in Excel 365

    Notes:

    1. An array formula enters by pressing Ctrl+Shift+Enter.
    2. You can press Ctrl+/ (forward slash) to select the cells that make up the array.
  • The Objects option selects all embedded objects, including charts, shapes, diagrams. See How to select all the embedded charts on the worksheet for more details.
  • The Row differences option analyses the selection and selects the different cells from other cells in each row.
  • The Column differences option analyses the selection and selects the cells different from other cells in each column.
  • The Precedents option selects cells referred to in the formulas in the active cell or selection (limited to the active sheet).

    Use the options in the Dependents option group to specify the type of precedents:

    • Direct only
    • All levels.

    For example:

    • the cells C1:C3 contains the sum of the columns A and B,
    • the cell C4 has a value of 7,
    • the cell C5 contains the formula = SUM (C1:C4):
    • The Direct only precedents for the cell C5:
      Direct only Precedents in Excel 365
    • The All levels precedents for the cell C5:
      All levels Precedents in Excel 365

    Notes:

    1. A formula cell's precedents are all the cells that contribute to the formula's result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn't used directly in the formula but is used by a cell you refer to.
    2. You can use the shortcuts to identify precedents:
      • Press Ctrl+[ to select all direct precedent cells on the active sheet.
      • Press Ctrl+Shift+{ to select all precedent cells (direct and indirect) on the active sheet.
  • The Dependents option selects cells with formulas that refer to the active cell or selection (limited to the active sheet).

    Use the options to specify the type of dependents:

    • Direct only
    • All levels.

    For example (the same for the precedents):

    • the cells C1:C3 contains the sum of the columns A and B,
    • the cell C4 has a value of 7,
    • the cell C5 contains the formula = SUM (C1:C4):
    • The Direct only dependents for cell A1 is only the cell C1:
      Direct only Dependents in Excel 365
    • The All levels dependents for cell A1 are cells C1 and C5:
      All levels Dependents in Excel 365

    Notes:

    1. A cell's dependents consist of all formula cells that use the cell. The formula cell can be a direct dependent or an indirect dependent.
    2. You can use the shortcuts to identify dependents:
      • Press Ctrl+] to select all direct dependent cells on the active sheet.
      • Press Ctrl+Shift+} to select all dependent cells (direct and indirect) on the active sheet.
  • The Last cell option selects the bottom-right cell in all data ranges in the sheet. Data ranges can contain any data, formulas, or even formatting.

    For example:

    Last cell in Excel 365
  • The Visible cells only option selects only visible cells in a filtered list or a table.
  • The Conditional formats option selects cells that have a conditional format applied. See Conditional formatting for more details.

    Use the options under the Data validation option group to specify types of formats to include:

    • The All option selects all cells with conditional formatting
    • The Same option selects only the cells with the same conditional formatting as the active cell.
  • The Data validation option selects cells set up for data entry validation. See Check data entry for invalid entries for more details.

    Use the following options to specify types of data validation functions:

    • The All option selects all cells with data validation rules
    • The Same option selects only the cells with the same validation rules as the active cell.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more