Chart data by month

Question

I've the following data in an excel spreadsheet:

Date (dd/mm/yyyy)     Value
----------------- ---------
   01/04/2011        123.45
   03/04/2011       7234.25
   16/05/2011         80.00
   11/06/2011        223.36
   12/06/2011        455.97
   29/06/2011       2345.12

I want to insert a chart with Month in the X axis and Value in the Y axis (that is, value sumarized by month).

Can I do that with a scatter chart (or using any other excel feature) without adding auxiliary calc in other cells?

If I can't, how can get the chart described with auxiliary calcs?

Note: I'm using excel 2010

Answer 1

Use a Pivot Chart and add month/year grouping on the Date field and your summarization choice (sum, average, count, etc.) on the Value field.

Answer 2

   A        B    C   D
1/12/2011   1   200 200
2/13/2011   2   34  34
3/24/2011   3   1   79
3/26/2011   3   78  22.5
4/10/2011   4   10  0
4/12/2011   4   12.5    

Where the formula in D is =IF(YEAR(A:A)=2011,SUMIF(B:B,"="&ROW(),C:C),0) and where column B is defined by Month(a:a)

It checks to make sure the dates are in the current year (skip if you don't need this), and then sums the values from months 1,2,3,4... into rows 1,2,3,4.

Then, make your chart out of column D like you normally would.

Answer 3

Based on your data, Excel will choose to plot the data on a daily scale (my top chart below). You can format the axis scale to use Days for its Base Unit, which will plot all data by month (my bottom chart). This gives you a marker for each data point, so multiple points for April and June.

If you want the data summarized (averaged or summed by month), use a pivot table as @mike-fitzpatrick suggests.