A histogram is usually shown in
Excel as a Column chart type (vertical bars). The labels of a Column chart are
aligned under the center of each bar, and there is no Excel feature for changing the
alignment. For continuous-valued data, a better histogram has a horizontal axis with numerical labels
aligned under the tick marks between the bars as shown below.
Click the Download button to download a ZIP file containing (a) the free XLA add-in file for automatically creating a better histogram from data on an Excel worksheet and (b) the How-To-Install-Addin.PDF file.
The PDF file has detailed instructions for opening or installing the TreePlan, SensIt, and RiskSim add-ins, and the same instructions apply to the Better Histogram XLA add-in file.
You may want to use Excel's MIN and MAX worksheet functions to determine the minimum and maximum values of your data before using the Better Histogram add-in, so that
you can decide on "nice" intervals for your histogram.
In the Excel worksheet containing your data, choose Tools | Better Histogram (Excel 2003 and 2011) or choose Add-Ins ribbon | Menu Commands section | Better Histogram (Excel 2007, 2011, and 2013). The following dialog box appears.
The data range must be a range of
cells containing only numbers, i.e., no text. The start value must be a number
less than or equal to the minimum value of the data range. The step value must be greater
than zero. The stop value must be greater
than or equal to the maximum value of the data range. The start, step, and stop values must be chosen so that there are at
least two intervals.
When you click OK, the frequency
distribution and better histogram are created on a new worksheet. You can modify
the chart titles as you wish. If you want to modify the horizontal axis scale, it is important to align the values of the XY (Scatter) chart type with the vertical bars of the Column chart type.
(The original method for creating a better histogram using Excel 2003 is described in detail in the paper Better-Histograms-Using-Excel-2003.pdf. The manual method is automated by the Better Histogram XLA add-in from the ZIP file available when you click the "Download Better Histogram" button above.)
|
