How to highlight non adjacent cells in Excel

What are non adjacent cells and why highlight them

In Excel, non adjacent cells are cells that are not next to each other, either in the same row or column or across multiple ranges. Highlighting non adjacent cells helps you focus analysis, improve readability, and prepare data for reporting. This guide explains reliable methods to select and format multiple non adjacent ranges using keyboard shortcuts, the Go To Special dialog, and named ranges. Steps are applicable in most modern versions of Excel for Windows and Mac.

Use the Go To Special dialog to select non adjacent ranges

The Go To Special feature lets you precisely target blank cells, constants, formulas, or visible cells across disjoint areas. This method is ideal when you know the criteria for the cells you want to highlight and want to apply formatting in one pass.

Step by step selection and highlighting

1. Press Ctrl+G or F5 to open the Go To dialog.
2. Click Special to open the Go To Special dialog.
3. Choose the type of cells you want, such as Constants, Formulas, Blanks, or Visible cells only.
4. Click OK. Excel selects all cells that match the criteria in the current sheet.
5. To restrict the selection to the current region only, press Ctrl+Shift+8 (Windows) or Command+Shift+8 (Mac) after Step 3, then click OK in the Go To Special dialog.
6. With the cells selected, choose a fill color on the Home tab, or open the Format Cells dialog (Ctrl+1) to set number format, font, or border styles.

Select multiple non adjacent ranges with the keyboard

For quick, manual selection of specific non adjacent cells or ranges, use standard keyboard and mouse combinations. This approach gives you direct control over which areas to include.

• Click the first cell or range.
• Hold Ctrl (Windows) or Command (Mac), then click additional cells or drag to select other ranges.
• Release the key after selecting all ranges. The active cell remains highlighted, while the other selections are selected and can be formatted together.

Use named ranges to group and highlight non adjacent cells

Named ranges let you assign a descriptive name to one or more non adjacent ranges. Once defined, you can quickly select all parts of the range and apply consistent formatting.

Create and use a named range for non adjacent cells

1. Select the first non adjacent range.
2. Hold Ctrl (Windows) or Command (Mac) and select additional non adjacent ranges.
3. In the Name Box (to the left of the formula bar), type a name and press Enter.
4. To select the named range later, type its name in the Name Box and press Enter.
5. Apply formatting such as fill color, font weight, or number format while the named range is selected.

Format non adjacent cells with a custom conditional formatting rule

Conditional formatting can dynamically highlight non adjacent cells based on rules, such as values above a threshold, duplicates, or text patterns. Use a formula-based rule to target specific cells across disjoint areas.

Apply conditional formatting to non adjacent ranges

1. Select at least one cell or range; this determines the active range in the Apply to fields.
2. On the Home tab, choose Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter a formula that references the active range or specific non adjacent areas, for example =A1>100 or =ISBLANK(A1).
5. Click Format to set fill, font, and border options.
6. If needed, adjust Applies to to include additional non adjacent ranges using commas to separate each range reference.
7. Click OK. Cells meeting the condition are highlighted automatically, even across disjoint regions.

Considerations when working with non adjacent selections

When you paste data, copy, or export, only the selected cells are affected. Pasting a copied range over a non adjacent selection will replace contents in each selected area with the new data. Named ranges and Go To Special selections remain available for repeated use, making them efficient for recurring highlighting tasks.

Quick comparison of methods to highlight non adjacent cells