Feb 16 2021 03:57 AM
Feb 16 2021 03:57 AM
Hey everyone! It's my first time asking an online community anything. I usually found out the answer on the internet (on previous posts) but recently I've been struggling a lot to understand this excel budget template. (link: https://templates.office.com/en-us/monthly-college-budget-tm02930047
I'm a beginner with advanced formulas like INDEX, scroll bar/dropdown linked to charts, and Dynamic Charts (PivotCharts). I did find the "Chart_Calcs" hidden worksheet but it did not help much. There's also the use of Power Query.
If someone could explain to me how the charts (clustered bar & line) are linked to the dropdown, the scroll bar and the income/expense/cash flow data at the same time, I'd be very grateful. This template seems very complicated.
Feb 16 2021 06:02 AMSolution
@arjunrr I understand Excel, and still fully agree with you that this template is very (I'd say "overly") complicated. It suffers from the same syndrome that too many of the Excel templates suffer from (IMHO) and that is that it's too cute. Whoever developed it was aiming for sizzle rather than substance. It may indeed have substance, but it's obscured by unnecessary features such as the "slider" (a drop down selector for the month would be equally effective, but less jazzy, so jazzy it is, functional it's not).
And unless I missed it, I didn't see how you would enter income and expenses in an itemized way; looked more like you had to track the raw data somewhere else.
Frankly, if I were you, I'd go find another template. Or create my own.
Feb 16 2021 06:33 AM - edited Feb 16 2021 06:36 AM
@arjunrr Usually, these kind of templates are overly complicated. Personally, I find them quite useless. Nice if you want to learn some Excel tricks, but often very difficult to maintain and/or adapt to a real life situation. They often contain hidden sheets (like this one) and a massive number of named ranges and named formulae. Named ranges and named formulae are great, but this particular has 18 such and if you are not the designer, you need to spend quite some time unraveling and understanding them.
This particular template does not use Power Query or Pivot Tables/Charts (as far as I can discover), but it has some techniques I would never think of using myself. But that's irrelevant. Is this template something you would want to use yourself, are are you just learning about Excel?
Feb 16 2021 06:34 AM - edited Feb 16 2021 07:04 AM
@mathetes Minds alike! Didn't see your post until half an hour after it was time stamped.
Feb 16 2021 07:10 AM
Minds alike indeed.
I've complained to Microsoft about the fact that a very high percentage of the templates I've looked at (which is admittedly a small number, so it would be unfair to paint with too broad a brush)...a very high percentage of the ones I've looked at suffer from this same tendency, to offer a lot of graphics and other sizzle features, but actually get in the way of functionality, by hiding things, limiting things that would be useful.
At the same time, I recognize the difficulty involved in creating a spreadsheet for others to use. You do that professionally. I've only done it (back before I retired) as a colleague, helping somebody else in the office improve a design, but always with the understanding that I was there to help if needed. Nevertheless, what I created (what was needed) was almost always a basic database or Table to track transactions, and then such things as Pivot Tables to produce meaningful summaries.
The income and expense tracker that I've created for myself is one that I'd consider publishing as a template, except that Money in Excel is already there and it links nicely with bank and credit cards. It doesn't, however, link with my bank, at least not with the dual factor authentication that protects my accounts. SO...I'm happily using my own design, with a lot more flexibility, lot more depth in budget categories.....
Feb 16 2021 01:59 PM
@Riny_van_Eekelen Actually I've been using this template for a while as a student, but I wanted to customize it. I first tried to change the data and the series, but the charts did not follow the changes. So I tried to understand and fix the problem but it was too complicated. That's why I asked for help. I did learn a lot in the process though.
I really like the fact that it's visually pleasing, I thought that I could change it for my own personal use. I think I'll create my own with PivotCharts, which is way simpler.
Feb 16 2021 02:17 PM
You're right I will create my own template, and I'll do the same if I have the same problems with future templates.
I thought it was complicated for me since I'm only a 'beginner' compare to people (like you) who use excel on a daily basis in their professional lives. I thought you'd find it easy to understand how the template works. My bad haha. I did learn some Excel tricks though. Now I understand why I found it so hard to understand the series and the linked charts of the template. Using my own design will be much simpler, and more flexible as you said.
Thanks a lot for your help!
Feb 16 2021 02:33 PM
Feb 16 2021 03:02 PM
Attached is a sample modeled on the income and expense tracker I use myself. It's one I created myself.
No graphs. A Pivot Table is used for summarizing income and expenses by month and by category.
Raw data is imported from the banks and credit cards; I then manually assign budget categories.
Feb 16 2021 03:12 PM