Excel offers templates for a many types of graphs. Unfortunately, most of these are not very useful to scientists, except in occasional situations. In this section, I’ll plot out examples using the same data set that I used earlier. In each of these examples, I’ll just use the default settings.
Types of graphs available in Microsoft Excel
Most scientific graphs make use of what Excel refers to as a “scatter plot”. This can sometimes be confusing to beginners, because you may have been trained to call this a “line graph”. Since most math classes begin with scatter plots of linear data, this isn’t unreasonable. However, a true scatter plot starts out with two columns of data. The first column is plotted on the x-axis, the second column is plotted on the y-axis directly above the number on the x-axis. Because the two columns of data might have a linear relationship (y=m x + b), a quadratic relationship (y=m x2 + bx + c), something more complicated, or even no relationship at all, the points on the graph might not trace out a nice straight line.
Microsoft programmers have set up Line Charts to plot each number in the first column as a point in a line. The second column is plotted as points in a second line. To make matters worse, the points are not even shown. Students in my introductory physics classes often make the mistake of plotting two columns of data using the Line Chart option, and are treated to a graph that looks like this…
I can barely stand to look at this graph, because it is nearly unreadable. Let’s add data points to make things slightly more clear.
Notice that the values from each column in this case are plotted as though we have a separate, hidden column of x-values that go 1, 2, 3, etc. In order to obtain the data used in this graph, we measured the time it took to move between photo gates located at 15 centimeter intervals. Because each 15 centimeter step is the same size, the data representing position looks like a straight line. As the object falls, it takes shorter amounts of time to reach the next photo gate. Therefore, the time data looks to be bent.
Notice that the default settings on this graph have done something troubling. Time and position have two different types of units. Both of these are being plotted on the same axis. By happy circumstance, the two measurements are measured in units that make the numerical values about the same. Let me replot the graph, but this time, the distance units will be in centimeters…
Now the numerical values of the distance intervals (15 cm, 30 cm, 45 cm, etc.) are quite a lot bigger than the numerical values of the time steps, which are all fractions of a second. Therefore, the time trace in the graph looks squashed.
Please never use Line Charts unless you are comparing two sets of numbers with the same dimensions.
The following chart is a column chart. If you look carefully, this is almost exactly the same chart as the first Line Chart above. This time, though, Excel has replaced the points with bars that reach from zero up to the value of the data on the y-axis. While this is slightly less aggravating than the line charts above, it really doesn’t make sense to plot position and time data in this way.
The only time you would want to use this type of a chart is when you are comparing data that naturally falls into categories. For example, I use this type of a chart often when showing the grade distribution for an exam. The A, A-, B+, B, B-, etc. give natural categories to the data, while the height of the column would represent the number of students earning a particular letter grade. You can see a typical example below. You could add a second column of data to this set to represent a different class. In a statistics class, you may have referred to this type of graph as a histogram.
Other Types of Graphs
Excel offers variations on the column chart such as the bar chart and the stock chart. You can even combine column charts and line charts. Other charts include surface and area charts, but these are just variations on the line chart. In most cases, scientists won’t use these types of charts, except under special circumstances.
The familiar pie chart is also available. These types of graph is only useful to display sets of data made of individual groups that add together. For example, if you are creating a budget, you want your total budget to add up to 100 percent of the available funds. A pie chart is made up of pieces that are percentages of the total. I’ll cover the pie chart separately in a later chapter.
Rather than continuing the tour of the types of graphs in Excel’s chart options, let’s stop here. Unless you are doing marketing, chances are that as a scientist, you will only ever need to make use of the scatter plot.