general-ledger-data-in-quickbooks

—

by

numberbook
in

Though QuickBooks has many options with which it can aid your business, still it is difficult to export the data from QuickBooks as a spreadsheet or Excel file. Many a times a third party software is required just to export the data from QuickBooks. Even after exporting there can be lack of many important fields that are required for better analysis of the data.

A General Ledger is necessary for creating financial statements. To reconstitute the General Ledger data follow the steps given below:-

  1. Export and Open the data in Spreadsheet or Excel Sheet format
  2. Add a new column under the aegis of Accounts.
  3. Then below ‘Account’ add the formula : =IF(Cell no of the parent account company =””, Cell no. in which the formula is written, Cell no of the parent account company). This formula will tell you the account name of the Parent company and if the cell is left blank then it will return the name from the previous row.
  4. Now copy the formula till the end of the column of your report. This can be done by copying or pasting. To save time just select the cell in which you entered the formula and drag the little square button to the end of column and double click it. This will automatically copy the formulae in all the cells of that column.
  5. Now the name of Parent account of every row will appear under ‘Account’. Now we need to work on Subaccounts.
  6. In the cell next to ‘Account’, type Subaccount and below it enter the formulae: IF(RIGHT(CELL WHICH YOU WANT TO CHECK,5)=”OTHER”,””,CELL WHICH YOU WANT TO CHECK). This will check that last five letters of that cell are ‘OTHER’ and if that is the case then it will return the Value of that cell. This will ignore the parent account with the title of Other as the tail.
  7. Now replace that formula with: IF(CELL WHICH YOU WANT TO CHECK=””,CELL WITH FORMULAE,IF(RIGHT(CELL WHICH YOU WANT TO CHECK,5)=”OTHER”,””,CELL WHICH YOU WANT TO CHECK)). This will abet you in filling the blank subaccounts with the name provided in the preceding cell.
  8. Now replace the previous formula with: IF(CELL WHICH YOU WANT TO CHECK IS BLANK OR NOT<>””,””,IF(CELL WHICH YOU WANT TO CHECK=””,CELL WITH FORMULAE,IF(RIGHT(CELL WHICH YOU WANT TO CHECK,5)=”OTHER”,””,CELL WHICH YOU WANT TO CHECK))). Now copy the formulas till the end of column. The subaccount names will appear in that column.
  9. Now create another heading besides Subaccount. Name it Joint. Below Joint enter: NAME OF CORRESPONDING CELL UNDER ACCOUNT&IF(CORRESPONDING CELL IN SUBACCOUNT=””,””,”:”&CORRESPONDING CELL IN SUBACCOUNT).
  10. After this copy the ‘Joint’ column and then paste as value. After this combine the Debit and Credit card transactions as one and your General Ledger Report is reconstituted in Excel or as Spreadsheet file.

Read More: What does rebuild data mean in QuickBooks & how automatically fixes minor company file data problems.

The above method may be arduous but it will do the trick.

Good Pages to Read

Fast Fix your QuickBooks

numberbook

Editor NumberBook

    Enquiry Form








    Phone

    +1800-942-0215
    Toll Free: 800-942-0215