Blog

Flash Fill with Excel

How to use Flash Fill in Microsoft Excel

You are probably familiar with Excel’s very powerful Autofill feature (if not, learn more about Autofill here and then read on). However, did you know there is a similar feature that is equally useful called Flash Fill?

Say you have information in Excel not formatted the way you need it to be and going through the entire list manually to correct it seems daunting. In this case, Flash Fill can do the work for you.

What is Flash Fill?

You use the Flash Fill feature to automatically fill-in values in one column from information referenced from another, adjacent column (extract, combine/concatenate, insert characters, reverse data, change case, format, etc.).

Flash Fill Examples

  • Splitting a single name column into separate first name and last name columns (or vice versa)
    • Sample Scenario: If you have a column that contains the first and last names of employees, you could enter just the last name of an employee into the adjacent column and then use Flash Fill to fill-in the corresponding values of all of the last names of the employees within the column. This can greatly reduce your dependence on text-parsing functions such as LEFT, RIGHT, MID, FIND, LEN, UPPER, LOWER, etc. within Excel.
  • Adding hyphens and parentheses to unformatted phone numbers or social security numbers
    • Sample Scenario: Use Flash Fill to format previously unformatted phone numbers of social security numbers. For example, if you had an adjacent cell with the value “9135551111” and then entered “(913) 555-1111” into the next column for the flash fill entry, all of the other flash-filled values would also use hyphens and parentheses in the formatting.
  • Changing lower case to upper case characters (or vice versa)
    • Sample Scenario: You can even use Flash Fill to change the text case. For example, if you had an adjacent cell with the value “Jon Doe” and then entered “doe” into the next column for the flash fill entry, all of the other flash-filled values would also use all lowercase last name entries.

How to Use Flash Fill in Excel

  1. Click in a blank cell within the column adjacent to the column that contains the data you want to retrieve.
  2. Then enter a data value (formatted as necessary) to extract from the adjacent cell.
  3. Finally, click in the next cell below the entered value and then do one of the following:
    • Type a second entry to establish the pattern for Excel to use
    • Press “Ctrl” + “E” on your keyboard
    • Click the “Data” tab in the Ribbon and then click the “Flash Fill” button in the “Data Tools” button group
    • Right-click the autofill handle as you drag down and then choose “Flash Fill”

Typing a second entry to establish the pattern for Excel to use

Excel should then extract all of the corresponding data values from the adjacent column to fill-in the cells within the current column. After you flash fill a column, you can press “Enter” to accept the suggestions or click the drop-down arrow next to the small icon that appears next to the values entered to display a menu of choices:

  • Select the “Undo Flash Fill” command to undo an unsuccessful flash fill attempt.
  • Select the “Accept suggestions” command to accept the flash fill changes.
  • Select the “Select blank cells” and “Select changed cells” commands to select the blank cells or changed cells within the flash filled column.

New column flash filled

When using the Flash Fill feature, the referenced data column can be non-adjacent as long as there is adjacent column data between the flash filled column and the referenced column. In other words, you cannot have an empty or blank column between the column that is being flash filled and the column values referenced. In this case, Excel will display a message box that informs you that it cannot find a pattern in the adjacent data.

Microsoft Excel didn't see a pattern for fitting in values for you

A Word of Caution

Flash fill works best when the referencing values have a consistent pattern of data entry. For example, if you are referencing values within a column where some names have a middle name, some do not, and others have just an initial, Flash Fill may parse the incorrect information from the fields that it references when it fills-in the column’s values. So be sure to double-check the values returned by the Flash Fill feature.

If errors occur, you can type more examples to help Flash Fill to “see” what you want it to do or you can undo or delete the entries made by the Flash Fill feature and then use the traditional text-parsing functions within Excel (mentioned above) to try to extract the needed values instead.

Excel Flash Fill example

Conclusion

As you can see, Flash Fill can be a tremendous time saver. Now that you know what it is, when and why you should use it, and how to use it, experiment with your own data!

For more information about Flash Fill and other Excel features, take an Excel Class from Centriq.

Be sure to watch for future blog articles in this Excel series. The next topic will feature Fantastic Formulas and Functions in Excel and later posts will discuss Awesome Add-ins and Super Settings. Centriq occasionally hosts free Excel Lunch and Learns that feature live demonstrations of items covered in posts like this. See the latest Excel Lunch and Learn here.

And, if you’d like more information about Excel or other training classes available, contact a Centriq Training Advisor online or at 913.322.7062 for assistance.

Start Date
End Date
Day/Eve
Break Weeks
Track
Jan 23, 2023
Jul 27, 2023
Eve
4/3/23-4/7/23
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-V
Jan 30, 2023
May 5, 2023
Day
3/6/23-3/10/23
4/10/23-4/14/23
CSSP-V
Feb 6, 2023
May 19, 2023
Day
3/27/23-3/31/23
FSCP-V
Mar 20, 2023
Jun 23, 2023
Day
4/24/23-4/28/23
5/22/23-5/26/23
CSSP-V
Apr 10, 2023
Jul 28, 2023
Day
5/29/23-6/2/23
7/3/23-7/7/23
FSCP-V
Apr 24, 2023
Oct 19, 2023
Eve
7/3/23-7/7/23
8/21/23-8/25/23
CSSP-V
May 15, 2023
Dec 14, 2023
Eve
7/3/23-7/7/23
9/4/23-9/8/23
11/20/23-11/24/23
FSCP-V
Jun 5, 2023
Sep 8, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-V
Jun 19, 2023
Oct 6, 2023
Day
7/3/23-7/7/23
9/4/23-9/8/23
FSCP-V
Jul 24, 2023
Jan 25, 2024
Eve
10/2/23-10/6/23
11/20/23-11/24/23
12/25/23-12/29/23
CSSP-V
Aug 14, 2023
Nov 17, 2023
Day
9/5/23-9/8/23
10/16/23-10/2/23
CSSP-V
Aug 28, 2023
Dec 15, 2023
Day
9/4/23-9/8/23
11/20/23-11/24/23
FSCP-V
Oct 30, 2023
Feb 2, 2024
Day
11/20/23-11/24/23
12/25/23-12/29/23
CSSP-V
Oct 30, 2023
May 2, 2024
Eve
11/20/23-11/23/23
3/4/23-3/7/23
CSSP-V
Nov 6, 2023
Mar 1, 2024
Day
11/20/23-11/24/23
12/25/23-12/29/23
1/29/24-2/2/24
FSCP-V
If you don't see the Cohort Start date you are looking for don't forget to check out our campus calendars.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.
Start Date
End Date
Day/Eve
Break Weeks
Track
Feb 13, 2023
Mar 19, 2023
Day
3/20/23-3/24/23
4/17/23-4/21/23
CSSP-I
Apr 17, 2023
Jul 21, 2023
Day
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-I
Jun 5, 2023
Sep 8, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-I
Jul 31, 2023
Nov 3, 2023
Day
9/4/23-9/8/23
10/2/23-10/6/23
CSSP-I
Sep 25, 2023
Dec 22, 2023
Day
11/20/23-11/24/23
CSSP-I
Nov 27, 2023
Mar 1, 2024
Day
12/25/23-12/29/23
1/29/24-2/2/24
CSSP-I
If you don't see the Cohort Start date you are looking for don't forget to check out our online instructor-led calendar.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.
Start Date
End Date
Day/Eve
Break Weeks
Track
Jan 16, 2023
Apr 21, 2023
Day
2/2023-2/24/23
3/20/23-3/24/23
CSSP-I
Jan 23, 2023
Jul 27, 2023
Eve
4/3/23-4/7/23
5/22/23-5/26/23
7/3/23-7/7/23
CSSP-I
Feb 27, 2023
Jun 2, 2023
Day
4/3/23-4/7/23
5/1/23-5/5/23
CSSP-I
Apr 10, 2023
Jul 14, 2023
Day
5/15/23-5/19/23
7/3/23-7/7/23
CSSP-I
May 22, 2023
Aug 18, 2023
Day
7/3/23-7/7/23
CSSP-I
Jun 26, 2023
Sep 29, 2023
Day
7/3/23-7/7/23
8/7/23-8/11/23
CSSP-I
Aug 7, 2023
Nov 10, 2023
Day
9/4/23-9/8/23
10/9/23-10/13/23
CSSP-I
Sep 11, 2023
Dec 15, 2023
Day
10/16/23-10/20/23
11/20/23-11/24/23
CSSP-I
Oct 23, 2023
Jan 26, 2024
Day
11/20/23-11/24/23
12/25/23-12/29/23
CSSP-I
Oct 30, 2023
May 2, 2024
Eve
11/20/23-11/23/23
12/25/23-12/28/23
3/4/24-3/7/24
CSSP-I
Dec 4, 2023
Mar 8, 2024
Day
12/25/23-12/29/23
2/5/24-2/9/24
CSSP-I
If you don't see the Cohort Start date you are looking for don't forget to check out our online instructor-led calendar.
CSSP-I: Cloud & Security Specialist Program (In-Person Modality)
CSSP-V: Cloud & Security Specialist Program (Live Virtual Modality)
FSCP-I: Full Stack Coding Program (In-Person Modality)
FSCP-V: Full Stack Coding Program (Live Virtual Modality)
Please note that Centriq will be closed on the following observed holidays: New Year’s Day, Memorial Day, Independence Day, Labor Day, Thanksgiving Day, the day following Thanksgiving Day, and Christmas Day.