Tuesday Tip: Excel Flash Fill

by Tony Rushin

[spacer height=”20px”]

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.

[spacer height=”20px”]

[spacer height=”20px”]

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.

[spacer height=”20px”]

[spacer height=”20px”]

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.

[spacer height=”20px”]

 

 

 

 

 

 

 

 

[spacer height=”20px”]

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.

Tony Rushin: In his role as VP of sales & marketing, he is responsible for hiring, managing and coaching the sales team and always staying on top of (and meeting) the needs of clients. Under his leadership, the company has steadily grown each year.

Network 1 designs, builds and supports the IT you need to run your business more securely, productively and successfully. Whether you want to outsource all of your IT needs to a reliable, responsive, service-oriented company, or need to supplement the work of your internal IT staff, we will carefully evaluate where you are now, discuss where you want to go and implement and support a plan to get you there with as little interruption as possible.

Leave a Comment





Related Posts