Plotting in EXCEL - Wake Forest University



Plotting in EXCEL 2010

No matter what anyone else tells you, you are not a suitably powerful person in any field in this world unless you know 1) the rudiments of calculus, and 2) how to plot in EXCEL (or other similar spreadsheet program). In case you don’t already know about #2, this exercise is intended to remedy that situation.

Basic Structure and Language:

Consider the following plot and the accompanying data…

[pic]

The column of x-values and the column of y-values make up what is called a “series”. Each line you put on the plot will be identified as a particular series. The x- and y-values do not have to be next to each other on the spreadsheet, nor do they have to be vertical.

To create a plot, you can identify/highlight the part of the spreadsheet describing the series, then click on the Insert tab and select the type of plot you would like to create. In this case we are selecting a Scatter plot that shows the points (Markers) but no lines.

[pic]

In this class you will always want to make Scatter plots. [You might be tempted to say you want to make a “line plot” – it sounds like a nice sort of plot to make, but it’s not.]

Building a Plot:

At this point all you should have is the following plot somewhere on your spreadsheet.

[pic]

This plot doesn’t convey as much information as the one on the first page: there are no titles, there is no trend line…. There are many features you can control that will improve the plot.

In general, the features you can control include…

- the Chart Title (e.g. “Some Useless Numbers”) and the Axis Titles (e.g. “Y-ness”)

- the axis scales

- the look of the points on the plot, the markers.

- the look of the line connecting the points

- the look of the gridlines.

- the look of the space the plot is drawn on, the Chart Area.

- the trend line is the line running through the points (this is different from a line connecting the points).

- the legend.

- you can add more lines (series).

Most of these features can be controlled using the Layout tab. However you won’t see a Layout tab unless the chart is “active”. To make a chart “active” simply click somewhere on the chart. A Chart Tools title shows up at the very top of the screen, and the tabs will include Layout.

[pic]

There are additional controls in the Design tab which allow you to change the Chart Type, Select Data to redefine or add series, Move the Chart to a separate worksheet, pick a Chart Layout, which gives you a complete plot based on a selected template, and even pick a Chart Style that can make the whole thing look unnecessarily fancy.

[pic]

To control the other features of a plot besides the ones covered explicitly in the Layout and Design tabs, one must first highlight the feature, then Format it. To highlight a feature, simply left-click on the feature. If you successfully hit the feature, its name will appear in the text window in the Current Selection box on the far left side of the Layout or Format tabs.

In the case below, the markers in Series 1 were highlighted by clicking on one of them, so the whole series is ready to be formatted.

[pic]

Once the desired feature is highlighted, simply click on Format Selection. From there you are on your own. Pick and choose whatever makes your plot look good.

If you have difficulty hitting the desired feature with your mouse, you can use the drop down menu on the text box and select the appropriate feature, or you can use the up and down arrows (↑ ↓) to step through all the possible features to format. This is particularly useful when you have a lot of lines on a plot, and you can’t highlight the one you want because most of its points are buried underneath another line.

For the present plot, the following formattings were used to go from the simple plot to the finished plot seen on the first page.

• To include the titles and vertical gridlines, or to delete the legend….

- From the Layout tab, select Chart Title. You will probably want the Above Chart

option. It will put its default title in the desired space. You can type the desired title in

the fx line (formula bar) or simply highlight what is there and replace it with whatever

you want.

- The process is very similar for adding axis titles.

[pic]

-To change the font in the titles, click on the title box in question then click on the Home

tab and make the changes in the Font box there.

[pic]

- To put in gridlines, click on the Gridlines in the Layout tab, then select the appropriate type – probably the Vertical Major Gridlines.

[pic]

- With only one line on the plot, you may want to remove the legend. Do this by clicking on Legend in the Layout tab.

[pic]

• To edit how the series is presented, in terms of marker and line colors, sizes, etc…

- Select Series 1 by clicking on one of the points.

- Click on Format Selection in the Current Selection box of the Layout tab. Pick what you want to change, and change it.

[pic]

Notice that you can use these windows to stop showing markers at all or to draw a line from point to point. This is useful if you want to include a smooth curve through a collection of other data points. In such a case you might have some experimental data points as one series and generate another series mathematically using some model equation describing smooth line going through the experimental points. When you plot the experimental points you would plot just the markers, but when you plot the modeled curve you would plot the lines without any markers.

• To change aspects of the axes…

- Select the appropriate axis for formatting in the Layout tab and select More Options.

- In Axis Options, for instance, you can change where the axis starts and stops.

[pic] [pic]

• To add a trendline and extend it picturesquely beyond the data…

- Select the Trendline box in the Layout tab, and select the Linear Trendline.

- To make the Trendline extend back and forward (Forecast) one extra unit beyond the data in the x-direction, type 1.0 in the appropriate box after selecting More Trendline Options.

[pic]

If you have been making the changes described above, you ought to now have the plot seen on the first page. Congratulations.

• To add another series to the plot…..

[pic]

- From the Design tab, click Select Data then Add. Click the icon to the right of

the x or y-values text boxes then highlight the appropriate values for the new series. Notice some extra data has been added to the spreadsheet.

- Series can also be removed or edited by clicking Select Data then Remove or Edit.

Annotating your plot:

From the Insert tab, click on Shapes, then select an appropriate arrow. On the plot, click and drag from where you want the tail to where you want the head of the arrow to be.

[pic]

From the Drawing Tools tab that opened up, you can Format the arrow.

[pic]

Finally, you can add a Text Box, also found in the Drawing Tools tab. After selecting the Horizontal Text Box, click and drag to select the location for the text box. Then you simply type the message you want.

Going back to the Drawing Tools tab, you can Format the text box Shape to be whatever Color or Weight or Style you want.

[pic]

So go forth and make plots…

There are many other aspects of EXCEL plots that can be controlled. You may have seen some of them in the areas of the formatting windows we didn’t use. Nonetheless, this introduction should give you enough background to make a good plot and to extend your talents to make more complicated plots. Most importantly, you also know enough now to adequately understand the information you might see if you call on the HELP menu item.

[pic]

Some Encouragement to use EXCEL when doing calculations:

There will be many instances in your computational future in which you will need to do the same calculation over and over again on different sets of inputs. Spreadsheets, like EXCEL, provide simplifications that make these tasks much simpler than doing them on your calculator. The following example comes from next week’s activity, a calculation of the density of a solid with a complicated formula using four mass measurements. We don’t need to understand the calculation now – you’ll figure that out next week. We’ll just start with the final calculation of density d, in terms of the four masses, m1, m2, m3, and m4, and the density of water dw.

[pic]

We have a hypothetical class of four students, and here is their data in a spreadsheet.

[pic]

By typing the following formula in cell G6, we set up the calculation for density from the first set of four masses.

=f6/((c6-d6)-(e6-f6-d6))*$d$2

[pic]

Highlighting cell G6, and hovering over the bottom right corner, the cursor turns into a black cross. Left-click, then drag down to G9, and release. This copies the formula into cells G7 through G9, but increasing all the cell indices by one each time, except the ones with a “$” in front.

[pic]

Look in the function window above, and notice the formula that is now in G9. It’s the same as the one you typed into G6, but now with 9’s instead of 6’s, except $D$2 didn’t change.

This example shows how you can speed up a repetitive calculation using EXCEL, but notice it also enables you to go back and check and edit the original formula in G6 very easily. It is slightly easier to check and edit with EXCEL than with a calculator, but it is the immense time savings one gets when repeating calculations where the benefit of using the spreadsheet is most apparent.

Assignment:

Use the following set of data that is representative of some data you’ll take in the Boiling Point Elevation lab in a few weeks.

[pic]

-Prepare the data for plotting. For each of the 9 BP’s measured calculate the increase in BP (i.e., the BP of a given solution minus the BP of the pure water) and the molal concentration of each. For acetic acid (HAc) the molal concentration is the volume(mL)/3.003; for NaCl the molal concentration is the mass(g)/2.922; and for Na2SO4 it is the mass(g)/7.102.

-Make a plot of the increase in boiling point vs molal concentration for each of the three solutes That is, three sets of data and three trendlines on one plot. Each series includes three points, one of which is zero molal and zero boiling point increase. If you aren’t going to plot it in color, use different shaped markers and line styles to distinguish the different solutes (HAc, NaCl, and Na2SO4).

- Force the trendlines to go through 0,0 and have their equation displayed with a

sensible number of significant figures.

- Use meaningful titles with units. Use font sizes that are easy to read.

- Use ranges for the axes that do not leave a lot of empty space, and focus only on the important parts of the data set.

Don’t be afraid to use HELP if you are unfamiliar with how to accomplish something.

Print out your final plot, and turn it in whenever your instructor says it’s due.

-----------------------

General Guidelines for Good Plots…

Keep in mind that the plot is there to help you tell a story. As such, it should be easy to read – especially the parts you want the reader to focus on.

Scale the axes so that the data and lines fill the allotted space efficiently.

Don’t go to extremes to accomplish this – you need to use nice round numbers for the

tic marks. Also, there is no rule that says that 0,0 needs to be the corner of the plot.

Label the axes. Labels usually include a name for the axis followed by the units in parentheses. Sometimes you see units identified as inverse of what you would expect, e.g. an axis labeled

“time · s-1”. This is useful if you are a purest and want to treat the values on a plot as they are written – as unitless numbers. In this case the unitless values are just the actual time in seconds multiplied by s-1. You can side-step this confusion by simply putting the units in parentheses.

Identify the plot with a title OR a figure caption. A title should not be so large as to overwhelm the plot.

Include horizontal and vertical gridlines if you want the reader to be able to interpolate values from the plot. If you simply want to impress the reader that all your points fall on a line or have some other easily demonstrated relationship, then you don’t need gridlines.

Most plots should include experimental data points and a trendline. To do this, the series for the data points should be markers only – no connecting lines. The only time you should include markers is when the series points are related to experimental data. If you are simply showing a fit function, like a trendline, or the behavior of some calculated function, just show the line connecting the points without any markers.

Don’t over burden your plot with glitz, like fancy fonts or shading. You are trying to educate the reader, not sell something.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download