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


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.

  • Yes, the built in tools make sense. – jonsca Jul 9 '11 at 3:17
   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.

  • I inadvertently Americanized your dates, but same idea. – jonsca Jul 8 '11 at 21:21

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.