Docs: Account Filter (Excel)

Overview

The Excel Account Filter sheet, based on the popular Sheets Account Filter, is useful if you want to review the transactions of any single account linked to your Tiller spreadsheet. This is a read-only sheet for viewing transactions that also already exist in the Transactions sheet. It’s an alternative if you prefer not to filter your Transactions sheet, but want to review transactions for a specific account.

How to Install the Account Filter sheet

Add Excel Tables to Your Workbook

If you have a newer version of the Excel Foundation Workbook, you can skip this step. Otherwise, you’ll need to add Excel tables to your spreadsheet that contain the contents of the Transactions and Categories worksheets before adding the worksheet.

In each worksheet, simply:

  1. Select the entire used data range (including the header row)
  2. Click Insert/Table in the file menu
  3. Check the “My table has headers” checkbox
  4. Click “Ok” in the Create Table modal dialog
  5. Assign a Table Name in the little text box in the top left of the toolbar— use “Categories” in the Categories worksheet and “Transactions” in the Transactions worksheet

Insert the Account Filter Worksheet

  1. Download the Account Filter 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.

How to use the Account Filter sheet

To use the sheet, select the account for which you want to view transactions in the drop down in cell B3.

Advanced users can pull in additional columns from the Transactions sheet by modifying the formula in cell B6.

Great sheet addition to Excel. One small issue is that data validation will not copy over from the account filter sheet v1.00 to your personal sheet.

I just copied the data validation source from the account filter sheet to my personal sheet, =Accounts!H2# and the sheet is working as intended! :smiley:

Just noticed this in another share, @TimA. Will see if there is a similar workaround for the Account Filter.

I implemented the change from this post, making the dropdown data validation reference local to the worksheet (in a hidden column at right). The new master is version 1.01 (with the link in the main body :point_up:).

If this change works, let me know… Thanks.

The category description


total does not show the $value. Not sure if it’s a bug or error from my template.

@adekunledauda - did you figure out how to get this to work? Maybe start with a fresh copy of the sheet and copy it into your workbook.

Nice! Works great. Good job.

The same issue persists.

@adekunledauda - sorry, I’m not able to reproduce this issue. It worked fine for me. Did you start with a fresh copy of this solution and move it into your workbook again?

@randy any ideas on this issue?

This is a fairly old post so hopefully this is still supposed to work. I am using Excel on a Mac. I downloaded the file and copied it to my template file. I updated the data link and although it never appears to work it does eventually link - after many tries. The sheet works for the Accounts - when I click on the down arrow for the accounts box I see all my accounts. I select an account and the only thing that happens is I get #CALC! in cell A6. Nothing else happens. The formula in cell A6 is:

=FILTER(CHOOSE({1,2,3,4,5},Transactions[Date],Transactions[Description],Transactions[Category],Transactions[Amount],Transactions[Account]),Transactions[Account]=B2)

Anyone have any ideas as to what might be the problem? Besides me! :slight_smile:

@randy Any ideas on what’s causing that CALC error?

Can you confirm you have a Transactions sheet with the following (referenced) columns, @JayHudson?

  • Date
  • Description
  • Category
  • Amount
  • Account

Can you also confirm that there are transactions in the Transactions sheet for the Account you chose in the dropdown?

Yes, the Transactions sheet has the columns.

  • Date
  • Description
  • Category
  • Amount
  • Account

There are transactions in the transactions sheet for the accounts I select from the dropdown. I tried every account I have and get the same error.

Randy - I got it fixed! The link in the hidden column G2 was =IFERROR(SORT(Accounts!H2#),“”)

I changed it to =IFERROR(SORT(Accounts!I2#),“”)

Changed the reference cell column from H to I and now it works.

Thanks for getting back with me so quick.

1 Like

Thanks for sharing the fix here!

This is awesome. Thank you @randy. Quick Question - I know that the Account Filter is Read-Only.
However, I am able to add it as a table without breaking anything, so that I can sort by Date instead of Description?

I think it sorts by Date by default, @ramona. If you want to change the sort, you can just change the column index at the end of this function in A6:

=iferror(sort(filter({INDIRECT(H13),indirect(H14),indirect(H15),indirect(H16),indirect(H17)},indirect(H17)=B3),1,false))