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

Last updated on May 24, 2011 By

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)

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)

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

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

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.