Easily Create and Update Excel Reports from QuickBooks

We will show you a new method you can use to save hours every month.

Trevor Lee | March 22, 2022 | Comment |INDEX, MATCH, VLOOKUP, XLOOKUP

In this article, we’ll show how to easily create and update Excel reports from QuickBooks. But, let’s back up a second. When QuickBooks has the report you want, awesome! But, when it doesn’t, we often turn to Excel. This can happen when QuickBooks doesn’t have all of the data you need for the report, for example, budget, forecast, or projection data. Or, when the built-in QuickBooks report format isn’t exactly what you need.

As with just about anything in Excel, there are several ways to get the data out of QuickBooks and into Excel. When the report is a one-time report, the approach you use isn’t as important because you are only building it once. HOWEVER … when you prepare and update the report often, the approach does matter because it can have a huge impact on overall efficiency.

We’ll first quickly go through a few traditional ways. Then we will show you a new method you can use to save hours every month.

Traditional Approaches

Typically, the first step is to login to QuickBooks. Then, you navigate to the report that has the data you need, even if it isn’t in the desired format.

Then, we Export to Excel and the report is stored in a new Excel workbook. Now we need to get those numbers into the format we need or combine them with other data such as budget values. There are several traditional ways to do this part.

Copy/Paste Individual Values

The first and most common option is to copy and paste specific values into the different locations in your desired report. This can be very tedious.

Pro tip: if you use this method, be sure to use Paste Values so that you avoid pasting cell formats.

Formulas with Direct Cell References

Another option, which is a bit more automated, is to create a sheet in your workbook where you paste all the new data each month. Then, you retrieve those values into the various report cells by using formulas with direct cell references. For example, we can retrieve a value from the data sheet using a formula as shown below:

='ProfitAndLoss-02-22-2022'!J11

The problem with relying on direct cell references is that they can break over time, for example, if the location of the data changes, the sort order changes, the format of your report changes, or if any categories are added or removed. When the formulas break, it is time consuming to rebuild them or start from scratch. Additionally, if you only paste the current period data, any prior period accounting adjustments in QuickBooks won’t be reflected in the workbook.

Formulas with Lookup Functions

Another method, which is a bit more automated, is to use lookup functions instead of direct cell references to retrieve values from the data sheet. For example, we can write formulas that use lookup functions such as VLOOKUP, INDEX/MATCH, or XLOOKUP.

As before, you manually paste your new data from QuickBooks into your reference worksheet. But this time, you write formulas into your report that use these lookup functions to target the names of your accounting categories. Once these are set up, they would save time when compared to the first 2 approaches. However, they still require you to spend time manually importing and checking the categories. Plus, depending on the data you export, they may not reflect historical data changes that may have occurred in QuickBooks.