The VBA code on this page assumes that the source data for the histogram is in another workbook, and the histogram will be put on a new sheet in that workbook. It also contains a simple userform, which is only referred to on this page. You can copy the code and paste it into a VBA module, but it is easier to download the zipped workbook with the code. The macro calculates interval size, counts frequencies and makes the histogram chart. The second example shows how to make a histogram with a user defined number of columns. In the first example I show how to make a histogram with a bell shaped curve like in Microsoft's example (like the image at the top), and you don't need Analysis Toolpak. You can make histograms manually, if you have installed Analysis Toolpak, but it is tedious work (see a guide here) - it is much, much faster to use macros. To be sure about the normal distribution-thing you need to test statistically, but I'll leave that to you - this is about VBA! It could be the population's age distribution or quality data, and from the shape you get a pretty good idea if it is a normal distribution or not. I also show how to make a bell shaped curve with normal distribution and the same standard deviation and mean value as the histogram.Ī histogram is a graphical representation of data distribution. Save the workgroup document to preserve your histogram.On this page I show how to make a histogram fast and easy in Excel with VBA macro code. Select 'Axis Labels' followed by 'Horizontal Axis Title' and type a horziontal access title in the space on the chart.Ĭlick on any bar in the histogram to select all bars, and then choose 'Format Data Point' in the context menu.Ĭhange the value in the 'Gap Width' box to '0%' to remove all space between the bars in the histogram, and then click the 'OK' button. Click the 'Vertical Axis Title' button and enter a vertical axis title in the space on the chart. Select the 'Axis Labels' button in the Labels group on the ribbon. Enter a title for your histogram in the space provided on the chart.

You can select and drag the chart to a convenient location on the screen.Ĭlick anywhere on the chart and select and highlight the data cells in the table again.Ĭlick the 'Chart Layout' tab in the main menu ribbon. Excel displays the selected data in a histogram format. Click the 'Column' button in the Insert Chart group, and then select the 'Clustered Column' option. Don't include the data labels that describe each column or row of data.Ĭlick the 'Charts' tab on the main menu ribbon. Select and highlight the range of data cells in the table.

How to plot a histogram in excel