How can I display the percentage of expenses based on categories?
Hi all!
I'm brand new to smartsheet and I'm playing around with the features. I put together a list of personal transactions and now I'm curious how to turn that into a nice pie chart that will display percentages of expenses based on categories.
Quicken does something like this, just posting the below as an example.
I'm assuming I have to set up some kind of formula to count categories and calculate the amount and turn that into a percentage? But I haven't played around with formulas before so I'm struggling to figure this out.
Any advice or has anyone done this before in smartsheet?
Thanks in Advance!
Answers
-
- Create a sheet called 'Expense Tracking' and have a dropdown column called 'Categories' with the following options:
Other, Shopping, Kids, Home, Food & Dining, Cash & ATM, Auto & Transport
Then have another column (text/number) called 'Expense', change the column to a $ value using the toolbar.
Record the expenses.
Then in the sheet summary (icon on the right toolbar), create a new field for 'Other' first, and type in the formula:
=SUMIF(Categories:Categories, "Other", Expense:Expense)
Repeat this with all of the categories, changing the 'Other' in the formula to the respective other names (e.g. Shopping).
Create a new Summary Report called 'Expense Rollup Report', select the sheet 'Expense Tracking' as the source. Select all of the fields we just created as columns (e.g. Other, Shopping etc).
Create a Dashboard, use the little pencil in the top right to add a widget, select the chart widget, click add data, select the 'Expense Rollup Report'. Change the Widget to a Donut. Select show % opposed to Amount.
Congrats!
If this helped please upvote, it helps me! -
@JIDEATTURRA Thank you!
So it looks like I'm halfway there with your instructions.. Now for some reason Pie/Donut charts are grayed out.I entered my expenses as negative entries as there are positive entries on this list (due to expenses that were refunded). I read somewhere that pie charts don't work with negative entries - is that true?
-
Yes, a pie chart cannot show negative because it wouldn't display as anything.. only a chart can do that!
If my answer above helped, please remember to vote it up!
-
Ah okay, so how can I change the numbers in the sheet summary to be positive? Is there another function I can tack on to the end of the function you provided?
-
Well, they will only be positive if the sum of them is positive! You can always put "+ then the amount" at the end of the function, but this kind of negates the benefit of it auto summing for you, you'd have to adjust each time!
-
It makes sense to have it negative as they are expenses and are deducted from your account, but I would have them all as positive in the sheet to negate this problem, then you are getting a sum of the total expenses.. should you then wish to add money back for refund, do the opposite and make it negative.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives