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, well show you how to solve common issues by utilizing both standard and Excels 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, well showcase some incredibly useful and time-saving tricks with the mystical powers of Excels 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, well teach you how to get the most out of the function. After youre 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 youd like your data to be presented. Flash Fill requires you to type more than one entry so Excel can recognize that youre 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, were 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, weve 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 contact@inciter.io