Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am attempting to make a histogram in Excel. I would like the X axis to be a percent distribution.

    |                     |||
   |||                  ||||||  
  |||||               |||||||||
0 10 20 30 40 50 60 70 80 90 100


Sample  Percent MC Percent  Bins MC Average Average
1        100    37.15        0     41.31    99.048      
2        95.24  36.31       10                          
3        100    51.47       20                          
4        100    35.98       30                          
5        100    45.63       40                  
                            50                  
                            60                  
                            70                  
                            80                  
                            90      
                            100                 

How can I do this?

share|improve this question

migrated from stackoverflow.com Feb 16 '13 at 20:11

This question came from our site for professional and enthusiast programmers.

3  
support.microsoft.com/kb/214269 –  Tim Williams Feb 15 '13 at 19:13
    
Can you clarify how your data is arranged? I can't quite figure out what you're trying to show in your histogram. –  dav Apr 1 '13 at 20:16

2 Answers 2

I’ve had the need to create a histogram in Excel on occasion and every time I’m forced to relearn the steps. I recently came across a question on superuser.com regarding a histogram and decided to create this guide to remind myself and perhaps help others.

The original question on superuser.com asked how to create a histogram in Excel with the x-axis as the percent distribution.

I'm sure there are many ways accomplish this task, and this is one approach that uses the frequency function and an array-table.

Let’s assume the data is already a percentage and we want the x-axis to represent 10% increments from 10 to 100.

Data Set

The data could represent anything. The next step is to create our bins.

Bins

Notice that the label for Bins is to the left and not above; this is to let Excel know that Bins is an axis and not a data set when we create the graph later.

My spreadsheet now looks like this:

Data and Bins

Now that we have our data and our table, we will use the Frequency function to create the data for our histogram chart.

In cell C:26 enter, =FREQUENCY(B2:B21,B26:B35) and press enter.

Frequency forumula

It should look like this:

Frequency again

Magic happens here!

Now we use the Excel magic known as an array formula. The first step is to select cells C26 through C35 and type F2 on Windows or CTRL-U on a Mac to enter edit mode:

Array Forumala Magic

Now simply press CTRL+SHIFT+ENTER to make this an array formula and Excel does its magic!

Abracadabra!

Let’s add a label above the data and we are ready to create our histogram.

Label

Now select cells A:25 through C35 (everything you see here) and create a clustered column chart.

Histogram, finally!

And there you have it. You can dress the chart up in a number of ways. For example, I like to remove the y-axis.

without y-axis

You can also add percentages to the bars by creating a % of total column and charting that instead of the count:

percent of total

And the chart looks like this:

even better histogram

There you have it: a simple way to create a histogram in Excel.

share|improve this answer

I will assume:

  1. You want histogram of values' percentage with respect to full data set min and max (where min = 0% or 0.0, max = 100% or 1.0)
  2. You want to (and already know how to) use the Analysis Toolpack histogram capability ...

The key is to do the percentage calculations first, then use that as input to the Analysis Toolpack histogram capability.

Suppose A1:A1000 has the raw data. In B1, put the following formula:

=A1-MIN($A$1:$A$1000)

Copy and paste it through to B1:B1000. (This formula "normalizes" the raw data to start at zero and go up from there. It is still in the units of the raw data, not percentages.)

In C1, put the following formula, and copy it down through C1:C1000:

=B1/(MAX($A$1:$A$1000)-MIN($A$1:$A$1000))

(This formula calculates the decimal percentage, from 0 to 1, of each original raw data point.)

Make a bin range somewhere else (e.g., 21 bins: 0.00, 0.05, 0.10, 0.15, etc. up to 1.00). Use column C as the Analysis Toolpack histogram input range.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.