plotting bar graph with dates axis

**Rezez88** · 01-12-2011, 09:22 PM

Hey guys,

I've attached a sheet that I'm currently working on. This might be really easy, but I need to construct a graph with the months on the x-axis (sorted from column C of the LYNX sheet) and for each of these months I want the number of each fault type (Column E of the LYNX sheet).
So the graph will have 7 bars per month with the numbers of each fault. If there is a better way of doing this, please let me know.

Cheers,
Pete.

**teylyn** · 01-12-2011, 10:18 PM

Hello,

this can be done very quickly with a pivot chart.

Make sure your data does not include blank rows at the end, then you can group the Date Reported by month. Drag Date Reported into the row area, the Fault Type into the column Area and again into the Data area, then use the Count option in the field settings.

Attached was created with 2010 but in compatibility mode.

cheers,

**Rezez88** · 01-13-2011, 01:38 AM

Thanks for that teylyn, I will learn how to use these tables. I will need blank rows at the end to keep adding data to it. Is there any way of doing this, possibly with some code??

Cheers,
Pete

**teylyn** · 01-13-2011, 04:02 AM

You can use dynamic range names to define your pivot table. In your attachment, the pivot table wizard automatically selected over 500 rows of data, probably because you have a formula copied all the way down into empty cells (not a good technique; there are better ways).

You should explore dynamic range names based on the number of values in column A, for example. Check out http://www.contextures.com/xlPivot01.html

cheers