Cranmere Accounting & Tax Services
With the 2012 release QuickBooks® Pro and above improves the integration with Excel so that a spreadsheet can be refreshed with new data without losing the spreadsheet formatting. In previous versions data could be exported to Excel but the spreadsheet would need to be re-formatted every time.
This enhancement will save time and will be useful where the same QuickBooks® reports are being regularly exported to Excel. One example would be the preparation of monthly management accounts or sales reports.
This feature can be accessed from within QuickBooks® or to update a previously exported report from within Excel. In Excel a QuickBooks® tab is added.
Excel QuickBooks Tab
When you have the spreadsheet open that you want to update just click the update report button.
To access the feature in QuickBooks® generate a report and then you will see the Excel button. Click on that button and you have two choices. Create a new Excel worksheet or update an existing worksheet.
Clicking on the create a new Excel report brings up a dialog giving you various choices on how to create the new worksheet and also gives you another chance to update an existing worksheet. The advanced button gives you extra options to control what is exported and how it is exported.
When you click the export button the report will be generated in Excel. You can then change the formatting, fonts, title descriptions and even add formulas. Once the spreadsheet is saved you can update it with new data from QuickBooks® without losing the formatting.
One useful feature is that QuickBooks® adds an Export Tips sheet to the spreadsheet to remind you of what can and cannot be done with the exporting. Also when you update the data it adds a copy of the sheet with the new data so that you don’t lose the original sheet if the update does not work as expected. In the event that QuickBooks® cannot work out how to update the data it will also give you an Alert and Log sheet to highlight how it resolved the issue.
A few issues to be aware of with this feature are:-
- Only the formatting in row and column headers will be retained. To format data modify the report in QuickBooks® before exporting it.
- Inserted empty rows will be lost
- Moved data cells will be lost
- Deleted or sorted columns will be lost
- To keep inserted text it must be entered as a formula e.g. =”text”
As you can see there are some gotchas to be aware of when using this feature. Our experience of using it has shown that you need to carefully follow the Tips worksheet and don’t be too ambitious about what you can change on the exported spreadsheet.
With care this will save time when you need to regularly update reports in Excel.
If you would like to set up Excel integration with your QuickBooks® system then give us a call on (480) 363-4808 or email us.
Posted By Mark Smith
Mark Smith, EA is an Enrolled Agent and accountant with over 30 years tax and accounting experience. He is the owner of Cranmere Accounting and Tax Services LLC. He can be contacted on (480) 363-4808 or by email at info@cranmereaccountingandtax.com if you need assistance with any of the above.
