How can I display the percentage of expenses based on categories?

JenniM

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!

Ide AirTrunk

• 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!

JenniM

@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?

Ide AirTrunk

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!

JenniM

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?

Ide AirTrunk

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!

Ide AirTrunk

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.