Questions? We have answers.

Data Formatting Guide - Quickbooks Sub-Accounts

Article author
Jonathon Plowman-Samson
  • Updated

This article will help you format any Quickbooks file with the following account structure: 

6a53afc1-15f3-40f2-a2a6-c4d5f791bcc7.png

The defining characteristic of this file type is that Quickbooks accounts and sub-accounts are laid out as layers, or separate cells. To assess general ledger activity, only the most granular level account level is required. In the screenshot above, “5110-00 - Staff Wages” is in the most granular account level. 

We only need to format the GL file to leverage the most granular account number and description, rather than “parent” accounts and descriptions preceding it. 

Part 1: creating a new “Accounts” column and header 

To begin, we will need to give a header to the rightmost column containing a sub-account (in this case, column E). We will name the header “Account.”  

a34c3d59-180a-4362-96de-d6b256d89b8f.png

Select the entire row by clicking on the columns heading.

abc88e8d-39fc-41fc-b17a-b6954d82c2fe.png

Once highlighted, the column header will be green and the entire column, except the first cell, will be shaded grey. 

92695bbf-6752-4bde-bd19-a44489231bab.png

Type the name of your header (in this case, “Account) in the formula bar and press Enter. This will create a header for the entire column, rather than data for the cell.  

8403da2e-b117-46f5-b9c9-b6c129c3c053.png

Part 2: formatting the column’s data

Now that the column has a header, highlight the entire column using the same method as above.

4a2aa9a0-8ad1-42c4-9512-cdc9555abbfe.png

From the Number section’s drop-down menu on the Home tab, change the format to General (if it's not already set to General). 

5674ce83-1000-43cd-b807-45a942009df2.png

Once changed, open the Text to Columns tool found in the Data Tools section of the Data tab. 

21309480-baf4-43db-a094-463eca700ce7.png

The Convert Text to Columns wizard will appear on your screen. Select Delimited and click Finish to apply the change to the entire column.

036f26cc-0019-4627-a18c-43b2b07a2623.png

Part 3: highlighting blank cells from the column

Once again, highlight all of column E. 

dffec7d9-2b95-495f-9545-09adaaedf90e.png

From the Home tab’s Editing section, click Find & Select, and select Go To Special from the drop-down. 

816a8184-98f3-48c4-8020-00412cfd2c37.png

The Go To Special window will appear on your screen. Select Blanks and click OK. This will highlight all the blank cells in the new column.

d6c0b2b4-bca9-4d47-80b6-6bf1b6e42b22.png

Part 4: concating existing columns into the new one  

Now that all the blank cells are highlighted, type the following formula into the formula bar (make sure that all of column E is highlighted):

=CONCAT(B2, C2, D2)

2af74037-41c7-4e9f-a7ed-112ee6b4cce4.png

Then press Ctrl+Enter to add this formula to all of the column’s cells. This formula will add the data from all columns left of the new column to our new column. So, every account and account number will be consolidated into our new row. 

NOTE: Depending on how complex your account structure is, you may need to add more rows to the formula above.

Once complete, you'll only see entries from columns B, C, and D in our new Accounts column.

68f8303a-1512-4cc2-acc0-de508cb63529.png

Part 5: verifying part 4 

To make sure that you completed the last step successfully, click the Filter drop-down in the Editing section of the Home tab and select Filter.

ca9c69e0-c3f2-4088-8c18-1e93b4130494.png

This will give all of your columns a “filter” button in their header.

22800497-6894-4df5-906f-3124903daa96.png

Click on the Account column's filter button. This will expose a list of available filters, including all the accounts in the column. Use this list to ensure that all of the accounts are present.

22be1e62-e691-4ca0-9e76-b5d3407ef5df.png

Part 6: copying and pasting values into the column 

Once you've ensured that all the accounts are included, highlight all of column E again and copy the entire column.  

38dddfc5-c262-4e1b-b577-d4bd8ca1da2d.png

Once copied, use Paste Values to re-paste the column as “values.” This will remove the formula we used to concact our columns and leave only the value (account) in the cell. (Visit https://trumpexcel.com/excel-paste-special-shortcuts/ for further reference.)

2379c20c-8ac4-4491-8a70-354b7d634f85.png

With column E still highlighted, use the Text to Columns function again and click Finish to mark our “blank” cells to their supposed state (Excel will not recognize them as truly blank until you complete this step).

3a5ea9b5-ff36-4f6a-9903-d56753d03cba.png

Part 7: selecting the column’s blank cells 

Again with the entire Accounts column highlighted, click Find & Select from the Home tab and select Go To Special

accbfa59-54ce-4814-9808-b7c335ef3113.png

Select Blanks and click OK. This will select the remaining blank cells in our Accounts column (column E).

bc575a20-45bf-4152-84b7-33057c853951.png

Part 8: adding a new formula

In the first empty cell, enter the following formula: 

=E2

2fbec138-2936-4b87-866e-7c0b90173e9e.png

Press Ctrl+Enter to input the formula in every selected blank cell, similar to part 4. This add the account number & description in every line item in our Accounts column . 

ef09f03d-50f5-48e6-9f71-8df6b01aeaee.png

Just as we removed the last formula in part 6, highlight the entire column, copy it, and Paste Values. Now the formula (=E2) will be removed and only the value (account number and description) will remain. 

Part 9: removing unnecessary columns  

Now that all of our accounts in one column, delete all of the columns to the left of the new Accounts column (in this case, columns A, B, C, and D).

14431f1b-ebae-45f6-9a33-be9b8557fd2c.pngPart 10: adding an "Account Description" column

Once the unnecessary columns are deleted, insert a new column to the right of your Account column.

b5c2ccd7-d5eb-4617-9f89-f84887ab95a4.png

Part 11: Identifying and setting the delimiter 

Highlight and copy only the dot that separates the account number and the account description in our Account column.

f9156766-8371-4d34-ab09-038bf258d355.png

Once copied, highlight the entire Account column (which was previously column E, but now column A).

e4f8c07b-e334-4a36-913c-094383c44da4.png

Return to the Text to Columns function in the Data tab’s Data Tools section. Make sure Delimited is selected then click Next.

baa961d7-21e7-435b-a936-5789a13308c7.png

Paste the dot you copied into the “Other” box to use it as a delimiter and click Finish.

945024fb-8a71-43c3-ae40-39d4da816205.png

This will move all of the account descriptions to column from our Accounts column to the new column B, separating account numbers and descriptions into their own respective columns. 

b6d1eeaf-bdd4-4d68-9532-7a55b58ff388.png

Add a header to the new column (as performed above) and name it “Account Description”.

9cd00bd5-416f-48d7-9380-286e1f5197ef.png

Use Sort and Filter again on your dataset’s Date column to only show blank rows and delete all of them. This will leave you with a full ungrouped data set that is ready for ingestion. 

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.