Instant Reconciliations with Power Query

When you have two different lists that you need to compare, how do you do it? For example, you want to identify which items on one list (e.g., a check register) appear on the other (e.g. bank activity)? I used to print both lists, grab a ruler and go down row by row ticking items with my pencil. The good news is that we have some updated tools that can help us automate this type of task. I’ll illustrate these tools using a classic bank reconciliation because most readers are familiar with it. However, the techniques presented here will apply to just about any list comparison.

Specifically, we’ll discover that Excel’s Power Query is an incredible tool for reconciliations. If you’ve never explored Power Query, it can do much more than automate reconciliations, so be sure to check it out.

Objective

For the purposes of this walkthrough, I’ll use a simplified bank reconciliation as seen in Figure 1.

Figure 1

Our objective is to have Excel automatically create a list of checks that have cleared the bank as well as the outstanding check total needed in Figure 1.

We will accomplish our objective with the following 5 steps:

1. Export lists to Excel
2. Import data to Power Query
3. Create list of cleared checks
4. Create the outstanding check list
5. Update bank reconciliation

Step 1: Export Lists to Excel

We begin by exporting our check register from our accounting system and pasting it into Excel, as seen in Figure 2.

Figure 2

Next, we download the checking activity from our bank and save it in the same Excel workbook so it looks like what you see in Figure 3.

Figure 3

Now we need to compare these two lists. We need to find out which items appear in both lists (i.e., checks that have cleared the bank) and which items appear on the check register but not on the bank download (i.e., outstanding checks). To do this, we will import both lists into Power Query.

Note: In this article, I assumed we pasted the data into Excel. However, Power Query can also retrieve data outside of Excel, for example, from a csv file.

Step 2: Import Data to Power Query

Let’s start with the check register (Figure 2). We select any cell in the check register table and select Data > From Table/Range. If the data is already stored in a Table, it will be shown in the Power Query window. Otherwise, you’ll be prompted to create the Table first. In the Power Query window, we can clean up the data if needed. Our check data looks clean, so we select the bottom half of the Close & Load button and select Close & Load To. In the resulting Import Data dialog, we click Only Create Connection and click OK.

Next, let’s get the bank activity (Figure 3) into Power Query. We essentially perform the same steps we used to import the check register.

With both data tables successfully loaded into Power Query, we can move to the first comparison and generate the list of cleared checks.

Step 3: Create List of Cleared Checks

First, let’s have Power Query generate our list of cleared checks. This represents checks that appear on both lists.

In Excel, Data > Get Data > Combine Queries > Merge.

Figure 4

In the resulting Merge dialog (Figure 4), pick the check register table from the top drop down and the bank data table from the bottom drop down. To tell Power Query which columns are expected to match, select the two check number columns by clicking the column headers. In the Join Kind drop down, select Inner (only matching rows). This means that we only want to see those checks that are found in both lists (i.e., checks that have cleared the bank).

When you click OK, you’ll see a list of the checks found in both lists. Send the results to Excel by selecting Close & Load To… and then select Table. The results flow into Excel (Figure 5).

Figure 5

Now we’ll do something similar and create the outstanding check list.

Bonus: if you’d like to ensure that the amounts from the check register match the bank download, check out my YouTube video:

Step 4: Outstanding Check List

The outstanding check list represents checks that appear on the check register but not the bank activity. We use the same steps that we did to create the list of cleared checks, except we select the Left Anti (rows only in first) join kind. We click Ok, and Power Query has created our list of outstanding checks. We Close & Load To, Table and OK. Once the outstanding check table has loaded into our Excel worksheet, we can select any cell in the Table and click the Table > Total Row checkbox to get the total of outstanding checks (Figure 6).

Figure 6

Step 5: Update bank reconciliation

The final step is to pull the total of the outstanding checks into our reconciliation by typing = and then clicking the total cell in the outstanding checks table. We get Figure 7.

Figure 7

We confirm that the Diff = 0, which makes our heart pitter patter with joy. Life is good.

Conclusion

The good news is that now that the queries are set up, we don’t need to set them up again next month. We can simply paste the new check register, paste the new bank activity, and click Data > Refresh All. Power Query will create a new list of cleared checks and a new outstanding check list. While this may not eliminate every manual step, it can certainly provide a great start to an otherwise tedious monthly reconciliation process.

Also, even if you don’t do bank recs, these same techniques apply to other types of lists you may want to compare. For example, comparing sales from an ecommerce export to sales in a QuickBooks export. Or, invoice detail to invoice summary. Or, clients in our tax system to clients in our billing system. And,
well, you get the idea: this works with just about any two lists you want to compare.

And remember, Excel rules.