Data Cleaning in Excel 101, Part 4: More Uses for Flash Fill
Having the right data in the right columns to meet specific requirements for your analysis plays a major role in the data cleaning process. In Parts 2, 3, and 4 of our Data Cleaning in Excel series, we’ll show you how to solve common issues by utilizing both standard and Excel’s powerful “Flash Fill” shortcut.
Part 2 focuses on splitting data from one cell to multiple cells. Part 3 covers the opposite: combining data from multiple columns into one column. Here, in Part 4, we’ll showcase some incredibly useful and time-saving tricks with the mystical powers of Excel’s “Flash Fill” function.
Flash Fill is the mind-reading short-cut us data folks have been waiting for. It works by analyzing your data entries, detecting a pattern, and then filling in the rest for you.
Here, we’ll teach you how to get the most out of the function. After you’re finished reading, we encourage you to revisit a spreadsheet of your own and test it out for yourself.
Let’s review how to execute Flash Fill:
1. In a column adjacent to your original data, type two entries to show Excel how you’d like your data to be presented. Flash Fill requires you to type more than one entry so Excel can recognize that you’re creating a pattern.
2. Drag down the column to fill the formula (or double click the fill handle to copy the formula down the column). Then, click the copy shortcut box and select “Flash Fill”.
More Uses for Flash Fill
1. Changing case
Change text to all lower case, upper case, or to sentence case.
Use Flash Fill to change text to sentence case for an alternative to =PROPER.
2. Abbreviations and Initials
Abbreviate words or create initials from names. In this example, we’re creating a column for first and last initial.
What about if your names include titles? Or middle initials? As long as you provide Flash Fill an example for what to do when it encounters these, it can continue to produce the output you want to see.
3. Change number formats and add characters
You can use Flash Fill to add new characters and spaces. In our example, we’ve changed numbers to a phone number format.
4. Pull data out of cells
Pull specific data out of cells. For example, your system export produces cells with multiple pieces of data jammed into one cell. You can extract what you need into a new column using Flash Fill. In our example below, type of payment, payment amount, and country are all contained in one column In our new column, we typed the payment amount into two rows. Excel recognized that the data we want is between two delimiters and continues the pattern with Flash Fill.
5. Create custom column formats
Lastly, create custom columns by arranging data in a specific order, adding symbols, or adding new text. For example, you can add parentheses, dollar signs, and percentage signs.
We encourage you to try out these examples and test the limits of Flash Fill for yourself. We hope these methods help you as you clean your data! Please let us know if there are other topics or data cleaning problems you would like us to tackle by emailing firstname.lastname@example.org