Bar Graphs

The procedure for making bar graphs is, not surprisingly, very similar to that for making scatterplots. In your Excel workbook, switch to the second worksheet ("Sheet 2"). Click on an empty cell next to the data, and then click on the "INSERT" tab:

This time we will be selecting the bar graph icon, indicated by the red arrow in the preceding diagram. From the resulting menu, select the leftmost bar graph icon from the "2-D Column" list, as indicated by the red arrow in the following picture:

As with the scatterplot, click on the "DESIGN" tab of "CHART TOOLS" (click on the graph if the dark green "CHART TOOLS" tab is not visible), and then click on "Select Data". Then click "Add" to add a series. The menu that this brings up is different from that for the scatterplot in that it does not have a place to select both the X and Y axis. When you select a series here, you will be selecting the values for the Y-axis, i.e., the dependent variable. We will sort out the X-axis later.

In the text box for "Series name", type in "Column C". Then, using the button to the right of the text box (the same way that you selected data for the scatterplot), select the numbers in column C, and then click "OK". If you can see your graph now, you will notice that the X-axis ranges from 1 to 10. To get the values that we want to use, from column A, you will need to select "Edit", under the "Horizontal (Category) Axis Labels", as indicated by the red arrow in the following picture:

Select the values in column A, hit "OK", and then hit "OK" again. Your graph should look like this:

Move the graph to its own sheet ("Chart 2"), and remove the chart title and gridlines, and add axis labels as before ("Independent" for the X-axis, and "Dependent" for the Y-axis). Then make the axis titles 18 point font, and the axis numbers 14 point font, and change their color to black as before. If you are having difficulties, refer back to the instructions for the scatterplot. Also make the lines for the axes black, as before.

Now, we are going to add a second series to the graph, by selecting "DESIGN" under "CHART TOOLS", and choosing "Select Data". Click on "Add", and name the second series "Column E". This time, to select the data, after pressing the button, you will have to click on the tab at the bottom of your worksheet to bring up "Sheet 2". Select the data in column E, click "OK", then click "OK" again to return to your graph, which should now look like this:

Click on the plus sign on the upper right of the graph (where you removed the chart title and gridlines) and select "Legend" to add a legend to your graph. You will see that the series titles that you used when you selected the data appear on the legend. If you decide later on to change the names, you can do so by going back to the "Select Data" menu, and editing the series.

Click on the legend to put it within a selection box, change the font to 16 point, and make the color black.

Whenever possible, we want our graphs to be in black and white. Click on one of the blue bars to highlight that series, and bring up the "Format Data Series" menu on the right hand side of the graph. You may have to double-click on the bar. As with the scatterplot, make sure selection boxes are around all of the bars, and not just one, or you will change only a single bar. If the latter occurs, click somewhere else on the graph, and then click on a bar again.

Select the paint can icon in the "Format Data Series" menu. This should give you a menu that includes "FILL" and "BORDER". Select "FILL", click the circle next to "Solid fill", click on the paint can icon next to "Color" to bring up the color palette, and select one of the black squares. Now click on "BORDER", click the circle next to "Solid line", and select a black square from the color palette.

Now, click on one of the orange bars, and repeat the process, except that you will select a white square from the color palette for the "FILL" menu. Alternatively, you could click the circle next to "No fill" in the "FILL" menu to achieve the same effect.

The last thing that we are going to do is get rid of some empty space by making the bars wider. Click once again on one of the bars (it doesn't matter which series) to bring up the "Format Data Series" menu. This time, select the icon with the bars (red arrow below) to bring up the "SERIES OPTIONS" menu. The parameter we are going to change is "Gap Width" (blue arrow below):

Highlight the current gap width, and change the value to 50%. To my eye, this is a much better ratio of bar to space.

The formatting that we covered, including font sizes, colors, and gap widths, is the formatting that you must use for ALL of the graphs that you will generate for the assignments for this course, so make sure that you understand all of the necessary steps.

Save this spreadsheet, and submit via Blackboard along with the Word document (also using your last name plus "ex1" as the filename) where your answers and the first scatterplot are saved.





Week 1 Objectives

Understand how to use the fill function in Excel

Understand how to create and copy formulas in Excel

Understand how to control the frame of reference when copying formulas in Excel by using anchors ($)

Understand how to create and format bar graphs, line graphs, and scatter plots in Excel





Send comments, suggestions, and corrections to: Derek Zelmer