Data Import & Export

Methods for Filling Missing Values in Excel Datasets

Maira Mehtab Sep 28, 2026 871 views

Question details

The user needs to determine the most appropriate imputation methods for handling missing ratings, yes-or-no responses, and job-title values in an Excel dataset.

Product: Excel
Device & OS: not provided
Scenario: Cleaning and preparing a dataset for analysis where multiple variables of different data types contain empty cells.
Observed behavior: The dataset contains blank cells that must be properly evaluated to decide whether they should be filled via statistical methods, excluded entirely, or analyzed separately.

Before you start

Before applying any imputation method, carefully review your dataset to understand why values are missing (e.g., non-response versus data entry error), as the reason dictates the correct filling strategy.

Solution 1Recommended

Use Mean or Mode Imputation for Standard Missing Values

Replace missing numerical ratings with the average (mean) and categorical values with the most frequent response (mode).

This is the most common baseline method for handling randomly missing data. Numerical variables benefit from mean imputation, whereas categorical inputs (like yes/no or job titles) require mode imputation.

Calculate the mean for numerical ratings

Select an empty cell outside your data table and use the formula =AVERAGE(Range) to find the mean of the available ratings.

Determine the mode for categorical data

For yes-or-no responses or job titles, use the formula =INDEX(Range, MODE(MATCH(Range, Range, 0))) to identify the most frequent text value.

Fill the missing values

Highlight the column with missing data, press Ctrl + G, click 'Special', select 'Blanks', and click OK. Type the calculated mean or mode, then press Ctrl + Enter to fill all selected blank cells.

Limitation of Mean Imputation: Applying the mean to a large number of missing values can artificially reduce the variance of your dataset. Use this method only when a small percentage of data is missing.

Solution 2

Apply Hot-Deck Imputation for Contextual Accuracy

Fill missing values based on similar complete records within the dataset to maintain contextual consistency.

Solution 3

Exclude or Analyze Missing Evaluations Separately

Handle intentional blanks by removing them or categorizing them separately rather than automatically filling them.

Efficient Data Cleaning

Easily Handle Missing Values with WPS Spreadsheet

WPS Spreadsheet provides powerful tools like advanced filtering, Find and Replace, and built-in statistical functions to help you quickly identify and fill missing values in your datasets.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .csv dataset file.
2. Locate all blank cells: Select your data range, press 'Ctrl + G' to open the 'Go To' dialog, choose 'Blanks', and click 'Go To'.
3. Batch fill the missing data: With all blanks highlighted, type your chosen imputation value (such as a calculated average or 'N/A') and press 'Ctrl + Enter' to populate all missing cells at once.

Fully compatible with Microsoft Excel (.xlsx, .csv) formats for seamless data import.Built-in statistical formulas like AVERAGE and MODE for quick mathematical imputation.Advanced Go To (Special) tools to instantly isolate and highlight blank cells.Free and lightweight alternative for fast data processing and analysis.

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between mean and mode imputation in Excel?

Mean imputation replaces missing numerical data with the mathematical average of the column. Mode imputation replaces missing categorical or numerical data with the most frequently occurring value in that column.

How can I quickly find all blank cells in my Excel dataset?

You can press Ctrl + G to open the 'Go To' dialog box, click on 'Special', select the 'Blanks' radio button, and click OK. Excel will automatically highlight all empty cells within your selected range.

When should I delete rows with missing values instead of filling them?

You should delete rows when the missing data indicates a deliberate non-response (e.g., the user opted out of the evaluation) or when the proportion of missing data is too large to accurately estimate without introducing significant bias.

What is hot-deck imputation?

Hot-deck imputation is a data cleaning method where a missing value is filled using an observed response from a 'similar' unit in the same dataset. For example, filling a missing rating by adopting the most common rating from respondents who share the exact same job title.

Excel & Data Problems