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

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
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers


Recommended Posts
Contact Us

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

Not readable? Change text.