Difference between revisions of "Introduction to Microsoft Office"

From EG1004 Lab Manual
Jump to: navigation, search
(1003 --> 1004)
 
(23 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<span style="color: red;">Important: Please note that you must register on the [http://eg.poly.edu EG1003 website] before performing the lab.</span>
<span style="color: red;">Important: Please note that you must register on the [http://eg.poly.edu EG1004 website] before performing the lab.</span>


= Objective =
= Objective =
The objective of this lab is to master the basics of Microsoft Excel, PowerPoint, and Word and to learn how to employ these applications. This exercise will produce templates for EG1003 lab reports and PowerPoint presentations.
The objective of this lab is to use Microsoft Excel to analyze two case studies. The first case study  will evaluate the acceleration due to gravity of a free-falling body and the second case study will analyze the historical trends for energy consumption in the United States.


= Overview =
= Overview =
This lab is designed to teach the skills needed to become proficient with the three primary Microsoft applications: Excel, PowerPoint, and Word. These programs are widely used in academia and the public and private sectors so proficiency in these applications is a requirement. Completing assignments in EG1003  requires a basic competency in them and the following exercises are designed to create that competency.
This lab is designed to teach proficiency with Microsoft Excel. This application is widely used in academia and the public and private sectors so proficiency in this application is a requirement. Completing assignments in EG1004 requires a basic competency in Microsoft Office and the following exercises are designed to create that competency.


== Microsoft Office ==
== Microsoft Office ==
Microsoft Office is a software suite that bundles Microsoft Excel, PowerPoint, Word, and more.
Microsoft Office is a software suite that bundles Microsoft Excel, PowerPoint, Word, and additional programs and apps.


Microsoft Excel is a spreadsheet program. Its function is collecting, processing, and analyzing data. Microsoft Excel has many features that streamline data analysis. It can be used to generate tables and graphs. It is useful for showing the relationships between data sets and identifying trends. Graphs will be required for many lab reports and presentations.
Microsoft Excel is a spreadsheet program. Its function is collecting, processing, and analyzing data. Microsoft Excel has many features that streamline data analysis. It can be used to generate tables and graphs. It is useful for showing the relationships between data sets and identifying trends. Graphs will be required for many lab reports and presentations.


Microsoft PowerPoint is a presentation application. Its function is to create slides for presentations. In this course, eight lab presentations, three Milestone presentations for the semester long design project, and a final sales presentation for the semester long design project will be created and presented using PowerPoint.
Microsoft PowerPoint is a presentation application. Its function is to create slides for presentations. In this course, eight lab presentations, three Milestone presentations for the semester-long design project, and a final sales presentation for the semester-long design project will be created and presented using PowerPoint.


Microsoft Word is a word processing application. Its function is writing and editing text. In this course, Microsoft Word will be used to write nine lab reports.
Microsoft Word is a word processing application. Its function is writing and editing text. In this course, Microsoft Word will be used to write nine lab reports.
== Saving Work ==
The information on the lab PCs cannot be accessed outside of the lab rooms. Email copies of all the files created on the lab PCs to a personal account.


= Materials and Equipment =
= Materials and Equipment =
Line 21: Line 24:


= Procedure =
= Procedure =
Complete the following exercises.


* Change all generic (highlighted in yellow) text to the information created in the lab
== 1. Case Study: Free-fall ==
* Copy and paste Excel tables and figures as pictures. Right-click and click the Paste as Picture icon [[Image:Lab 1A1.png|176px|thumb|center||Figure 1: Paste as Picture]]
In this exercise, common Excel equations and tools will be explored through the context of a problem.
* Label tables and figures
 
* Use the Equation function in Word and PowerPoint
A ball is dropped from the roof of the Bern Dibner Library building and the distance between the ball and the ground is measured and recorded as a function of time with a Lidar scanner. The time and distance data are shown in Table 1. The velocity in meters per second, average velocity in meters per second, and acceleration due to gravity in meters per second squared of the ball will be calculated using Excel.
 
<center>
{| class="wikitable"
|+ Table 1: Time and Height Data
!Time (s)!!Distance (m)
|-
|<center>0</center>||<center>19.620</center>
|-
|<center>0.2</center>||<center>19.424</center>
|-
|<center>0.4</center>||<center>18.835</center>
|-
|<center>0.6</center>||<center>17.854</center>
|-
|<center>0.8</center>||<center>16.481</center>
|-
|<center>1.0</center>||<center>14.715</center>
|-
|<center>1.2</center>||<center>12.557</center>
|-
|<center>1.4</center>||<center>10.006</center>
|-
|<center>1.6</center>||<center>7.063</center>
|-
|<center>1.8</center>||<center>3.728</center>
|-
|<center>2.0</center>||<center>0</center>
 
|}</center>
 
#Open the [[Media: Microsoft_Excel_Exercise_.xlsx|Microsoft Excel Template]] containing Table 1. The velocity of the ball is the change in distance over time. To calculate the instantaneous velocity at each point in time, a formula (1) is used.
#:<center><math>v = \frac{x_2 - x_1}{t_2 - t_1}\,</math></center> <p style="text-align:right">(1)</p>
##In (1),<i>v</i> is the instantaneous velocity, <i><math>x_2</math></i> is the second distance point, <i><math>x_1</math></i> is the first distance point, <i><math>t_2</math></i>is the time corresponding to the second distance point, and <i><math>t_1</math></i> is the time corresponding to the first distance point.
###Assuming the initial velocity is zero, enter 0 into cell C2. In cell C3, enter the formula =(B3 - B2)/(A3 - A2). The velocity will be negative because the ball is traveling downwards.
#Reselect cell C3. Click and drag from the bottom-right corner of the cell down to cell C12. This will copy the formula for the other cells in the column using the data in column A and column B (A3, A4, B3, B4 for cell C4, and so on).
#To calculate the average velocity of the ball across the 11 data points, select cell D2. Enter the formula =AVERAGE(C2:C12).
#Microsoft Excel is commonly used to visually represent data in graphs. Create a scatter plot for the relationship between velocity and time.
##On the '''Excel''' ribbon, click on the '''Insert tab''', and click '''Scatter''' on the '''Scatter option''' under the '''Charts''' group. This should create a blank graph, as shown in Figure 1.
##:[[Image:Lab_ScatterPlots_Office.png|500px|thumb|center|Figure 1: Inserting a Scatter Plot]]
##Right-click on the empty graph and select '''Select Data'''.
##Under the '''Legends Entries(Series)''' tab, click on the '''Add option''', as shown in Figure 2. A new window called '''Edit Series''' will appear.
##:[[Image:Lab_Select_Office.png|500px|thumb|center|Figure 2: Utilizing the Select Data Source Window]]
##In the '''Series X''' values textbox, delete the {1} and highlight cells A2 to A12. In the "Series Y" values textbox, delete the {1} and highlight cells C2 to C12. Click '''OK''' to generate the scatter plot.
##Add axis titles to the graph by clicking '''Axis Titles''' in the dropdown menu at the top right of the graph. Rename the axis and title.
#Acceleration is the rate of change of the velocity, or the slope of the velocity vs. time graph. For roughly linear data on Microsoft Excel, a line of best fit is used to approximate the relationship between the data points in (2).
#:<center><math>y = mx + b</math></center> <p style="text-align:right">(2)</p>
##In the dropdown menu at the top right of the graph, hover over '''Trendline''' and click on the black arrow. Select '''More Options'''.
##Under the '''Format Trendline''' window, check off '''Display Equation''' on chart.
##Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 <math>\frac{m}{s^2}</math>. Discuss and explain the discrepancy.


== Saving Work ==
== 2. Case Study: Historical Energy Statistics ==
The information on the Lab PCs cannot be accessed outside of the lab rooms. Email copies of all the files created to a personal account.
This exercise provides an overview of creating line graphs and pie charts in Excel  to analyze the historical trends of renewable energy consumption in the United States.


The templates created in this exercise for Microsoft Word, Excel, and PowerPoint <b>do not</b> need to be submitted on the [https://eg.poly.edu/submit.php EG1003 website].
The statistical data presented in this exercise is derived from the U.S. Energy Information Administration (EIA). Using the data, four graphs will be constructed: a line graph showing the yearly trend for hydroelectric power, geothermal power, solar power, wind power, and biomass power, and a pie chart representing the 2019 distribution in renewable energy. These graphs will  be appended with data regarding fossil fuel energy consumption.


== 1. Microsoft Excel ==
=== Modeling a Yearly Trend ===
Fill in the empty highlighted cells in the [[Media: Excel Template.xlsx|Microsoft Excel Template]] to illustrate the Ideal Gas Law. Use the following formulas to fill the spreadsheet.
#Download the [[Media: Lab_Excel_Raw_Data.xlsx| Excel file]] containing the  data for the yearly energy consumption of various renewable energy sources from 1949 to 2019.
#The line graph will contain multiple lines that represent the different renewable energy sources. The procedure to graph the renewable energy consumption for hydroelectric power is provided. Complete a similar procedure for the remaining renewable energy sources.
##On the Excel ribbon, click on the '''Insert tab''', and click '''Scatter with Straight Line''' on the Scatter option under the '''Charts''' group. This should create a blank line graph, as shown in Figure 3.[[Image:Lab10_excel_ribbion.png|500px|thumb|center|Figure 3: Inserting a Line Graph]]
##Right-click on the empty graph and choose the '''Select Data''' option. A new window will appear where the graphed data can be inputted.
##Under the '''Legends Entries (Series)''' tab, click on the “Add” option, as shown in Figure 4. A new window called '''Edit Series''' will appear.[[Image:Lab10_excel_dataSource.png|500px|thumb|center|Figure 4: Utilizing the Select Data Source Window ]]
## In the '''Series name''' textbox, type in the name of the power source, Hydroelectric Power.
## In the '''Series X values''' textbox, highlight the years in column A from 1949 to 2019.
##In the '''Series Y values''' textbox, highlight the total energy consumption from hydroelectric power in column B that corresponds to 1949 to 2019. The '''Edit Series''' should appear similar to Figure 5.[[Image:Lab10_excel_editSeries.png|500px|thumb|center|Figure 5: Utilizing the Edit Series Window ]]
## Click '''OK''' to graph the Hydroelectric Power data.
# Next the data for the remaining renewable energy sources must be added to the same graph. To add the data, repeat steps 2b to 2g for each renewable energy source.
# Rename the axes and titles for the energy source in the graph. Include a legend by checking off '''Legend''' in the dropdown menu at the top right of the graph.


#Fill in the B column for Temperature (Co) using the Fahrenheit to Celsius conversion formula.
=== Modeling the 2019 Distribution ===
##<math>{^\circ C} = \frac{5}{9} \left( {^\circ F} - 32 \right)\,</math>
#To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the 2019 year. This part will continue with the raw data obtained from the previous part.  
##In cell B7, enter the formula “ = (5/9) * (A7 - 32)”. This will calculate the temperature using the data in cell A7. After clicking away from the cell, it should display the result of the equation.  
#On the '''Excel''' ribbon, click on the '''Insert tab''', and click '''2-D Pie''' on the '''Pie Chart''' option under the '''Charts''' group. This should create a blank line graph, as shown in Figure 6.[[Image:Lab10_pieChart.png|500px|thumb|center|Figure 6: Inserting a Pie Chart]]
##Reselect cell B7. Click and drag from the bottom-right corner of the cell, down to cell B16. This will copy the formula for the other cells in the column, using the respective data in column A (A8 for cell B8, and so on). [[Image:Lab1.2.png|300px|thumb|center||Figure 2: Copying the equation to other cells]]
#Right-click on the empty graph and choose the '''Select Data''' option. A new window will appear where the graphed data can be inputted.
#Fill in the C column for Temperature (K) using the Celsius to Kelvin conversion formula
#Under the '''Legends Entries (Series)''' tab, click on the '''Add''' option. A new window called '''Edit Series''' will appear.
##<math>K = {^\circ C} + 273.15\,</math>
#In the '''Series Values''' textbox, highlight each renewable energy sources’ total energy consumption for 2019. Completing this will automatically create five labels under the '''Horizontal (Category) Axis Labels''' tab, as shown in Figure 7.[[Image:Lab10_horizontalAxisLabels.png|500px|thumb|center|Figure 7: Horizontal Axis Labels]]
##Refer to the procedure for Step 1 to fill out this column
#Select the row labeled 1 and click on the '''Edit''' option under '''Horizontal (Category) Axis Labels''' tab. In the '''Axis-label range''' textbox, select the names of each renewable energy  source (B2:F2) type in Hydroelectric Power and select '''OK'''. Repeat this step with the following rows with their corresponding energy sources.
#Fill in the D column for Volume (L) using the Ideal Gas Law
#Rename the title for the energy source shown in the graph.
##<math>PV = nRT\,</math>
##The values for n, R, and P are in cells B2, B3, and B4 respectively
##When entering the cells for n, R, and P into the formula, use the format “$LETTER$NUMBER” (e.g. $B$2). ##This will ensure that the cell doesn’t change when copying the formula into other cells.
#The graph in the template should look like Figure 3 after completing Step 3.[[Image:Lab1.3.png|500px|thumb|center||Figure 3: Graph after step 3]]
#Edit the graph and axis titles to the appropriate headers.


== 2. Microsoft PowerPoint ==
=== Fossil Fuel Energy Modeling ===
Recreate the [[Media:Presentation_Template_(1).pdf|Lab Presentation Template]] from scratch for lab presentations to be given during the semester. Use the results from completing the Excel worksheet to fill out the Data/Observation slides.
#Copy the data in the Fossil Fuel data sheet (located at the bottom left of the Excel spreadsheet) to the main data sheet. Ensure that the data on the main sheet is not overwritten by the Fossil Fuel data.
<!--=== Printing Instructions for Recitation ===
#Following the same steps as the Modeling a Yearly Trend procedure and Modeling a 2019 Distribution, create a new scatter plot and a new pie chart representing both renewable energy sources and fossil fuel energy sources.
When printing handouts for Recitation, select the following options in the print dialog.
##The renewable energy sources scatter plot should have five series: Hydroelectric, Geothermal, Solar, Wind, & Biomass.
* Handouts, six slides horizontal
##The fossil fuel energy sources  should have three series: Coal, Natural Gas, & Petroleum.
* Portrait orientation
#There should be eight series.
* Black and white or grayscale-->


== 3. Microsoft Word ==
At the end of this case study there should be four graphs:  
Recreate the [[Media: Report Template.pdf|Lab Report Template]] from scratch for lab reports to be written during the semester. Use the results from completing the Excel worksheet to fill out the Data/Observation section.
*A scatter plot displaying the renewable energy sources
*A pie chart showing the 2019 distribution in renewable energy
*A scatter plot displaying both renewable energy sources and fossil fuel energy sources
*A pie chart showing the 2019 distribution in renewable energy sources and fossil fuel energy sources


= Assignment =
= Assignment =


There is <b>no</b> individual lab report or team presentation for this part of Lab 1.
The Excel files must be zipped in a folder and submitted to the [https://eg.poly.edu/submit.php EG1004 website] by 11:59 PM the night before your next lab. There is no individual lab report or team presentation for this part of Lab 1.
 
{{Laboratory Experiments}}

Latest revision as of 02:05, 31 August 2022

Important: Please note that you must register on the EG1004 website before performing the lab.

Objective

The objective of this lab is to use Microsoft Excel to analyze two case studies. The first case study will evaluate the acceleration due to gravity of a free-falling body and the second case study will analyze the historical trends for energy consumption in the United States.

Overview

This lab is designed to teach proficiency with Microsoft Excel. This application is widely used in academia and the public and private sectors so proficiency in this application is a requirement. Completing assignments in EG1004 requires a basic competency in Microsoft Office and the following exercises are designed to create that competency.

Microsoft Office

Microsoft Office is a software suite that bundles Microsoft Excel, PowerPoint, Word, and additional programs and apps.

Microsoft Excel is a spreadsheet program. Its function is collecting, processing, and analyzing data. Microsoft Excel has many features that streamline data analysis. It can be used to generate tables and graphs. It is useful for showing the relationships between data sets and identifying trends. Graphs will be required for many lab reports and presentations.

Microsoft PowerPoint is a presentation application. Its function is to create slides for presentations. In this course, eight lab presentations, three Milestone presentations for the semester-long design project, and a final sales presentation for the semester-long design project will be created and presented using PowerPoint.

Microsoft Word is a word processing application. Its function is writing and editing text. In this course, Microsoft Word will be used to write nine lab reports.

Saving Work

The information on the lab PCs cannot be accessed outside of the lab rooms. Email copies of all the files created on the lab PCs to a personal account.

Materials and Equipment

  • A lab PC
  • Microsoft Office suite

Procedure

1. Case Study: Free-fall

In this exercise, common Excel equations and tools will be explored through the context of a problem.

A ball is dropped from the roof of the Bern Dibner Library building and the distance between the ball and the ground is measured and recorded as a function of time with a Lidar scanner. The time and distance data are shown in Table 1. The velocity in meters per second, average velocity in meters per second, and acceleration due to gravity in meters per second squared of the ball will be calculated using Excel.

Table 1: Time and Height Data
Time (s) Distance (m)
0
19.620
0.2
19.424
0.4
18.835
0.6
17.854
0.8
16.481
1.0
14.715
1.2
12.557
1.4
10.006
1.6
7.063
1.8
3.728
2.0
0
  1. Open the Microsoft Excel Template containing Table 1. The velocity of the ball is the change in distance over time. To calculate the instantaneous velocity at each point in time, a formula (1) is used.

    (1)

    1. In (1),v is the instantaneous velocity, is the second distance point, is the first distance point, is the time corresponding to the second distance point, and is the time corresponding to the first distance point.
      1. Assuming the initial velocity is zero, enter 0 into cell C2. In cell C3, enter the formula =(B3 - B2)/(A3 - A2). The velocity will be negative because the ball is traveling downwards.
  2. Reselect cell C3. Click and drag from the bottom-right corner of the cell down to cell C12. This will copy the formula for the other cells in the column using the data in column A and column B (A3, A4, B3, B4 for cell C4, and so on).
  3. To calculate the average velocity of the ball across the 11 data points, select cell D2. Enter the formula =AVERAGE(C2:C12).
  4. Microsoft Excel is commonly used to visually represent data in graphs. Create a scatter plot for the relationship between velocity and time.
    1. On the Excel ribbon, click on the Insert tab, and click Scatter on the Scatter option under the Charts group. This should create a blank graph, as shown in Figure 1.
      Figure 1: Inserting a Scatter Plot
    2. Right-click on the empty graph and select Select Data.
    3. Under the Legends Entries(Series) tab, click on the Add option, as shown in Figure 2. A new window called Edit Series will appear.
      Figure 2: Utilizing the Select Data Source Window
    4. In the Series X values textbox, delete the {1} and highlight cells A2 to A12. In the "Series Y" values textbox, delete the {1} and highlight cells C2 to C12. Click OK to generate the scatter plot.
    5. Add axis titles to the graph by clicking Axis Titles in the dropdown menu at the top right of the graph. Rename the axis and title.
  5. Acceleration is the rate of change of the velocity, or the slope of the velocity vs. time graph. For roughly linear data on Microsoft Excel, a line of best fit is used to approximate the relationship between the data points in (2).

    (2)

    1. In the dropdown menu at the top right of the graph, hover over Trendline and click on the black arrow. Select More Options.
    2. Under the Format Trendline window, check off Display Equation on chart.
    3. Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 . Discuss and explain the discrepancy.

2. Case Study: Historical Energy Statistics

This exercise provides an overview of creating line graphs and pie charts in Excel to analyze the historical trends of renewable energy consumption in the United States.

The statistical data presented in this exercise is derived from the U.S. Energy Information Administration (EIA). Using the data, four graphs will be constructed: a line graph showing the yearly trend for hydroelectric power, geothermal power, solar power, wind power, and biomass power, and a pie chart representing the 2019 distribution in renewable energy. These graphs will be appended with data regarding fossil fuel energy consumption.

Modeling a Yearly Trend

  1. Download the Excel file containing the data for the yearly energy consumption of various renewable energy sources from 1949 to 2019.
  2. The line graph will contain multiple lines that represent the different renewable energy sources. The procedure to graph the renewable energy consumption for hydroelectric power is provided. Complete a similar procedure for the remaining renewable energy sources.
    1. On the Excel ribbon, click on the Insert tab, and click Scatter with Straight Line on the Scatter option under the Charts group. This should create a blank line graph, as shown in Figure 3.
      Figure 3: Inserting a Line Graph
    2. Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
    3. Under the Legends Entries (Series) tab, click on the “Add” option, as shown in Figure 4. A new window called Edit Series will appear.
      Figure 4: Utilizing the Select Data Source Window
    4. In the Series name textbox, type in the name of the power source, Hydroelectric Power.
    5. In the Series X values textbox, highlight the years in column A from 1949 to 2019.
    6. In the Series Y values textbox, highlight the total energy consumption from hydroelectric power in column B that corresponds to 1949 to 2019. The Edit Series should appear similar to Figure 5.
      Figure 5: Utilizing the Edit Series Window
    7. Click OK to graph the Hydroelectric Power data.
  3. Next the data for the remaining renewable energy sources must be added to the same graph. To add the data, repeat steps 2b to 2g for each renewable energy source.
  4. Rename the axes and titles for the energy source in the graph. Include a legend by checking off Legend in the dropdown menu at the top right of the graph.

Modeling the 2019 Distribution

  1. To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the 2019 year. This part will continue with the raw data obtained from the previous part.
  2. On the Excel ribbon, click on the Insert tab, and click 2-D Pie on the Pie Chart option under the Charts group. This should create a blank line graph, as shown in Figure 6.
    Figure 6: Inserting a Pie Chart
  3. Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
  4. Under the Legends Entries (Series) tab, click on the Add option. A new window called Edit Series will appear.
  5. In the Series Values textbox, highlight each renewable energy sources’ total energy consumption for 2019. Completing this will automatically create five labels under the Horizontal (Category) Axis Labels tab, as shown in Figure 7.
    Figure 7: Horizontal Axis Labels
  6. Select the row labeled 1 and click on the Edit option under Horizontal (Category) Axis Labels tab. In the Axis-label range textbox, select the names of each renewable energy source (B2:F2) type in Hydroelectric Power and select OK. Repeat this step with the following rows with their corresponding energy sources.
  7. Rename the title for the energy source shown in the graph.

Fossil Fuel Energy Modeling

  1. Copy the data in the Fossil Fuel data sheet (located at the bottom left of the Excel spreadsheet) to the main data sheet. Ensure that the data on the main sheet is not overwritten by the Fossil Fuel data.
  2. Following the same steps as the Modeling a Yearly Trend procedure and Modeling a 2019 Distribution, create a new scatter plot and a new pie chart representing both renewable energy sources and fossil fuel energy sources.
    1. The renewable energy sources scatter plot should have five series: Hydroelectric, Geothermal, Solar, Wind, & Biomass.
    2. The fossil fuel energy sources should have three series: Coal, Natural Gas, & Petroleum.
  3. There should be eight series.

At the end of this case study there should be four graphs:

  • A scatter plot displaying the renewable energy sources
  • A pie chart showing the 2019 distribution in renewable energy
  • A scatter plot displaying both renewable energy sources and fossil fuel energy sources
  • A pie chart showing the 2019 distribution in renewable energy sources and fossil fuel energy sources

Assignment

The Excel files must be zipped in a folder and submitted to the EG1004 website by 11:59 PM the night before your next lab. There is no individual lab report or team presentation for this part of Lab 1.