QuickBooks

Reconstituting QuickBooks General Ledger Data

Written by David Ringstrom

QuickBooks often makes me feel like the proverbial monkey that can’t get his hand and the banana out of the jar at the same time. To wit, once transactions are in the software, it’s often difficult at best to get the data back out to Excel without the aid of third-party tools. One report that does enable me to get much of my data out of QuickBooks is the General Ledger report. However, like most QuickBooks reports this report is designed to look good printed on paper, but does not lend itself to easy analysis. In this article I’ll share some formula techniques that I rely on to transform the QuickBooks General Ledger report into an analysis-ready format.

As shown in Figure 1, a typical general ledger exported from QuickBooks to Excel is going to pose several problems:

  • Account names only appear on a single row. This becomes a deal-breaker if we’re hoping to use a Pivot Table in Excel to analyse our data.
  • Subaccounts appear in separate columns from the parent account, which further complicates analysis.
  • Activity that is coded to the parent-account gets tagged as Other, instead of the parent account name. For instance, rows 3167 and 3168 in Figure 1 show 63600 • Professional Fees – Other. The chart of accounts doesn’t actually contain a Professional Fees – Other account, this is simply the QuickBooks method for reporting activity reported to parent accounts that also include subaccounts. Thus this Other tag can muddy the analysis waters further.

I’ll use three columns of formulas to resolve all of these issues. The formulas will appear rather complex to the uninitiated, so I’ll only realign a single level of subaccounts, but the subaccount formula can be adapted to handle as many levels of subaccounts as you may have.

QuickBooks General Ledger Data

Figure 1: Some accounts contain subaccounts in QuickBooks. This is how they appear when exported into Excel.

The general ledger that I’m working with was exported from the Sample Product-based Company in QuickBooks Desktop. The last column of the report appears in Column N, so I’ll start adding new formulas starting in Column O. You can use whatever the next available column adjacent to your report if it differs from this example.

To get started, enter the word Account in the first cell of the column. In my case, I’ll add Account to cell O1. Next, on row 2 enter the following formula:

=IF(B2=””,O1,B2)

In this case cell B2 should be a cell that contains the first parent account name on your general ledger. Depending on your QuickBooks settings, you may need to reference cell C2 or another column instead. The formula is testing to see if cell B2 is blank, and if so, returns the account name from the previous row in column O. Otherwise, it returns the account name from column B.

General Ledger - Figure 2

Figure 2: This formula will make the parent account name appear on every row.

At this point we’re ready to copy the formula down to the bottom of your report. As shown in Figure 3, the easiest way to do so is to select cell O2 and then double-click the Fill Handle in the lower right-hand corner. Excel copies the formula down to the last row of your report. At this point the parent account names should appear on every row, and any rows that contain account totals should appear to be blank.

General Ledger - Figure 3

Figure 3: Double-click the Fill Handle in cell O2 to copy the formula down the length of the column.

Next we’ll need to identify the subaccounts in a similar fashion. To begin, we’ll label cell P1 as Subaccount. We’re going to need to nest three levels of IF statements, so we’ll build the formula one step at a time. The first step is to ignore parent accounts that are masquerading as subaccounts within the general ledger. To do so, we’ll place the following formula in cell P2, as shown in Figure 4.

=IF(RIGHT(C2,5)=”Other”,””,C2)

In this case, we’re testing cell C2 to determine if the last 5 characters in cell C2 are the word Other. If so, the formula will return the value of cell C2. Excel formulas that reference a blank cell return zero, so don’t be startled by the zero that appears, because cell C2 is blank.

The next step is to bring down the heading from the previous row if a subaccount is blank, so we’ll wrap another IF statement around our existing formula:

=IF(C2=””,P1,IF(RIGHT(C2,5)=”Other”,””,C2))

Presently, this will return the word Subaccount, as shown in the second layer of Figure 4.

We’ll now extend the formula further:

=IF(B2<>””,””,IF(C2=””,P1,IF(RIGHT(C2,5)=”Other”,””,C2)))

General Ledger - Figure 4

Figure 4: This formula makes the subaccount names appear on the applicable rows.

In this case, we’re testing to see if cell B2 is not blank, as evidenced by the <>””. If cell B2 contains a parent account name, we know that cell C2 will not contain a subaccount name, and so we’ll make the cell appear as blank. Otherwise, if cell B2 is blank, then we’ll carry out our previous tests. Cell P2 will now appear as blank, which is expected since cell B2 contains an account name, as shown in the third layer of Figure 4. Select cell P2 and then double-click the Fill Handle to copy the formula down the column. As you scroll down your ledger you should find that the subaccount names appear on the appropriate rows. Otherwise, the cells are blank, as shown in Figure 5.

General Ledger - Figure 5

Figure 5: Column P now has the subaccount name associated with transactions coded to subaccounts.

This now brings us to Column Q, by which we’ll combine the parent and subaccounts together. Enter the words Combined Account in cell Q1. We’re now going to craft a formula that will pull in the parent account name, and if a subaccount name exists, then add a colon and the subaccount name. This too will require an IF statement:

=O2&IF(P2=””,””,”:”&P2)

=O2 carries over the parent account name. Next we test to see if cell P2 is blank. If so, the formula returns two double-quotes. Otherwise, it tacks on a colon and the subaccount name. Double-click on the Fill Handle in cell Q2 to copy this formula down the column.

General Ledger - Figure 6

Figure 6: This formula combines parent and subaccount names together when applicable.

At this point you can copy Column Q to the clipboard and then paste it as values, as shown in Figure 7.

General Ledger - Figure 7

Figure 7: Copy and paste the formulas a values.

The final step is to delete the rows that do not contain transactions, as shown in Figure 8:

  1. Activate Excel’s Data
  2. Choose Filter
  3. Click the filter arrow in cell F1, or the Date column of your report, as dates only appear on transaction rows
  4. Uncheck the Select All
  5. Check the Blanks
  6. Click OK.

You can now select and delete the visible rows in the usual fashion.

General Ledger - Figure 8

Figure 8: Use Excel’s Filter feature to show the non-transaction rows, which you can in turn delete.

The final step is to create an Amounts column that will combine the debit and credit amounts, as shown in Figure 9. You can then copy that formula down the column, and then convert it to values as shown earlier. You’re now free to delete any unwanted columns from the General Ledger report, and then commence to analyzing your data by way of pivot tables or work with the data as you wish.

General Ledger - Figure 9

Figure 9: Use an Amounts column to combine the Debits and the Credits.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

2 Comments

  • An alternative is to use the Go To Special menu (F5, Ctrl-G, HOME/Find & Select/GoTo)

    1.Highlight all cells in column B from B2 to end of table
    2. Press F5 and select special, then blanks. This selects all blank cells with the first blank cell available highlighted to type in.
    3. Enter formula – =concatenate(B2,” “,C3,” “,D3) and Ctrl+Enter to complete formula. This will copy main account heading above, and add a space followed by the sub account name where applicable, and apply it to all blank cells. (this is for 3 nested subaccounts so add E3 etc for any others)
    4. Highlight all cells in column B and copy and paste values to turn formulas into text.
    5. Instead of using the filter to remove non active rows you can also use the F5-Special-blanks to select empty cells in say the date column then right click a blank cell and delete rows to remove all non active rows.
    6. add amounts formula to combine debits and credits and hey presto a pivot ready table.