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.
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:
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.
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.
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.
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:
Presently, this will return the word Subaccount, as shown in the second layer of Figure 4.
We’ll now extend the formula further:
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.
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 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.
At this point you can copy Column Q to the clipboard and then paste it as values, as shown in Figure 7.
The final step is to delete the rows that do not contain transactions, as shown in Figure 8:
- Activate Excel’s Data
- Choose Filter
- Click the filter arrow in cell F1, or the Date column of your report, as dates only appear on transaction rows
- Uncheck the Select All
- Check the Blanks
- Click OK.
You can now select and delete the visible rows in the usual fashion.
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.