Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Histogram Chart in Excel

Example: E01a, E01b, E02a, E03a would be the bins.

Hi! I know how to use the histogram tool, but I was wondering if there's a
way to make it so that if a user changes some of the input values manually,
the histogram calculations are changed as well. Thanks in advance.

I'm using the Histogram function and trying to set my own bin values.
However, Excel ignores them and uses the default bin values. My bin values
are in ascending order and are formatted as a number. I do not get any error
messages, just the wrong bin numbers.

Thank you!

I have a dynamic hystogram chart that keeps track of current process
performance. So it is always changing as new data is entered.
I would like to have a vertical line indicating the average. (And if I get
that figured out I would add more vertical lines representing UCL and LCL
control limits)
I have tried methods using a secondary axis and error bars, but they don't
quite work because the scale for the secondary category axis and the primary
category axis are not the same, so the line is in the wrong place.
I could set the scales equal manually, but that doesn't quite work for me
because the charts are always changing and I need the scales to move
Any help on this would be awesome.
Thanks so much!

Trying to add a Gaussian (Normal Distribution) curve to a histogram using a
scatter chart over top of a bar chart.
The problem I have is that the scale of the histogram doesn't match the
scale of the Normal Curve. I could set them equal manually, but that is
impractical because the scale of the histogram is always changing as new data
points are added.
Can I plot the Normal Distribution on the same X axis as the histogram, or
can I have the scales of the two series match somehow?



i am trying to create a histogram. everytime i put in the input range i get
an error saying input range must be a contiguous reference. also i am trying
to go from one sheet to another which it keeps telling me that that name is
not valid. anyone got some ideas

I am trying to create a histogram and each time I begin to proceed a screen
pops up that asks me to entere the input range and bin range. I have tried
to do this a numebr of times and I have not been successful. This is the
first time I am trying to create the hisogram so I am not sure how to enter
this information.


I am wondering if there is a formula/script which will do the following:

1. Plot the histogram
2. Calculate Weighted Average
3. Calculate Standard Deviation

of a given sample data.

I am attached a sample file with data in it.


I want to make a histogram graph with the percentages of total frequency on the y-axis instead of the frequency. Is this possible?

I found here how to make a histogram, but it's only with frequency: http://www.ehow.com/how_4480465_make...oft-excel.html

I am attempting to create a Relative Frequency Histogram with the following
values. Total population is 217. Sample values are 23, 33, 63, 68, 19, 10,
1, 0. How do I create a Histogram showing this as relative frequency

Hello. I'm trying to create a column based histogram with 50 as the centering point. As an example, if you normally had one column of negative numbers and one of positive numbers you could create a histogram with 0 as the center point and the positive numbers plotted above and the negative numbers plotted below. How might I accomplish that when all numbers are positive? I'd like 50 to be the center line and numbers 51-100 plotted above and numbers 49-0 plotted below.

Here is an example.



At the risk of sounding a little dumb, does anyone know how to put a line graph over the top of a normal histogram?

There used to be a little button to click which basically did it for me but I'm now working on this new fangled excel (windows 7) and it's rather confusing!

Many thanks

Okay, I have a set of a data. The data is a bunch of decimal numbers in each row. I saw how to make a histogram but how can you make a histogram where the bin is a range such as from 0-1,1-2,2-3 and such. Thanks for your help.

I want to draw some histograms of data on a worksheet, and I want a macro to do this.

I switched on "record macro", and drew a histogram of some of the data using Data > Data Analysis > Select Histogram and then filling in the ranges and so on. Then I stopped recording and looked at the resulting macro.

This looked like :
Application.Run "ATPVBAEN.XLAM!Histogram", ActiveSheet.Range(suchandsuch).........
.....(more stuff)

So then I rubbed out the histogram data columns, I rubbed out the chart of the histogram, saved everything, close the Excel workbook, re-open the Excel workbook.

Then I ran macro3, expecting it to re-draw the histogram.

But it didn't. It gave a debug error, highlighting in yellow the item I have summarised between <code>..</code>

Could someone explain this to me, please ? What should I have done ?

Good morning folks!

Those "False, False, False, False" parameters to run a histogram, whatever do they mean?

Application.Run "ATPVBAEN.XLAM!Histogram", Range("Inputs"), Hist.Offset(0, 3), Worksheets("OutputSheet").Range("Outputs"), False, False, False, False

Through recorded code, I've seen that to create a histogram, the third False is a "True"... but let's say I've already got the blank histogram created...

I'm running a simulation (where the inputs range is being updated with every run) , and I want the histogram to be filled in at the end of all the runs. What do these parameters have to do with it?

Having them all as false, like I've been told, isn't working..

Greatly appreciate any tidbits of wisdom thrown my way!


Hi, I am attempting to create a histogram using Analysis Toolpak in Excel 07. Every time I choose the option "chart output", it causes Excel to crash. Does anyone know why it's doing this?

wish to display two different sets of data on the same histogram chart?

the data is as follows

Bin Frequency
(7.7)% 1
(6.2)% 6
(4.8)% 3
(3.3)% 7
(1.8)% 9
(0.3)% 17
1.1% 121
2.6% 30
4.1% 22
5.6% 8
7.0% 7
8.5% 3
10.0% 3
11.5% 1
12.9% 1
More 1


Bin Frequency
(12.9)% 1
(11.0)% 1
(9.2)% 4
(7.4)% 6
(5.6)% 8
(3.7)% 12
(1.9)% 19
(0.1)% 37
1.7% 50
3.6% 49
5.4% 28
7.2% 13
9.1% 6
10.9% 3
12.7% 2
More 1

All help gratefully received?

I have a histogram that I construct using the frequency function. The bin values are dynamically constructed from one use of the graph to the next, because of the different data I am examining.

Is there any way to show the mean (and potentially other statistical points e.g. 95%, UCL, etc. on the histogram, perhaps something like the attached mock-up?

I've tried sorting the mean value into the bins column, but because of the frequency array function, it can't be done that way.

Any suggestions?

Thanks in advance,


I calculate number of flights falling in hour interval, so with the help of histogram - where my input range is departure time and with bin for interval(00:00,01:00 and so far) , I find out the number of flights in the one hour intervals from 00:00-23:59 Hrs. How do i calculate total number of pax in these hours? Histogram just gives me two inputs.
here is the table
Airline (Arrival) Flight No. (Arrival) SCHEDULE TIME No. of Seats xxx 105 18:30 180 xxx 111 9:05 180 xxx 121 11:45 180 xxx 121 12:20 180 xxx 123 10:40 180 xxx 131 23:15 180 xxx 151 9:35 180 xxx 153 19:05 180 xxx 153 19:05 180
here is the histogram result
Bin Frequency 1:00 1 2:00 0 3:00 0 4:00 2 5:00 0 6:00 2 7:00 6 8:00 9 9:00 11 10:00 37 11:00 10 12:00 9 13:00 5 14:00 3
till 23:00-00:00 ----Now this gives me number of flights in the interval,How do i get sum of pax in the interval?(preferably through histogram)

I have a data base made up of 5 populations of data. I would like to create histogram data for the total of all 5 populations plus histogram data for each population. I tried to create histogram data using data filters but that does not seem to work. The only way I see how to do this is to:
1) filter the data
2) copy the filtered data to a new sheet
3) create the histogram data for the entries on the new sheet.

Is there an easier way?

I need to plot water level data and rainfall data on the same graph. Unfortunately, the water level data is an x-y scatergram with dates on the x axis while the rainfall is plotted as a histogram. When I plot them together with he rainfall on a secondary y axis, the dates will not match up. I presume because the histogram data are sequencal and not directly related to dates.

Any suggestions on getting them aligned?

Hello all!

I hope this isn't one of those forever re-posted questions, but I have spent a couple of days looking over the archives on this site, as well as experimenting with Excel 2007, and am coming up a bit short.

Simply put, I would like to take a column of date/time strings, for example

1/6/2010 12:01

and create a series of ranges, for example 1/6/2010 12:00 - 1/6/2010 12:05, and sum the number of input times that appear in each of the 5 minute ranges. I've seen a variety of postings on this topic, but none that I've been able to put together to really nail this.

I have been experimenting with "Data Analysis" tab in Excel, and do have all my date input cells created as actual date/times (=ISNUMBER(CELL) = TRUE), but everything currently seems to land in my last bin, not distributed.

Again, my apologies for being redundant, but would sure appreciate any guidance! Thanks all!


I want to write a macro to make a Histogram using the built in Histogram Function in Data Analysis. I created Named Ranges called Data and Bins.
Then I wrote the following macro, but get errors.

Sub Plot_Histogram()
' Plot_Histogram Macro
Histogram(Data, "", Bins, "", "", True, False)
End Sub

In VBE I have referenced ATPVBAEN.XLS which has the Histogram function, which shows parameters as follows:

Sub Histogram(inprng, [outrng], [binrng], [pareto], [chartc], [chart], [labels])

I just don't seem to get the syntax right and don't know what I am doing wrong.

I am able to make the Histogram manually by putting in Data and Bins in the function GUI. But I want to do it with a macro. Can anyone help me understand how to call this built in function correctly ? Thanks


I am having some problem with excel. I know how to create a histogram and a normal distibution curve, but what I want to do is after creating a histogram superimpose a normal distribution curve over the histogram. Can you please give me some info on how to do this! Treat me as a newbie.

Thanks in advanced.


Hello everyone,

what I am trying to achieve with the final graph is:

1) horizontal axis: the continuous variable of interest with different class intervals
2) vertical axis: the frequency density given by Frequency/Class width

....I have no problem doing the first 2 points...the tricky bit is the following

3) I want to insert the data label in every column, which in this case should be represented by the frequency (the area) given by width*height of every rectangle
Whenever I try to insert data label it comes up with the data of the vertical axis (density frequency) and not frequency...

Hope I've been clear enough...


Why do I have More with a number in the results?
How do I get rid of the More and number?
Without having to delete and resort.
Thanks for your help in advance.

Hi! I used the two suggestions on this thread:
to make a graph with the frequency distribution (histogram) and normal distribution curve.

However, I have a question about how I can make sure that the values on the Y axes are correct such that the curve is fitting the histogram correctly. That is, I can change the max in the Y axis to make the curve go above or under various parts of the historgram, so how do I know what the correct values are such that they historgram and curve are exactly where they should be in relation to each other? This is more of an issue now that I'm trying to fit two different data sets onto the same graph (pre-intervention vs. post-intervention) and the curves are different heights, and the frequency distributions have different max values.

I have lots of data in the form of a histogram. Example:
A1 = 0-10 range, B1 = 500 counts in that range
A2 = 11-20 range, B2 = 345 counts in that range
A3 = 21-30 range, B3 = 756 counts ...

and so on.

Is it possible for me to calculate the standard deviation? for these 1601 data points?

Thank you.


basically, the title says all

I have some data, that, in-between, has some non-numeric (logical) data: FALSE;

I.e. the data is like


etc etc.

When I use the histogram maker in the Data Analysis Toolpak and select as "input range" the data above, it says that "the data contains non-numeric data."

Is it possible to somehow do this directly, ignoring the non-numeric AND/OR logical data? or will I have to use another column, and copy the values over, leaving out the logical values with some IF clause.