More

More

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

by K2 Enterprises | Oct 05, 2016
Asian-woman-laptop-whitebg-blog-horizontal-400x250

Excel 2013 introduced Flash Fill, 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.

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).

flash-fill-tech-tip-figure-1-500x264
Figure 1

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.

flash-fill-tech-tip-figure-2-600x223
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.

flash-fill-tech-tip-figure-3-600x225
Figure 3

We can manipulate the preliminary results generated by Flash Fill by providing the tool with some additional guidance. Simply click in cell D21 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.

flash-fill-tech-tip-figure-4-600x226
Figure 4

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.

flash-fill-tech-tip-figure-5-600x226
Figure 5

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.

Click here for a video demonstration of this tip.

This article appeared in the Summer 2016 issue of The WashingtonCPA magazine.

Please log in to post a comment.

ABOUT WSCPA

The Washington Society of Certified Public Accountants is the only organization in the state of Washington dedicated to serving the professional needs of CPAs, educating consumers about CPAs and the services they provide, and encouraging students to study accounting and enter the profession.

Your Profession. Your Future. Your Advocate.

CONTACT

Washington Society of CPAs
902 140th Ave NE
Bellevue, WA 98005-3480

  • (P) 425-644-4800
  • (F) 425-562-8853

The WSCPA's business hours are 7:30 a.m. to 4:30 p.m., Monday through Friday.