Yearly Category Chart

Google Sheets Show & Tell
1

What is the goal of your workflow? What problem does it solve, or how does it help you?
This sheet shows you a chart of your actual vs budget for a single category over the course of a year. For non-regular categories which vary from month to month, this sheet gives a quick visual representation of how I’m doing.

Yearly Category Chart@2x
Yearly Category Chart@2x2686×1652 220 KB

The bars show the actual (black) and budget (blue) amounts for each month.

The lines show the running totals for the actuals (up to the current month) and budget (for the whole year.)

How did you come up with the idea for your workflow?
I wanted to see how I was doing in a single category compared to by budget over the course of a year. The existing Yearly Budget sheet addresses this question but I wanted a less complex solution using a yearly chart.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
This sheet requires a Tiller Transaction sheet and Tiller Categories sheet with monthly budgets.

You can get a copy of the sheet here:

Click on the “Use Template” blue button on the top right to download your own copy.

Open your copy and click on the triangle by the sheet name to get the pop-up. From there, select “Copy to” “Existing spreadsheet” and copy the URL of your Tiller spreadsheet in to the box under “Or paste a web address here:”

yearly chart popup@2x
yearly chart popup@2x870×890 56.2 KB

The copied sheet will be named “Copy of Yearly Chart”, but you can rename it just “Yearly Chart”.

There are only 3 settings to adjust:

  1. The category you want to view
  2. The starting month (usually 1 of January, but it can be adjusted)
  3. The starting year (usually this year, but it can be adjusted. If you pick a different year, you will need monthly budgets for that year on the Categories sheet.)

Anything else you’d like people to know?
Don’t add or delete any rows or columns.

The chart can likely be adjusted to view Groups instead of Categories. If someone is interested, give it a try.

Is it ok for others to copy, use, and modify your workflow?
Yes, that’s why I’m posting it here. You don’t need to ask for permission to get this sheet. Use the link above and the Use template button.

Please share your feedback below.

Jon

2

This is simple but sleek, @jono. I’m enjoying it.
Thanks for sharing!

1 Like
3

This is very cool. Thank you!

1 Like
4

LOVE this sheet, and so very helpful. It is simple, sleek, clear in its presentation and data. Thanks!

1 Like
5

This is FABULOUS!! Thanks so much for sharing it. I love the simplicity of it and that it’s a visualization vs most of my analysis is just numbers, so this is way more “fun” to look at. haha!

1 Like
6

Very nice, thank you!

1 Like
7

How would it be possible to change from “Category” to “Group”? I’m not too familiar with Google Sheets, so just curious and wanted to ask. I really like the presentation of the sheet.

8

If it were me, I’d do a FILTER() around A42 for categories in the lookup group then reuse most of the formula in B42:M42 to pull actuals for each by month. Total them up and put them on the chart. You could do something similar for the budgets.

9

Hi @jstrilich ,
@randy 's suggestion is a good one.

Some Tiller Community templates have an option to use Category or Group as their data source either in the hidden part of the sheet or the main part. But this sheet doesn’t have that feature.

Jon

10

Thanks @jono and @randy for suggestions and feedback!

11

I also am trying to adapt the Yearly Category Chart (very useful as a reality check for budgets!) for use with Groups. I have B42-M42 accurately reporting the actuals, but I’m flummoxed by B45-M45.

I realize that I’ve got to sum the budget amount for all categories in the target group for the month shown in cells B41-M41, sourcing that data from the Categories sheet. Your suggestion about applying a FILTER around A42 (do you mean B42?) isn’t clear to me. Can you elaborate?

Thanks!

12

Sounds like you’re pretty close, @GregC.

It should work with something like this:
=sum(filter(offset(Categories!$A$2:$A,0,MATCH(B$41,Categories!$1:$1,FALSE)-1),INDIRECT($R$11)="Giving"))

… where $R$11 is a Categories/Group lookup that renders something like Categories!B2:B. You can copy this kind of lookup O10:R10.

Good luck.

13

Did anyone get this working for Groups? I’m happy to try to figure it out on my own, this is a very helpful thread, but if someone else has cracked the code I’d be amazingly grateful for a copy/paste.

14

This was just what I was seeking for better category trending, thaks so much!

1 Like
15

Glad it’s a good fit for you

16

Hi, @wreed4!

Sorry for the long-delayed response! I’m just seeing this.

Here is what I came up with:
Yearly Group chart

No data here, just formulas, but it works in my Foundation sheet. My formula solutions are less sophisticated than many I see in the Community, so I suspect there is room for much improvement, so have at it!

Greg

17

Thanks for sharing, @GregC.
Did this solution help with your workflow, @wreed4?

18

Thanks so much!! This gets me a lot closer. I’m seeing the budget values show up, but not the actuals… I’ll do some debugging. It’s possible it’s due to an extra column in my Transactions page…

19

Actually I think it’s a missing column. Transactions!K2:K on my sheet is the month column… I don’t actually have a group column anywhere on my transactions sheet which it looks like this relies on?

20

That did it!! It works now, thanks! For anyone else with the same problem, https://help.tillerhq.com/en/articles/3137561-add-a-group-column-to-the-transactions-sheet

2 Likes