Use flash fill in Excel and feel fresh

Microsoft Excel is undoubtedly most user friendly tool and being extensively used all over the world. We might be using it daily or maybe casually, but probably always wished that we knew more about this dynamic program. Most of us don't use even 5% of its capabilities.

While most of us know use of VLOOKUP, HLOOKUP, MATCH & INDEX formulas, there is one more useful formula for us, which is FLASH FILL.

What's so great about FLASH FILL?

It's quick and easy to learn how to use, and it'll save you hours and hours of work writing formulas or VBA to do the same sort of things. It is a data tool in MS Excel that will allow you to combine, extract or transform data based on few examples. It automatically fills your data when its senses a pattern.

How it can help us?

For e.g. In Income tax we have received a mail of Intimation of processing of ITR or Intimation of Rectification request or any other notice or order which contains password and which is combination of pan in lower case and DOB, now we do have excel sheet containing two columns ,column A is PAN and second column B is DOB , now by using Flash Fill if you establish a pattern by typing combination of PAN and DOB in Column C, Excel's Flash fill feature will fill in the rest for you based on the pattern you provided.

Now take second scenario, you have database of GSTIN of clients and now you want PAN of these clients, it will be cumbersome process to retrieve these data, but with FLASH FILL it's just one click away, Column A is GSTIN, Now in Column B you establish a pattern by typing PAN out of GSTIN and run a FLASH FILL and you will really feel fresh because you got PAN of all GSTIN in one go.

Flash Fill is data tools in MS Excel that will allow you to combine, extract or transform data based on a few examples. You only need to provide a couple examples of the results you want. Excel will guess the pattern and fill in the rest of the data for you. This is Pretty awesome and way easier than creating an Excel formula to do the same thing.

Which version does have this formula?

Flash Fill was released in Excel 2013, So, all later i.e. Excel 2013, 2016, 2019 and Excel for Office 365 includes this formula.

Excel 2010 or any other previous version will not have Flash Fill.

Where is this function located?

The Flash Fill command can be found in the Data tab of the ribbon. Go to the Data tab ➜ Flash Fill in the Data Tools section. If Flash Fill doesn't generate the preview, it might not be turned on. You can go to Data > Flash Fill to run it manually, or press Ctrl+E. To turn Flash Fill on, go to Tools > Options > Advanced > Editing Options > check the Automatically Flash Fill box.

Another option for using Flash Fill is to add it to the Quick Access Toolbar. This way the command will always be available regardless which ribbon tab you're currently on.

Right click anywhere in the Quick Access Tool bar or Ribbon ➜ select Customize Quick Access Toolbar.

1. Choose All Commands.
2. Select Flash Fill from the list of commands.
3. Press the Add button.
4. Press the OK button.

Say bye- bye to other formulas.

FLASH FILL is more powerful and can replace one or more of the following i.e we all have used left, right, middle, text to columns, concatenate, upward or downward ,Rounding off etc all have some specific requirements which you need to fulfill if you require answers and it is limited one also. It Fill will work automatically after entering a few examples if it's enabled in the Excel options. You can get it to automatically fill results by typing out the first few examples. Excel will then show a preview in light grey and you can accept the results by pressing Enter. Flash Fill can't fill upward with the automatic fill. You can start your examples anywhere in the column, but Flash Fill will only fill downward from there.

You'll be able to do a lot with Flash Fill. Here are a few examples which can be useful to us for our office use.

There is no thumb rule to use this formula, if your pattern has sense then only this formula will work. If Flash Fill is unable to determine a pattern, then it will show the following error.

We looked at all the data next to your selection and didn't see a pattern for filling in values for you.

To use Flash Fill, enter a couple of examples of the output you'd like to see, keep the active cell in the column you want filled in, and click the Flash Fill button again.

Want to learn more about excel - CLICK HERE 

Potential Remedies When Flash Fill Fails.

When Flash Fill fails to return any results or fails to return the correct results, there are possible actions you can take to remedy the problem.

1. Review your examples and correct any errors. A small spelling mistake or missing number can result in Excel failing to find a pattern.
2. Delete your examples and start over. Sometimes you might not be able to see your error when inspecting your examples and starting over might help.
3. Provide Flash Fill with more examples. Excel may need a few more examples to get the correct pattern.
4. If Flash Fill doesn't automatically run after providing the first couple examples, you can use the ribbon command or keyboard shortcut to make it run.
5. If Flash Fill doesn't automatically run, it may be disabled. Check the Excel options menu to make sure the option to automatically run Flash Fill is enabled.

Limitations Of Flash Fill:

While Flash Fill is very powerful, it does have limitations.

1. Results are not dynamic. Flash Fill values will not update when you change the values they're based on. You will need to perform the Flash Fill again in order to update the values.

2. Flash Fill might not always return results. The pattern might be too complex for Excel.

3. Flash Fill may incorrectly identify the pattern and return undesired results. With lots of data, incorrect results might be hard to spot so you essentially need to trust a black box algorithm. for this try to type in 2-3 columns than press ctrl + E and before submitting data cross verify some with examples.

4. Flash Fill doesn't fill results horizontally. Your data will need to be vertical.

Flash fill is quick and easy to use. It's also very powerful and can do all the hard work when it comes to changing your data based on a pattern. It will definitely save you time. In a lot of cases; it will help you avoid complex formulas to manipulate your data.

The author is Chartered Accountant in practice at Bhuj,Gujarat and can be reached at bmmehta89@gmail.com