currently I have a column looks like this:

  A B ...
1 A
4 B
7 C

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!

  • Select column A
  • hit F5 to open the "Go To" dialog. enter image description here
  • click “Special…”
  • tick “Blanks” and click “OK”

    enter image description here

  • 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".

enter image description here

  • Did you mean select column A or B? Also - what is F5 doing? For which version of Excel? In 2010 - F5 does nothing relevant. Sounds like you wanted to do a filter. If so, this answer should be updated to make it clear. Doing a filter like that is a clever idea... – yosh m Oct 1 '16 at 19:04
  • Actually, I pressed <enter> a little too soon. I don't see how to apply this solution. As described, it does not work for me. – yosh m Oct 1 '16 at 19:07
  • @yoshm Take a breath. Select the column(s) with the missing values. In the example that is column A. It has some values with blanks between. F5 opens the "Go To" dialog in all Windows versions of Excel since the dinosaurs. If you have a laptop, your Function keys may be set to laptop controls and you may need to hold down the Fn key before hitting the F5 key. Another keyboard shortcut to open the "Go To" dialog is Ctrl-G. Or use Home ribbon > Editing group > Find & Select drop-down > Go to. Or put the "Go To" command into your QAT, so it's only one click away. Then follow the other steps. – teylyn Oct 1 '16 at 20:49
  • @yoshm I added some screenshots to my answer. Hope that makes it more clear. – teylyn Oct 1 '16 at 21:00

protected by Community Jun 5 '17 at 20:29

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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