Lab 1
MGTSC 312: Lab One Excel Basics – September 6, 2012
Meet the team • Lab instructors ‒ Ted Ritzen: 8 AM, 9 AM, & 10 AM • [email protected]
‒ Kurtis Letwin: 11 AM & 12 PM • [email protected]
‒ Fernanda Campello de Souza: 1 PM & 2 PM • [email protected]
• Grading managers ‒ Cara Noble & Mark Wang • [email protected] & [email protected]
• Tech manager ‒ Nathan Ryhard • [email protected] MGTSC 312: Lab One - Excel Basics
2
Getting started • Authenticate ‒ CCID ‒ Password
• Login to uLearn (https://ulearn.ualberta.ca) ‒ Browse to “Fall 2012 MGTSC 312 Common” ‒ From the “Labs” section on the left, download the files for today ‒ Click on both the Excel file and the PowerPoint file, and SAVE TO DESKTOP
MGTSC 312: Lab One - Excel Basics
3
uLearn • Similar to eClass/WebCT, with small differences • On the course website you will find… ‒ Staff information under “Contacts” ‒ Lab materials, assignments, etc. ‒ Lecture materials ‒ And much more
MGTSC 312: Lab One - Excel Basics
4
Assignments/HW • Due on date indicated on „Assignments‟ page on uLearn • You can submit as many times as you want ‒ We will mark the most recent submission only
• Always place your student ID# on your homework • Cheating and plagiarism of homework is a serious offense
‒ Protect yourself: on public (lab) machines, delete files and empty the Recycle bin
MGTSC 312: Lab One - Excel Basics
5
Exams/Quizzes • Quizzes will take place in both the lab and the lectures • Quizzes in the lab will have a time limit of 10-15 minutes, and will begin at the start of the lab (on the hour) • Should you be late for a quiz, it is unlikely that you will be given extra time • Exams will take place in the lab • Exams will have a time limit and will begin five minutes after the start of the lab (five minutes after the hour) • Should you be late for an exam, you will not be given extra time • It is imperative that you attend the lab section that you are registered in for your Exams and Quizzes. Students will be unable to write Exams/Quizzes in lab sections that they are not registered in. • More information on Exams/Quizzes will follow prior to the first one. MGTSC 312: Lab One - Excel Basics
6
Excel 2007 vs. 2010 • You will be using Excel 2010 for exams in this course ‒ Files posted on uLearn will be in Excel 2010 (.xlsx) format ‒ Assignment and exam submissions should be saved in Office 2010 format ‒ Very Minute Differences between Excel 2007 and Excel 2010
• http://www.theultimatesteal.ca – Office Professional Academic 2010 yours for only $89 CAD
MGTSC 312: Lab One - Excel Basics
7
Help Sessions/Excel Brush-Up • Help Sessions will be offered in the lab prior to in-lab Quizzes and/or Exams • Help Sessions are designed to help students go over material that is causing them problems. There will be no lecturing during Help Sessions, only TAs available to answer questions. ‒ As time is limited during Help Sessions, please come prepared with your questions. It is best to keep on top of what you don‟t know – the Help Session is intended to be a last-minute review, not a time to learn new material!
• Should you feel that you are not up to speed with Excel, the OM Club offers an “Excel Brush-Up” training session to help! ‒ Dates: Monday, September 10th and 17th ‒ Time: 7:00 PM ‒ Location: BusB-18 ‒ Cost: FREE!! MGTSC 312: Lab One - Excel Basics
8
Excel Basics Introduction
Getting started… still • If you haven‟t already, download the lab files for Lab 1 from uLearn and save the Excel file to the Desktop before you open it
• Follow along ‒ It is good to put the PowerPoint file on one screen and the Excel file on the other so that you are able to follow along with both the notes and with what is happening on the overhead screen.
MGTSC 312: Lab One - Excel Basics
10
Getting around • CTRL + arrow keys ‒ Jumps to the end of a range of full cells ‒ Jumps over an empty range to the next full cell • CTRL + SHIFT + arrow keys ‒ Same effect as above, but selects all the cells you jumped over
• SHIFT + arrow keys ‒ Adds the next cell in the specified direction to your selected range of cells • Drag & fill: double click the lower right hand part of a cell or click and drag with mouse
MGTSC 312: Lab One - Excel Basics
11
Terminology • Workbook • Worksheet • Data set ‒ Variable = Column ‒ Individual Observation = Row
MGTSC 312: Lab One - Excel Basics
12
Random Samples • We are going to learn how to make two random samples from a large data set, using the =RAND() function, and will learn some basic Excel tricks along the way • First, if you haven‟t done so already, open the Excel file containing 5000+ data (Census Large Sample) ‒ This data was taken from a Canadian long-form census completed in 2006.
• As you can see, this data set contains data for over 5000 individuals who responded to the census • To perform tests with the data, we would like to make some smaller sub-samples to make our lives a bit easier • We will make two random sub-samples, one of 30 individuals and one of 300 individuals MGTSC 312: Lab One - Excel Basics
13
Random Samples • Start by right-clicking on the A for column A. Scroll down to Insert and select it to insert a new column in front of the data • Call this column Rand
• In the column Rand, type the function =RAND() and press ENTER • You will see that a random number has been generated between 0 and 1. If you push ENTER again, the random number will change
• Select cell A2, and click and drag the black square in the bottomright corner down to fill the function into the cells below A2 • A quick way to do this for the entire data set is to double-click the black square in the bottom-right corner of the cell
MGTSC 312: Lab One - Excel Basics
14
Random Samples • Now that we have propagated our =RAND() formula down, we must use Paste, Special Values to lock in our values so that they won‟t change every time we do anything on our data set • To use Paste Special, Values, first highlight the entire Column A ‒ You can do this by selecting cell A1 and dragging down until the bottom, by selecting cell A1 and pressing Control + Shift and the Down Arrow, or simply by clicking the A of Column A
• Next, Copy the data (Control + C, or right-click and select Copy)
MGTSC 312: Lab One - Excel Basics
15
Paste Special… • Values: converts formulae to • Right click on desired cell numbers, pastes without formatting
• Keyboard shortcut – Press the key combination ALT + E, S, V • Hold ALT while pressing the three letters in sequential order
MGTSC 312: Lab One - Excel Basics
16
Random Samples • Now that we have Pasted our values into Column A, let‟s sort the data from smallest to largest • Select the first value in Column A, and on the Home screen click Sort AZ
• Now that the data is sorted from smallest to largest, let‟s pick our sample of 30. • Choose the first 30 rows of data (from rows 1 to 31, as we need to include our titles) • Use keyboard shortcuts or simple highlighting to select all the data for these 31 rows • Copy the data (Control + C) MGTSC 312: Lab One - Excel Basics
17
Random Samples • Go to a New Worksheet, the tab to the right of Data 5000 and Paste the data into it (Control + V, or right-click and select Paste) • You should now have a data set with data for 30 individuals • Let‟s delete the Rand column in the new sheet by right-clicking the A of Column A, and selecting Delete • We can now sort our sample of 30 by highlighting the column PPSORT, and selecting Sort AZ on the Home screen
• Take five minutes (now if you have time or after the lab) to go through and do the same thing for a sample of 300 individuals. Put the data in Sheet 3. Remember to put RAND() back into the full data sheet. MGTSC 312: Lab One - Excel Basics
18
Random Samples • Let‟s save these two worksheets to a new Excel workbook • Right-click on Sheet 2, and select Move or Copy • In the drop-down menu, select New Workbook. Click OK. • Go back to our original work book (Census Large Sample), rightclick on Sheet 3 and select Move or Copy • In the drop-down menu, select Workbook 2. Click OK. • Save-As the new workbook to the DESKTOP, and either email yourself a copy or save it to a USB. We will be using this workbook in future labs.
MGTSC 312: Lab One - Excel Basics
19
Finishing Up • In the sample of 30 observations that we have created, let‟s find the average employment income of the respondents. • In cell L35, type the function =AVERAGE( • Now, select ONLY THE DATA from the column titled EMPIN, by selecting the first value and then pressing Cntrl + Shift + down arrow. • In cell L35 you should now have =AVERAGE(L2:L31 • Close the brackets and click ENTER • What did you find? The average employment income of the 5000+ observations is 42,417. Since we have all made our own random samples, everyone will have a different answer!
MGTSC 312: Lab One - Excel Basics
20
Formulae, functions, tools & wizards • Formula ‒ =B2*D2 Updates automatically if the data in B2 or D2 changes
• Function ‒ =SUM(F2:F6) Updates automatically if any data between F2 and F6 inclusive changes • Tools ‒ Data > Data Analysis ‒ Some automatically update while others don‟t • Wizards ‒ Chart (Insert > Choose Chart Type) ‒ PivotTable (Insert > PivotTable)
MGTSC 312: Lab One - Excel Basics
21
Using functions • Commonly used functions include… ‒ ‒ ‒ ‒ ‒
=SUM() =MAX() and =MIN() =AVERAGE() =STDEV() =NORMDIST(), =NORMSDIST(), =NORMINV(), NORMSINV()
• Toolbar icon S
• Insert > Function MGTSC 312: Lab One - Excel Basics
22
Closing thoughts • If you ever aren‟t sure of how to do something, ask! The TAs are here to answer any questions you may have.
• Practice makes perfect! If you are uncomfortable with Excel, take some time and play around with it. • MGTSC 312 Course Pack available for sale through the OM Club at the back of the lab now for $30 (Cash Only).
MGTSC 312: Lab One - Excel Basics
23
Meet the team • Lab instructors ‒ Ted Ritzen: 8 AM, 9 AM, & 10 AM • [email protected]
‒ Kurtis Letwin: 11 AM & 12 PM • [email protected]
‒ Fernanda Campello de Souza: 1 PM & 2 PM • [email protected]
• Grading managers ‒ Cara Noble & Mark Wang • [email protected] & [email protected]
• Tech manager ‒ Nathan Ryhard • [email protected] MGTSC 312: Lab One - Excel Basics
2
Getting started • Authenticate ‒ CCID ‒ Password
• Login to uLearn (https://ulearn.ualberta.ca) ‒ Browse to “Fall 2012 MGTSC 312 Common” ‒ From the “Labs” section on the left, download the files for today ‒ Click on both the Excel file and the PowerPoint file, and SAVE TO DESKTOP
MGTSC 312: Lab One - Excel Basics
3
uLearn • Similar to eClass/WebCT, with small differences • On the course website you will find… ‒ Staff information under “Contacts” ‒ Lab materials, assignments, etc. ‒ Lecture materials ‒ And much more
MGTSC 312: Lab One - Excel Basics
4
Assignments/HW • Due on date indicated on „Assignments‟ page on uLearn • You can submit as many times as you want ‒ We will mark the most recent submission only
• Always place your student ID# on your homework • Cheating and plagiarism of homework is a serious offense
‒ Protect yourself: on public (lab) machines, delete files and empty the Recycle bin
MGTSC 312: Lab One - Excel Basics
5
Exams/Quizzes • Quizzes will take place in both the lab and the lectures • Quizzes in the lab will have a time limit of 10-15 minutes, and will begin at the start of the lab (on the hour) • Should you be late for a quiz, it is unlikely that you will be given extra time • Exams will take place in the lab • Exams will have a time limit and will begin five minutes after the start of the lab (five minutes after the hour) • Should you be late for an exam, you will not be given extra time • It is imperative that you attend the lab section that you are registered in for your Exams and Quizzes. Students will be unable to write Exams/Quizzes in lab sections that they are not registered in. • More information on Exams/Quizzes will follow prior to the first one. MGTSC 312: Lab One - Excel Basics
6
Excel 2007 vs. 2010 • You will be using Excel 2010 for exams in this course ‒ Files posted on uLearn will be in Excel 2010 (.xlsx) format ‒ Assignment and exam submissions should be saved in Office 2010 format ‒ Very Minute Differences between Excel 2007 and Excel 2010
• http://www.theultimatesteal.ca – Office Professional Academic 2010 yours for only $89 CAD
MGTSC 312: Lab One - Excel Basics
7
Help Sessions/Excel Brush-Up • Help Sessions will be offered in the lab prior to in-lab Quizzes and/or Exams • Help Sessions are designed to help students go over material that is causing them problems. There will be no lecturing during Help Sessions, only TAs available to answer questions. ‒ As time is limited during Help Sessions, please come prepared with your questions. It is best to keep on top of what you don‟t know – the Help Session is intended to be a last-minute review, not a time to learn new material!
• Should you feel that you are not up to speed with Excel, the OM Club offers an “Excel Brush-Up” training session to help! ‒ Dates: Monday, September 10th and 17th ‒ Time: 7:00 PM ‒ Location: BusB-18 ‒ Cost: FREE!! MGTSC 312: Lab One - Excel Basics
8
Excel Basics Introduction
Getting started… still • If you haven‟t already, download the lab files for Lab 1 from uLearn and save the Excel file to the Desktop before you open it
• Follow along ‒ It is good to put the PowerPoint file on one screen and the Excel file on the other so that you are able to follow along with both the notes and with what is happening on the overhead screen.
MGTSC 312: Lab One - Excel Basics
10
Getting around • CTRL + arrow keys ‒ Jumps to the end of a range of full cells ‒ Jumps over an empty range to the next full cell • CTRL + SHIFT + arrow keys ‒ Same effect as above, but selects all the cells you jumped over
• SHIFT + arrow keys ‒ Adds the next cell in the specified direction to your selected range of cells • Drag & fill: double click the lower right hand part of a cell or click and drag with mouse
MGTSC 312: Lab One - Excel Basics
11
Terminology • Workbook • Worksheet • Data set ‒ Variable = Column ‒ Individual Observation = Row
MGTSC 312: Lab One - Excel Basics
12
Random Samples • We are going to learn how to make two random samples from a large data set, using the =RAND() function, and will learn some basic Excel tricks along the way • First, if you haven‟t done so already, open the Excel file containing 5000+ data (Census Large Sample) ‒ This data was taken from a Canadian long-form census completed in 2006.
• As you can see, this data set contains data for over 5000 individuals who responded to the census • To perform tests with the data, we would like to make some smaller sub-samples to make our lives a bit easier • We will make two random sub-samples, one of 30 individuals and one of 300 individuals MGTSC 312: Lab One - Excel Basics
13
Random Samples • Start by right-clicking on the A for column A. Scroll down to Insert and select it to insert a new column in front of the data • Call this column Rand
• In the column Rand, type the function =RAND() and press ENTER • You will see that a random number has been generated between 0 and 1. If you push ENTER again, the random number will change
• Select cell A2, and click and drag the black square in the bottomright corner down to fill the function into the cells below A2 • A quick way to do this for the entire data set is to double-click the black square in the bottom-right corner of the cell
MGTSC 312: Lab One - Excel Basics
14
Random Samples • Now that we have propagated our =RAND() formula down, we must use Paste, Special Values to lock in our values so that they won‟t change every time we do anything on our data set • To use Paste Special, Values, first highlight the entire Column A ‒ You can do this by selecting cell A1 and dragging down until the bottom, by selecting cell A1 and pressing Control + Shift and the Down Arrow, or simply by clicking the A of Column A
• Next, Copy the data (Control + C, or right-click and select Copy)
MGTSC 312: Lab One - Excel Basics
15
Paste Special… • Values: converts formulae to • Right click on desired cell numbers, pastes without formatting
• Keyboard shortcut – Press the key combination ALT + E, S, V • Hold ALT while pressing the three letters in sequential order
MGTSC 312: Lab One - Excel Basics
16
Random Samples • Now that we have Pasted our values into Column A, let‟s sort the data from smallest to largest • Select the first value in Column A, and on the Home screen click Sort AZ
• Now that the data is sorted from smallest to largest, let‟s pick our sample of 30. • Choose the first 30 rows of data (from rows 1 to 31, as we need to include our titles) • Use keyboard shortcuts or simple highlighting to select all the data for these 31 rows • Copy the data (Control + C) MGTSC 312: Lab One - Excel Basics
17
Random Samples • Go to a New Worksheet, the tab to the right of Data 5000 and Paste the data into it (Control + V, or right-click and select Paste) • You should now have a data set with data for 30 individuals • Let‟s delete the Rand column in the new sheet by right-clicking the A of Column A, and selecting Delete • We can now sort our sample of 30 by highlighting the column PPSORT, and selecting Sort AZ on the Home screen
• Take five minutes (now if you have time or after the lab) to go through and do the same thing for a sample of 300 individuals. Put the data in Sheet 3. Remember to put RAND() back into the full data sheet. MGTSC 312: Lab One - Excel Basics
18
Random Samples • Let‟s save these two worksheets to a new Excel workbook • Right-click on Sheet 2, and select Move or Copy • In the drop-down menu, select New Workbook. Click OK. • Go back to our original work book (Census Large Sample), rightclick on Sheet 3 and select Move or Copy • In the drop-down menu, select Workbook 2. Click OK. • Save-As the new workbook to the DESKTOP, and either email yourself a copy or save it to a USB. We will be using this workbook in future labs.
MGTSC 312: Lab One - Excel Basics
19
Finishing Up • In the sample of 30 observations that we have created, let‟s find the average employment income of the respondents. • In cell L35, type the function =AVERAGE( • Now, select ONLY THE DATA from the column titled EMPIN, by selecting the first value and then pressing Cntrl + Shift + down arrow. • In cell L35 you should now have =AVERAGE(L2:L31 • Close the brackets and click ENTER • What did you find? The average employment income of the 5000+ observations is 42,417. Since we have all made our own random samples, everyone will have a different answer!
MGTSC 312: Lab One - Excel Basics
20
Formulae, functions, tools & wizards • Formula ‒ =B2*D2 Updates automatically if the data in B2 or D2 changes
• Function ‒ =SUM(F2:F6) Updates automatically if any data between F2 and F6 inclusive changes • Tools ‒ Data > Data Analysis ‒ Some automatically update while others don‟t • Wizards ‒ Chart (Insert > Choose Chart Type) ‒ PivotTable (Insert > PivotTable)
MGTSC 312: Lab One - Excel Basics
21
Using functions • Commonly used functions include… ‒ ‒ ‒ ‒ ‒
=SUM() =MAX() and =MIN() =AVERAGE() =STDEV() =NORMDIST(), =NORMSDIST(), =NORMINV(), NORMSINV()
• Toolbar icon S
• Insert > Function MGTSC 312: Lab One - Excel Basics
22
Closing thoughts • If you ever aren‟t sure of how to do something, ask! The TAs are here to answer any questions you may have.
• Practice makes perfect! If you are uncomfortable with Excel, take some time and play around with it. • MGTSC 312 Course Pack available for sale through the OM Club at the back of the lab now for $30 (Cash Only).
MGTSC 312: Lab One - Excel Basics
23
