Easily Create and Update Excel Reports from QuickBooks

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. For example, a P&L:

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.

Direct Connect with Genius Sheets

A solution that helps us easily create and update Excel reports from QuickBooks is to use an Excel add-in like Genius Sheets. Genius Sheets will let you link individual cells in your Excel reports directly to general ledger accounts in QuickBooks. It’s easy to set up and once the linkage is created you won’t have to worry about it breaking.

Updating the Excel report each period is a simple one-click refresh. The add-in retrieves the updated values directly from QuickBooks and instantly updates the Excel report. With Genius Sheets you can even drill down on your data to view and understand the underlying transactions right from the comfort of your workbook.

Note: currently, Genius Sheets works with QuickBooks Online. If you want to be notified when QuickBooks Desktop or other software integrations are available, join the waitlist here.

How to Install Genius Sheets

To try out Genius Sheets you can sign up for a free account on the Genius Sheets website and connect your QuickBooks Online account right on the dashboard.

Once you have connected your account to a QuickBooks company you can add the Excel Add-in from either the website or directly through the Microsoft Office Store from within Excel (Insert > Add-ins > Get Add-In > Genius Sheets).

Once the Add-In is installed, you can use various Genius Sheets Formulas to retrieve individual values directly from QuickBooks or retrieve entire reports.

Genius Sheets Formulas

One of the challenges to onboarding new software for financial reporting is that you have already spent so much time and effort building and formatting your existing reports in Excel. With Genius Sheets you can keep everything you already have, but now streamline your ability to get the data. This helps us easily create and update Excel reports from QuickBooks data.

The way Genius Sheets formulas work is that you can access any account from your Balance Sheet, Cash Flow Statement, or Profit and Loss statement through individual cells. This lets you pull either entire reports or individual line items into any of your existing reports and dashboards. The functions are:

GS.IS(category, startdate, enddate) – For the Income Statement
GS.BS(category, startdate, enddate) - For the Balance Sheet
GS.CF(category, startdate, enddate) – For the Cash Flow Statement

Within the add-in, the menu option List Categories will show you the exact names of the categories, including account numbers, from your QuickBooks Online account that you can reference for the formulas. You can also generate a report with the formulas already built-in by selecting that option from the Pull Reports menu.

To start mapping the names of your categories, you can use the Genius Sheets formulas with cell references in order pull the category name and date you are looking for.

Pro tip: if you only enter a start date, it will pull the full month data.

In your model, the names of the categories must exactly match the name in QuickBooks.

If they need to be different, you can always use an intermediate mapping table as demonstrated in these posts.

You can pull the entire report with Genius Sheets for the same period to ensure you picked up any new categories or category name changes. Plus, you can pull the report with the formulas already built-in.

Category Groupings

One of the benefits of using Genius Sheets formulas is that you can consolidate multiple line items by combining multiple functions.

In this case, you may want to reference the categories through text instead of cell references such as:

=GS.IS("Travel","2021-12-01",) + GS.IS("Travel and Expenses","2021-12-01",)

This formula will automatically add the two categories together, allowing you to easily combine line items in a single cell. This is great for categories you want to keep separate in your underlying accounting data, but want to combine for financial modeling or dashboarding purposes.

Pro tip: if you have a lot of groupings, you can always use an intermediate mapping table as discussed in these articles.

Once your data has been replaced with the Genius Sheets formulas, you can hit the Refresh Data button on the add-in home screen anytime. This will sync all the data in the workbook to the latest data in QuickBooks.

Note: the add-in only retrieves data from QuickBooks … it does not write data to QuickBooks. So any changes you make manually in Excel do not get pushed back to QuickBooks.

Drill Down Functionality

Now that your report is built, you may want an easy way to start analyzing the data. By right-clicking on cells that have a Genius Sheets formula, you can generate a transaction report for specific line items.

This functionality lets you quickly see the underlying transactions and compare what is new or unexpected in your report, without having to go back and forth to QuickBooks Online.

Genius Sheets provides a user-friendly way to update reports, view details, and save a lot of time. It helps us easily create and update Excel reports from QuickBooks data.

Plus, you don’t have to worry that your workbook data will be out-of-date when you reconcile accounts or make changes in QuickBooks!

Special Excel University Offer

If you are interested in trying Genius Sheets, we are offering a 10% Excel University discount! Just use code EXCELU at checkout. All subscriptions come with a 7-day free trial as well as free onboarding assistance.

• For more information on pricing: Plans and Pricing

Any questions? We are here to help! Just email us directly: [email protected]

Disclosures and Notes

• This is a sponsored post for Genius Sheets. Genius Sheets is not affiliated with nor endorses any other products or services mentioned on this site.
• 100% of sponsor proceeds fund the Excel University scholarship and financial aid programs … thank you!
• If you’d like information on becoming a sponsor, please check out our sponsorship opportunities page.