Hello,
I am new here. I am not a novice excel user per se but just getting familiar with pivit charts. I am looking for a way to use the info in the Tiller Foundations worksheet for Excel to create a chart showing how much I have spent up to this date as a percent of what has been budgeted up until this date. Similar to what is shown in tabular form on the “Yearly Budget” tab but also adding in the percent calculation.
Thanks,
Titus
@kanaikid619, welcome!
This is a great idea. I don’t know the exact solution to it as I am not as savvy in Excel as in Google Sheets, but you can definitely insert additional worksheets into your workbook and build a custom dashboard to present this information. You could use a pivot table to show you the spent “to date” amount and then perhaps a query or something equivalent to pull in the budgeted amount? I don’t think there are any show & tells that are doing this already, but a great idea for sure!
Tagging @Builders to see if anyone is interested in building out something like this to submit for builder rewards.
I created a worksheet that does just this…but instead of percentage, I use conditional formatting to color code the cells. If there is plenty of money left in a category, the cells are green. If I am close, yellow; over red (there are 5 shades total I use). I then do the same thing but looking at the year.
This is the formula I use to calculate how much I have spent in a category for the month:
=-SUMIFS(Transactions!D:D,Transactions!C:C,‘Monthly-Progress’!B8,Transactions!A:A,“>=”&C$5,Transactions!A:A,“<=”&C$3)
$C$3 is a cell with today’s data (=TODAY())
$C$5 is the start of this month (=EOMONTH(C3,-1)+1)
Monthly-Progress’!B8 is the category I want to look at
I am a novice excel user, so I’m sure there are much netter ways to do this.