We help IT Professionals succeed at work.

How To Fill Blank Cells With Value Above In Excel

I want to fill blank cells with above value cells, I am using excel 2016 and and i tried highlighting range of cells and went to GOTO>Special>Blanks and when I do that I am getting no cells were found.

How else can I fill Blank Cells With Value Above In Excel?
Comment
Watch Question

Top Expert 2016

Commented:
Hi,

Could you send a dummy?

Regards

Author

Commented:
There you go
Sample.xlsx
Rob HensonFinance Analyst

Commented:
Select the table of data and apply an AutoFilter

In the relevant column then use the dropdown next to the header to select only Blanks. In the first blank row type the formula:

=A3   (assuming column A and first blank row is row 4)

Then select the remainder of that column and press Ctrl + D. This will then fill down the remainder of the column but will only populate the visible cells.

Now if you remove the AutoFilter you will see all rows populated. The column can then be copied and pasted as values to remove the formula links.

Author

Commented:
Rob,

When I try the above solution all the blank rows will be populated with the first non blank value. I dont want to do that, I have values in other rows in the middle and the blank columns below should be filled with Value above In the cell
Rob HensonFinance Analyst

Commented:
With the filter in place, only the visible cells will be affected thus the blank cells will be populated with the formula which copies the value of the cell above and will adjust as it is copied down to refer to the cell above.
Finance Analyst
Commented:
Also, in your sample, the only cell which is truly blank is C3. The other cells contain an apostrophe. I suspect this is a result of a conditional formula which had an option of "" eg IF(Condition,Result,"") and then the formulas have been copied and pasted as values. Those cells which had the "" result will have been overwritten with just the single apostrophe.

An AutoFilter will still recognise these as blank but the "GoTo>Specials>Blanks" does not recognise them as blank.