For those of you that know me, I am an efficiency seeker and delegator in the best (worst) possible way. I want processes that are repeatable and easy to understand. PowerQuery can elevate QuickBooks Analysis in Excel by streamlining QuickBooks reports that are not Excel Table friendly.

This kind of data manipulation has become crucial in Fractional CFO work because some of the teams I work with are thinly resourced and part of my job is to raise the level of performance for everyone. Having locked-down repeatable processes across all clients give me so much more leverage in my day. One fundamental use case between QuickBooks and Excel involves combining QuickBooks’ monthly Profit and Loss (P&L) report with Excel so that I can mash actuals with forecasts. This kind of report gives management a time series view of how the numbers are evolving on a monthly basis into the future.

As I have written in the past, I do use Bunker for financial reporting across my clients, but when it comes to more complex variance analysis that involve merging forecasts with actuals, using Excel is the go-to solution for now. You can read about Bunker in this post from a few weeks ago. As a side note, QuickBooks does have an Excel add-in that allows for automated downloading of reports into Excel. This is one way to get the data into Excel, but I prefer to download the report in the method described here. Specifically, I’ll emphasize the handling of data so that you have a canned, repeatable solution for subsequent periods using PowerQuery—a key tool for maintaining consistency and accuracy.

The Starting Point: QuickBooks P&L Report

In QuickBooks, you will want to create a monthly P&L report that goes as far back in time a you like but always is up-to-date when you download it. After generating your monthly P&L report, export it to an Excel file. This report encapsulates your company’s financial performance—revenues, expenses, and net income—all neatly organized.

Save this file to a folder that becomes a main resusable folder for you. This folder will be critical to repeating this exercise every month because you will overwrite this file after each subsequent closing with the latest downloaded file.

Another side note, I am not going to get into the chart of accounts but this assumes you are happy with the current set up. Honestly, I make changes to the chart of accounts once I understand the client’s business model. Often, clients use the standard QuickBooks set up, but if you are planning to produce reports that make sense in a standard financial presentation you might need to reorganize the chart of accounts.

PowerQuery

Now, let’s talk about PowerQuery. It’s like having a wizard for manipulating data at your disposal. PowerQuery is going to let us turn the QuickBooks report into a table that is more readily usable in Excel.

Here’s how we’ll leverage it:

• Creating a New Workbook: Open a fresh Excel workbook. Think of it as our canvas for data transformation.
• Importing Data: Navigate to the Data tab and click Get Data > From File > From Workbook. Select the QuickBooks-exported file.
• Selecting the Right Sheet: In the PowerQuery editor, choose the sheet containing your P&L data (usually named ‘Profit and Loss’). Click Transform Data to get going.

Data Cleanup and Transformation

To clean up the data do the following in the standard QuickBooks Online monthly P&L report:

• Removing Unnecessary Rows: In the Home menu, use PowerQuery’s remove rows to trim any header or footer rows that clutter the data. Typically there are a few at the top and at the bottom.
• Setting Headers: In the Transform menu, use the set first row as column headers. It ensures clarity and consistency and puts all of the monthly dates at the top of the sheet so you have a usable table.
• Column Cleanup: You can delete irrelevant columns (like those ‘Total’ columns). Just right click on a column header to delete it. This is optional. I just leave all the columns as they are.

Loading into an Excel Table

Once your data is ready, load it into Excel. This step ensures dynamic updates when you refresh your data. This is the file you originally started with that you saved in the location for repeated use. This file now has the PowerQuery code embedded in it so that in the future you can simply download the QuickBooks report and refresh your spreadsheet as described below.

Handling Future Data

Now, we move to the handling of data for subsequent periods.

• Export the Updated Report: When the next month ends, export the updated P&L report from QuickBooks. Ensure it covers the new month.
• Replace the Old File: Replace the existing Excel file with the new one. Keep the same file name and folder path. This continuity is crucial. Excel has the code to manipulate this file in your spreadsheet and it needs to look in the same exact place for a similarly formatted file in order to repeat the steps it was trained on.
• Refresh the Data: Open the updated Excel file, enable editing, and hit Data > Refresh All. Voilà! Your table now reflects the latest financial figures.

A few last tips

Now you have a clean table of data, the chart of accounts is referenceable using lookup functions. My preference is for using Index-Match instead of lookups, but either will work. QuickBooks puts spaces in front of the row labels, which I don’t like, but don’t go through the process of trimming them, which you can do in PowerQuery as described above. I just keep the spaces in all places where I reference that row.

A bigger deal for me is that QuickBooks column headers are going to be text and if you are building a report from the table using lookup functions in Excel, you won’t be able to match your serial dates with the text dates because they are two different data types. I leave the QuickBooks table alone and I create a new row of dates in Excel from my serial dates using this function =TEXT(C7,”mmm yyyy”). Change the number m’s and y’s to get the exact format that QuickBooks exports.

I use the new Text row to lookup to the data that PowerQuery loaded into my spreadsheet, This example should work for you, but QuickBooks can have a mind of its own sometimes. This formatting issue can also be resolved in PowerQuery but that’s more advanced and I won’t cover it here, so the Excel fix is easy and becomes part of the template so I know it works with all refreshes.

By combining QuickBooks’ data with Excel’s dynamic capabilities, you’re not just crunching numbers—you’re gaining insights. As a fractional CFO, this streamlined process frees up your time for strategic decision-making. Remember: Financial data isn’t static; it’s a living, breathing asset. With PowerQuery as your ally, you can save time and effort to build efficiency into your reporting habits.