Function Problems

Excel Formula to Return the First Nonzero Number or Carry Forward Blanks

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula that returns the current cell's value or carries forward the previous row's value if the current cell is blank or contains a zero.

Product: Excel
Device & OS: not provided
Scenario: Filling data gaps in a column by dynamically pulling down the last known non-blank or non-zero text/number.
Observed behavior: The source column contains missing data (blanks) or unwanted zeroes, and the goal is to populate a new column with continuous valid values based on the preceding data.

Before you start

Ensure your source data is organized in a single column and determine whether actual zero values should be treated as blanks or kept as valid data.

Solution 1Recommended

Use IF and OR Functions to Carry Forward Values

This method uses standard logical functions to evaluate whether a cell is blank or zero, pulling the previous row's value if the condition is met.

By combining the IF and OR functions, you can create a dynamic column that automatically fills in gaps. This approach is highly flexible and works for both text strings and numerical data.

Set up the first cell

Assuming your source data begins in cell A1, click on the adjacent cell B1 and enter the formula =IF(A1="","",A1). This simply copies the first value or leaves it blank if A1 is empty.

Apply the logic to the second cell

In cell B2, enter the formula =IF(OR(A2="",A2=0),B1,A2). This instructs Excel to look at A2; if it is blank or zero, it pulls the value from B1. Otherwise, it returns the value in A2.

Fill the formula down

Press Enter to confirm the formula. Click on cell B2, hover over the bottom-right corner until the cursor turns into a cross, and drag the fill handle down to apply the formula to the rest of your column.

Handling Valid Zeros: If a zero is a valid data point in your dataset that should not be overwritten, simply remove the zero test from the formula and use =IF(A2="",B1,A2) instead.

Easily Manage Data and Formulas with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced logical formulas like IF and OR. You can easily process blank cells, carry forward data, and manage complex datasets with zero hassle.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data with blanks or zeros.
2. Enter the initial formula: Select the target cell next to your first data point and input the initial =IF(A1="","",A1) formula.
3. Input the carry-forward logic: In the cell directly below it, type the carry-forward formula =IF(OR(A2="",A2=0),B1,A2) and press Enter.
4. Drag to fill: Use the fill handle in the bottom-right corner of the cell to drag the formula down, instantly filling the rest of your dataset.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight and fast, ensuring smooth performance even when processing large columns of data.Completely free to use with a familiar, user-friendly interface.Built-in data cleaning tools to handle blanks and formatting issues efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use this formula for text values as well as numbers?

Yes, the IF function evaluates cell contents universally. It will detect if a cell is completely blank and pull down the previous text string exactly as it does for numeric values.

What if my blank cells actually contain hidden spaces?

If cells contain spaces, the standard A2="" condition might not recognize them as blank. You can wrap the cell reference in the TRIM function, such as TRIM(A2)="", to remove extra spaces before the logical test evaluates.

Is there a way to fill blanks without using formulas?

Yes. Select your data column, press Ctrl+G to open 'Go To', click 'Special', and choose 'Blanks'. With all blank cells highlighted, type = and press the Up arrow key to select the cell above, then press Ctrl+Enter to populate all blanks simultaneously.

How do I stop the formula from breaking if I delete the source column?

Once you have carried forward all your values, select the new formula column, copy it (Ctrl+C), right-click the same selection, and choose 'Paste as Values'. This removes the underlying formulas and leaves only the static data, allowing you to safely delete the original source column.

Excel & Data Problems