Function Problems

How to Fill Blank Cells with the Value Above in Excel & WPS

Phi Hung Vo Sep 25, 2026 871 views

Question details

The user wants to fill down values into consecutive blank rows based on the last non-blank cell above them, preserving new values as they appear.

How to Fill Blank Cells with Values from Above

Product: Spreadsheet
Device & OS: not provided
Scenario: Filling gaps in a dataset where values are only entered once and assumed to apply to subsequent blank rows.
Observed behavior: The user needs a way to automate repeating values down approximately 20,000 rows without copying and pasting manually.

Before you start

Ensure your dataset is sorted properly if needed and verify that the top-most cell in your column contains a value rather than being blank.

Solution 1Recommended

Use an IF Formula with a Helper Column

Use a simple logical formula in an adjacent column to carry down the most recent non-blank value while keeping existing data intact.

This method is highly reliable for large datasets (like 20,000+ rows) because it gives you a safe column to verify the results before permanently altering your original data.

Insert the IF formula

In an empty adjacent column (for example, H2), enter the formula: =IF(G2="",H1,G2). This tells the spreadsheet to pull the value from above if the current cell is empty, or keep the current cell's value if it is not.

Fill the formula down

Hover your mouse over the bottom-right corner of cell H2 until the cursor turns into a plus sign. Double-click to automatically fill the formula down the entire column.

Copy the new values

Select the entire helper column (Column H) and press Ctrl+C to copy the newly generated data.

Paste as Values

Right-click the first cell of your original column (G1 or G2) and select 'Paste Special' > 'Values'. This replaces your incomplete data with the filled data and removes the underlying formula, allowing you to delete the helper column safely.

First Cell Assumption: Ensure that your reference logic aligns correctly at the top. For instance, if your data starts at row 2, ensure H1 and G1 share the same header so the formula references correctly.

Solution 2

Use the Go To Special Feature

Quickly select all blank cells in the column and fill them simultaneously using a relative reference shortcut.

Effortless Data Management

Quickly Fill Missing Data with WPS Spreadsheet

WPS Spreadsheet provides fast and intuitive tools like logical formulas and the Go To Special feature, allowing you to clean and fill massive datasets in seconds without lag.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data gaps.
2. Use the fill formula: Create a helper column and enter the =IF(G2="",H1,G2) formula.
3. Apply across all rows: Double-click the bottom-right corner of the cell to fill it down instantly.
4. Convert to values: Copy the completed column and use Paste as Values over your original data.

Easily process large datasets (over 20,000 rows) with smooth performance.Fully compatible with Microsoft Excel formulas and the .xlsx format.Built-in advanced features like Go To Special for rapid data formatting.Free to download with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I stop the IF formula from overwriting my existing data?

The IF formula =IF(G2="",H1,G2) specifically checks if the current cell (G2) is empty. If G2 contains data, the formula returns G2 (the existing value). This ensures your original data is always preserved while only the blanks are overwritten.

Why does the Go To Special method return an error in some cells?

This usually happens if you press 'Enter' instead of 'Ctrl+Enter' after typing your formula. Pressing 'Ctrl+Enter' is required to broadcast the relative formula simultaneously to all highlighted blank cells.

Can I use Power Query to fill down values?

Yes. If you prefer automated data cleaning, you can load your table into Power Query, right-click the specific column header, select 'Fill', and choose 'Down'. This will automatically populate the gaps without manual formulas.

Excel & Data Problems