In Excel, how do you auto fill until next value?

Question

currently I have a column looks like this:

  A B ...
1 A
2
3
4 B
5 
6 
7 C
8 
...

And I want to make it looks like this

  A B ...
1 A
2 A
3 A
4 B
5 B
6 B
7 C
8 C
...

Is there a way to finish the job quickly? Thanks a lot!

Answer 1

• Select column A
• hit F5 to open the "Go To" dialog.
• click “Special…”

• tick “Blanks” and click “OK”

  

• enter an equals sign, =
• hit the up arrow key, ↑
• hold down Ctrl and hit Enter

Now all the previously empty cells have a formula that references the cell above.

To replace the formulas with the values, copy column A and paste over itself with “Paste Special” → “Values”.

Note: If the F5 key does not open the "Go To" dialogue, the function keys on a laptop may use the laptop controls as a default. In that case, hold the Fn key while pressing F5.

The "Go To" dialog can also be accessed with Ctrl-G in Excel for Windows.

The ribbon has the "Go To" command in the "Find and Select" dropdown at the very right of the Home ribbon, where you can save a click if you hit "Go To Special".