Excel

Easily Create and Update Excel Reports from QuickBooks

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.

Learn More

Comments (0)

There are no comments yet, Be the first to comment.
Log In
Your message is required.
Markdown cheatsheet.

Share This Article

Looking For More?

View all Excel Articles

View More Articles

What's Trending?

Trending topics & tools for the CPA community

Why CPAs Should Partner With an HCM Company

It’s about your role as a trusted advisor. Business clients now have a greater expectation that their CPA will refer them to services to help their business grow. Learn why 72% of small business owners expect CPAs to offer HCM advice, and how 1,000s of CPAs are benefiting from referring HCM.

Download eBook

Investing in Opportunity Zones

This white paper covers advanced tax planning real estate strategies: Current real estate and political environment with implications for buyers and sellers. Real estate tax planning strategies......

Read More...

2023 Contribution Limits and Why You Should Make a Contribution

A common question you hear in the retirement world is “how can I retire comfortably”, and we are constantly reminded how important it is to......

Read More...