Excel - Fill in blank data quickly using Ctrl-Enter

Introduction
I had a list (data set) in Excel where there were multiple rows that were similar but missing data in one column. The list was very large so filling in the data by hand would've taken a long time. However, there's a little trick you can do to make Excel fill in data quickly. You must use the Special Search to find the blanks and press Ctrl-Enter. Using a simple table/list of towns and counties in England I'll demonstrate how this works step-by-step:


Instructions
The following screen shot shows a list of data. There are columns for towns in England. In column B the county name is listed. However, for the counties of Bedfordshire and Berkshire we have blank cells. We know that the towns below them are in those counties; Ampthill to Woburn (A18) are towns in Bedfordshire. Ascot (A19) to Woodley (A34) are in Berkshire.

In the above the blanks should be filled in. Cells B3 to B18 should contain the word "Bedfordshire" and B20 to B34 should contain "Berkshire". 

The first thing to do is select the blank cells:

Click Find & Select

Click Go To Special

Click Blanks

Click OK

All the blank cells are selected:

With the blank cells selected (as shown above) click into the formula bar at the top - see the screen shot below (be careful though, don't click on anything because the selection will change and you'd have to start the steps all over again):

Type the cell reference of the first filled in cell. For example, above I typed in the formula bar:
=b2

Press Ctrl-Enter  

You'll see the result, all the cells are filled in - as shown in my example below:

You'll notice that Excel is clever enough to realise that the cells B20 to B34 also had to be filled in but using the cell B19 - so Berkshire is correctly entered for those cells.


Conclusion
This is really useful, thanks Microsoft! If you have a report generated from a business intelligence or reporting tool, sometimes when you import it into Excel you have missing values like this. It can happen because of formatting in your report. In Excel you need all the cells filled in so that it's possible to use a pivot table. The above steps can save you hours of copy/paste work because Excel will fill in all the cells for you.


Excel Version?
It works for 2013 and 2010, probably earlier versions too.


Thanks
Many thanks to my colleague Claas who enlightened me to this little Excel trick!


Reference
I found the sample data used in this example here:
http://www.sp-tech.co.uk/resources-towns.php