Slow to Fast 3

This is the final post in the Slow to Fast series, where we are incrementally improving the efficiency of reconciliations.

In the first post, we looked at conditional formatting. This was a great option when the lists were relatively short, stored on the same worksheet, and only required us to look at a single column.

In the second post, we examined lookup functions. This enabled us to quickly reconcile lists that were on separate sheets and that required us to compare the amount. But, if we perform such a task on a recurring basis, we can consider using Power Query. Before we jump into the details of Power Query, let’s confirm our objective.

Objective

Our check register is stored in a table called check_register:

Our bank data is stored in a table named bank_activity:

We want to compare these lists. Specifically, we want to find out which checks appear on both lists (which have cleared the bank) and which checks do not appear on the bank activity (are outstanding). Let’s see how Power Query can help.

Video

Details

We’ll use these steps:

  • Retrieve tables
  • Cleared checks
  • Outstanding checks

Let’s get to it.

Retrieve tables

The first step is to retrieve both tables with Power Query. In this illustration, both tables are inside Excel already. But, Power Query is able to retrieve data from many different data sources if needed.

We select the check register table, and then click the Data > Get Data > From Other Sources > From Table/Range command. We see a preview of the table in the Power Query editor, like this:

We then click the bottom half of the Close & Load command, and pick Close & Load To. In the resulting dialog, we opt to Only Create Connection:

Next, we do the same thing for the bank activity. Data > Get Data > From Other Sources > From Table/Range. From the resulting Power Query editor, we Close & Load To. Only Create Connection.

We now have access to both the check_register and bank_activity data, as shown in the Queries & Connections pane:

With that complete, it is time to generate our list of cleared checks (those that appear on both lists).

Cleared checks

The checks that appear on both lists have cleared the bank. To create a list of the cleared checks, we’ll compare these two lists. To do so, we select Data > Get Data > Combine Queries > Merge.

In the resulting Merge dialog, we pick check_register from the top drop-down and bank_activity from the bottom drop-down. We also need to click the Check column in both the top and bottom tables. Finally, we need to select Inner (only matching rows) from the Join Kind drop-down:

We hit OK and can see this in the editor:

We delete the selected bank_activity column by pressing the Delete key on our keyboard, or by using the Home > Manage Columns > Remove Columns command.

Once complete, we Close & Load To a Table:

We hit OK, and bam:

Next we need to create a list of checks that appear on the check register but NOT the bank activity.

Outstanding checks

The list of oustanding checks (those on the check register but not the bank activity) is created in a very similar way, with only one little twist.

We begin by using the Data > Get Data > Combine Queries > Merge. In the resulting dialog, we once again select check_register and then bank_activity. We also click the Check column in both lists.

Now, here is the little twist. From the Join Kind drop-down, we select Left Anti (rows only in first).

We click OK and see the following preview:

Once again we delete the bank_activity column, and then Close & Load To a Table. Bam:

If desired, we could quickly compute the total of outstanding checks by using the Table > Total Row checkbox.

Conclusion

Now, the really cool part of using Power Query is that it is easy to refresh in future periods. We can open the workbook and use Data > Refresh All to update the reconciliation.

I hope that the three features discussed during this series will help you get your list comparisons and reconciliations done more quickly 🙂

Sample File

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

9 Comments

  1. Tatiana Petkova on February 23, 2021 at 8:17 am

    Very useful and elegant solution!

  2. Danny Severns on February 23, 2021 at 9:23 am

    This series of webinars are excellent illustrations of their main principle, which is that Excel has really matured by adding and enhancing Power Query & Connections, enabling workbooks to be populated quickly and easily and eliminate a lot of manual data entry. Jeff also illustrates other Excel feature refinements that enable Slow To Fast. In my opinion, the data retrieval features alone have transformed Excel into a competitive report writer because it can now be tied directly to databases and file systems with complex SQL retrieval when needed. Jeff shows how quickly spreadsheets/reports can be created, populated, and function as a 1-and-done template that is automatically updated. Excel has been able to do many of those functions for a long time, but it was very kludgy and mostly manual. For the last few releases Excel works like Crystal Reports and other report writers and enhanced even further with Power BI. Even if you are familiar with these features, the webinars are worth watching to reinforce the ease and speed of doing tasks quickly in Excel. It will give you some good ideas on other places to use these capabilities and how important it is to learn how to go from Slow To Fast and to use Power Query.

  3. John A. Williams on February 23, 2021 at 10:23 am

    Jeff, I would add one change:

    You merged on the check number and deleted the system amount column. There is an assumption that the bank amount cleared equals the accounting system amount. That is not always the case. A Power Query change could be to bring in both amount columns and subtract one from the other. This would return four columns (check number, bank amount, system amount and variance). Sometimes banks miscode the amounts and this quickly reports any variance.

    Thanks for your trainings!

    • Jeff Lenning on February 23, 2021 at 10:26 am

      Yes … computing the diff between bank and book amounts is a great idea, thanks 🙂

  4. LARRY RUBENS on February 23, 2021 at 10:36 am

    While listening to Jeff, I thought of a potential problem that wasn’t covered. What if something cleared the bank that was not on the check register? Suppose someone else’s check was withdrawn from my account in error.

    • Jeff Lenning on February 23, 2021 at 10:41 am

      Hi Larry! We could set up a test for that by matching the other way, from the bank to the book. This test would be the same basic steps in PQ, except you tell PQ to show everything on the bank activity and show matching items on the register. Anything unmatched could then be reviewed.
      Thanks
      Jeff

  5. Steve on April 25, 2021 at 9:22 pm

    The check amounts I entered into the tables are in dollars and cents but the cleared checks and outstanding checks lists are rounded numbers without cents. How can I those numbers to be in dollar and cents?

Leave a Comment