Back to Articles
Tutorials

How to Clean Data with Missing Values: Complete Handling Guide

Learn how to clean data with missing values in Excel. Discover methods to identify, handle, and fill missing data using various strategies for accurate analysis.

RowTidy Team
Nov 19, 2025
13 min read
Excel, Data Cleaning, Missing Data, Data Quality, Analysis

How to Clean Data with Missing Values: Complete Handling Guide

If your Excel data has missing values—blanks, "N/A", or NULL entries—your analysis will be incomplete and inaccurate. 75% of datasets contain missing values that need proper handling before analysis.

By the end of this guide, you'll know how to identify, handle, and clean missing values in Excel—ensuring complete, analysis-ready datasets.

Quick Summary

  • Identify missing values - Find blanks, "N/A", NULL, and other missing data representations
  • Choose handling strategy - Remove, fill, or impute missing values
  • Fill missing data - Use various methods to complete datasets
  • Validate completeness - Ensure data quality after handling missing values

Common Types of Missing Values

  1. Blank cells - Empty cells (truly missing)
  2. "N/A" or "NA" - Text representing missing data
  3. "NULL" or "null" - Database-style missing indicators
  4. "-" or "--" - Dash representing missing
  5. "0" as missing - Zero used to represent missing (problematic)
  6. "?" or "???" - Question marks indicating unknown
  7. Whitespace - Spaces that look like blanks
  8. "Missing" or "Unknown" - Text labels for missing data
  9. Inconsistent representations - Mix of above formats
  10. Systematic missing - Missing in patterns (e.g., all weekends)

Step-by-Step: How to Clean Data with Missing Values

Step 1: Identify Missing Values

Before handling, identify all missing value types.

Find Blank Cells

Method 1: Go To Special

  1. Select data range
  2. Press F5 (Go To)
  3. Click Special
  4. Select Blanks
  5. Click OK
  6. All blank cells selected

Method 2: Conditional Formatting

  1. Select data range
  2. Home > Conditional Formatting > Highlight Cells Rules > More Rules
  3. Choose Blanks
  4. Set format color
  5. Click OK
  6. Blanks highlighted

Method 3: Formula Detection

=IF(ISBLANK(A2), "Missing", "Has Value")

Returns "Missing" for blank cells.

Find Text Representations

Find "N/A", "NULL", etc.:

=IF(OR(A2="", A2="N/A", A2="NULL", A2="-", A2="NA"), "Missing", "Has Value")

Or use Find:

  1. Press Ctrl+F
  2. Find: N/A (or other text)
  3. Find All
  4. See all instances

Count Missing Values

Count blanks:

=COUNTBLANK(A2:A1000)

Count all missing types:

=COUNTIFS(A2:A1000, "", A2:A1000, "N/A", A2:A1000, "NULL")

Step 2: Choose Handling Strategy

Decide how to handle missing values based on your analysis needs.

Strategy Options

1. Remove Missing Values

  • Delete rows with missing data
  • Use when missing is random and small percentage
  • Risk: Loses data, reduces sample size

2. Fill Missing Values

  • Replace with default value
  • Use when missing is acceptable
  • Options: Mean, median, mode, zero, "Unknown"

3. Impute Missing Values

  • Estimate missing values
  • Use statistical methods
  • More sophisticated approach

4. Flag Missing Values

  • Keep missing, mark for review
  • Use when missing is important
  • Analyze missing patterns

Decision Matrix

Missing % Pattern Strategy
< 5% Random Remove or fill
5-20% Random Fill or impute
> 20% Random Impute or flag
Any % Systematic Investigate cause

Step 3: Remove Missing Values

Delete rows or columns with missing data.

Remove Rows with Missing Values

Method 1: Filter and Delete

  1. Add filter (Ctrl+Shift+L)
  2. Filter column to show blanks
  3. Select visible rows
  4. Right-click > Delete Rows
  5. Remove filter

Method 2: Go To Special

  1. Select data range
  2. F5 > Special > Blanks
  3. Right-click > Delete > Entire Row

Method 3: Power Query

  1. Data > From Table/Range
  2. Home > Remove Rows > Remove Blank Rows
  3. Close & Load

Remove Columns with Too Many Missing

Identify columns:

=COUNTBLANK(A:A)/COUNTA(A:A)

Shows percentage of missing values.

Delete columns:

  1. If > 50% missing, consider deleting
  2. Select column
  3. Right-click > Delete

Step 4: Fill Missing Values

Replace missing values with appropriate defaults.

Fill with Default Value

Fill with "N/A":

  1. Select range with blanks
  2. Press F5 > Special > Blanks
  3. Type N/A
  4. Press Ctrl+Enter
  5. All blanks filled

Fill with Zero:

  1. Select range
  2. F5 > Special > Blanks
  3. Type 0
  4. Press Ctrl+Enter

Fill with "Unknown":

  1. Select range
  2. F5 > Special > Blanks
  3. Type Unknown
  4. Press Ctrl+Enter

Fill with Statistical Values

Fill with Mean:

=IF(ISBLANK(A2), AVERAGE($A$2:$A$1000), A2)

Fill with Median:

=IF(ISBLANK(A2), MEDIAN($A$2:$A$1000), A2)

Fill with Mode:

=IF(ISBLANK(A2), MODE($A$2:$A$1000), A2)

Apply formula:

  1. Add formula in adjacent column
  2. Copy down
  3. Copy values over original
  4. Delete formula column

Fill with Previous Value (Forward Fill)

Fill down from previous:

  1. Select range including blanks
  2. F5 > Special > Blanks
  3. Type = and reference cell above
  4. Press Ctrl+Enter
  5. Blanks filled with previous value

Or use formula:

=IF(ISBLANK(A2), A1, A2)

Fill with Next Value (Backward Fill)

Fill up from next:

  1. Select range
  2. Sort in reverse order
  3. Use forward fill
  4. Sort back to original order

Step 5: Impute Missing Values

Use advanced methods to estimate missing values.

Simple Imputation Methods

Mean Imputation:

  • Replace with column mean
  • Good for numerical data
  • Preserves mean but reduces variance

Median Imputation:

  • Replace with column median
  • Good for skewed data
  • More robust to outliers

Mode Imputation:

  • Replace with most common value
  • Good for categorical data
  • Preserves distribution

Regression Imputation

Use other columns to predict:

=IF(ISBLANK(A2), FORECAST(B2, Known_A, Known_B), A2)

Predicts missing A based on B.

K-Nearest Neighbors (KNN)

Use similar rows:

  • Find k most similar rows
  • Use their values to impute
  • More sophisticated method
  • May need specialized tools

Step 6: Standardize Missing Value Representation

Ensure consistent representation of missing data.

Find All Missing Types

Identify variations:

  • Blanks
  • "N/A", "NA", "n/a"
  • "NULL", "null"
  • "-", "--"
  • "?", "???"
  • "Missing", "Unknown"

Standardize to One Format

Replace all with "N/A":

  1. Press Ctrl+H (Find & Replace)
  2. Find: NULL
  3. Replace: N/A
  4. Click Replace All
  5. Repeat for each variation

Or use formula:

=IF(OR(A2="", A2="NULL", A2="-", A2="?"), "N/A", A2)

Step 7: Handle Missing Values by Context

Different strategies for different data types.

Numerical Data

Options:

  • Mean/median imputation
  • Zero (if appropriate)
  • Remove if too many missing

Example:

  • Price missing → Use average price
  • Quantity missing → Use 0 or median

Categorical Data

Options:

  • Mode (most common)
  • "Unknown" category
  • Remove if critical

Example:

  • Category missing → Use most common category
  • Status missing → Use "Unknown"

Date Data

Options:

  • Remove (dates often critical)
  • Use previous/next date
  • Use average date (rarely appropriate)

Example:

  • Order date missing → Remove row (critical field)
  • Last login missing → Leave blank or use "Never"

Step 8: Analyze Missing Patterns

Understand why data is missing.

Check Missing Patterns

Random Missing:

  • No pattern
  • Missing across all categories
  • Easier to handle

Systematic Missing:

  • Pattern in missing data
  • Missing in specific groups
  • May indicate data quality issue

Identify Patterns

By category:

=COUNTIFS($A$2:$A$1000, "Category1", $B$2:$B$1000, "")

Counts missing in specific category.

By time period:

  • Check if missing in specific dates
  • Weekends, holidays, etc.
  • May indicate systematic issue

Step 9: Validate After Handling

Verify data quality after handling missing values.

Check Completeness

Count remaining missing:

=COUNTBLANK(A2:A1000)

Should be zero after handling.

Verify Imputations

Check filled values:

  • Review statistical summaries
  • Compare before/after
  • Ensure values make sense

Data Quality Metrics

Create quality report:

Metric Before After Target
Missing % 15% 0% < 5%
Complete Rows 850 1000 100%
Valid Values 8500 10000 100%

Real Example: Cleaning Missing Values

Before (Data with Missing Values):

Name Age Email Category
John 25 john@email.com Electronics
Jane - jane@email.com -
Bob 30 - Furniture
Alice - alice@email.com NULL

Issues:

  • Age missing (represented as "-")
  • Email missing (blank)
  • Category missing ("-" and "NULL")

After (Cleaned Data):

Name Age Email Category
John 25 john@email.com Electronics
Jane 27.5 jane@email.com Electronics
Bob 30 bob@email.com Furniture
Alice 27.5 alice@email.com Electronics

Handling Applied:

  1. Age: Filled with mean (27.5)
  2. Email: Filled with "unknown@email.com" (or removed row if critical)
  3. Category: Filled with mode (Electronics)

Handling Strategy Guide

Data Type Missing % Recommended Strategy
Numerical < 5% Mean/Median imputation
Numerical 5-20% Regression imputation
Numerical > 20% Remove or flag
Categorical < 10% Mode imputation
Categorical > 10% "Unknown" category
Date Any Remove (dates critical)
Text < 5% Fill with "N/A"
Text > 5% Remove or flag

Mini Automation Using RowTidy

You can clean data with missing values automatically using RowTidy's intelligent handling.

The Problem:
Handling missing values manually is time-consuming:

  • Identifying all missing types
  • Choosing appropriate strategy
  • Filling or removing values
  • Validating results

The Solution:
RowTidy handles missing values automatically:

  1. Upload Excel file - Drag and drop
  2. AI detects missing values - Finds all missing types
  3. Suggests handling strategy - Based on data type and pattern
  4. Fills or removes - Applies appropriate method
  5. Downloads clean file - Get complete dataset

RowTidy Features:

  • Missing value detection - Finds blanks, "N/A", NULL, etc.
  • Intelligent imputation - Uses mean, median, mode appropriately
  • Context-aware filling - Different strategies for different data types
  • Pattern analysis - Identifies systematic vs random missing
  • Validation - Ensures data quality after handling
  • Standardization - Consistent missing value representation

Time saved: 2 hours handling missing values → 3 minutes automated

Instead of manually handling missing values, let RowTidy automate the process. Try RowTidy's missing value handling →

FAQ

1. How do I find missing values in Excel?

Use Go To Special > Blanks, conditional formatting to highlight blanks, or formulas like ISBLANK() or COUNTBLANK(). RowTidy detects all missing value types automatically.

2. Should I remove or fill missing values?

Depends on missing percentage and pattern. < 5% random missing: fill. > 20% missing: consider removing. Systematic missing: investigate cause. RowTidy suggests appropriate strategy.

3. How do I fill missing values with mean?

Use formula: =IF(ISBLANK(A2), AVERAGE($A$2:$A$1000), A2). Or select blanks, type mean value, press Ctrl+Enter. RowTidy fills with statistical values automatically.

4. What's the best way to handle missing categorical data?

Use mode (most common category) for < 10% missing, or create "Unknown" category for > 10% missing. RowTidy handles categorical missing values intelligently.

5. Can I impute missing values in Excel?

Yes. Use mean/median/mode imputation with formulas, or regression imputation for more advanced methods. RowTidy provides intelligent imputation.

6. How do I standardize missing value representation?

Use Find & Replace to convert all variations ("NULL", "-", "?") to one format ("N/A"). Or use formulas. RowTidy standardizes automatically.

7. Should I remove rows with missing values?

If missing is < 5% and random, consider filling. If > 20% or systematic, may need to remove. Depends on analysis needs. RowTidy suggests best approach.

8. How do I analyze missing value patterns?

Check if missing is random or systematic by category, time period, or other factors. Use COUNTIFS to analyze patterns. RowTidy identifies missing patterns.

9. What if missing values are in critical columns?

For critical columns (like IDs, dates), consider removing rows with missing values rather than filling. Filling may create invalid data. RowTidy handles critical columns appropriately.

10. Can RowTidy handle missing values automatically?

Yes. RowTidy detects all missing value types, suggests appropriate handling strategy, and fills or removes values based on data type and context automatically.

Related Guides

Conclusion

Cleaning data with missing values requires identifying all missing types, choosing appropriate handling strategy, filling or removing values, and validating results. Use Excel formulas, statistical methods, or tools like RowTidy to automate the process. Proper handling ensures complete, analysis-ready datasets.

Try RowTidy — automatically handle missing values and get complete, analysis-ready Excel files.