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
Share This Article
What's Trending?
Trending topics & tools for the CPA community
Pay no licensing fee for 12 months!
As the year comes to a close, it's time to evaluate your goals for next year. Set your firm up for its most profitable yet with CPACharge in your corner.
The Future of Senior Health, Wealth & AgeTech
Medicarians is the home of the product creators, distributors, provider networks, plan administrators, venture investors, and innovators helping the people live longer, better lives physically, financially, and mentally. Join us April 8 - 10, 2024, at Fontainebleau, Las Vegas.
Fit for the Future: A Guide to Attracting and Retaining Accounting Talent
Discover how your firm can combat accountant shortages and inspire future business growth.
Resources
Valuable information provided by our sponsors.
Celebrate with CPE Savings
Busy season is over and summer is right around the corner! Now's the time to...
The Guide to Updates for Tax Season 2024
With the new 2024 tax season right around the corner, are you confident that you...
Free 1 CPE: How to Grow Your Practice Through Business Advisory Services!
Free CPE Webinar for Accountants, CPAs, and Bookkeepers. How to Start a CFO Service. Grow your practice...
Retire Your Way: The Solo 401 Strategy for Entrepreneurs
Join Quest President Nathan Long as he shares what makes the Solo 401k so desirable,...
CPAdirectory members have access to discounted auto and home insurance
At CPAdirectory, we think it's a good thing to provide our members with access to...
5 Excel Tips - Complimentary 1 CPE
Learn Excel and earn CPE credit while doing so! In this complimentary 1-hour webinar, you'll discover...