Basically, what I want to do is fill all of the blank cell in a column with the value of the last non-blank cells. For example:

From this ---------> to this.

I was wondering if there was a way to do this without having to do the auto-fill individually for each set of cells with the same value, as I have a sheet that's thousands of entries long.

1 Answer 1


To do this with formula:

1.) In a completely blank column, enter the following formula in the top cell (e.g., B1):


This will fill the array.

2.) Click the letter at the top of the output column to select the entire column.

3.) Hit Ctrl-C to copy the entire contents of that column to clipboard.

4.) Click the A at the top of Column A to select the entire original column.

5.) Click Ctrl-Alt-V ("Paste Special"). A small clipboard icon will appear lower right of the visible screen area. Click it and choose Paste values only.

6.) Click the letter at the top of the formula column to select that entire column again and then click the Delete key to delete the formula and its results from that column, returning it to its original blank state.

The entire process should take about 10 seconds.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.