Introduction to Excel 2007 Part 3: Bar Graphs and Histograms

1. Introduction to Excel 2007Part 3: Bar Graphs and Histograms Psych 209 University of Washington

2. 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 • Let’s say Instructors 1 and 2 both teach the same Psych class. You are trying to decide which instructor to take the class from (based on the grades they gave last quarter). • First, you take a look at a bar graph…

3. 1. Types of Information • Bar graphs compare the mean scores for two or more groups (i.e., levels of a variable). We can see from the bar graph above that the average grades given by Instructors 1 and 2 last quarter are identical. We cannot compare the individual grades given by each instructor, though, to see if they differ in terms of grade distribution....

4. 1. Types of Information • Histograms show the frequency with which a value appears in the data set (e.g., how many students received a grade of 3.0 from Instructor 1). Thus, histograms represent ALL of the values found within a given data set. Based on the histograms above, from which instructor would you choose to take Class A? Why?

5. 2. The x-axis • The x-axis of a histogram includes the range of possible scores in the data set. In our example, students received grades ranging from 2.4 to 4.0, so the x-axis includes these values. - 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. • The x-axis of a bar graph simply identifies the levels of the independent variable (i.e., the groups you wish to compare: Instructor 1 and Instructor 2).

6. 3. The y-axis • In 209, the y-axis of a histogram is always labeled “Frequency,” since it represents a count of how many times (how frequently) each value appears in the data set. - 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). • The y-axis of a bar graph always represents values of the variable and must be labeled as such. Since these values are means, rather than counts, the y-axis does NOT have to contain only consecutive whole numbers.

7. Creating a Bar Graph 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.

8. Step 3: Under the ‘2-D Column’ heading, select ‘Clustered Column.’

9. 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.

10. 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.

11. Step 7: Now you will add a chart title and y-axis title under the ‘Layout’ tab: • For ‘Chart Title’ you want the ‘Above Chart’ option. Type your title and press Enter. (Note for your Excel assignment your title of the graph is your name and student number) • For ‘Axis Titles’ add a vertical (y-axis) title by choosing the ‘Rotated Title’ option. Type your title and press Enter. • NOTE: You could add an x-axis (horizontal) title below your variable names, as well, but we won’t for this example.

12. 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.

13. 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.’

14. 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.

15. Creating a Histogram 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.

16. 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.

17. 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.

18. 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!

19. 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.

20. Step 4: Copy the COUNTIF formula from cell E2 through all the cells in column E down to cell E10. A quick way to do this is to: • Make sure that cell E2 is selected, • Move the mouse cursor close to the little square in the lower right hand corner of the selected cell, • Press and hold the left mouse button while dragging downward to the bottom of cell E10. Release the left mouse button once the correct range has been selected. 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.

21. 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.

22. Steps 6-9: A general strategy for cleaning up the chart. Fix y-axis units Update title Delete the legend Provide missing axis titles Excel automatically assigned the bin values as axis unit labels

23. 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

24. 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”.

25. Your histogram is now complete! If you have time, try creating one for the Instructor 2 data set.