We are going to work on making 2 different types of graphs: scatterplots, and bar graphs. Scatterplots are used when dealing with continuous data (where values can be measured as fractions, e.g., temperature) for the independent variable, but also can be used when the independent variable consists of discrete data (where values are measured as integers), if the discrete data are meant to be representative of continuous data. As an example, time is most certainly a continuous variable, with the precision, i.e., number of decimal places, being constrained only by how much money you have to spend on a timepiece. Often, however, we collect temporal data in a discrete manner, e.g., day 1, day 2, day 5, but still would wish to display it as a continuous variable.
Go back to Sheet 1. The first step in making a graph in Excel involves selecting the graph that we wish to use. Unfortunately, doing so will insert the graph into the spreadsheet, which is not desirable, because it can make things confusing. Also, if you do not tell Excel where to insert the graph, it likely will pick data for you (that are almost never the data that you wish to be graphed), and make a graph for you. To avoid the unnecessary step of having to delete series from the graph, click on an empty cell in Excel before undergoing the procedure to insert the graph. From the top toolbar, click on "INSERT", right next to the "HOME" tab, to bring up the following menu:
To select a scatterplot, click on the icon with the dots, indicated by the red arrow in the preceding picture. You will be presented with a menu of choices that looks like the following:
The only choices we will be interested in are just the dots (red arrow) or just the straight lines (blue arrow). The options with the curved lines should NEVER be used, because filling in those trends implies information that we do not have , i.e., it is not a representation of our data, or a model we have fit to the data for some specific purpose.
For now, click on the option that just has the dots, as indicated by the red arrow in the preceding picture. This will insert a graph into your page. If you remembered to click on an empty cell prior to inserting the graph, you should get a nice blank canvas. If there are data on your graph, just click on it, delete it, and start over, or keep reading to find out how to delete series.
Before moving the graph to its own sheet, we will go ahead and select the data while they are on the same sheet. Inserting the graph, or clicking on it, should light up 2 tabs in the tool bar with a dark green color: "DESIGN" and "FORMAT", under "CHART TOOLS":
Select "DESIGN", as indicated by the red arrow in the previous picture, to bring up the tool bar that has "Select Data" off to the right, as indicated by the red arrow in the following picture:
Clicking on "Select Data" will bring up the following menu:
The top text bar is "Chart data range:", and is not something we will be using unless we forgot to click on an empty cell before inserting the graph, in which case we click on the text box to highlight the data range, and promptly press "Delete" on our keyboard. If, however, we started with a nice, blank canvas, we can now select the data that we wish to use, by clicking the "Add" button, indicated by the red arrow on the preceding picture. This will bring up the following menu:
"Series name:" allows you to provide a name for the series that will appear on your legend if you choose to add one to the graph. Let's name the first series "X-squared", by typing it into the text box. Next, we will select our X-coordinates from the data. To do so, click on the button with the little red arrow (indicated by the big red arrow on the preceding picture), next to the text box for "Series X values:". This will being up a smaller text box:
Again, click on the button next to the text box, indicated by the red arrow in the preceding picture. Now, use your mouse to highlight the data in column A, i.e., highlight cells A2 to A11. Then click the button next to the text box again, and you will see that series filled in for the text box for "Series X values:".
Do the same thing for the "Series Y values", this time selecting cells B2 to B11 for the Y-values. Click "OK" on the "Edit Series" menu. This brings you back to the "Select Data Source" menu. Next to the "Add" button you will see an "Edit" button. This will allow you to go back and make changes, or to add or change the name for the series. Click "OK" again on the "Select Data Source" menu, and you should see the following graph on your page:
It certainly is a graph, but there are a number of issues with it that we need to fix. The first thing that we need to do is to move it to its own worksheet. Once again, click on "DESIGN", under "CHART TOOLS". If you don't see the green highlight for the "CHART TOOLS" tab, then click on the graph, and it will appear. From that tool bar, to the right of the button we used to select the data, click on the "Move Chart Location" button:
This will bring up the following menu:
Click on the button for "New sheet:", as indicated by the red arrow, leave the default title for the sheet (Chart 1), and click "OK". The graph should now open in a new worksheet entitled "Chart 1". The first things that we are going to do to the graph are to get rid of the title at the top (figure titles always go at the bottom, and require more detail than Excel will give us space for), get rid of the gridlines, and add axis titles.
Click on the plus sign that appears next to the upper right corner of the graph when you click on the graph:
Click on the boxes next to "Chart Title" and "Gridlines" to uncheck them, and click on the box next to "Axis Titles" to add the check. Looking at the graph, you will see that the title and gridlines are gone, and there are now generic titles for the axes.
We still, however, have a ways to go. First, change the axis titles to "Dependent" for the Y (vertical) axis, and "Independent" for the X (horizontal) axis. You can do this by clicking on the title to generate a selection box (four circles connected by lines) around it, using your cursor to highlight the existing title, and typing in the desired title.
The axis titles, and the numbers on the axis are still a little small to be easily readable. Also, the text and the lines are grey by default in Excel, which is less than helpful if you are using your graph for a presentation. I have found that an 18 point font for the axis titles, and a 14 point font for the axis numbers (and 16 point font for the legend, when present) to be visually appealing. You are free to disagree with my assessment (beauty is, after all, in the eye of the beholder), but you also might wish to ponder the advantages of turning in graphs that I find visually appealing...
The new system of tabs in the toolbar, unfortunately, has made this a cumbersome process. To change the font size for the x-axis title, first click on the title to place it within the selection box. Then click on the "HOME" tab on the top toolbar. The second box on the toolbar is where you change the font, by clicking on the arrow next to the font size to generate a drop-down menu of font sizes:
Select "18" as the font size. Now it is time to learn another time-saving feature in Excel. The function Key "F4" will repeat the previous action. To test this, click on the y-axis title to place it within the selection box, and hit the "F4" key. If you have lived a clean life, this should change the font size for that title to 18 point. Now, with the Y-axis title still in the selection box, click the "HOME" tab again, and this time, in the "Font" window, click the arrow next to the "A" to get the drop down menu for the colors:
Click on one of the black squares, and the y-axis title should now be in black. Click on the x-axis title (you may have to click twice) to put it in the selection box, and hit the "F4" key to make that title black as well. Now click on the line for the x-axis, to put the x-axis and the numbers in a selection box. The "F4" key should make those numbers black as well (we'll deal with the line in a minute), a process that you can repeat for the y-axis. Changing the font for the numbers on the axes is the same as changing the font for the titles. Make these 14 point font.
Now we need to make the lines on the axes black as well. As you know, clicking once on the axis puts a selection box around the axis. Clicking on the axis twice (which you may have discovered by accident) brings up a "Format Axis" menu to the right of your graph:
Before moving to the part of the menu that lets us change the line color, let's examine the options present. Note, for future reference, that these options are under the tab with the icon that looks like bars on a bar graph. The "Bounds" option lets you set the limits for the axis. In general, you want as little empty space in a graph as possible. For example, if the values for your dependent variable ranged from 90 to 110, there is little point in starting the y-axis at zero, as the trend will be more visible if the y-axis begins at 80 or 85. You can also changes the stepping of the numbers on the axis here under "Units", but that is seldom necessary.
With that useful information about axis manipulation stored away for future use (if you haven't been taking notes, you need to start doing so...), we can now get back to our original objective of making the lines for the axes black. Click on the paint can icon (indicated by the red arrow in the preceding diagram) to bring up a menu with 2 options: "FILL" and "LINE". As you might have guessed, we will click on "LINE" to bring up the following menu:
Click the button next to "Solid line", and then click the arrow next to the paint can (indicated by the red arrow) to bring up the color palette. Select one of the black squares. You can use the "F4" key to do the same thing to the other axis line, or repeat the process in its entirety if you prefer.
Our final steps for this graph will be to first change the color of the points to black, and make them slightly larger. Then we will make the points disappear and depict the relationship as a line. Clearly, if our initial goal was to have just a line, we could have selected that option when first inserting the graph. In this case, however, our goal is much more noble than that. We are learning valuable skills.
Click on one of the dots on the graph. This should put a selection box around each one of the dots, and change the menu on the right. If it does not, i.e., only one dot is selected, click on an empty space on the graph, and then click on one of the dots again. The menu on the right should now read "Format Data Series":
Selecting "MARKER", and then selecting "MARKER OPTIONS" should bring up the following menu:
Under marker options, "Type" will let you select the shape of the points. We will stick with a circle. We will, however, use "Size" to change the size from 5 points to 8 points. You can do so by clicking on the little arrows, or by highlighting the "5" with your cursor, and typing in "8".
Next we will click on "FILL", click on the circle next to "Solid fill", click on the paint can icon to bring up the color palette, and select one of the black squares. Unfortunately, we are not done, because Excel also lets you choose a border color for the shapes. For our usage, this is a matter of form over function, and just adds an extra step. Click on "Solid line" under "BORDER", click on the paint can icon, and select one of the black squares. Your graph should now look like this:
If it doesn't, scroll up and try to find out what you may have missed. If it does, congratulations! We are now ready to move on, but not before we learn another valuable trick. Those of you who skipped the last steps, knowing that we would be getting rid of the dots anyway, should be congratulated for your focus on efficiency, but will still have to go back and complete the exercise, because the new skill that we are about to learn is how to paste a graph into Microsoft Word. Open the Word document that contains the answers for the previous exercise (have you been saving your work?). On your Excel spreadsheet, click on the graph in the border region, so that a selection box forms around the entire graph (not just around the box containing the data points). Use Ctrl-C to copy the graph.
In your Word document, click on the page to locate the cursor below the answers that you already have completed. Click on the dropdown menu for "Paste", and select "Paste Special". You should see the following menu:
Select the "Picture (Enhanced Metafile)" option to paste the graph into the document. The reason for doing it this way is that just pasting it will sometimes change the proportions of the graph, which we have worked so hard to get right. Part of the reason it changes is that Word will establish a link to the spreadsheet, and also allow you to make changes. While this might be useful in some circumstances, it also can cause issues when working with the document. For the sake of simplicity, we will make our changes in Excel, and put the graph into our Word document as an image.
If you are pasting a graph into a Word document that has no text, make sure to hit "Enter" first, or you won't be able to move the cursor above the graph later on.
To make this a line graph, we will first add the lines, and then remove the markers. If we remove the markers first, it will be difficult to select the series if we get away from the menu for some reason. Click on one of the dots to select the series, and select "LINE" from the "FORMAT DATA SERIES" menu. Click the circle next to "Solid line" to add the line. If the color of the line is anything other than black, click on the paint can icon to bring up the color palette, and select one of the black squares.
To remove the dots, select "MARKER" from the "FORMAT DATA SERIES" menu. Click on "MARKER OPTIONS", and select the circle next to "None". You should now be left with a single, curved line on your graph.
If you have not done so already, NOW might be a good time to save your worksheet...
On to the BAR GRAPH!
Send comments, suggestions, and corrections to: Derek Zelmer