Page 1 of 13

Tutorial for Using Excel for Data Entry and Making a Graph

This tutorial will guide students through using Excel to produce a graph. It also includes instruction on
performing basic functions such as inputting data in the spreadsheet, performing calculations and
formatting cells. This guide is meant to be used for completing the report for Lab 1 in CHEM104. These
instructions can also be followed whenever you need to make a scatter plot to compare pairs of
readings.

Notes on Different Versions of Excel:

This guide was prepared with that you can use different versions of Excel, including Excel for
Windows, for MacOS and the Web-Version of Excel.

Please see the notes applicable for each version for additional instructions on how to create the graph
and generate the information needed to complete the assignment

The guide is not suitable for Excel 2010 or earlier. If you are using other spreadsheet software such as
Google Sheets, you should be able to complete the exercise with these software programs without any
issues. However, you may have to search online for guides on how to use your program.
 Page 2 of 13

1. Start by running Excel on your computer. The main program screen should look like the following:

The spreadsheet is made up of a series of individual cells. Each cell is referred to by its column (A, B, C,
etc) and its row (1, 2, 3, etc). For example, the uppermost left cell is cell A1.

This is the A1 cell. The green

square around the cell shows
that this cell is currently
selected.

2. Let’s start by typing some information into the spreadsheet. Input the following information into the
spreadsheet:
Cell Information
A1 Current Date
A2 You Name (and Partners)
A3 Assignment Name
There are two ways of inputting data into a cell. The first way is to select the cell you want and start
typing in the information. You can change the currently selected cell by clicking the new cell with your
mouse or move to the new cell using the arrow keys.
 Page 3 of 13

The second way to input data into a cell is to select the cell you want, click the formula bar directly
under the main toolbar and then type in the information. Using the formula bar is helpful when you
want to edit text already in a cell.

This is the
formula bar.

The spreadsheet should look similar to the following:

3. Next, input headings for the data that you want to graph:

Cell Information
B5 Unknown Number
B6 Total Volume of Liquid in
Vial
C6 Total Mass of Liquid in Vial
B7 mL
C7 g

Do not worry about the text in the cells being cut off for the time being. We will format them in the next
step.

Your spreadsheet should look like the following:

 Page 4 of 13

4. We will now format the cells so the text is displayed properly. First, group select cells B6 to D7. To do
this, click and hold the mouse button on B7, drag the mouse to D7and then let go of the mouse button.
Alternatively, move the cursor to B6, hold the Shift key and move the cursor over to D7 using the arrow
key.

With the cells selected, click on the “Wrap Text” and “Center Justify” icons on the toolbar.

Note: For the Web Version of Excel, these icons are in a different location on the toolbar.
 Page 5 of 13

5. Once all the headings have been formatted, input the actual data you collected from the lab into the
spreadsheet. Input the Unknown Number for the sample you were provided, the Total Volume of Liquid
in Vial and Total Mass of Liquid in Vial of the 4 trials.

6. Notice the data do not display the proper number of significant figures. By default, Excel will drop
trailing zeroes. We want to display the buret readings and the mass readings to 2 decimal places. Group
select cells B8 to C11 and then click on the “Increase Decimal” icon on the toolbar to set the readings to
the correct number of decimal places. Also center justify these values.
 Page 6 of 13

7. At this point, we are ready to make a graph from the data.

We will now create a graph of Mass versus Volume (Y versus X). Remember this means that the Mass
readings go on the Y-Axis and the Volume readings go on the X-Axis. Group select the data from B8 to
C11. Then go to the INSERT tab on the toolbar, click on the dropdown menu for Scatter under the
Charts area and click on the first option presented.

Note: For the Web Version of Excel, group select B8 to C11, go to the INSERT tab on the toolbar, and
then click on “Scatter with only Markers”
 Page 7 of 13

8. This will create the graph from your data. Double check that the data points have been plotted
correctly. Each data point should a coordinate (X, Y) of (Total Volume of Liquid in Vial), Total Mass of
Liquid in Vial). If it does not, double check that you have followed the instructions in this tutorial
correctly or contact your lab instructor.

9. Next, we add Axes Titles and a Trendline to the graph. Select the graph by clicking on the any part of
the graph. Click on the + icon that is on the upper right of the graph. Check on Axes Titles and Trendline.

ALTERNATIVELY: You can add Chart Elements via the menu toolbar. Make sure the Graph is Selected,
click on the DESIGN tab under Chart Tools, and click on the Add Chart Element. This will open a
dropdown menu. Axes Titles are under the Axes sub-menu and select a Linear Trendline under the
Trendline sub-menu.
 Page 8 of 13

For the Web Version of Excel:

Make sure the graph is selected, click on the Chart tab in the toolbar, the options to toggle the Chart
Title and Axis Titles are under the labeled icons.

For Chart Title, click on the “Chart Title” icon and then select “Above Chart”. Select “Chart Title” again,
then “Edit Chart Title”. Type in “Density of an Unknown Mixture” into the prompt.

For the Axis Titles, click on the “Axis Titles” icon, then “Primary Horizontal Axis Title”, then “Title Below
Axis”. A prompt will open for you to type in the title text. Type “Volume of Liquid (mL)”.
 Page 9 of 13

Repeat for the “Primary Vertical Axis Title”, select “Rotated Title” and type in “Mass of Liquid (g)” in the
Title Text prompt.

Your graph should now look like this:

Skip ahead to Step 12

 Page 10 of 13

10. To change the text displayed in the graph, first click on the text to select it, then click again to edit
the text (do not double click). The chart title should be descriptive and not be “Mass vs Volume”. Axes
titles must include the correct units.

Density of an Unknown Mixture

20.00
18.00
16.00
Mass of Liquid (g)

14.00
12.00
10.00
8.00
6.00
4.00
2.00
0.00
0.00 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00
Volume of Liquid (mL)

11. Whenever we include a trendline of the data, we should also show the equation of the trendline and
R2 value on the graph. Double click anywhere along the trendline (dotted line), don’t double click on the
actual data points (solid blue dots). A window should open on the right-hand side. Scroll down along
this new window to the very bottom and check Display Equation on chart and Display R-squared value
on chart. You can close this window afterwards.

Skip ahead to Step 13

 Page 11 of 13

12. This step is only if you are using the Web Version of Excel. You will not be able to create a trendline
using the web version of Excel. Use the following instructions to get the information necessary to create
the trendline manually. Type the following into the following cells:

Cell Information
A13 Slope
A14 y-intercept
A15 R-Squared
A17 Trendline
B13 =slope(C8:C11,B8:B11)
B14 =intercept(C8:C11,B8:B11)
B15 =RSQ(C8:C11,B8:B11)

These cells should now look like this:

To create the trendline, type the following into B17:

y=(slope value)x+(y-intercept value)

Replacing (slope value) and (y-intercept value) with the values in cells B13 and B14, respectively. For the
example used in this tutorial, the trendline would be. Note: round the slope and y-intercept to 4 decimal
places when putting it into the trendline:
 Page 12 of 13

The completed graph should now look like the following:

To save your graph as a PDF, Go to File in the toolbar, then Print, and then click on Print.

In the preview window, make sure that all the information is shown on one page, change the printer to
Download as PDF and then click Download. You are done and can skip the remaining steps.
 Page 13 of 13

13. We are now ready to save the graph as a PDF. We want to fit everything onto a single page. Move
the graph so that it is below the data points.

Go to File in the toolbar, then Print. In the new window that opens, make sure all the information is
shown on one page. Select the printer to Print to PDF and then Print. If you do not have an option to
print to PDF, you can install a free one such as CutePDF Writer or Adobe Acrobat.