Show
Page 2What 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 ShortcutFlash 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.
How to Use Flash Fill in ExcelUsing 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.
Using Automatic Flash Fill in ExcelAutomatic 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 RibbonIf 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:
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.
Using Flash Fill from the Fill HandleUsing 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.
Examples of What You Can Do With Flash FillLet's see what all you can do with the flash fill tool in excel with examples. Example 1 – Combining data from multiple fieldsThe 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 dataOne 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 MaskingThere 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.
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 SpacesIf 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 StringThe 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:
Example 6 – Setting Date Format with Flash FillConverting 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 LimitationsFlash 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 RecognitionThe Excel Flash Fill has its fair share with recognizing patterns in the data. However, various issues can occur:
2. Lack of Dynamic ResultsWhile 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 VerticallyThe 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 OFFFlash 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:
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 ThemThe 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 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. ConclusionThe 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. |