the groups that will correspond to the bars in the chart. In a column, list the midpoints of several
value ranges that cover the range of the sample evenly. You specify how many ranges by the
number of bins you create, and you may need to experiment with that to yield a useful Histogram
- not too many since the purpose is to summarize, but enough to show the main pattern and
pattern of distribution. Too much aggregation will hide important patterns.
Click TOOLS and DATA ANALYSIS, then HISTOGRAM. In the dialog box, specify
the data range for the values of the variable you want to analyze, and then the bin range. If you
want a cumulative percent, check that box. Click Chart Output to have Excel make the chart
automatically. Click OK, and that produces a table that lists the count of observations that fall
within the bin, the frequency, and the Chart. You can also create a column that has relative
frequency, the percent of observations in that bin.
Click Chart Wizard and Column chart. Put the count or relative frequency from the
Histogram results in the variable box and use the bins as the Category (X) labels. Follow
through the remaining steps of Chart Wizard to label the chart and save it. The result is a column
graph where the height of the columns indicates the count or relative frequency of observations
in evenly spaced groups.
The HISTOGRAM option at DATA ANALYSIS allows you to create a Chart Output for
the frequency table. It will not allow you to chart two or more variables together. For that you
can use HISTOGRAM on each variable individually to create the frequency table, and then use
and Excel Chart to create a Histogram for two or more variables together.
2) Line
Choose a Line chart for time series data. You can chart several data series on the y-axis
against a label, such as the year or other marker of time, on the x-axis. The x-axis is entered in
the Chart Source Data step as the Category (X) label. This is especially useful for monthly or
quarterly dates which might have the form: 1999:12 or 2003 Q2. If these are chosen as the X
variable in another type of chart, the months or quarters for each year will be condensed. The
Category (X) treats these as names or labels for each observation rather than values of a variable,
so you can’t use them for numerical calculations.
If the data proceed in a definite time pattern, you might want to choose a chart sub-type
that is a connected line to emphasize the pattern over time. If the data oscillate or change
dramatically back and forth, the connecting lines will crowd the chart and obscure the pattern.
Decide whether the time sequence matters for what you are presenting in the chart.
3) XY Scatter
When association, such as a correlation, is important, the XY Scatter chart is useful. If
you select the data before opening Chart Wizard, make sure that the variable you want on the x-
axis is in the column on the left and the variable for the y-axis is adjacent on the right. If you
open Chart Wizard first, you can have the data in any order as you will select each variable
separately. The Name box refers to the variable to be plotted on the y-axis, and then the Chart
Source Data step, Series tab, allows you to select ranges for the X values on the X-axis and the
variable values for the Y-axis. These are variables with numerical values, so you can use them
for a calculation, such as fitting a trend line.
Again you may just want a scatter diagram of dots if the relation between x and y is most
important and independent of the sequence of observations. If the sequence or ordering of the
observations is meaningful, you can select a chart sub-type that connects the points with a line.