top of page
  • Lowell

Excel Can Be Flashy

Excel has an interesting feature in the Data Tools Group of the Data Ribbon. It’s called Flash Fill and I believe it arrived in Excel 2013. You can use the keyboard shortcut Ctrl+E or click the Flash Fill icon:

Flash Fill is useful for splitting data into separate cells. Suppose your data has last and first names combined into a single cell and you want to split the data into last name/first name columns. In the table below, I’ve purposely used various delimiters like commas, semicolons, comma and space, semicolon and space, and only spaces to separate the names.


You could write very difficult formulas to check for the various delimiters and then separate the names accordingly, you could use Text to Columns (which alters your original data and will produce three different name columns for that last entry), or you can use Flash Fill. Please note that the columns to be filled must be next to the data you're splitting. In this case, the Last/First columns must be in Columns B & C.

Normally, you would begin in cell B2, type Brown, and press Enter, put the cursor back into cell B2 and click the Flash Fill icon


or press Ctrl+E. However, that would produce incorrect results with the name del Rey Sonya. So, we begin in cell B6 and type del Rey, capitalizing Rey, and pressing Enter. Then we click back into cell B6 and click the Flash Fill icon or press Ctrl+E. To split the first names into Column C, click into cell C2 or C6, Enter the appropriate first name, and initiate Flash Fill. Your data should look like this:


More tips

  1. Flash Fill does not require you to select the data before splitting, as long as there are no empty rows in the data.

  2. Flash Fill leaves the original data intact, so you can compare the results to the original data for accuracy.

  3. In the example used here, I could have begun in Column C and then populated Column B.

  4. Flash Fill works on a single column at a time

3 views

Recent Posts

See All

Excel is a Square

Source: https://www.makeuseof.com/turn-excel-cells-into-squares/ Uses: Calendar Grids, Print your own graph paper Here are two ways to create square cells in Excel. In either method, you can begin wit

A Cat Can't Concatenate, But Excel Can

The Excel CONCATENATE function lets you link or string together text and calculations. I use a simple target heart rate formula in the example below to illustrate the CONCATENATE function. Target hear

Sort Excel Data as You Enter

In December 2020, I spent some time writing functions that would allow data to be sorted as I entered it into Excel. Now I've discovered that Excel in my Microsoft 365 subscription has a function that

Comments


bottom of page