Using Excel's Flash Fill Tool to Clean-Up a QuickBooks Trial Balance

Excel’s Flash Fill tool — first introduced with the 2013 release of Excel — is a great feature for manipulating data in a number of ways. You can use Flash Fill to format data, parse or split apart data, and concatenate or join data. And as you will see in this tip, you can use Flash Fill to clean up a QuickBooks-generated Trial Balance report and put it in a format that most accountants prefer.

Working with Flash Fill

To begin, export the Trial Balance report from QuickBooks into Excel by opening the report in QuickBooks and choosing the Export option. Upon doing so, your report will look similar to the one pictured in Figure 1 (with some rows hidden for presentation purposes).

What is problematic in this case, to many accountants, is the presence of the account numbers and account names in the same column in the Excel worksheet. Additionally, subaccounts in the same cell as their parent account – such as Federal Withholding sharing cell A21 with Payroll Liabilities – is also an issue for many. Fortunately, we can quickly reformat this report in Excel using Flash Fill.

Flash Fill is a pattern-recognition tool, meaning that it manipulates data based on patterns that you specify. Flash Fill is not perfect, as it will not always interpret the patterns correctly. However, as you provide additional guidance to Flash Fill, it “learns” from its mistakes.

To address the issues identified with the report in Figure 1, begin by specifying the pattern you want Flash Fill to use. In this case, we will enter “10100” for the Account No field in cell D2 and “Checking” for the Account field in cell E2, as shown in Figure 2.

Next, we click in cell D2 and execute Flash Fill by using either a CTRL + E keyboard shortcut or by choosing Flash Fill from the Data tab of the Ribbon. Based on the pattern it recognized, Flash Fill provides the results shown in Figure 3 – a good start, but not exactly what we were looking for because Flash Fill did not correctly interpret the pattern as it relates to the nested subaccounts.

We can manipulate the preliminary results generated by Flash Fill by providing the tool with some additional guidance. Simply click in cell E21 and type “24010” – the subaccount number. Upon doing so and pressing enter, Flash Fill’s pattern recognition engine interprets this additional guidance and generates the results shown in Figure 4. Notice that Flash Fill has correctly extracted all of the Account Numbers from column A and placed them in corresponding cells in column D.

With the Account Number field addressed, let us now focus on the Account Name field. Because we have already established a pattern in cell E2 for Flash Fill to use, all that is necessary is to click in cell E2 and run Flash Fill. Upon doing so, Flash Fill provides the results illustrated in Figure 5. As you can see, in a matter of just a few seconds, we have completely cleaned up the report using this incredibly simple, yet powerful, feature of Excel.

Summary

Flash Fill – added to the Excel 2013 release – is indeed a versatile and powerful tool. Using pattern-recognition algorithms, Flash Fill was able to quickly address the formatting issues that many QuickBooks users have with Trial Balance reports generated by the application. Of course, the usefulness of Flash Fill doesn’t end there…give it a try whenever you have a set of data you need to manipulate.

We encourage you to enjoy the video below in which the example presented in this article is demonstrated.

ARE YOU RECEIVING THE K2 TECH UPDATE NEWSLETTER
BY EMAIL EVERY MONTH?

Sign up now so you don’t miss the next issue.

Using Excel’s Flash Fill Tool to Clean-Up a QuickBooks Trial Balance