Small and medium-sized businesses across the globe are using the Journal Entry feature of QuickBooks accounting software to maintain a General Ledger that keeps a record of all the transactions. The Journal Entry feature of QuickBooks can also be used to maintain a 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 to the account. However, to reduce the chances 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 the 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 analyzing the data. Let’s go ahead and see how you can make the General Ledger report generated by QuickBooks ready for analyzing the data.
Method of Reconstitute General Ledger Data in QuickBooks for analysis
When you look at a General Ledger report imported from QuickBooks to an Excel worksheet, you will see that it includes many issues that can obstruct the process of analyzing transaction data. First of all you would notice that the names of accounts 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 will notice that there is a separate column for sub-accounts and primary accounts. This will make the process of analyzing the data 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 to 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 dragging the small square button down to 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 the 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 finds 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 number 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 enters the 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 analyze 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 General Ledger data in QuickBooks, you can reach out to our QuickBooks Support Team at Bigxperts.com. Our QuickBooks experts can help you analyze your financial transactions, export data from QuickBooks accounting software to Microsoft Excel worksheet, use the General Ledger Data feature of QuickBooks, etc. without any hassle. For more info, Call 📞.
Speak to A Specialist about QuickBooks Software
Headache🥺. Huh, Save time & money with QuickBooks Support 24×7 @ +1/, we provide assistance to those who face problems while using QuickBooks (Support all U.S. & Canada Editions)
--- Support - We provide solutions for the following QuickBooks (Pro, Premier, Accountant, Enterprise, Payroll, Cloud) ---
- Error Support
- Data Import
- Data Conversion
- Payment Solutions
- Payroll Support
- Data Export
- Software Integration
- QuickBooks Printer Support
- Complete QuickBooks Solutions
- Tax-Related Issues
- Data Damage Recovery
- Installation, Activation Help
- QuickBooks Windows Support