How to Pivot and Filter a QuickBooks GL Detail in 30 seconds

If you are an auditor or accounting consultant for small businesses, you probably understand the value of having the general ledger detail at your disposal. At its core, the GL detail is an incredibly helpful data set that can be used to tell the story of the company's activity during a given time period. However, the default GL detail export from QuickBooks cannot be efficiently analyzed right away because some of the data is not in the right place for you to capitalize on the tools that Excel gives you for data analysis. Today, I'm going to walk you through a step-by-step process to format a QuickBooks GL detail export in about 30 seconds. 

Step 1: Identify and Label the Account Column

In a default QuickBooks GL detail report, the account label is placed in the leftmost column on the first line of each account's activity in the workbook. Additionally, a total line can be found in this report as well on the last line of each account's activity. This column usually has no label on the top, but in order for this set of instructions to work, we will need to put some information in the top row to label this column. Once you have filled in that cell, you are ready to move on to step two.

Step 2: Select the Account Column's Blanks

The problem with QuickBooks GL exports is that not every line is tagged to an account by default. The next step to solving this problem is to fill in the empty cells with the right information. You could have an intern manually copy and paste the account information into each of the relevant cells, but that would take far too long. Instead select the blank cells by following these instructions:

1. Highlight the entire column

2. Hit F5, go to special, select blanks

Great! Now you’ve selected all the blanks in the column that need to be filled. The next step is to fill in those gaps!

Step 3: Fill the Gaps

With all the blanks selected:

1. Press the up arrow to select the cell above the first blank as a relative reference

2. Press CTRL+Enter to put that formula in every selected cell

You should now have a column that is entirely filled with the account information for each GL line item that can be filtered using the filter tools in Excel. 

Optional Step 4: Further Cleaning and Formatting

Here’s some of the extra steps that I take after getting the data put into the cells that are really helpful for further analysis and manipulation: 

1. Copy and paste values for the column so that any sorting or copying doesn’t mess with the intended use of the cells.

2. Filter for “total” rows and change the text and cell color (I use red text and dark gray fill) so that it’s easier to spot these rows when scrolling through the GL detail.

There you have it! Now you have a QuickBooks GL detail export that can be easily skimmed and analyzed using Excel tools in less than 30 seconds (once you get the hang of doing all the above inputs). I can confidently say that this trick is the best ROI for the time spent. I hope it helps you too!