How To Create A Reconciliation Report In Excel

Automate bank reconciliations with Excel Power Query

Want to reconcile data files in a heartbeat? This article shows how to make it happen.

After introducing you to Power Query in my last column, I turn my attention this time to showing how the Excel tool can help you handle bank reconciliations.

If you missed the June 30 column and are new (or relatively new) to Power Query, you would probably benefit from reading it. However, to save you some time, let's quickly review how to find Power Query.

Power Query started in 2010 as an Excel add-in before becoming native to the application with Excel 2016. To access the tool, go to the Get & Transform Data group on the Data tab of the Excel Ribbon, as shown in the screenshot below. The walk-through that follows provides further instructions specific to automating a large part of the bank reconciliation process.

get-transform-data

And now for the main event: Reconciliation

Imagine you had two data tables — it could be your bank statement and a spreadsheet — and you wanted to perform a reconciliation. For this example, let's consider the "Revenues" and "Costs" tables shown in the screenshots below.

revenues-costs

Do you see that both lists use a five-digit code number (in the left column)? I want to see which items do not reconcile. Yes, this could be performed with COUNTIF formulas in Excel, but again, manual manipulation is required.

An alternative is to both merge and append tables:

Merging is when columns from one table are added to another (this requires a field that can be used to identify which rows match).
Appending is when rows from one table are added to another (this requires the tables to share the same field names).

The first thing is to import both tables into Power Query. You do this for each table by picking one of the options (that matches the file format) in the Get & Transform Data group on the Data tab of the Excel Ribbon. Once there, select one of the tables (say "Revenues") and then click on the Merge dropdown (circled in the screenshot below) on the Home tab and click on Merge Queries as New (to generate a new table rather than merge with an existing one).

merge-queries

This gives rise to the Merge dialog box, shown in the screenshot below, where the two tables and fields may be selected:

merge-dialogue-box

Don't worry about the revised names in the screenshot — I have simply ordered the two tables based upon their Code. There are several join kinds (pictured above); the one required is the Left Anti ("left" is the top table and "right" is the bottom one). This creates a table where records in the top table do not have a corresponding item in the second table (ie, cannot be reconciled). Clicking OK creates the following:

query-settings

Notice the final column says Table (in green). This means Power Query cannot display all of the fields in one column. However, note there is an icon to the right of the final field header. Clicking on this and selecting the correct fields generates the following table:

merge-1

We have the first part of the table, and this query should be saved. Repeating this entire process and using the Right Anti join creates the other half of the reconciliation — this time, rows in the second table not in the first.

Appending (using the feature on the Home tab of the Ribbon) and closing and loading these two tables will then produce the following report (once sorted by code):

right-anti

You can Close & Load this data back into Excel now, all cleaned up. Power Query has recorded all of your steps. If the source data were to be updated, all you would need to do is click Refresh All on the Data tab and your transformed report would be updated in a heartbeat.

This might be an involved process, but again, it only ever has to be performed once. Because Power Query records all the steps, refreshing is all that's required for future reconciliations. All that would be required is to click Refresh All on the Data tab and a new report would be completed in a flash. Tedious bank reconciliation tasks will become a thing of the past!

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com . To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com .