Flash Fill - A Good Trick to Know.

Flash Fill

If you download data or use a lot of Text functions, Flash Fill is a good trick to know. It is a feature that sometimes allows you to avoid using Text functions and copying the results.
This feature is only available for users of Excel 2013 and higher. 
Flash Fill looks at your data entry and tries to duplicate your pattern.

For example, in this screen shot, I clicked in cell B2 and typed the domain name millcreekpublishingllc.com and then clicked on the Data tab and selected the Flash Fill icon on the tool bar.

 Flash Fill looked at the pattern and then duplicated  and then copied it down.  I did not even have to tell Excel what cells to look at. It kept going down until it did not see a pattern anymore.

So, I didn’t have to worry about using  text functions such as SEARCH, MID, LEFT or RIGHT. Super easy right? It doesn’t replace text functions, but it may lessen your reliance on them particularly if you are doing something quick.

Here are a couple of things to be aware of though.

Be careful as it only works if it detects the pattern on existing text. If I inserted a row between row 3 and row 4 or entered a new email on row 5 and then tried to copy down, it would just copy what was above it so in these case I would have 2 domains showing gmail.com. 

If you right-click, you should see Flash Fill on the short cut menu.

Flash Fill will also allow you to create a pattern. For example, in this screenshot, when I downloaded phone numbers, just the numbers downloaded. So, I can type in the format that I want, in the top cell, and then use Flash Fill.

Now, it is not perfect.  Notice that Flash filled in the text above the cell I was in as well. So my heading now has parentheses after the first 3 characters and the hyphen has been applied as well.  Also, in this case, it assumed that all the cells contain the same number of characters. If I add in a phone number that does not have an area code and use Flash Fill, Excel will still follow the pattern as best it can. 

Leading zeroes can also create issues so be careful of that as well. This is definitely a feature to try. It could be a big timesaver.

Tired of Nesting IF after IF?
Get off the Nest and use IFS()

IFS()  is a Logical Function that can be found in Excel 2019 and Office 365.
This function allows you to test for up to 127 conditions and determine if they are true.
So, no more pulling out the magnifying glass to find your mismatched parentheses or mumbling the formulas to yourself as you go through trying to figure out where your error is.
IFS() makes it much easier although it is not perfect. Two things to be aware of. It only tests for TRUE conditions and displays a #N/A error if it finds a FALSE. Also, Excel stops as soon as it finds a TRUE condition so the order of your tests (conditions) can matter sometimes. Here is a quick video on the IFS() function.

BTW - If you are doing nested IFs that contain anywhere near 127 conditions, please read about INDEX MATCH and VLOOKUP because no one should be doing nested IFs to that degree. I need to get an aspirin, just thinking about it!