Introduction to Excel 2007 Part 3: Bar Graphs and Histograms. Psych 209. Bar Graphs and Histograms. Although they may look similar, bar graphs and histograms differ in several important ways: 1. The types of information they convey 2. The x-axis 3. The y-axis
Introduction to Excel 2007Part 3:Bar Graphs and Histograms
1. The types of information they convey
2. The x-axis
3. The y-axis
1. Types of Information
1. Types of Information
2. The x-axis
- NOTE: Even though Instructor 2 gave no grades of 3.0, 3.3, etc., ALL consecutive values between 2.4 and 4.0 are included on the x-axis (you must do the same).
- NOTE: We could have included all values from 0.0 to 4.0, but this would simply shift the bars in the histogram to the right, leaving an empty space on the left side of the figure.
3. The y-axis
- NOTE: The values on the y-axis of a histogram must always be consecutive whole numbers (e.g., you cannot have a value appear 2.5 times in a data set, but it can appear 0,1, 2, 3… times).
Step 1: Enter the following data into Excel. Calculate the means for Instructor 1 and Instructor 2.
Step 2: Highlight the cells containing the two means, click on the ‘Insert’ tab at the top, and select ‘Column’ under the ‘Charts’ menu.
Step 3: Under the ‘2-D Column’ heading, select ‘Clustered Column.’
When your graph first appears, it will look kind of funny, and you will notice there are no chart or axis titles:
Step 4: First remove the legend (‘Series1’) by selecting it and pressing ‘Delete.’
Step 5: Then right-click on the chart and choose ‘Select Data’ from the menu.
Step 6: In the ‘Select Data Source’ dialog box, click on ‘Edit’ under ‘Horizontal (Category) Axis Labels.’ When the ‘Axis Labels’ box appears, highlight both of your variable names on the spreadsheet and hit OK twice.
Step 7: Now you will add a chart title and y-axis title under the ‘Layout’ tab:
Step 8: The last step is to fix the formatting of the y-axis, specifically the numbering scale. Right-click on the axis numbers, then select ‘Format Axis…’ from the drop-down menu.
Change the ‘Minimum’ value to 0.0, the ‘Maximum’ value to 4.0, and the ‘Major unit’ to 0.5 by first selecting ‘Fixed’ and then typing in your new values in the boxes. When you have finished, click ‘Close.’
That’s it! Your bar graph should now have a much more conventional (normal) appearance than when you first created it:
We will now turn our attention to creating histograms.
When you create a histogram in Excel, you need to make “bins.” Bins represent the entire range of values in your data set, and show how many times each value appears within your set.
First, let’s consider our variable: GPA is measured on a scale of 0.0 through 4.0, so we could potentially create bins for each grade (0.0, 0.1, 0.2….3.9, 4.0).
However, the scores (i.e., grade values) for Instructor 1 only range from 2.7 to 3.3. If we included bins for 0.0-4.0, we would have a lot of empty bins (since there are no scores of 0.0, 0.1, etc., represented in the data set.
Instead, we will use a bin range of 2.6 through 3.4, which includes all of the values in the Instructor 1 data set, as well as one empty bin on either end of the figure.
NOTE: Whatever bin range you choose, you MUST include ALL consecutive values between the lowest and highest score, regardless of whether that value is found in the data set. For example, even though the data set for Instructor 2 does not contain any grades of 3.3 or 3.4, we must still include these consecutive values in our histogram.
Now we will create a histogram using the Instructor 1 data set.
Step 1: Create a column for the bins. In this example, our bin range will be 2.6 through 3.4 (since the lowest grade value for Instructor 1 is 2.7, and the highest value is 3.3). This will give us one empty bin on either end of the figure. However, having one empty bin on either end is not required for histograms.
Precede each value with a single quote (‘) when entering them into the bin list, as in ‘2.5, ‘2.6, etc. Ignore any green triangles or caution signs. These indicate warnings but will not affect future calculations.
Step 2: Histograms depict counts or frequencies for specific data values. One way to count specific frequencies in Excel is to use the function COUNTIF. Create a label in cell E1 named “Frequency.” In cell E2 enter your function: =COUNTIF(B2:B11,D2).
B2:B11 is the data range we wish to count.
D2 is the cell that contains the value we are interested in counting.
In other words, this function says: count all values in our list of grades for Instructor 1 that are equal to 2.6.
DO NOT PRESS [Enter] YET!
Step 3: Next the formula must be copied for each of the other bins. When formulas are copied in Excel, cell information gets updated or changed automatically depending on the location to which the formula was copied. This is not good when there is a fixed data range, as with the list of class grades. To prevent the range of grades from changing, add a $ before each column letter and row number as illustrated:
Leave D2 unchanged. Remember that D2 contains the criteria that tells the COUNTIF what to count. We want that to be different for each bin that is going to be counted.
Now press [Enter] to store your formula. Cell E2 should show a 0 corresponding to no occurrences of that grade in the list of class grades.
Notice how this action formed a frequency table! Click on different cells in the Frequency column. Notice how the range specifying the grades to be counted does not change but the cell changes depending on the row.
Step 5: Select the cells in the Frequency and Bin column as illustrated. Select the Insert tab on the command bar and click Column. A drop-down menu of chart type options will appear. Select the left-most option under 2-D Column.
Steps 6-9: A general strategy for cleaning up the chart.
Fix y-axis units
Delete the legend
Provide missing axis titles
Excel automatically assigned the bin values as axis unit labels
Step 6: Remove the legend by selecting it and pressing Delete.
Step 7: Double-click on the chart title and change it to something more informative. (Note for your Excel assignment your title of the graph is your name and student number).
Step 8: The scale of your y-axis should NEVER include numbers with decimal places (remember these are frequencies). Change your y-axis scale by right-clicking on the numbers and selecting ‘Format Axis…’ Change the Major unit from 0.5 to 1
Step 9: Add axis titles for both the x and y axes by selecting the ‘Layout’ tab on the command bar, clicking on ‘Axis Titles’, and selecting the desired title style. Double-click on each axis title to activate and change it. Note that the y- axis title should always be “Frequency”.
Your histogram is now complete! If you have time, try creating one for the Instructor 2 data set.