Tuesday Tip: Excel Flash Fill
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.
email@example.com or 404.997.7633
Spending 30 years in high-technology sales & marketing, from IBM to start-ups, Tony brings his broad experience in business development, marketing and IT business strategy to Network 1’s leadership team, clients and partners. His passion is to help people achieve greatness, however, they define it.
Network 1 Consulting is a 21-year-old, IT Support company in Atlanta, GA. We become – or augment – the IT department for law firms, medical practices, and real estate & construction companies. Our IT experts can fix computers, but what our clients value most are the industry-specific best practices we bring to their firms. This is especially important with technology, along with regulations and cyber threats, changing so rapidly. We take a proactive approach to helping our clients use technology to gain and keep their competitive advantage.
Here are three of the most popular Tuesday Tips: