Category Tracker (Excel)

The Google Sheets Category Tracker has been our third-most-popular template historically with thousands and thousands of downloads. This build of the Excel Category Tracker rebrands it from “Excel Money Tracker” and harmonizes it with the popular features in the Sheets version, including the addition of Description totals (the columns at right) for the selected period.

This template will help you dig deeper into your spending for a selected category by reviewing the Transaction details over a configurable time period. See the sum of spending for all categories for a given date range.

This worksheet is just a personal contribution to the Tiller community and is thereby not supported by our Customer Success team.

How To Install the Worksheet

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.

Add Excel Tables to Your Workbook

Before adding the worksheet, you’ll need to add Excel tables to your spreadsheet that contain the contents of the Transactions and Categories worksheets.

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

Download the Category Tracker Workbook

Download the workbook containing the Category Tracker v1.61.
(Version was bumped April 20, 2022.)

Insert the Category Tracker Worksheet

  1. Open you linked Tiller Feeds workbook in Excel.
  2. Open the workbook you just downloaded containing Category Tracker in Excel.
  3. Right click on the Category Tracker sheet in the workbook you just downloaded and click “Move or Copy”.
  4. In the “To book” dropdown, select your linked Tiller Feeds workbook.
  5. Click the “Create a copy” checkbox.
  6. In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
  7. Rename the new worksheet to “Category Tracker” (if needed).
  8. Navigate to “Data / Edit Links…” in the menu (not the ribbon).
  9. Select the link to the downloaded workbook in the box.
  10. Click the “Change Source…” button.
  11. Navigate to the active workbook (i.e. select your personal spreadsheet). This will point the references back to the new sheet.

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

We are still learning how to manage and optimize the sheet insertion process. If you run into any issues or have clarifications to improve the instructions above, let us know!

How to Use the Worksheet

Most of the improvements are under the hood. Functionally, the new Category Tracker is the same to use as the old one which is documented here.

The chart and description totals are new, but are pretty self explanatory.

Give Us Feedback (Please)

If you take this for a spin :oncoming_automobile:, please let us know how it works for you.
We are planning some improvements to our Excel solution and your feedback will help guide us.

One thing to verify is if using the “Links” option you can replace the link to the new workbook. That might be a safer option.
In the image below, it’s the first “links” option. This icon won’t show up if your workbook has no links.
I use the search bar to get it, but I’m sure there’s another way.
link1

Hi,
I would love more use of Excel with Tiller solutions!
I tried to insert your v1.50 sheet into my existing Tracker, but it really didn’t work. I had better luck moving my transactions and categories to the v1.50 template. Now it is working fine.
I would really love a Tiller solution of some kind for Envelope budgeting for Excel. I’ve tried this, but I’m not proficient enough with Excel to do it. It became impossible for me because Excel has no easy equivelent to the Sheets Query function. Thanks!

Thanks for the feedback @yossiea and @jemmoa7. I apologize for not getting back to you sooner, but have been working on some other things.

I like your suggestion of using the “Edit Links…” workflow. Unfortunately, I couldn’t get the “Break Link” button to do the trick (it just converted my data to static values) but I was able to get the “Change Source” button to work. I updated the original instructions. Any chance you can try again and let me know if it works for you… or if you have feedback to improve the instructions?

P.S. I hear you, @jemmoa7, on the need for an Envelope Budget in Excel. That is on our roadmap but unfortunately isn’t imminent. For now, I recommend using the Savings Budget in Sheets.

I think you’re right, I just tried it again. I ended up opening up my file from OneDrive and getting the path from Info.

I am though experiencing #N/A errors. See the below screenshot (ignore my chocolate and lottery. :slight_smile: )


I’m not 100% sure about the formula, but based on the template, I think the first column isn’t bringing everything across.

There’s something with the named range, I can’t even edit “Categories”
also, IIRC, you can filter and return more than one column at a time in Excel.

-edit- Actually, the error is in the Group/Type columns. You are filtering on Categories, so hidden stuff still show up because no categories are named “Hidden.” You still need to filter on “Hide From Reports.”

In the header, you include Transfers, but in most cases those will be hidden, I’m not seeing any transfers on my end. (Could be the fix I had to put in got rid of it, but in any event, transfers don’t really impact the numbers so it might not be needed.)
There should be an option to sort by Category or Type/Group, and possibly even having the option to sort/summarize by Group/Category.

Good catch, @yossiea! I think you’re right that it is a bug in the template that occurs when there are hidden categories. Looking into this now.

I fixed the hide-bug, @yossiea, and bumped the version to 1.51. Could you try again with the new version (new link in main topic body)?

Grateful for your help and feedback,
Randy

Randy,
Looks good on my end!

1 Like

One suggestion to make things easier to edit/view, if you change the formula in I3 to this, you can get rid of the index and match.

=FILTER(Categories[Category]:Categories[Type],Categories[Hide From Reports]<>"Hide")

You’d have to play with the Amount field, then but I think it looks cleaner.

Thanks for your help!

1 Like

BTW, one of the things that might be helpful is filtering only certain categories/groups.
You can do this in the filter, or a drop box. I have one off categories that I don’t want to really see since it won’t change my budget, so I would exclude that oftentimes.

(Categories[Hide From Reports]<>"Hide")*( Categories[Category]<>D1))
1 Like

The reason I didn’t do it this way, @yossiea, is that people may have a different column order in their Categories sheet (or a new personalized column inserted in the middle of the standard Categories-Groups-Type) and I want to pick out those specific columns. I agree that it is harder to read but I think it is more robust when users personalize their workbooks.

1 Like

Not sure if this was from you or from Tiller, but if you go to name manager, you will see some ranges that are no longer applicable and hardcoded to c:…

1 Like

Folks, incorporated Tracker v1.51 Workbook. Followed instructions and works great in my new Excel Tiller Feeds workbook. Tried both Randy’s and yossiea’s “Links” methods. Both option work great. The only issue I had was learning the “Date Features” for the Pie chart associated with Transactions and Categories. After “Creating a Copy” of Tracker v1.51 Workbook into my Excel Tiller feeds, the Pie char was scrambled… I then modified the Dates to Jan 1 2022 to Mar 31, 2022 and now have a nice pie chart!. Great new worksheet. Great instruction how to get it incorporated into my Excel Tiller Feeds workbook. Fun to see Excel come alive with regard to Tiller!!!

1 Like

Nice work! I added it to both my Excel sheet and Google Sheet. Installation was seamless and easy.
Thanks!

Thanks for the feedback, @jemmoa7. Install was seamless on Excel too?

The only “issue” if you can call it one, was on Excel, the Category dropdown on the far right of the sheet was not set up. I set it up and all was great.
Jim

The description not working. it shows #Calc on the description and total