Skip to main content

Fill Info in Excel in a Flash


Did you know that Excel 2013 and 2016 have a built in feature to help you fill and format columns of info? It's called Flash Fill and it does just what it says it does. I love finding new tips and sharing them.
How long did it take you figure out how to separate names by using a long formula to find the "," character and then pull info from the left or right of that? Or better yet, the faster way of using Text to Columns to separate the text. Well, now there's an even quicker way.
Start off with a column of info, such as names and make sure the headers are formatted differently to help Excel know where to look for the patterns in data. In this case, I'm using Last Name, First Name. Start a new column to the right for First Name. Type the first one like you want it and hit Enter to go to the next line. Start typing the next one and you'll see a grey-out list of suggestions pop up. If you like it, hit Enter. Boom! Your column is already filled. Do the same for the last names.
If this is not working automatically for you, check Excel's options in the backstage view. Go to File> Options> Advanced and scroll to Editing Options. Check the box for "Automatically Flash Fill". You can also force it to work by typing the first entry and then hitting Ctrl+E.
You may have some rows that Excel doesn't quite know what do with. Yes, I just ended a sentence with a preposition. Maybe some will have initials or middle names. Just go to those cells and make the entry and the rest will fill in for you.
Try separating the raw data of 123121234 into 123-12-1234.
Phone numbers: 5551231234 to (555) 123-1234
Is your original data in ALL CAPS or all lower case? Type the first cell in the Proper Case and it will be corrected for you. ESTHER, POLLY becomes Polly Esther.
Have a mix of initials and middle names?
  • JEST, SHIRLEY U. becomes Shirley U. | Jest
  • brown, jim ed becomes Jim E. | Brown
As with most features in Excel, there are multiple ways to accomplish most tasks. You could format phone numbers, social security numbers and zip codes with the Special number format, but Flash Fill handles this without having to use the mouse or click through several dialog boxes. It's nice to have options.

Popular posts from this blog

Getting My House In Order

I'll be the first one to confess that I am NOT the world's best housekeeper. I have been a single mom of my 18-year old "baby" since he was just a couple of months old and it hasn't always been easy. As a matter of fact, I think the toddler was more tidy than the teenager. He has a habit of opening cabinets and not closing them, dropping things on the floor wherever he happens to be, leaving items on the counter and so on. After lots of arguments and not making much headway, I decided to pick my battles and let him do his thing in his own room. Then a few things made their way to the living room and little by little, you get used to things being out. And yes, I got lazy and didn't feel like messing with it.  Then one day I looked around and thought that if someone happened to pop in unannounced, I would be totally embarrassed. So, something had to give.

There are as many methods of cleaning and decluttering as there are ways to make a mess.  I have looked thro…

Rideshare in B/CS

It's official!  I'm now also a driver for Uber. Here's hoping the two programs overlap nicely.

My Referral Code is denisev472ue and you can get a 1st rider discount or a bonus for signing up as a driver using my code.

I am driving in the Brazos Valley - Brazos and Burleson Counties.

I'm a MCT!

My certificate finally came in the mail!  I actually feel like a Microsoft Certified Trainer now.


This certification took several years, lots of exams and quite a bit of money.  I had to have the Master level Microsoft Office Specialist certification for versions 2007, 2010 and 2013.  Each certification level was made up of 4 or more exams and half of them being Expert level exams. The exams were around $100 each just for the voucher.  Each exam was almost an hour long and for the last version, I had to travel an hour and a half to Austin or Houston as our local Certiport testing center in College Station no longer proctors the exams.

I think any major goal takes a lot of effort to make you appreciate it all the more.  If it was easy, it wouldn't be special.

I have actually had the Master MOS since the 2003 version. Lots of buttons and functions have been added, or more than likely, moved around and renamed. I have always been "into" the Microsoft Office suite of product…