Last week we demonstrated how to create Excel charts and worksheets from QuickBooks data. This week we examine how to automatically update those Excel sheets with new data, automatically.
What About Updating the Numbers?
The key to making something like this work, is to make it automatic after the initial construction. If you had to create this spreadsheet each time you wanted to examine the numbers, it would never happen.
The data can be updated from either QuickBooks or Excel. When exporting a report from QuickBooks to Excel, QuickBooks gives the user two options. Choosing the ‘Update Existing Worksheet’ will allow the user to fill in the information informing QuickBooks where the Excel workbook is located and which worksheet to update.
An alternative is to remain in the Excel program, not re-running the reports in QuickBooks, and simply update from the spreadsheet.
With newer versions of QuickBooks, a tab will be added to the Excel program activating a QuickBooks ribbon when selected. There is only one icon on the ribbon, but it’s the one we need.
Note the sales numbers in the dashboard graphic above, then check the one below. I updated the Profit and Loss report to include one month’s data only.
It’s important to note that nothing was changed on this worksheet by hand. It all updated as QuickBooks updated the accompanying worksheet that is the Profit and Loss report.
The green chart in the upper right did not change. It is tied to another worksheet looking at YTD sales by month. I did not change the date parameters and update that worksheet. To better explain, all the numbers on this dashboard come from two other worksheets in this Excel workbook. Both of those other worksheets are simply reports updated from QuickBooks as one desires to view different time periods.
Hector Garcia, CPA
Certified Advanced QuickBooks ProAdvisor
12401 Orange Drive #136
Davie, FL 33330
954-414-1524
hector@garciacpa.com
