Small and medium sized businesses across the globe are using the Journal Entry feature of QuickBooks accounting software to maintain General Ledger that keeps a record of all the transactions. The Journal Entry feature of QuickBooks can also be used to maintain record of other entries such as managing inventory which an accountant cannot register on normal transaction registers.
Unlike the general account managing software, the Journal Entry feature of QuickBooks also enables you to make some special adjustments in the account. However, to reduce the changes of any manual error, it is recommended to take the opinion and help of your business accountant.
If you wish to export the transaction data stored in QuickBooks accounting software to an Excel worksheet, you need to take the help of General Ledger report feature. Though the General Ledger report exported on the Excel worksheet is good for taking printouts of the transactions, they are not good for analysing the data. Let’s go ahead and see how you can make the General Ledger report generated by QuickBooks ready for analysing the data.
Method of reconstituting QuickBooks General Ledger Data for analysis
When you look at a General Ledger report imported from QuickBooks to Excel worksheet, you will see that it includes many issues that can obstruct the process of analysing transaction data. First of all you would notice that the names of account are displayed in a single row only.
This can pose a problem if you are going to create a Pivot Table in the Excel worksheet for data analysis. Also, you would notice that the there is a separate column for sub-accounts and primary account. This will make the process of analysing the data further more complicated. All the activities that are related to the parent account are entered under the column titled ‘Other’.
This mis-tagging of data further complicated the data analysis process. A General Ledger is important for the process of creation of business reports.
Here is how you can reconstitute the General Ledger data.
Total Time: 6 minutes
Open the exported data in Excel worksheet.
Now insert a new column under Accounts.
Once the column is added, under the ‘Account’ insert the formula =IF(Cell number of the parent company=””, Cell number where the formula is written, Cell number of the parent account company). By this formula, you would come to know about the parent company’s account name. If you leave this cell blank then it will take the account name from the previous row.
Now you need to copy the formula till the bottom of the column of your report. You can do so by copy pasting the formula or by selecting the cell where you have entered the formula and dragging the small square button down till the desired row and double clicking. This will copy the formula in all the cells of the column.
By doing so, you would notice that the name of Parent account of every row will appear under ‘Account’. Once this is confirmed, you need to add Subaccounts. To do so,
Type Subaccount in the cell which is next to ‘Account’. Now below the cell enter the formulae: IF(Right(number of cell that you want to check, 5)=”Other”,””,Number of cell that you want to check). This formula will check the last five letters of the cell which are ‘Other’ and if find it then the Value of that cell will be returned. It will ignore the parent account with the title of Other.
Now you have to replace the formula with : IF(cell number that you want to check=””, cell number that contains formulae,IF(Right(Cell mumber that you want to check,5)=”Other”,””,Cell number that you want to check)). This will help assist you in filling the blank cells under Subaccounts with the name entered in the preceding cell.
Now replace the earlier formula with : IF(Cell number that you want to check is blank or not<>””,””,IF(Cell number that you want to check=””,Cell number that contains the formulae,IF(Right(Cell number that you want to check,5)=”Other”,””,Cell number that you want to check))). Now you have to copy the formula till the bottom of the column using any one of the methods explained above. With this you would see the names of subaccount appearing in the column.
This step involves creating one more heading other than Subaccount, and naming it as Joint. Below it enter: Name of the corresponding cell under account&IF(Corresponding cell in subaccount=””,””,”:”&Corresponding cell under subaccount).
In this final step, you have to copy the column named ‘Joint’ and then paste it as value. Once done, combine the debit and credit money transactions into one. Now you will see that the General Ledger report has been reconstituted in Microsoft Excel as a worksheet file. Once this is done, you can easily delete any unnecessary column from the General Ledger report, and then analyse the data by creating a pivot table or any other method as per your requirement.
Need Professional Help?
If you have any query or doubt related to reconstituting of General Ledger data in QuickBooks, you can reach out to our QuickBooks Support Team at accountingerrors.co. Our QuickBooks experts can help you in analysing your financial transactions, exporting data from QuickBooks accounting software to Microsoft Excel worksheet, using the General Ledger Data feature of QuickBooks, etc. without any hassle. For more info, Call 📞.