Got some gappy data?

You can fill the blanks by repeating the last visible value until a new one appears, using one of these methods:

Select a range with a value at the top followed by blank cells.

Then press Ctrl + D to copy the top value and populate the rest of the cells.

Repeat this for each of the other values in the column.

Use this formula to automatically fill the blank cells in A2:A17:

=SCAN("",
A2:A17,
LAMBDA(previous,current,
IF(current="",previous,current)
)

SCAN cycles through the range from top to bottom.
previous is the accumulated value from the previous step.
current is the current cell in column A.
IF statement — if the current cell is blank, return the previous value; otherwise, keep the current value.

Type =PY( to enter Python Mode and then put:

xl("A2:A17").ffill()

The .ffill() method fills the blank cells by carrying the last non-blank value forward.

Press Ctrl + Enter to confirm the code.

It will load as a DataFrame initially, but choose Excel Value in the Python Output dropdown to ensure the values display as desired.

Fill blanks with value above in Excel

Watch the video