Transaction Comparison - Excel

Overview

Sometimes it’s useful to compare transactions from one time period against transactions from another time period. This allows you to specify two time periods, and then an optional Group, Category,Tag and/or Description text to filter by. I have previously made a Google Sheets version available here.

How did you come up with the idea for your workflow?
Someone in the community was looking for this functionality, and I could envision what it might look like.

Installation

  1. Download the Transaction Comparison workbook .
  2. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Account Reconciliation sheet is installed.)

At this point your new template should be functional and linked to your local workbook’s data.

Setup

If you’d like to filter your comparison by tags you’ll need to have a Tags column setup in your Transactions sheet, and of course your transactions will need to be tagged.

Usage

Only change the green highlighted cells. By default, it will compare all transactions from last year against those from this year. You can change those start and end dates, and can filter the transactions down to a Group or Category, by Tag or by Description, or all. A ‘Total Amount’ appears at the top of each list to make it easy to compare the overall total of all the transactions shown.

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yes, please, and please provide feedback on any issues you encounter and/or suggestions on how to make improvements!

Notes

If you don’t use tags, you could hide columns F and P to save a little space on your screen.

Thank you for the excellent, powerful Transaction Comparison template. I immensely love it! It supports many time periods. It allows me to filter transactions based on 4 options (Group, Category, Tag, and description). I’m not quite sure about Filter Hidden check box.

I gave you my vote for this essential template.

Glad it’s useful for you, thanks for the vote! On the ‘Categories’ sheet there is an option to ‘Hide from Reports’, which prevents selected categories (like transfers) from appearing in reports. The ‘Filter Hidden’ checkbox on my template filters hidden categories if it’s checked, and if it’s not, will include hidden categories. While you wouldn’t want to see hidden most of the time, I wanted the ability to compare hidden, just in case that was something someone needed to do.

2 Likes

Thank you for the tip I’ll play with the filled category and see what happens with the filter box checked and unchecked. It’s a good template. I can easily get transactions with this month option so I can easily compare with bank statement.

Thanks again,

David

1 Like

I found that the checkbox I have in I13 doesn’t work in the web-version of Excel, so I changed it to a dropdown in the template. This changes cell I13, which now uses Data Validation and cell V5, which contains a formula that looks at which menu item was chosen in I13.

For some reason checkboxes don’t work. On my sheet, I just noted that you should put in TRUE or FALSE depending on what you want to query. It’s not a pretty solution, but it works and I can avoid even more formulas.

It is so strange to me that Excel, as a much more mature product than Sheets, can be so fussy.

I just added the ability to compare by Account. Remove your old sheet and re-add the template again to gain this functionality. The version is now 1.2

1 Like

Fixed bug in Case Sensitivity for Account names, bumped version to 1.2.1