Formula Errors

How to Format and Fill Blank Columns for Data Cleanup in Excel

Natalie Taylor Oct 12, 2026 872 views

Question details

The user needs to consistently format columns, fill in missing blank values based on previous columns, and filter out incomplete rows in a large Excel dataset while avoiding formula errors.

How to Format and Fill Blank Columns for Data Cleanup in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning and formatting a large dataset of thousands of records by filling blanks and removing incomplete rows.
Observed behavior: The user encounters formula errors when attempting to type or paste formulas to fill blank cells, preventing successful data cleanup.

Before you start

Before making bulk changes, ensure your raw data has clear column headers and note the specific regional settings on your computer, as incorrect list separators (like commas vs. semicolons) are a common cause of formula errors.

Solution 1Recommended

Use a Helper Column and Excel Tables to Fill Blanks

This method uses a simple IF formula within an Excel Table to automatically fill blank cells with data from adjacent columns, avoiding complex array formula errors.

Converting your data to an Excel Table ensures that formulas automatically fill down the entire column correctly without needing manual dragging.

Using a helper column allows you to preserve your original data while creating a clean, filtered version next to it.

Convert Data to a Table

Select any cell inside your data range and press `Ctrl + T` (or go to Insert > Table) to format the dataset as a Table. Ensure 'My table has headers' is checked.

Create a Helper Column

Add a new column immediately to the right of your target data and name it 'Cleaned Data'.

Apply the IF Formula

In the first cell of the helper column, enter a formula like `=IF(B2="", A2, B2)`. This tells Excel: if column B is blank, use the value from column A; otherwise, keep the value from column B.

Filter and Delete Blanks

Click the filter dropdown arrow on your new helper column header, uncheck the '(Blanks)' option to reveal incomplete rows, highlight those rows, right-click, and select 'Delete Row'.

Use a Helper Column and Excel Tables to Fill Blanks

Check Formula Separators: If you receive an immediate error when typing or pasting the formula, try changing the comma (,) to a semicolon (;). For example: `=IF(B2=""; A2; B2)`. This is required if your computer's regional settings use a comma as a decimal point.

Solution 2

Fill Blank Cells Using Go To Special

If you do not want to use helper columns, you can quickly fill all blank cells with the value from the cell directly above or beside them using the Go To Special tool.

Clean and Manage Large Datasets Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data formatting, filtering, and table management tools that make cleaning thousands of records quick and error-free.

1. Open your data file: Launch WPS Spreadsheet and open your raw data file.
2. Format as Table: Highlight your dataset and navigate to Insert > Table to convert it.
3. Apply Helper Formula: Add a helper column and use the IF formula to dynamically fill missing values.
4. Filter and Cleanup: Use the built-in AutoFilter on the column headers to hide blanks and delete incomplete rows.

100% compatible with Microsoft Excel formulas, tables, and formattingIntuitive 'Go To Special' features for instant blank-fillingLightweight software that handles large datasets without lagging

Frequently Asked Questions

Why do I get a formula error when I copy and paste a formula from a forum?

This is often due to regional settings on your computer. Many regions (especially in Europe) use a semicolon (;) instead of a comma (,) to separate arguments in Excel formulas. Try replacing the commas in the copied formula with semicolons.

How can I remove the formulas after filling in the blank cells?

Select the entire column containing the formulas, press `Ctrl + C` to copy it, then right-click the same selection and choose `Paste Special` > `Values`. This will replace the underlying formulas with static text, allowing you to delete the original columns safely.

Why isn't my helper column formula filling down automatically?

If the formula doesn't copy down automatically, ensure your data is properly formatted as a Table (using `Ctrl + T`). Alternatively, you can manually double-click the small square at the bottom-right corner of the formula cell (the fill handle) to push the formula down to the bottom of the data set.

Excel & Data Problems