Can I display daily data in month buckets using only excel's chart formatting?

Question

I have daily sales figures that I'd like to plot on a simple linegraph.

I would like them to be shown in monthly buckets (i.e. if I sold 5€ on Jan 01 and 10€ on Jan 24, I would like to see only one data point for January with 15€ in it).

Please note that I don't want to use any supporting formula/VBA script, I want to do this using only chart formatting.

I tried setting the chart's X-axis type to "date axis" and I chose "months" as the base unit. This almost works, but the line graph ends up being kind of weird. Changing the chart type to histogram doesn't help much either. The individual sales are not "piled up" like I would but, instead, they're hidden one behind the other. Stacked histogram doesn't work either.

Any clue on how I can force excel to bucketize my data using only chart formatting? This can't be that hard...

Solution 1

Try creating a pivot chart, then grouping the dates by month (right-click a date in the pivot table, and click "Group by... Month".

Solution 2

If you want to group by Years as well - hold down the Ctrl Key whilst you select the Month (both categories will then show up)

Comments

[Admin]

Works like a charm! Only thing is, I had to group by both month and year, otherwise Jan 2011 and Jan 2011 get summed together. I didn't specify this in my question so my bad. Thanks man!

[mikemaccana]

Creating a new pivot chart isn't an example of using chart formatting (and is quite difficult in itself)

[gkubed]

Just to clarify, but to group by month in Excel 2007 you right click on a date in the chart, not the actual table. After this, it's helpful to right click on the chart and choose Expand/Collapse > Collapse Entire Field.