Excel – How to create bar charts with ranges as the x-axis in Excel 2010

chartsmicrosoft-excel-2010

I am trying to create a bar chart for say some data, say for example

1 1 2 2 3 4 5

I want to create a bar graph so each bar shows the frequency of a range. For example, the frequency of a value in my data set that is between 1 and 3, is 5. So there would be a bar that goes up to 5 on the y-axis. The next bar would go up to 2 on the y-axis since the range would be from 4 to 5.

How would I do this in Excel 2010?

Best Answer

NOTE: This question is vague in the sense of exactly what you are dealing with, but there is enough information here to answer the question. You will have to alter things in this answer to fit your exact scenario.

The easiest way may be to create some helper cells to count the occurrences which fall into the groups you have outlined. Then from those results you can create a chart which has the bars as you described.

In the example below, I used the data you described with the COUNTIFS function (click the link to read more about the function).

enter image description here

To do this, choose cells outside your working data table. Then enter the formula so it checks the range of your data. I added the data to A1 thru G1. Then I used A3 and B3 for labels while the formula goes in the cells directly below. In A4 the formula counts numbers greater or equal to 1 and also numbers less than or equal to 3. You will notice the range must be given for each criteria.

=COUNTIFS(A1:G1,">=1",A1:G1,"<=3")

Using the same format, the formula was placed in B4 to count numbers between 4 and 5 is as follows;

=COUNTIFS(A1:G1,">=4",A1:G1,"<=5")

With the numbers counted, select A3 thru B4 and choose to insert a chart of your choice from the Insert tab.

enter image description here

Related Question