13

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!

Frank
  • 131
  • 1
  • 1
  • 3
  • 1
    Possible duplicate of [Excel keyboard shortcut to copy/fill down for all cells with non-blank adjacent cells?](http://superuser.com/questions/298276/excel-keyboard-shortcut-to-copy-fill-down-for-all-cells-with-non-blank-adjacent) – phuclv Aug 18 '16 at 04:27
  • 1
    The proposed duplicate addresses continuous fill. This problem is different. It's filling in gaps with varying content. With any significant amount of data, the methods in the proposed duplicate wouldn't be that useful. – fixer1234 Aug 21 '16 at 03:44

1 Answers1

23
  • 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

yosh m
  • 2,226
  • 3
  • 25
  • 28
teylyn
  • 22,498
  • 2
  • 40
  • 54
  • 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 01 '16 at 19:04
  • Actually, I pressed a little too soon. I don't see how to apply this solution. As described, it does not work for me. – yosh m Oct 01 '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 01 '16 at 20:49
  • @yoshm I added some screenshots to my answer. Hope that makes it more clear. – teylyn Oct 01 '16 at 21:00