Graph spending vs date

**Taiden** · 05-28-2009, 04:13 PM

I've been keeping a record of everything I buy for my car along with hard copy receipts in a folder.

I want to make an automatically updating graph that is total money spent in a week on the y axis with the date of that week on the x axis.

You can see in Chart1 I started to attempt this... but quickly saw that failure was inevitable.

**teylyn** · 05-28-2009, 08:21 PM

Hi Taiden,

good start, but you've come up against some challenges. I attach an Excel 2003 version of your spreadsheet with a few changes:

1. Since you want to total your spend by week, you need to sum by week, too. Excel charts don't do data summarizing, you have to prepare the data yourself. So I introduced a new column that calculates what week number applies to the date of the line item. This week number plays an important role in a new table, that I've set up to the right of your data entry area. This is where the data gets summarised into spend by week. The week numbers are listed again and a SUMIF formula is used to total up the amounts in your data entry area that have that week number.

I've created a column chart that shows the dollars spent per week.

2. Just a suggestion for your area chart: I've changed it, so now it shows the accumulated spend. The column that is charted is now not column B with the individual amounts, but column E, where the amount of every item is added to the total spend since the start.

Does that make sense to you?

**Taiden** · 05-28-2009, 08:35 PM

That's very helpful, I appreciate it. Is there a way to 'hide' a column of data so the graph can still use it but it's not visible?

Best wishes,
Luke

**Taiden** · 05-28-2009, 08:39 PM

Oh, what happens when we get to 2010? Wont the week numbers start over? Is there a way around this?

**teylyn** · 05-28-2009, 09:21 PM

Is there a way to 'hide' a column of data so the graph can still use it but it's not visible?

Yes. In Excel 2003 do:
- Select the chart
- go Tools - Options - Chart tab
- uncheck "Plot visible cells only"

what happens when we get to 2010? Wont the week numbers start over?

Yes, they will.

Is there a way around this?

No, every year starts with week 1. What we can do, though, is tell excel to combine the week number and the year number to make an individual reference. See attached version