Flash Fill is a great feature in Excel to do one time auto fills:
You can download the workbook here to follow along.
Flash fill is a new feature in Excel 2013. If you have an older version, you will not be able to enjoy the benefits of this option. The keyboard shortcut for Flash Fill is Ctrl + E. It is to be used to fill in data in a column based on the entries in an adjacent column automatically by Excel figuring out what the pattern of your entry is. It does not update based on any changes you make to your data as a formula would, so it should be used for one time fills only.
Here are several examples of how Flash Fill works.
In this example, I want to change the formatting of my part numbers from BC120 to bc-120. I enter the first example like this:
And when I enter Ctrl + E, Excel auto fills the rest of the list:
Even if I want to change the layout more dramatically, like this:
Excel can still figure out what my pattern should be:
Here are a few more examples of what Flash Fill can do. In each of these, I entered only the top cell, clicked on Ctrl + E and Excel did the rest:
In this example, when I try to Flash Fill just the first names, this is how it comes out:
Excel did well, but what I really wanted was the period after those with just an initial. In that case, I need to give Excel two examples:
And now it know what I want it to do:
Pretty slick! Now, you try different things and see what works and what you may need to modify to get Excel to fill what you are looking for.