Chemistry Excel Assignment for Module 3

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.

Getting Started:

All student computers on campus have both Microsoft Word and Excel Installed, and you are welcomed to use these computers to complete the assignment.

Students at Florida International University can download the Microsoft Office Suite to their personal computers for FREE. It is important to use a downloaded copy as the online version does have all the features you will need. Please note that the license is valid until you graduate from FIU. Once your graduate the license gets deactivated automatically.

Instructions on how to install Office on your personal computer can be found below

1. Go to the following website- http://freeoffice.fiu.edu/

2. Login with your FIU credentials (the same ones you use to login into myFIU)

3. You will be given a link where you can download the full office suite including Microsoft Excel, Microsoft office, Microsoft Power Point, Microsoft outlook and a whole host of other applications that come as part of the suite.

4. It will then walk you through the process of installing this on your personal computer.

5. If you have trouble with the installation, please reach out to FIU IT at 305-348-2284 directly for assistance.

Disclaimer:

Your version of excel may differ a bit in where things are because it depends on which version you have, whether you have a MAC or a PC and a variety of other situations that we cannot predict. So, you will have to do some googling and may have to watch some videos to get this to work in your specific situation. If you need further assistance, please reach out to your TA or to FIU IT if you have questions. There is also a location on the second floor of GL where you can seek assistance.

Opening excel and some general concepts to know:

To Begin:

Open the Excel program, and select blank workbook option as shown in the image to the left.

Saving the workbook:

Adopt a naming convention for your excel files as that keeps them organized and makes them easy to find. Here is an example

“course_semester and year_last name_first name_lab name

A file name could be CHM1045_Fall22_Doe_John_Excel assignment.

Figure 1. Image of Excel upon opening

1) Each box in the spreadsheet is called a cell, and these cells can be referenced using a combination of a letter and a number. The letters represent the columns (vertical grouping) of the spreadsheet and the numbers represent the rows (horizontal grouping) of the spreadsheet.

2) While working in, make sure to save your work frequently in order to secure it.

3) To the save the workbook, go to file and click the tab save as. Choose a location to save you file and select it. Then name and save the file as instructed above.

Exercise:

1) Locate cell D5. This cell can be found in the fourth column and fifth row of the spreadsheet.

2) What is the code used to reference the cell in the eleventh column and fortieth row?

Some spreadsheet fundamentals:

Operation	Excel command
Addition	=2+3 = D3+D4
Subtraction	=2-3 = D3-D4
Multiplication	=2*3 = D3*D4
Division	=2/3 = D2/D3
Exponents	=2^3 = D2^D3

Now that we have a frame of reference for discussing spreadsheets, let’s discuss some of the more fundamental uses of spreadsheets.

A spreadsheet can be used as a basic calculator, but unlike most calculators, it stores your results, which can be referenced in subsequent calculations. Calculations in a spreadsheet must begin with an equal sign, indicating to the program that you are performing a calculation. The table to the right shows some of the basic mathematical operations and the correlated commands. You can use the same commands with numbers as well as with cells that contain these numbers.

Notice that multiplication must be done using the asterisk (*); Excel does not know how to perform multiplication using “X” i.e. 2 x 3 or parenthesis, i.e. (2)(3). The correct way to do this is either 2*3 or (2)*(3).

In excel, commands can also be combined to perform a specific function, but in these cases special attention must be paid to the use of parentheses. i.e. Average = (1+2+3+4+5)/5 or (A1+A2+A3+A4+A5)/5 or (A1:A5)/5

These functions can also be done using functions already installed in excel. (Ask your TA about these).

Procedure: *Follow the procedures to answer the questions of the Data in word and Excel Assignment

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 in your assignment.

Part A: Creating Tables in Microsoft Word

Using the tutorial below, recreate the following table:

Table 1

Samples	Concentration (ppb)	Absorbance
1	5.00	0.0400
2	10.00	0.0853
3	15.00	0.1305

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 so after you filled the table to ensure proper sizing.

Initially Using AutoFit: Using AutoFit After Data Input:

General Chemistry Lab 2022

1

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: Zn
Concentration (ppm)	Absorbance
0	0.002
0.3	0.209
0.6	0.423
0.9	0.601
1.2	0.811
1.5	0.987

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 graphs and edit them. The first graph should be a scatter plot only and the second a scatter plot with trendline graph. To each graph, label the x and y-axis and provide a title on the graph. Finally, add a linear trend-line, equation, and R2 value to the second graph.

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, choose 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, equation, and R2 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

4. 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.

5. If you wish to edit the scale of either one of your axes, you may do so using the “Axes” drop down menu.

· Graphs should be captioned below the graph and formatted as “Figure X: Title of Graph. Description if needed). The title should not be obnoxiously large at the top center of the figure (Excel default). For your reference, an example of an incorrect graph (Figure X) and a corrected version (Figure Y):

Figure X: Incorrect version of a graph Figure Y: Correct version of a graph.

Name:

Panther ID:

Data in word and Excel

Assignment

Assignment (Based on the procedures, answer the following questions to complete the assignment)

After completing the assignment, please save the file with only the questions and answers (do not include the procedures) and upload the file to the correct submission link on Canvas.

1. What is the code used to reference the cell in the eleventh column and fortieth row?

2. Recreate Table 1 in Word (Part A).

3. Recreate Table 2 in Excel and Word (Part B).

· Include a screenshot of the table you created in excel.

· Recreate the table in Word below

4. Import the appropriately labeled scatter plot and scatter plot with trendline graph that you created using Table 2 into this document (Part C)

5. Which type of graph do you think should be used when plotting scientific data? Why? Explain your answer in detail and cite your sources.

6. What information does the R2 value give you about the graph? Explain your answer and cite your sources.

7. What information does the equation on the graph give you? Explain your answer and cite your sources.

Applying: Using Microsoft Excel, create a graph in which you plot the given data and then insert it below the data table. You must provide:

· An appropriate graph title

· Axis labels

· A fitting trendline

· An equation of line

· R-squared value

· A brief graph description.

Table 1. Oxygen solubility data (mg l−1) in aqueous solutions at p=p0

Temperature (°C)	Solubility coefficient
20	9.43
30	7.98
40	7.11
50	6.50
60	6.10
70	5.82
80	5.65

Data is an excerpt from https://doi.org/10.1016/S0043-1354(99)00217-1

1. Analyze the graph obtained above and answer the following questions:

a. Which is your x-axis and why did you pick that column to represent the x-axis?

b. Which is your y-axis and why did you pick that column to represent the y-axis?

c. What type of trendline did you select to best fit the data and what was the R-squared value obtained?

d. What does the R-squared value indicate?

e. What information can you get from the equation on the graph?

2. Explain how you would interpret the graph. Use complete sentences to explain your thoughts and write a paragraph summarizing your ideas ensuring that you discuss/Address all the following questions:

· What relationship exists between the variables?

· What type of explanation might the graph offer for global warming?

· Does carbon dioxide concentration have a correlation to the increase in temperature? Does your graph provide evidence of that? Explain your answer.

3. Do you think you could have come to the same conclusions as above with another type of graph (e.g. bar graph, pie chart, etc) using the same data? Explain your reasoning.

4. Could you use the equation of the line to predict the solubility coefficient for oxygen at a temperature of 90°C? Could you predict an exact value from the data you currently have? Why or why not? Explain your reasoning clearly in complete grammatically correct sentences and show work as needed. (Hint: Extrapolation)