Missing values
How to Find, Count, Highlight and Fill Blank Cells in Excel
Blank cells are the most visible kind of missing value, but the same gap can also appear as N/A, null, a dash or a formula that returns an empty string. Find and count the gaps first, highlight them without changing any data, and decide what each blank means before you fill it.
By the TidyX team · Updated
How to find and count blank cells in Excel
Work on a copy and select the data range before you start. With a single cell selected, Go To Special searches the whole worksheet instead.
- To select every empty cell in the range, choose Home → Find & Select → Go To (or press Ctrl+G), select Special, choose Blanks and select OK. The selection shows where the gaps are without changing any value.
- To count the gaps in one column, enter =COUNTBLANK(A2:A101), adjusted to your data. COUNTBLANK also counts formulas that return an empty string (""), but not cells that contain zero.
- To mark each row, enter =ISBLANK(A2) in a helper column and fill down. ISBLANK returns TRUE only for a truly empty cell; use =A2="" instead if formulas that return an empty string should count too. Neither check treats a cell that contains a space as empty.
- To review the records, filter the helper column to TRUE. You can also select Data → Filter, open the column’s filter, clear (Select All) and check (Blanks). Filtering hides the other rows; it does not delete them.
Count empty cells in your browser
The local check on the TidyX home page counts empty cells in a CSV or XLSX file without uploading it, and names up to three of the most affected columns with the share of empty cells in each. Rows that are blank in every column are counted separately; the free blank-row tool can remove them after you review each one.
How to highlight blank cells in Excel
Highlighting shows where the gaps are and changes only the formatting; every value stays as it was.
Use the colored cells to decide whether each blank means unknown, not applicable or zero before you fill or remove anything.
- Select the data range, then choose Home → Conditional Formatting → New Rule.
- Under Select a Rule Type, choose Format only cells that contain. In the Format only cells with list, select Blanks.
- Select Format, pick a color on the Fill tab, and select OK in both dialogs.
- To color the whole row when a key cell is empty, select the data rows and, in step 2, choose Use a formula to determine which cells to format. Enter a formula such as =$C2="", where C is the key column and 2 is the first selected row.
Tell blank cells apart from N/A, null and other placeholders
Exports often fill a gap with text such as N/A, NA, null, none, a dash or unknown. Excel treats that text as a value, so COUNTBLANK and the Blanks rule skip it. Count each placeholder you find with =COUNTIF(A2:A101,"N/A"); COUNTIF criteria are not case sensitive, so n/a is counted too.
Keep the text N/A separate from the #N/A error value (value not available) that formulas can return. ISNA identifies the error; check the formula that produced it rather than treating it as a placeholder.
In TidyX, an empty cell counts as missing when a file is profiled; a cell that holds only spaces does not. The profile reports the missing percentage of each column and can flag placeholder tokens such as N/A, null, none, a dash or unknown.
The supported normalize-missing-tokens operation converts an explicit list of tokens into the engine’s standard missing representation. It matches whole cells, ignoring case and surrounding spaces. The target columns and token list are visible in the plan, can be edited through controlled parameters, and remain part of the frozen run snapshot.
This is different from guessing a replacement value. Normalization makes missing data consistent so downstream tools can count and handle it predictably without inventing observations.
How to fill blank cells in Excel, and when not to
Fill a blank only when you know what it should hold. A blank is not automatically zero: AVERAGE ignores empty cells but includes zeros, so typing 0 into the gaps changes the result.
If you have no defensible replacement, leave the cell empty and note it in your analysis. Do not delete an entire row merely because one cell is blank.
When a label appears once per group and the blanks below it mean the same label, fill them with the value above:
- Select the column from the first label to the last row, then use Go To Special → Blanks to select the gaps.
- Type = and the address of the cell directly above the active cell, for example =A2 when A3 is active, then press Ctrl+Enter. The relative reference adjusts for each selected blank, so every gap shows the value above it.
- Copy the column and paste it back with Paste Options → Values Only before you sort or filter, so the filled entries no longer depend on the rows above.
Fill blanks with a constant
Select the blanks with Go To Special, type the value, such as 0 or Unknown, and press Ctrl+Enter. Use a constant only when it is the true value or a documented code.
Fill blanks with a statistic
Calculate replacements in a new column, for example =IF(ISBLANK(A2),MEDIAN($A$2:$A$101),A2), and keep =ISBLANK(A2) in another column so every filled row stays visible. MEDIAN, like AVERAGE, ignores empty cells, and a filled value is an estimate, not an observation.
Fill down in Power Query instead
For a table you refresh often, select a cell in it and choose Data → From Table/Range. In the Power Query Editor, right-click the column and choose Fill → Down: Power Query replaces null values with the last nonempty value above them. If the gaps load as empty text rather than null, first use Replace Values with Value To Find left blank and null as the replacement. Choose Home → Close & Load when you are done.
What TidyX fills, and what it does not
Mean, median, mode, constant, interpolation, or model-based imputation can materially change analysis. The choice depends on column meaning, distribution, missingness mechanism, and downstream use—not simply on which function is available.
TidyX supports fixed, mean, median, mode and groupwise filling. Each is a high-risk rule that stays blocked until you acknowledge it, and its targets and parameters are validated. Mean and median use only the numeric values in a column; when a column has no usable values, the blanks stay and the report shows a warning. Nothing is filled by default.
No rule fills a blank with the value above it, and interpolation and model-based imputation are not part of the current allowlist. For a value-above fill, use Excel or Power Query and review the result.
- Supported: normalize explicit missing tokens
- High-risk review: drop fully empty rows or columns
- High-risk review: fixed, mean, median, mode or groupwise fill
- High-risk review: drop rows missing values in named columns
- Not supported: filling from the value above
- Not supported: interpolation or model-based imputation
Verify the result before you use it
In Excel, run COUNTBLANK again after filling, compare the result with your first count, and keep the column that marks filled rows next to the data.
In TidyX, pre-run AI estimates are preliminary. The completed report uses actual Worker results and warnings, so a rule that encountered failed values or unexpected input remains visible.
The cleaned file is a new artifact. If a reviewed rule removes empty rows or columns, that change applies only to the output and is represented in the report rather than overwriting the source.