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

excel charts formatting bucket
Bruder picture Bruder · Mar 19, 2012 · Viewed 63.9k times · Source

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

Answer

Cutter picture Cutter · Mar 19, 2012

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

Related questions

Swap x and y axis without manually swapping values

So I have a set of data that has conditional formatting. Basically it's a set of photometric measurements of stars that I want to put on a chart with different colors for stars in different ranges of Color Index (basically …

Read More
Reordering Chart Data Series

How does one reorder series used to create a chart in Excel? For example, I go to the chart, right click > Select Data. In the left column I see series 1, series 2, to series n. Say, I want to move …

Read More
How can I color dots in a xy scatterplot according to column value?

Consider the following worksheet: A B C D 1 COMPANY XVALUE YVALUE GROUP 2 Apple 45 35 red 3 Xerox 45 38 red 4 KMart 63 50 orange 5 Exxon 53 59 green I have used the scatterplot function in Excel to create the following chart: However, each point in the chart has …

Read More