Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Flash fills are like the automatic fillers in the cells of the excel table, excel senses the pattern in the data from the previous fills of the excel, when we type some characters in the adjacent cells next to it automatically flashes the suggested data, it is available in the data tools section in the data tab or the keyboard shortcut CTRL + E.
Flash Fill in Excel (Table of Contents)
What is Flash Fill in Excel?
I term Flash Fill as Life Saver. I term Flash Fill as productivity increaser. I term Flash Fill as a time saver and many more. Flash Fill in Excel understands your data pattern and repeat the same data pattern entered by you in excel.
One simple request to all our readers. Please install the latest version of Excel to see the magic of Flash Fill option in excel. Flash Fill is available from Excel 2013 and Excel 2016
How to Turn On Flash Fill in Excel 2013 and 2016?
By default, excel must have turned on Flash Fill option in your system. Just in case it has not turned on turn on by following below steps.
Go to File > Options.
Then Go to Advanced > CheckBox Automatically Flash Fill.
How to Use Flash Fill in Excel 2013 and 2016?
Now take a look at some of the live common examples in the practical world.
Example #1 – Extract FY From the Invoice Number
I have data of Invoice numbers. I want to extract FY from this list. Applying many complex formulas will be a tough task for the intermediate user. But Flash Fill save me here.
Step 1: Firstly we need to tell the excel what we are doing so type the first FY in the cell B2.
Step 2: Now go to Data > Flash Fill.
The Shortcut key to apply Flash Fills is:
Step 3: After typing the first FY into the cell now excel understands the pattern of the fill. We can Flash Fill in two ways here.
Firstly, drag the first entry until the end and Now click on the AUTOFILL option and select the Flash Fill.
Now it will insert all the FY numbers from the invoice numbers columns.
Secondly, after typing the first FY into the cell, press CTRL + E it will extract FY the invoice numbers columns.
Example #2 – Extract First Name & Last Name
This is the common task we all have done in the past. Flash Fill can help us here.
From the above list, I want to get first name & last name separately.
Firstly I will type the first name in B2 cell and last name in the C2 cell.
Now I will go to B2 cell and press CTRL + E.
I will go to C2 cell and press CTRL + E.
Example #3 – Format Numbers Using Flash Fill
Flash Fill not only extracts a portion of the cell but also helps us format the cell value. Now take a look at the below example. I have phone numbers and I want to format like this: 9056-2358-90.
Firstly, I will type the format in the first cell.
By this time excel already knows the pattern of the data series Now I will type CTRL + E.
Example #4 – Combine Two Values Using Flash Fill
Flash Fill not only separates the first name and last name but also combines as well. Now I have first name & last name in two different columns, I want the full name in the next column.
Type the required patter name in the cell C2.
Now press the shortcut key to Flash Fill.
Limitations of Flash Fill in Excel 2013 and 2016
Flash Fill has its own limitations too. It is very important to know them:
- Flash Fill is not dynamic. Remember it is not a formula to change the resulting cell if any changes.
- Flash Fill can wrongly enter the data. Take look at the below example.
In the above image, I wanted to extract the middle name. In the first cell, I have typed the middle name and pressed CTRL + E. But for few names, there are no middle names so it has extracted the first name itself.
Things to Remember
Flash Fill works based on the pattern of the data. If there is no pattern excel will show the below error message.
This has been a guide to Flash Fill in Excel. Here we discuss how to Use Flash Fill in Excel 2013 and 2016 using Data Tab, Shortcut key and Auto Fill option along with practical examples and downloadable excel template. You may learn more about excel from the following articles –