CHM 1020 FIU Chemistry Data in Word and Excel Lab Report

• The video provided gives you a general idea on how to use excel to create graphs.

Lab 6: Data in Word and Excel
Purpose:
The purpose of this assignment is to become proficient in Microsoft Word and Excel by creating and editing graphs and
tables from experimental data.
Introduction:
Microsoft Word and Excel are two software programs that are commonly used throughout the scientific community for
organizing and reporting scientific findings. Microsoft Word is used to write manuscripts (or, in this case, lab reports),
and has the capability of creating tables and equations appropriate for publication. Excel, on the other hand, allows for
the organization and manipulation of data from experiments to create graphs and tables. Excel can also assist in
calculations required for data analysis. It is important for you, as an emerging scientist, to become familiar with these
programs, as they are integral for publishing work and turning in lab reports.
Procedure:
This assignment has several parts. Each part will contain a tutorial with screenshots to assist you in recreating examples.
Some sections may also contain questions for you to answer. When asked to recreate examples, be sure to generate
them exactly as they appear within the assignment – the tutorial should assist you in producing them properly. If any
section has questions to answer, be sure to do so concisely and in complete sentences therein your lab report.
Part A: Creating Tables in Microsoft Word
Using the tutorial below, recreate the following table.
Table 1
Samples Concentration (mg) Intensity
1
10
12356
2
20
56346
3
30
108975
Part B: Creating Tables in Excel and Importing Them into Word
Using the tutorial below, recreate the table below and import such into Word.
Table 2
Element: Mg25
Concentration (ppb)
Intensity
0
972
10
17624
75
122194
50
82256
100
163387
5
9431
25
41961
Part C: Creating Graphs in Excel
Using the tutorial below and the data in Part B (Table 2):
1. make two types of graphs and edit them (the first graph should be a scatter plot and the second a line graph)
2. to each graph, label the x and y-axis and provide a title on the graph
3. add a linear trend-line and an R2 value to each graph.
Which type of graph do you think should be used when plotting scientific data? Why?
Tutorial:
Part A: Creating Tables in Microsoft Word
Using the tutorial below, recreate the following table:
Table 1
Samples Concentration (mg) Intensity
1
10
12356
2
20
56346
3
30
108975
1. Click on the “Insert” tab at the top of the screen. A drop-down menu for “Table” appears on the left-hand side. Click
on the down arrow and go down to “Insert Table.”
2. A box will appear that looks something like the image below. Make sure you do the following.
a. Select the number of columns and rows needed to create the table you want (look at Table 1). If you find you
need to add more columns and/or rows after creating your table, you can use the “Table Tools” tab.
b. When sizing the rows and columns, it’s best to “Auto-fit to contents” – this will create small cells that will
automatically expand as you type into them, creating neat and organized tables. Note, to use the “Auto-fit to
contents” feature later, you must select the entire table and click on “AutoFit” within the “Cell Size” options
therein the “Layout” tab under “Table Tools.” Make sure you always do such after you filled the table to ensure
proper sizing.
Initially Using AutoFit:
Using AutoFit After Data Input:
3. Once you have made your selections, click “OK”. A grid will appear on your paper.
From here, you can type into any cell and add text or data. Text within the cells can be aligned, bolded, etc. by using the
“Home” tab or the “Table Tools” tab at the top of your screen. The “Table Tools” tab also contains options for inserting
rows, merging cells, and changing text direction. To merge cells or change text direction, the cells of interest must be
highlighted, then the option must be selected from the menu. Alternatively, you can highlight and right-click on cells to
perform the same operations.
4. Ensure you have the same formatting as the table provided. Remember to use the “Auto-fit to contents” feature.
Also, in addition of selecting the table and centering the information using the “Paragraph” tools under the “Home”
tab, make sure to center everything by selecting your table, going to the new “Layout” tab under “Table Tools,” and
selecting “Align Center” within the “Alignment” tools.
Part B: Creating Tables in Excel and Importing into Word:
Using the tutorial below, recreate the table below and import such into Word.
Table 2
Element: Mg25
Concentration (ppb)
Intensity
0
972
10
17624
75
122194
50
82256
100
163387
5
9431
25
41961
Creating Tables in Excel Tutorial:
1. When you open Excel, you will see blank cells in which you
can enter your data and any accompanying text.
Manipulation of the text and the numbers is done similarly
as in Word – the toolbar at the top of the page allows you
to do several functions, such as formatting text (change
size, font, bold, superscripts, etc.) Other icons allow you to
format the cells themselves (such as centering data/text,
shading, etc.)
Alternatively, you can highlight the text/data, right click, and
select “Format Cells.” A second window will appear and contain
several tabs that can be used to make any changes.
2. To merge two cells together, highlight the cells of interest and click on the “Merge and Center” icon.
3. To add borders and grid lines, highlight the data/text of interest. You can either right click and “Format Cells”
(images on the left) or use the border drop-down menu present in the “Home Tab” (images on the right)
4. Once you have formatted the table in Excel how you want it, you can import the table into Word via several
different ways.
a. Copy the graph from Excel, then go into Word. In the “Home” tab, the first icon on the left hand side is
“Paste.” If you click on the arrow to display the drop-down menu, “Paste Options” will display the variety of
ways in which you can paste the graph. The best option is to “Keep Source Formatting” – this will keep the
graph as you organized it in Excel and allow you to edit information within the table if needed.
b. Copy the graph from Excel and paste the table directly into Word. The default paste method is to “Keep
Source Formatting;” however, if you wish to change the format, a small “Paste” symbol will appear at the
bottom of your figure. Click on the drop-down menu for other options.
Part C: Creating Graphs in Excel
Using the tutorial below and the data from the Table 2, make two types of graphs and edit them. The first graph should
be a scatter plot and the second a line graph. To each graph, label the x and y-axis and provide a title on the graph.
Finally, add a linear trend-line and R2 value to each graph. Which type of graph do you think should be used when
plotting scientific data? Why?
Creating Graphs in Excel:
1. There are several ways in which you can create a graph based on your data. The simplest way is to highlight the data
that you wish to create a graph for (it’s best to leave out any text – it’s more of a reference for you and can be added
to the axis later). Once highlighted, go to the “Insert” tab at the top of the screen and select the type of chart you
wish to create.
Each type of chart contains a drop-down menu in which you can decide what the graph will look like. NOTE: When using
a scatter plot, it is preferred to create a plot in which the data points are NOT connected. Therefore, when creating your
scatter plot, chose the first option, “Scatter.”
2. Once you have selected the type of graph you want, it will appear as an object within the workbook. In addition, a
new tab will appear at the top of the screen – “Chart Tools” – that will be used to add axis labels, etc. to the graph.
To add all the required elements (x-axis, y-axis, title, trendline, and R 2 value), click on “Add Chart Element.” A menu will
display all the available elements you can add to your graph. When labeling axes, be sure to also include the units
associated with the data – this is where the text in your table comes in handy!
3. To make sure you add the trendline equation and the R2 value, click on “Trendline.” Then, select “More Trendline
Options.” A new section will pop up on the right side of the sheet. The new section will have three tabs that alter the
color, formatting, or type of trendline. Feel free to change the color but select adequate hues. Select the tab with
the bars and make sure your trendline is linear. Then, scroll to the bottom and check the “Display Equation on chart”
and the “Display R-squared value on chart” boxes.
Note, you can select and move the text box with the trendline equation and the R2 value to your liking.
Most of the data you will work with will fit a linear trend; however, you may encounter data which may fit a different
type of regression better. Consider this when adding a regression to your data!
You may also be asked to look at data at certain points or manipulate the trendline based on instructions. Therefore,
options such “Set Intercept” (to set the y-intercept to a certain value) and “Forecast” (to extend the trendline further
than the data points) can also be used. Note, remember that extrapolation is not encouraged.
3. If the data does not graph properly, you can edit the data within the graph by right-clicking on the graph and
choosing “Select Data.” A new window will pop up that looks something like the following.
Under “Legend Entries (Series)”, you can remove, add, or edit any data series that are present. For the sake of this lab,
you do not need to do much here.
4. If you wish to edit the scale of either one of your axes, you may do so using the “Axes” drop down menu.
Vanessa Thompson / Lorenzo Pla
Revised: Spring 2021
CHM 1020L
V3
Lab 6: Data in Word and Excel
Uma Swamy
Part A: Creating Tables in Microsoft Word
Using the tutorial provided (PDF on Canvas), recreate Table 1 below.
Part B: Creating Tables in Excel and Importing into Word
Using the tutorial, recreate Table 2 in excel and import such into Word. Show the final document as it looks in
Word below.
Don’t forget to include screenshots from your computer showing Table 2 in excel and the process of importing
it into word.
Feedback
Were the instructions provided helpful? If yes, explain your answer.
Were there parts of the instructions that were incomplete/incorrect? If yes, which parts? Explain your answer.
Which resource did you use to help you complete the assignment instead? Explain your answer.
CHM 1020L
V3
Uma Swamy
Part C: Creating Graphs in Excel
Using the tutorial below and the data from Table 2, complete the following.
Scatter Plot
Show the scatter plot you created from the data in Table 2 below and please ensure that you:
1. label the x and y -axis and provide a title on the graph
2. add a linear trend-line and an R2 value to the graph.
Straight Line Graph
Show the line graph you created from the data in Table 2 below and please ensure that you:
1. label the x and y-axis and provide a title on the graph
2. add a linear trend-line and an R2 value to the graph.
Watch the following video (https://youtu.be/Q-TtIPF0fCU) and answer the next two questions to better
understand regression models and the meaning of the R2 value. Note, the video talks a bit about statistics; focus
on the general definition of a regression model and the explanation of the R2 value.
1. In your own words, what is a linear regression model? What relationship between variables is described?
2. In your own words, what does the R2 value describe?
CHM 1020L
V3
Uma Swamy
Which type of graph do you think should be used when plotting scientific data? Why?
Can you think of some situations where it is best to use a line graph? Do some research online and don’t forget
to cite your sources.
Can you think of some situations where it is best to use a scatter plot? Do some research online and don’t forget
to cite your sources.
Part D: Creating Tables in Word and Graphs in Excel for an Unknown Data Set
Use the data provided1, recreate the table in excel and generate a graph that will allow you to determine the
concentrations of the unknown solutions.
Note, Å stands for Angstrom, which is a unit of wavelength.
1
2
3
4
5
6
7
Unk 1
Unk 2
Unk 3
Protein concentration (mg/mL)
50
25
12.5
6.25
3.125
1.56
0
?
?
?
Absorbance (450 Å)
0.377
0.317
0.277
0.235
0.221
0.203
0.172
0.298
0.418
0.253
Watch the following video (https://youtu.be/c4_MJg_c49k) to help you think about how to predict the unknown
values. Make sure you don’t extrapolate.
Why can extrapolation sometimes lead to inadequate predictions?
CHM 1020L
V3
Uma Swamy
Outline the steps showing how you used the data provided to find the unknown concentrations.
In the space provided below, clearly show the calculation of how you calculated the concentrations of the three
unknown solutions.
Unk 1:
Unk 2:
Unk 3:
Include the graph you made for Part D within the space below. Remember to include all the previously
mentioned graph components (title, axes titles, trendline equation, and the R2 value).
References
1. Caguioa, Joshua Joye O.; Manundan, Jay Mark.; Rapanot, Jhone Dennille (2019).Measuring Protein
Concentration of Bovine Albumin Serum using Spectrophotometric Analysis: A Magnesium Chloride
based Biuret Assay. 10.13140/RG.2.2.29387.54567
Revised 2021