Flash Fill in Excel 2013 By Nate Moore, CPA, MBA, CMPE you have ever found some of Excel’s complex functions and formulas intimidating or time-consuming to implement, this column is for you. One of the powerful new features in Excel 2013, Flash Fill, can recognize patterns in data and extract interesting information for you without writing any formulas. All you need to do is show Excel what you are looking for, and Flash Fill takes care of the rest. How to Use Flash Fill Figure 1 shows a sample list of patient names that might have been extracted from a billing system. To use Flash Fill, simply start typing the data you want extracted from each row in the previous column. In Figure 2, the patient’s last name was typed in the column next to the patient names. As soon as a few patient last names were entered, Excel recognized the pattern and proposed the last name for the rest of the patients, as shown in gray in Figure 2. To accept Excel’s suggestions, simply press enter. After pressing enter, Excel displays the “Flash Fill Options” button. Clicking the button shows the menu shown in Figure 3. The “Flash Fill Options” menu makes it easy to undo the proposed changes or to select all of the changed cells. If you are looking for Flash Fill in the ribbon, you can find it under the “Fill” menu on the “Home” tab as shown in Figure 4 or under the “Data” tab as shown in Figure 5. For comparison, extracting the last name from this column can be accomplished with the following formula (assuming all patients have a middle initial and the full name is stored in cell L4): =RIGHT(L4,LEN(L4)-FIND(".",L4)-1) Simply giving Excel a few examples by typing in last names is much faster than generating a formula like that! What Flash Fill Can Do Flash Fill can extract all kinds of interesting information from adjacent columns. For example, in Figure 6, Excel has extracted the day from each of the dates in the previous column. In Figure 7, Excel has extracted two numbers from the pipe-delimited data (the data are separated by the vertical bar/pipe symbol). Remember that Flash Fill only works by entering example data 36 HBMA BILLING • MARCH.APRIL.2014 in the column adjacent to the data. The trick in Figure 7 is that, after extracting the middle number (580, 611, 78, etc.), I inserted a new column next to the data. I entered 22, 12, and 52 in the new column to extract the number at the end of the pipedelimited data. Flash Fill can do more than extract data: it can also easily add data to cell ranges. The data in Figure 8 was originally exported as a nine-digit number. In the column to the right, I added two hyphens to format the data as a Social Security number. Flash Fill followed that example and formatted the remaining columns in the same way. Flash Fill can also format phone numbers as shown in Figure 9. Both of these examples would involve a long formula to accomplish what Flash Fill can recognize in seconds. The best way to see what Flash Fill can do is to try it. Find a column of data that you need to extract data from or add data to and give Excel some examples in an adjacent column. Even though the examples in this article have been immediately to the right of the data, Flash Fill also works with examples immediately to the left of the data as well. Flash Fill works in adjacent columns, but not in adjacent rows. If you need Flash Fill to work in data stored in rows, copy the data and use the “Paste Special Transpose” function as shown in Figure 10 to transform the row into a column. Once the data is in a column, use Flash Fill. If you need the data back as rows, copy the data again and use the Paste Special Transpose function to get the data back in rows. If you would like to see Flash Fill in action, watch Excel Video 373 at www.mooresolutionsinc.com. Remember that Flash Fill is new in Excel 2013. Nate Moore, CPA, MBA, FACMPE, writes custom SQL Server code to connect practice management data to Excel for data mining with practices throughout America. Nate’s first book, Better Data, Better Decisions: Using Business Intelligence in the Medical Practice, was just published by MGMA. His free Excel Videos have been viewed more than 600,000 times and are available at mooresolutionsinc.com. Like PivotTableGuy on Facebook or follow PivotTableGuy on Twitter to be notified each time a new Excel Video is released. If
Billing_MA14
To see the actual publication please follow the link above