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

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

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

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