Duplicate x-axis labels in column chart

Question

Hi!

I am using Excel 2010 on a Windows 8.1 OP.

I am trying to make histograms of air particulate concentration (y-axis) and weather data (x-axis).  There are many instances where the value of weather data is repeated on different occasions. Rather than culminating all the spores that occur at a specific weather value (giving one bar per weather value) the column chart makes multiple bars (a new bar for every instance of a certain weather value). I have made an example of what I am getting:

I want to have one bar per weather value (see below), not duplicates for every instance of a value. Is there a way to do this with out manually having to go through and sum everything? The data set is very large. Any help would be appreciated :)

Answer 1

The Histogram tool is not appropriate for your data. I use the term "histogram" to refer to a column chart of a frequency distribution, and the Excel Histogram tool requires raw data as input. Your data is already partially grouped, and you seem to want a method for grouping the data further so that you can create a Column chart like the "Cumulative" chart shown in your original post. You mentioned that the data set is very large, so I'm reluctant to propose a solution for your example data, because the solution may not be appropriate for your larger data set. A solution for grouping could use Data > Filter or Insert > PivotTable or worksheet formulas like SUMIF or SUMIFS.

Answer 2

Hi Sherri

I guess the hidden decimals are responsible for what visually is a repetition but in fact they are different values. I have the following suggestions:

1. If you care for decimal places, then go to axis options and under Number format increase the decimal place for x-axis
2. If you do not care for decimal places, then add one column each for Temp & Spores and add a rounding formula to round the figures to nearest whole no. Then base your histogram on the rounded nos.

Hope this helps

Cheers

Khalid Suddle

Answer 3

Hi Khalid,

Thank you for your suggestions! However, I do not think this is my problem. The axis are formatted in a general format, and (in the actual data set) the labels are displayed to 1 decimal.  It is definitely a repeating value, not different values with hidden decimals. Even if I change the format of the axis labels, it does not fix the problem.

The closest I have come to getting it to kind of work, is using a scatter XY plot. This groups the data how I want, but I need it presented as columns rather than markers.

Cheers,

Sherri

Answer 4

You may get more relevant advice if you describe in detail how you are preparing the histogram. 

Are you using the Histogram feature of the Analysis ToolPak?

In Excel 2010 the Histogram feature is accessed using the Data Analysis item of the Data ribbon.

That feature creates a frequency distribution of the worksheet data and displays a Column chart.

Another way is to use my free Better Histogram add-in, available from

http://betterhistogram.com/free-download/

Answer 5

Hi Mike,

Thank you! I did not know there was a histogram function in Excel. I was simply making a column graph of my data. I have now installed the Data Analysis ToolPak and have tried to make a histogram of the same sample data I used previously. It lists the categories on the x-axis properly, but it only gives one bar.

I am not sure why, I selected the cells under "spores" for the "input range" and the cells under "temp" for the "bin range"...what am I doing wrong? I tried to use the Excel help topic, but they were...unhelpful.

Thanks, 

Sherri

Data	Histogram Data
Temp	Spores	Bin	Frequency
20	3	20	11
21	4	21	0
21	4	21	0
22	5	22	0
22	15	22	0
23	8	23	0
24	10	24	0
24	11	24	0
24	20	24	0
25	20	25	0
25	6	25	0
		More	0