🏆 Transaction Comparison - Excel

Microsoft Excel Show & Tell
1

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.

Screenshot 2022-07-09 203413
Screenshot 2022-07-09 2034131774×351 28.6 KB

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.

2

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.

3

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
4

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
5

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.

6

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.

7

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

8

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
9

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

10

Hi. I’m new to Tiller and care deeply about being able to compare costs from period to period. While I successfully was able to add this sheet to my foundation template, the Group sorting feature is not working for me. It seems to be returning blanks. The category and account sorting features work perfectly so I am not sure what is wrong. I did not add any rows or columns to my category tab. Any help would be appreciated.

11

If you reveal the hidden Helper Data columns by clicking the + above column BB, Do you see groups listed in columns AQ and AZ? This is where the drop down menus get the list of groups, and those are based on a VLOOKUP of the Groups list in Column AH. AH won’t get the correct list of groups from the Categories sheet unless the “change source” step is done when you first installed the sheet, so it’s looking at YOUR categories sheet and not trying to look at the one in the shared template you downloaded. Hopefully this will help you track down where the issue is!

12

Thanks for helping. I did review that column and the groups there are correct (my custom groups are showing up). I did a little more playing around and it seems that the drop down for filter hidden needs to say Don’t Filter Hidden for the groups to work. However, when I sort by category or account this requirement is not necessary

13

Hi, Thank for your Excel version template which I find very useful. I have found an issue when dealing with two transactions that are identical (except for their Transaction IDs), but not duplicates. The template only reports one of the two transactions in Office 365 desktop Excel. Examples where this occurs are automatic monthly payments for my and my wife’s Medicare Supplement payments and also our Part D drug plan payments where the amounts are exactly the same and withdrawn from the same account on the same day. Again, the only difference between the transactions on the Tiller Transaction sheet are the Transaction IDs. I am not using any custom tags or fields on the Tiller Transaction sheet. Thanks.

14

In the hidden staging area I filter the transactions using the ‘UNIQUE’ function (don’t recall why, but I’m sure there must have been a reason). I think removing that will fix your issue, but not sure what the side effects might be. Replace cell AK5 with:

=IFERROR(SORT(FILTER(CHOOSE({1,2,3,4,5,6},Transactions[Date],Transactions[Description],Transactions[Category],Transactions[Amount],INDIRECT(Y8),Transactions[Account]),(IF($J$7="",TRUE,Transactions[Category]=$J$7)) * (IF($J$9="",TRUE,INDIRECT(Y8)=$J$9)) * (Transactions[Date]>=AA17) * (Transactions[Date]<=AA18) *(IF($J$13="",TRUE,Transactions[Account]=$J$13))* (IF($J$11="",TRUE,ISNUMBER(SEARCH($J$11,Transactions[Description]))))),,1),"")

Replace cell AT5 with:

=IFERROR(SORT(FILTER(CHOOSE({1,2,3,4,5,6},Transactions[Date],Transactions[Description],Transactions[Category],Transactions[Amount],INDIRECT(Y8),Transactions[Account]),(IF($J$7="",TRUE,Transactions[Category]=$J$7)) * (IF($J$9="",TRUE,INDIRECT(Y8)=$J$9)) * (Transactions[Date]>=AD17) * (Transactions[Date]<=AD18) *(IF($J$13="",TRUE,Transactions[Account]=$J$13))* (IF($J$11="",TRUE,ISNUMBER(SEARCH($J$11,Transactions[Description]))))),,1),"")
15

Thanks Joseph. The updates to AK5 and AT5 appear to fix the issue. I will let you know if I find any side effects caused by eliminating the “UNIQUE” function. I did try adding Transaction[Transaction ID] to the original formulas but it didn’t work, but then it was an attempted “quick fix” and I need to study your original formulas more closely. Thanks again.

1 Like