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:
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:
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:
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:
- Ozgrid – Fill blank cells via formula and via macro
- Contextures – Fill blank cells manually & programmatically
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