how to use flash fill in excel
What is Excel Flash Fill, and more importantly, what is so special about it?
Flash Fill is a special tool in Excel that can automatically fill in values for you once it recognizes a pattern in the data.
Have you ever wondered if Machine Learning exists in Excel? And if it does, where does it exist? Flash Fill is one of those tools that use Machine Learning techniques to evaluate a pattern in the data, learn the pattern, and use that pattern to fill the cells. 
What started quite surprisingly at an airport became a vital feature for all Excel users since it was introduced in Excel 2013 and all later versions. Automation of data became possible in Excel with the help of this tool.
You can start using this feature as soon as you have entered a few cells of data. Depending on your usage, you will be able to save from minutes to hours to days using the Flash Fill tool. With auto-formatting and optimization included, it will make everything easy and smooth.
Location of Flash Fill and Flash Fill Shortcut
Flash Fill is a vital tool that every hardcore Excel user uses to ensure efficiency and effectiveness. Because of its value, the Flash Fill is located in two different yet easily accessible locations for ease of use.
- Head over to the Data Tab > Data Tools section, and you can see it right there.
- If you want to use the alternate way, it can be found in the Home tab. Click on the Fill drop-down menu, and you will find it right there.
- You can also use it by its shortcut. Use CTRL + E to use Flash Fill.
How to Use Flash Fill in Excel
Using Flash Fill is easy. All you have to do is provide a pattern in the data selection you wish to apply the Flash Fill upon. Keep in mind that the pattern is a must. If there is no pattern, you will see the following error.
Once you have input data as seen in the screenshot below.
As seen in the screenshot, the data is entered. As you can tell, the data is arranged as Name, Country, and phone number. To start using Flash Fill, what you need to do is provide a pattern as we have.
- Add a new column right to the data that you have already entered.
- Add the desired value for that cell in the new column, as seen here.
- Now you can apply the Flash Fill feature by pressing CTRL + E or use either of the ways that we told you about.
- Press Enter to confirm your selection.
- Voila! It is all done.
Using Automatic Flash Fill in Excel
Automatic Flash Fill works extremely well, but it will only work once it recognizes a definite pattern. While in the screenshot seen here, we have only one column that is filled and the automatic Flash Fill will not work because the pattern is insufficient.
But once we add another column and fill the first cell, it automatically recognizes the pattern as it is a clear one. Now, Flash Fill is sure that the user is entering data in a pattern, and it automatically provides a preview of it if the user wishes to confirm as seen in the screenshot below.
Once you press enter, the data is confirmed and entered.
Using Flash Fill from Ribbon
If you wish to use the Flash Fill option from the Ribbon, it's easy. Here are the steps you can follow to make it work:
- Create a pattern in the data to allow the tool to acknowledge and automate
- Select that column. Selecting a single value is enough too.
- Once you have entered a field and selected it, click on the Flash Fill button.
- It is all done.
Using this option is equivalent to manually using the Flash Fill tool. If it is not automatically working for you, you can use the Flash Fill button from the Ribbon.
Using Flash Fill from Shortcut (CTRL + E)
The steps to use Flash Fill from the shortcut are similar to what we followed before.
- Select the column where you want to use the Flash Fill tool.
- Press the combination of CTRL + E on your keyboard and the data will be populated.
Using Flash Fill from the Fill Handle
Using Flash Fill from the fill handle is one of the most convenient ways to automate your data and fill the column automatically. The Fill menu or handle is the one that you use by dragging a cell from the right bottom corner.
The only thing that you have to do when dragging the cell downwards is to hold the right-click of the mouse/touchpad instead of the left.
- Select the data on which you want to apply the FlashFill feature.
- Press and hold the right-click.
- Drag it down till the cell you want to fill is reached.
- When reached, release the right-click and select Flash Fill from the menu, as shown in the screenshot.
Examples of What You Can Do With Flash Fill
Let's see what all you can do with the flash fill tool in excel with examples.
Example 1 – Combining data from multiple fields
The Excel Flash Fill feature is particularly productive when you have to combine data from multiple columns or cells. The feature will learn your pattern and do the job seamlessly.
As you can see in the screenshot below, we have used two columns namely Name and Country to generate a sentence. Since one example is added and the Flash Fill feature now knows what to do, let's see what happens when we generate results.
As evident in the screenshot below, results have been generated perfectly. We have our complete information combining data from the first two columns.
Example 2 – Generate email addresses using data
One of our favorite things to do using the Flash Fill feature is to generate email addresses using data from multiple columns. This is a particularly useful example because there are often major organizations that have to create email addresses on their domain for their employees. It can be done with a click using the Flash Fill option.
As seen in the screenshot above, we used the Name and Country to generate an email address. When we apply the Flash Fill feature on this data, the following results can be seen.
Thus, all of the email addresses were generated without any hassles and with a single click.
Example 3 – Replace Data or Data Masking
There are important numbers and data, such as social security numbers or credit card numbers, that do not need to be shown in the spreadsheets when they are extracted. You can censor or hide particular parts of data using the Excel Flash Fill feature in a number of steps.
- Provide an example of the masked data in the first cell.
- Once an example is established, you can apply the Flash Fill feature and see the results if they are perfect or need any changes.
Similarly, you can also mask the credit card numbers or other important data that you feel needs to be hidden.
Example 4 – Formatting data or Removing Unnecessary Spaces
If formatting data is the requirement, the Flash Fill tool can handle it as well. As long as there's an established pattern, the tool will not have any issues generating the results. Here, we can see an example where data is unformatted in the screenshot below.
There are unnecessary spaces, and the characters are not capitalized. To correct this, the Flash Fill tool can be used.
Providing one example in the first cell with the value of "Leo Messi", the Flash Fill feature is applied to obtain properly formatted results.
As you can see, all the inconsistent and unnecessary spaces are removed. The names are capitalized correctly as well.
Example 5 – Extraction of Data from a String
The Flash Fill feature can be used to extract particular parts of data from a string as shown below.
Some of the most common extraction uses are as follows:
- Extract the first or last name or other custom data such as occupation from a text string.
- Extract company names from email addresses.
- Extract names from email addresses.
- Extract birth year from birth dates.
- Extract numbers from any string.
- Extract letters from any string.
Example 6 – Setting Date Format with Flash Fill
Converting data is also possible using the Flash Fill feature in Excel. You can convert data in any way that could create a possible pattern that the tool can recognize and capitalize upon. One of the best examples is converting date strings to actual dates, as shown in the screenshot below.
Excel Flash Fill Limitations
Flash Fill might be an awesome way to automate and fill your data but it still has its shortcomings. The thing is that nothing is perfect in this world, and this tool is no exception. Because of real data sets that machines are still catching up on understanding, the Excel Flash Fill feature may not work as it is supposed to.
Here are three Flash Fill limitations that could make or break this feature for you.
1. Occasional Issues With Pattern Recognition
The Excel Flash Fill has its fair share with recognizing patterns in the data. However, various issues can occur:
- There might be no visible pattern in the data
- The pattern is too complex that the feature will not return results
- Incorrect results due to misunderstanding of the pattern
2. Lack of Dynamic Results
While most of the Excel features provide an automatic updating system that updates the cells as new data is entered in already used cells, the Flash Fill feature does not provide this option. Once you have used the Flash Fill feature, results will not be updated even if you have updated the cells that it is picking data from. As you can see in the screenshot below, we have used Flash Fill in the Email column creating emails from Name and Country.
In the screenshot below, the country of AZ Collins is updated to USA but the Country column and Email column both are showing Australia as we will have to manually update it.
The only way to correct this is to use Flash Fill again so you can obtain updated values. It is particularly annoying when you are dealing with lots of data in multiple spreadsheets. The user can often forget where they have updated and where they have not which could result in missing the latest data.
3. Can Only Work Vertically
The Flash Fill feature only works on vertical data. Horizontal data is not supported with this feature.
How to Turn Automatic Flash Fill Feature ON or OFF
Flash Fill feature can be turned on or off as per requirement. In case it is not working as you desired, you can disable it. On the contrary, if it is not working at all, it might be disabled and it must be enabled. To enable or disable the option, you can follow these steps:
- Open Excel and go to File Tab.
- Go to Options and then Advanced Tab.
- Under the Editing Tab in the Advanced Options, the option to turn the Automatic Flash Fill on or off can be found.
- The checkbox can be checked or unchecked depending upon how the user wants to use it.
This will turn off the automatic Flash Fill feature. Flash Fill can still be used manually through the shortcut, ribbon, or fill handle.
Excel Flash Fill Errors and How to fix Them
The error that exists and will possibly bug the user the most is the pattern recognition error.
Fixing this error could be easy or tricky depending upon your data, the size of your data, and the complexity of your data. There are three ways you can try to fix it.
- If the data is insufficient, add more examples to show the Flash Fill feature how you want it to work. If the user can recognize the pattern in the data, the Flash Fill will get there too.
- Manually applying it everywhere you want. Sometimes the automatic Flash Fill won't show previews or won't work. In that case, the user can manually select the cell or column where it should work and apply the feature through the ribbon, the shortcut, or the fill handle.
- Rearrange the data. The issue persists because the Excel Flash Fill feature cannot recognize a pattern. Sometimes, it can be as easy as shuffling the columns and trying the Flash Fill again manually.
If the error persists, you are unfortunately out of luck, and you will have to manually enter all the data and formulas that you wanted in the first place.
Conclusion
The Excel Flash Fill can be an incredibly useful tool to work if you learn how to use it properly. This tutorial tries to teach you the use of the Flash Fill providing every piece of information that you could and using numerous examples that will help you understand.
Hopefully, by now you have a good idea of how it works and how you can use it on your data to save a lot of time and effort.
how to use flash fill in excel
Source: https://www.exceltrick.com/how_to/excel-flash-fill/
Posted by: cuadradolifeatchas.blogspot.com
0 Response to "how to use flash fill in excel"
Post a Comment