How to Fill in Blank Cells in Microsoft Excel using the values above

 In Excel Tips

Film clapper from Launch Excel Here is a useful tip that I found out years ago, when I had just started looking at ways to process data faster in Excel.

It makes use of the Go To feature, which you activate by pressing Ctrl + G or F5. This tip can save you from pulling out your hair in frustration. Read on to find out more…

How to fill in Blank Cells using the Data in the Cells above

In this post, we look at an efficient way to fill in blank cells using the data in the cells above. You’ll sometimes find yourself with a table of data that’s missing lots of values in rows, because there is one header row and then lots of blanks, then another header row and more blanks:

Table of company names with header rows only (and lots of blanks)

Table of company names with header rows only (and lots of blanks)

This could happen if you copy and paste values from a pivot table, or maybe the table was set up to look ‘nice’ and only show the first row of company names, instead of lots of repeated rows like this:

Table of names with no missing rows (you can do lookups & counts with this layout)

Table of names with no missing rows (you can do lookups & counts with this layout)

While it looks nicer if there is just one header row instead of lots of repeated rows, this can give rise to problems. For example you can’t do lookups or counts in columns with empty cells.

When I came across this problem years ago, I started to pull my hair out because my data table had hundreds of rows with missing data, so it would take me a really long time if I wanted to copy and paste data manually.

Plus I’d need to do this again when the data was refreshed, as I needed to perform a VLOOKUP on the data.

Thankfully someone told me that I could Go To the blank cells and fill in the values from above. Huh I said? Can you show me how? Yes he said, this is how you do it:

Fill in blank cells from above

Fill in blank cells from above

Step by Step for Filling in Blank Cells from Above:

  • Select all the cells, including all the ones with values and all the ones with blanks
  • Press F5 to activate the Go To Feature (or you could use Ctrl + G, “G” is for “Go To”)
  • Click on “Special”
  • Click on “Blank cells”, this takes you out of the Go To Feature and selects all blank cells
  • Type in “=” then use the UP arrow key to move to the cell above
  • Press Ctrl + Enter, which fills the formula into all selected cells (this is important, don’t just press Enter, as this only fills in one cell)
  • I want to convert the formulas into values, so I select all the cells again
  • Right click to copy
  • Right click and paste special as values

Of course this is not the only way to approach the problem. If you want to create a custom macro to do this programmatically, here are two links that will help you:

And finally… a Youtube video!

I created a video that’s hosted on Youtube. It shows you how to fill in blank cells from above using the F5 shortcut, and shows how fast this method works when compared to manually copying hundreds of rows. Jump to 2:00 minutes if you want to see the step-by-step walk through.

Launch Excel Youtube Channel

You can check out more Excel tutorial screencasts on my Youtube Channel

Victor Chan
Victor has been using Excel intensively since 2002. He is a Chartered Accountant (Fellow of the ICAEW) and has an MEng in Manufacturing Engineering from the University of Cambridge. He is on a mission to help you learn Excel and VBA with practical video tutorials and awesome online courses.
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts
Showing 3 comments
  • creativesuitcase.com

    First off I would like to say superb blog!
    I had a quick question that I’d like to ask if you don’t mind.

    I was curious to know how you center yourself and clear your mind before writing.
    I have had trouble clearing my thoughts in getting my
    ideas out. I truly do enjoy writing but it just seems
    like the first 10 to 15 minutes tend to be lost just trying to
    figure out how to begin. Any recommendations or hints? Cheers!

    • Connie

      Brilliant! You saved me hours of work, thanks!

  • %anchor_text%

    Awesome blog! Is your theme custom made or did you download it from somewhere?

    A theme like yours with a few simple tweeks would really make my blog stand
    out. Please let me know where you got your theme.
    With thanks

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.