by Tony Rushin
I am not an Excel power user. However, I do use it on a regular basis every week to export information, reformat a report, and do some analysis. I’ve been using it for 30+ years so I definitely have my Excel habits; but, I’ve been making an effort to learn some of the new (for me) tricks excel has to offer. Here’s one I really like.
Flash Fill is a real time saver. It fills in data automatically for you when it sees a pattern. I use it most often when I export information from one of our systems and I want to do some quick formatting touch-ups before sharing it with a client. For example, I exported this list of client computers from our monitoring system: you can see it’s messy with “HP” or “Dell” in there twice.
With Flash Fill, I simply insert a column, type how I want the data to look and then hit <enter>. Flash Fill kicks in when it picks up a pattern – which it did below right after I typed “HP Pro” in the second row. Then it filled in the rest of the rows with what it thinks I want.
It’s like magic! If it doesn’t automatically fill for you, simply fill in a couple of the rows, copy them down to the bottom and select the Flash Fill option.
Finally, If that doesn’t work, make sure you have it turned on: Tools > Options > Advanced > Editing Options > check the Automatically Flash Fill box.
Here is a short list of situations where Flash Fill can save you time:
- Extracting anything out of cells that have multiple pieces of data: names into first names and last names, zip from address, etc
- Formatting phone numbers
- Capitalizing proper nouns that are lower case (Flash Fill recognizes upper and lower case)
- Censoring data, such as changing the last 4 digits of each social security # to “xxxx”
If you want to see Flash Fill in action, here is a short tutorial video. Flash Fill takes less than 10 minutes to learn and will save you loads of time.
