After watching the following YouTube video called How To Build A Custom Dashboard In Tiller Money by Shelby Grosch, it seemed like his 6 charts would be most helpful, but as a newbie in Google Sheets would need some help to build them. Does anyone know how to create the dashboard & charts he recommends? OR is there something already created?
Here’s the charts that he recommends.
Any help would be most appreciated.
I’d love to have just the one Net Worth Over Time chart but have never been able to figure out how to create it. For some reason, I just can’t get the info I need out of his video. It seems so easy, but…
I found his other video showed the graphs in more detail How I Manage My Money In 10 Minutes Per Month - Tiller Money - YouTube however the setup was not explained.
He seems to use the pivot tables for these graphs, i was able to add the last six months of discretionary spending without too much fuss,
I’ve stepped away from the computer now but his explanation of how to create things did leave a bit to be desired.
I worked on the discretionary spending for the last 6 months graph and had to use a formula on the month part to filter out and only show the most recent 6 months, it was something like greater than =eomonth(today(),-6) i did not see this explained in the video. I’ll continue working on the others and may be able to explain the steps more.
@susandennis I’ll try that one next and see if i can help, were you able to add the 3 columns to the transaction sheet? This part was explained well and worked flawlessly for me. (I copied from the description)
I was also able to build the Discretionary chart using his video instructions. Have you been able to build any of the other charts?
Not yet but i will try and let you know how it goes, i would imagine it should be similar with a pivot table feeding the graph and filters in place to show the information you want
I have created a pivot table previously for balances off of the balance history tab, i do know you need to change the sum of the value to median or average otherwise it would show a lot more money than you actually have unfortunately
That part does seem the most clear but also not something needed for longer views of just net worth.
Hi All,
I can get as far as the pivot tables, but I was wondering about the signs of the transitions.
I’m not a fan of seeing negative numbers on a report, unless it is comparing two things. Is there an easy way to turn the Expenses into positive numbers?
I actually found mostly what I was looking for - a net worth over time graph/data sheet. It’s called - oddly - Net Worth. There’s one for Google and one for Excel. The Google one is very nearly impossible to find. Most references on this forum call it New Worth Tracker and say that it’s listed in a variety of places that are now vestigial. I finally found it by using the search box on the community solutions drop down and using the term ‘net worth’.
I wanted to update that I ran into a problem with the year arrayformula, it caused a 2nd date column due to it being displayed as date to be put into my sheet. I didn’t realize this until my next tiller fill and it incorrectly found that column to put the date. I was able to work around this by adding some text to the array so it should be
={“Year”;ARRAYFORMULA(RIGHT(B2:B,4))}
I’m also playing around with positive and negatives currently to see about creating the net worth graph as I do see benefit of having all 6 of these graphs together. This is proving to be a challenge for me currently lol All of the balances are positive and to get the net worth I have to subtract the liabilities, so I’m stumped here as well.
And @susandennis yes if you find that the net worth template is all you need then Great! it is very robust and I find it useful myself.
Hi @bentyre1. I have a dashboard that is Net Income. The way I did it was do a pivot table of 2 lines: Income and Expense. A few rows below that I subtracted the cells myself. Then I graphed all three lines by month. Income and Expense are side-by-side bar graphs, while Net Income is a line graph.
For my positive numbers field I used a simple if statement in the cells:
=if(F6=“Expense”,G6*-1,G6)
I wish I knew how to moved that the field header. I have to manually update the formula for new records.
I don’t know what the specific formula would be, but to get it to populate automatically you’d want to use an =arrayformula
I’m sure @benlcollins has some great tutorials on arrayformulas on his site https://www.benlcollins.com/
Can you clarify this a bit more, is the formula below the pivot tables but uses the values from the pivot table?
If its using pivot table values then the pivot table should be static, otherwise if its a column then an arrayformula may be better like heather mentioned.
Yes, you can create formulas from pivot table data. The danger is if other rows of data are added, the pivot table will write over them. In this case, I plan to only compare 2 things, so I should always have 2 rows.
I’ll play with the arrayformula. I wanted to get the proof or concept down for the dashboards I wanted, and I didn’t want tinkering with the sign reversal to get in my way. 
Thanks Randy! For the other fields I added to transactions, I was able to copy other Arrayformula and figured it out. But for that one it was not playing nice with me for some reason.
I’ll check out the content and try again.
Thanks Heather! I go back and play with it. I wanted to make the pivot tables and dashboard work, so I took a short cut.
I have another dashboard in mind with Balance History over time, but that may be a whole other thread. I want to bring in the Account Groups I created where the balances are. For example, what is the trend for my savings accounts? Or Credit Card balances.
Sure thing. The Net Worth Tracker might be a good place to start. It doesn’t show the groups but it does break it up by assets/liabilities.
Thanks! I’ll check out the Net Worth Tracker.
Also, I was able to create the array formula, which worked great.
={"Report Amt";ARRAYFORMULA(if($F2:$F="Expense",$G2:$G*-1,$G2:$G))}
Flips the signs of my expenses to positive numbers. A refund is of course now negative. In my case Column F is my Type of transaction, and Column G is the transaction amount.
