Summary

This article will assist you in formatting any Quickbooks file with the following account structure: 

The defining characteristic of this file type is that Quickbooks accounts and sub-accounts are laid out as layers, or in 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 therefore only need to format the GL file to leverage the most granular account number and description, rather than “parent” accounts and descriptions preceding it. 

Table of contents

• Part 1: Creating a new "Accounts" column and header
• Part 2: Formatting the column's data
• Part 3: Highlighting blank cells from the column
• Part 4: Concatenating existing columns into the new one
• Part 5: Verifying Part 4
• Part 6: Copying and pasting values into the column
• Part 7: Selecting the column's blank cells
• Part 8: Adding a new formula
• Part 9: Removing unnecessary columns
• Part 10: Adding an "Account Description" column
• Part 11: Identifying and setting delimiter
  
  

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

1. 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.”
   
   
   
2. Select the entire row by clicking on the columns heading.
   
   
   
3. Once highlighted, the column header will be green and the entire column, except the first cell, will be shaded grey.
   
   
   
4. 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.
   
   
   

Part 2: Formatting the column’s data

1. Now that the column has a header, highlight the entire column using the same method as above.
   
   
   
2. 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). 
   
   
   
3. Once changed, open the Text to Columns tool found in the Data Tools section of the Data tab.
   
   
   
4. The Convert Text to Columns wizard will appear on your screen. Select Delimited and click Finish to apply the change to the entire column.
   
   
   

Part 3: highlighting blank cells from the column

1. Once again, highlight all of column E.
   
   
   
2. From the Home tab’s Editing section, click Find & Select, and select Go To Special from the drop-down.
   
   
   
3. 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.
   
   
   

Part 4: Concatenating existing columns into the new one

1. 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)
   
   
   
2. 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 columns to the formula above.
3. Once complete, you'll only see entries from columns B, C, and D in our new Accounts column.
   
   
   

Part 5: Verifying part 4

1. 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.
   
   
   
2. This will give all of your columns a “filter” button in their header.
   
   
   
3. 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.
   
   
   

Part 6: Copying and pasting values into the column 

1. Once you've ensured that all the accounts are included, highlight all of column E again and copy the entire column.  
   
   
   
2. 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 Excel Paste Special Shortcuts for further reference.)
   
   
   
3. 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).
   
   
   

Part 7: Selecting the column’s blank cells

1. Again with the entire Accounts column highlighted, click Find & Select from the Home tab and select Go To Special. 
   
   
   
2. Select Blanks and click OK. This will select the remaining blank cells in our Accounts column (column E).
   
   
   

Part 8: Adding a new formula

1. In the first empty cell, enter the following formula: =E2
   
   
   
2. 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.
   
   
   
3. 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

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

   
   
   

Part 10: Adding an "Account Description" column

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

Part 11: Identifying and setting the delimiter 

1. Highlight and copy only the dot that separates the account number and the account description in our Account column.
   
   
   
2. Once copied, highlight the entire Account column (which was previously column E, but now column A).
   
   
   
3. Return to the Text to Columns function in the Data tab’s Data Tools section. Make sure Delimited is selected then click Next.
   
   
   
4. Paste the dot you copied into the “Other” box to use it as a delimiter and click Finish.
   
   
   
5. 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. 
   
   
   
6. Add a header to the new column (as performed above) and name it “Account Description”.
   
   
   
7. 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.