Excel Rules: Instant Reconciliations with Power Query.

• Author: Lenning, Jeff

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.

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.

FIGURE 1 Balance per bank 100,000 Less: outstanding checks Adjusted bank balance 100,000 Balance per books 67,352 Diff 32,648 We will accomplish our objective with the following 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.

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.

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.

FIGURE 2 Check Amount 1001 1686 1002 7088 1003 7340 1004 6374 1005 2544 1006 9218 1007 6791 FIGURE 3 Memo Amount 1001 -1686 1008 9656 1005 -2544 1003 -7340 1011 -3346 1006 -9218 1004 -6374 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...