Slow to Fast 1

This is the first post in the Slow to Fast series, where we will incrementally improve the task of comparing two lists (commonly called a reconciliation). The illustration I’ll use is a bank reconciliation. We want to compare the list of checks in our accounting system with the list of checks from our bank download.

The manual way is often performed by looking for the first item on the check register. Once we make a mental note of the check number, we visually scan the bank list to see if it is there. If it is, we will often note this somehow, perhaps by typing a value into a cell or by using cell formatting. This would be the slow way.

In this series, we’ll look at increasingly more efficient ways to accomplish this task. We’ll explore conditional formatting, lookup functions, and Power Query. In this post, we begin with conditional formatting. Let’s get started.

Objective

Before we get too far, let’s take a look at our task. The two lists I’ll use for the illustration is a check register and banking activity. Depending on what you are working on, you can imagine other types of lists that need to be compared. For example, the item list from your e-commerce system and your inventory system. Or, your account list from your accounting system and your budget system.

For this illustration, List 1 represents the check register (the list of the checks written):

And here is List 2, the bank activity (a list of checks that have been processed by the bank):

Now, our goal is to compare these two lists. Specifically, we want to know which check numbers appear on both lists (which checks have cleared the bank) and which checks are in the check register but not the bank activity (the outstanding checks).

If we were doing this manually, we would look for the first check number on the check register (1001) in the bank activity. If we find it there, we could type a value into an adjacent cell or use cell formatting. Once we’ve finished, we may end up with something like this:

Rather than doing this manually, we’d like Excel to do this for us. So, our journey begins with a feature called conditional formatting.

Video

Detail

Conditional formatting is a feature in Excel that formats a cell automatically based on your formatting rule (or rules).

We can use conditional formatting to help with our list comparison when the lists are relatively small, on the same worksheet, and we only need to look at one column.

Note: future posts in the series will address these limitations.

To begin, we simply select the two lists of check numbers by holding down the CTRL key, like this:

Once selected, we head to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.

The Duplicate Values dialog appears, where we can pick Duplicate or Unique and the related cell format. Let’s select Duplicate and Yellow Fill, like this:

We hit OK and bam … our check register looks like this:

WOW!!!! That was WAY faster than doing it manually!!

Another pretty cool thing is that we can just as easily identify which checks have not cleared the bank (which are on the check register but not the bank activity list) by selecting Unique from dialog instead of Duplicate, like this:

We hit OK and bam … outstanding checks are identified:

Note: you can actually apply multiple rules to the same cells, so, if you are trying this and all cells are formatted you’ll want to clear the existing rule first by selecting Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Oh, and one more pretty cool thing is that we can actually apply a filter based on the cell formatting. This provides a quick way to view the outstanding checks only. To do so, just select any cell in the range and click Data > Filter. You’ll see the little filter controls on the header row. Just click the drop-down for the Check column and pick Filter > By Color. Bam:

If you want to compute the total of the outstanding checks after applying the filter above, select the cell under the last amount and hit Alt =. This will insert the SUBTOTAL function and show the total of the visible rows, like this:

This Conditional Formatting approach works well when the lists are on the same sheet, when they are relatively small, and when we only care about one column (the Check number column). Otherwise, we need something more powerful. And we’ll cover that in the next post 🙂

Conclusion

This is the first of three ways to help automate reconciliations. In the second post, we’ll see how lookup functions provide additional capabilities!

Sample File