Ok, maybe I oversold this a little in the title, but I got your attention! This is the fourth post in a series on how nonprofits can leverage Excel to simplify, streamline, and accelerate their data analysis. The IllumiLab is grateful to have one of our clients, Megan Ondr-Cooper of CASA St. Louis, sharing her enthusiasm and expertise with us. This time, she’s sharing a feature that blew my mind – Flash Fill! Here’s Megan…
Last time, we covered conditional formatting for identifying cells, comparing cells, and visualizing patterns. This week, I’ll share two of my favorite tools for saving time using Excel – autofill and flash fill. Instead of spending time manually entering repetitive data, let Excel do the work with autofill and flash fill. These tools are helpful when there’s an obvious pattern in your data that you want to continue, but you don’t want to do it yourself. These tools essentially get Excel to read your mind and do it for you!
Autofill
Autofill is what it sounds like – it automates the process of filling your cells. You can autofill in Excel using the fill handle, which is the little black + that appears when you hover over the bottom right-hand corner of the active cell. Click and pull the fill handle down or over to fill the cells. This will extend a sequence or copy values, formulas, or formatting (colors, borders, etc.). Add one value into a cell to copy with the fill handle – it’s a lot quicker than copying and pasting. But the real beauty of Autofill is what it can do with formatting and series. Enter values into a few cells (i.e. Sunday, Monday, Tuesday or 1, 2, 3) and then use the fill handle to continue the pattern into new cells. Excel will notice your pattern and continue it.
Flash Fill
If you haven’t used this tool yet, get ready because your Excel game is about to be forever changed. Flash fill is a newer tool for me, and it’s been a while since I’ve been this excited about something in Excel. This really is one of the biggest Excel time saver tools for combining and dividing cells.
You can use Flash Fill to join the values from two columns in your data into one new column. For example, if your dataset exports First Name and Last Name, but you want one column for Full Name, flash fill will make this so easy for you.
Add a column to fill the Full Name data into and then enter a full name as you want it formatted into the first row of data. Then select the cell with the full name and hit Ctrl + E on your keyboard. You can also manually select Flash Fill from icon on the Data tab. Flash fill will combine the name of all following rows into the Full Name column. See why I called it a game changer?!
You can also use this tool to extract or separate values from a cell. For example, if your dataset has a Full Name column and you want to separate First Name and Last Name. You can also extract values from the middle of cell. For example, if your name data included middle names you could extract them using flash fill.
Other Options
Unfortunately, flash fill is only available in Excel 2013 and later. If you have an older version of Excel, check out Text to Columns for dividing cells. To combine values, use the CONCATENATE function or use the ampersand (&) symbol in a formula.
Bottom Line
You’re welcome!
Next time, I will cover an Excel feature that can be intimidating– formulas. If you’ve been too intimidated to try formulas or you want to learn a few new formulas, don’t miss the final post in my Excel series.