Select Non-Empty Cells in Excel: Methods That Work

How-to basics · Updated November 18, 2026

To select every non-empty cell in a chosen range—including both typed entries and formulas—in Excel for Microsoft 365 on Windows, use the VBA macro below: it combines Excel’s formula cells and constant cells into one selection. The built-in Go To Special command selects those two groups separately, so it is useful when you need just one group or want to inspect what Excel considers populated.

What Excel Counts as a Non-Empty Cell

Excel can treat a cell as occupied even when it looks blank. A cell containing a number, text, date, error, or formula has content. A formula that displays an empty string, such as a formula returning "", is still a formula and is therefore not truly empty to Excel. A cell containing one or more spaces is also populated, even though the spaces can be difficult to see.

By contrast, a cell that has only formatting—such as a fill color, border, or number format—but no entry or formula is normally considered blank by Go To Special’s Constants and Formulas options. This distinction matters if your goal is to find cells that look occupied, rather than cells that actually contain something.

Before selecting, decide what “non-empty” means for your task. For example, if you want to remove records with no entered values, a formula returning an empty string may still need attention. If you want to select every cell that contains a formula for auditing, that same formula should count even when its displayed result is blank.

Use Go To Special for Constants or Formulas

Go To Special is the quickest built-in option when you want to select populated cells of one type within a defined area. First select the range to search. Then choose Home > Find & Select > Go To Special. In the dialog, choose Constants to select typed entries, or choose Formulas to select formula cells.

For Constants, the dialog lets you narrow the selection to numbers, text, logical values, or errors. Leave all four boxes selected if you want every kind of constant. For Formulas, the corresponding result-type boxes let you narrow the selection by the kind of value the formula returns. Leave them all checked to find formulas regardless of displayed result.

These are separate choices: selecting Constants does not include formulas, and selecting Formulas does not include typed values. If you need both categories selected simultaneously, use the macro in the next section. If you only need to format or inspect one category, Go To Special avoids running code and is usually the simpler choice.

Select Constants and Formulas Together with VBA

This macro combines the constants and formulas found inside the range you select. It then selects their union, meaning one selection can contain separate blocks or individual cells. Select a cell range first, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Run the macro with the cursor inside it, or return to Excel and run it from the Macro dialog.

Sub SelectNonEmptyCells()
    Dim targetRange As Range
    Dim constantCells As Range
    Dim formulaCells As Range
    Dim resultRange As Range

    ' Use the currently selected cell range as the search area.
    Set targetRange = Selection

    ' SpecialCells raises an error when it finds no matching cells.
    On Error Resume Next
    Set constantCells = targetRange.SpecialCells(xlCellTypeConstants)
    Set formulaCells = targetRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not constantCells Is Nothing Then
        Set resultRange = constantCells
    End If

    If Not formulaCells Is Nothing Then
        If resultRange Is Nothing Then
            Set resultRange = formulaCells
        Else
            Set resultRange = Union(resultRange, formulaCells)
        End If
    End If

    If resultRange Is Nothing Then
        MsgBox "No non-empty cells were found in the selected range."
    Else
        resultRange.Select
    End If
End Sub

For example, if you select A1:C20 before running the macro, it selects the cells with constants or formulas inside that area and leaves genuinely empty cells out. It does not change cell contents. If the selected range contains no constants or formulas, Excel displays a message instead of making a selection. The macro assumes the active selection is a worksheet cell range; select cells before you run it.

Save a copy of the workbook before running any macro. Macros cannot be undone with Ctrl+Z, and a workbook containing VBA generally needs to be saved in a macro-enabled format such as .xlsm if you want to keep the code. On a Mac, Excel also has a Visual Basic Editor, though the shortcut and security prompts may differ; use the Developer tab or Excel’s macro commands if the Windows shortcut is unavailable.

Choose the Range Before Selecting

Scope is important. If you want to work only in a table column, select that column’s data area first rather than the entire worksheet. Go To Special searches the selected area, and the macro uses the current selection as its target. For instance, selecting B2:B500 before choosing Constants is safer than selecting all of column B when you are about to delete or format the results.

You can select a rectangular range by dragging across cells or by clicking the Name Box to the left of the formula bar, typing a reference such as A2:D100, and pressing Enter. The Name Box is also handy for large ranges that are hard to reach by scrolling. If your data is an Excel table, select the relevant table column or data cells so headings and unrelated worksheet content are not included.

On Windows, Ctrl+G or F5 opens the Go To dialog; from there choose Special. The ribbon route is easier to discover and works without remembering a shortcut. In Excel for Mac, the menus and dialog are similar, while keyboard shortcuts can differ depending on the keyboard and macOS settings. Excel for the web does not provide the desktop VBA editor, so use the desktop app for the macro method.

Why Ctrl+A and Find Blanks Are Different

Ctrl+A does not mean “select every non-empty cell.” In a data region, it commonly expands the selection to the current region, which can include blank cells inside the region and may stop at blank rows or columns. Pressing it again can select more of the worksheet, depending on where the active cell is. That makes it useful for selecting a table-like block, but not for isolating only cells with content.

Similarly, Go To Special > Blanks selects empty cells, not populated cells. It can help locate gaps in a range, but it is not an inverse-selection command: Excel does not turn the blank selection into a selection of all remaining non-empty cells through a simple built-in toggle. If you need the combined set of constants and formulas, the macro avoids relying on that assumption.

Find can be useful for locating particular text, but a wildcard search is not a dependable all-types replacement for this task. Search behavior depends on what is being searched and how the cell is stored; a search aimed at text should not be assumed to capture numbers, errors, and formulas consistently. Use Go To Special for a category or the macro for the combined selection.

Common Problems and How to Check Them

  • A cell looks blank but gets selected: inspect the formula bar. The cell may contain a formula returning an empty string, a space, or another invisible character.
  • A cell looks filled but is not selected: it may have formatting only, or the value may be outside the range you selected. Check the cell’s contents and confirm the search area.
  • Only some populated cells are selected: Go To Special may be set to Constants or Formulas rather than both. Use the macro for a combined selection, or run the relevant built-in command to inspect each category separately.
  • The macro reports no matches: confirm that a worksheet cell range was selected and that it includes the cells you expect. A formula that displays nothing should still count as a formula.
  • The selection includes unexpected cells: check for spaces, formulas, or error values, and make sure the target range does not include headers or adjacent data.

When results seem inconsistent, test a few representative cells. Click a cell and look at the formula bar: a visible value may be stored as a constant, while a displayed result may come from a formula. This quick check often explains why a cell is selected even when it appears empty, or why a formatted cell is left out.

After the Cells Are Selected

A selection is only the starting point. You might apply a fill color, copy the cells, inspect their formulas, or clear their contents. Be cautious with actions that remove data: when a selection contains multiple areas, a delete or paste operation can have consequences different from editing one continuous block. Confirm the selection outline and the target range before changing or clearing cells.

To check the scope, look at the highlighted cells and the Name Box. For a selection made with Go To Special, Excel can show a multi-area selection where not every cell between the highlighted cells is selected. Do not assume that a rectangular outline means all cells inside it matched. If you plan to delete values, consider copying the range to a spare sheet or saving a version of the workbook first.

Frequently Asked Questions

How do I select all non-empty cells in an Excel range?
Select the range, then use the VBA macro above to combine constants and formulas. Go To Special can select either constants or formulas separately, but not both in one category choice.

Does Excel count a formula returning an empty string as blank?
No. The cell contains a formula, so Go To Special > Formulas and the macro include it even if the displayed result looks empty. A function that checks displayed text may treat it differently, so match the method to your goal.

How do I select nonblank cells in one column without VBA?
For only constants or only formulas, select the column’s data range and use the corresponding Go To Special option. If you need both types selected together, Excel’s standard Go To Special categories are separate; use the macro or handle the categories in separate operations.

Will Go To Special select cells that only have formatting?
Not when you choose Constants or Formulas. Those options look for cell contents or formulas, not merely borders, colors, or number formats. A formatted but otherwise empty cell is not a populated cell under this method.

Can I do this in Excel for the web?
You can use web features for many selection and editing tasks, but the VBA editor and VBA macros are desktop Excel features. Open the workbook in Excel for Windows or Mac to run the macro; the Go To Special options available can vary by platform.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.