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…
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
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:
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.
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
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.
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.