column cart

[Methix]

So this is about what my personal expenses are looking like. I'd like to graph out these numbers in a column chart that shows my weekly expenses and my weekly savings.. so the x-axis should show time and the y should show both the expenses and the savings from one week. i have no idea how to even get started on a chart like that.. is it possible? thanks!

____A_______B_________C____
01…Date...Description...Amount
02…9/02...Payday..........500
03…9/03...Rent............-200
04…9/04...Gas...............-50
05…9/05...Groceries......-100
06…9/06...Cable Bill........-50
07…9/07...Savings........-100
08…9/08...Payday..........500
09…9/09...Rent............-200
10…9/10...Gas...............-50
11…9/11...Groceries......-100
12…9/12...Cable Bill........-50
13…9/13...Savings........-100
14…9/14...Payday..........500
15…9/15...Rent............-200
16…9/16...Gas..............-50
_________Total............250
_________Savings.........200

 

[Steve=True]

Hi Methix,

Depends on what you are looking for.

You can add another column for Cumulative/Running total and only plot that with a line graph.


Steve=True
www.exceldashboardtemplates.com

 

[Methix]

hey steve,

the "total" on the bottom kind of represents a cumulative/running total for the current week, since i put whatever is not used for every week into "savings", which zeros out each week. but id like the graph to show every week with the total money used during that week and the money saved as well.

so it looks kind of like this:

|..______....______....______
|..|saved|...|saved|...|saved|
|..|_____|...|_____|...|_____|
|..|........|...|........|...|........|
|..|spent|...|spent|....|spent|
|_|_____|__|_____|__|_____|_
.....sep-1......sep-8......sep-15...

and maybe if possible could i split the "spent" into larger groups like rent, gas, groceries, etc?

thanks!

 

[Steve=True]

I see what you are looking for now. You want to make a Stacked Bar Chart by week.

Since you want a weekly total by category, the easiest way would be to add one more column for Weeknum.

Then make a Pivot Table of the Data by week by category

Then plot the chart.

Steve=True
www.exceldashboardtemplates.com

 

[Methix]

what formula would i need to put in the weeknum column?

 

[Steve=True]

Just a simple one

=WEEKNUM(a2)

copy down.

this will create bins/buckets for your data inorder to group it by weeknumber for a total

 

[Methix]

so how would it be best now to total by week number and then plot it in a stacked bar chart?