Excel Basics Lesson 2 Packet Data
Excel Basics Lesson 2 Packet Key
Excel
Overview
- Intro to Stats Lesson 1 Video
- Complete Intro to Stats Lesson 1 Packet - note you will need to use excel and online lesson data
- Class Study Session for Help or a Workday - Complete Intro to Stats Lesson 1 Packet Practice Exam
- Intro to Stats Lesson 1 Exam (10 pts)
- Intro to Stats Lesson 2 Video
- Complete Intro to Stats Lesson 2 Packet - note you will need to use excel and online lesson data
- Submit Intro to Stats Lesson 2 Packet excel work in blackboard submittal area (5 pts)
Excel Lesson 1
Excel Basics Lesson 1 Packet
Assignment 1.1 – 1.11 will be done in one excel workbook. When complete with Excel Basics Lesson 1 & 2 Packet Submit to Blackboard > Excel Basics > Excel Basics Lesson 1 & 2 Packet Submit – There are videos that walk you through each assignment. Watch each section, pause the video, complete the task.
Navigate to Blackboard > 1. Excel Basics > Lesson 1 > Excel Basics Lesson 1 Video – This is the video for this unit.
Navigate to Blackboard > 1. Excel Basics > Lesson 1 > Excel Basics Lesson 1 Packet– Open all documents and excel files. This is what you will need to start with.
Assignment 1-1: Excel as a Calculator
- When you open Data Set #1 – Excel Basics, the only sheet available will be titled “Calculator” the Excel file will look like this
- Right-click on cell A1 and select the Insert option. Choose Shift cells down, then select OK.
- In Cell A1, type in “Using Excel as a Calculator”
- Highlight cells A1:C1, then click on the Merge & Center button in the Alignment section of the Home Ribbon, then click on the Left Align button
- In cell A3, rewrite 2+4 as a formula, type in =2+4, then hit enter. The cell should return the answer of 6. Double-click on cell A3 to see the formula that you entered.
Assignment 1-2: Cut, Copy, Paste Tutorial
Make sure you can do the following in excel:
- CTL + C (Copies)
- Copy the contents of cell A2
- CTL + V (Pastes)
- Paste the contents from the clipboard into Cell B2
- CTL + Z (Undo)
- Undo the pasting of the contents into Cell B2
- CTL + X (Cuts – Adds to the clipboard, essentially does the same thing as copy, except when you paste it to a new location, it cuts the original)
- ESC gets rid of selections
Note: When you right click and paste there are different paste options
Assignment 1-3: Order of Evaluation
Make sure you understand PEMDAS and that parenthesis are important in excel
Parenthesis, Exponents, Multiplication, Division, Addition, Subtraction
- In cell A5 the contents are 2+4*7, retype that formula into cell B5 using the = sign first to tell excel to do the calculation, excel should return 30
- If you wanted Excel to do the addition first, you need to put a parenthesis around the 2+4, see the contents of A7
- In cell B7 plug-in the formula needed to make the addition occur first. Excel should return to you 42.
Assignment 1-4: Basic Graph
- Add a new sheet to the Excel file. Rename Sheet2 “Basic Graph”
- Insert the following info beginning in cell A1
- Highlight all data and insert a column chart
- Change the title of the chart to “Basic Graph”
- Change the layout of the graph to “Layout 9”
- Change the fill of the chart to yellow
Assignment 1-5: Introduction to Formulas
- Add a new sheet to the right of “Basic Graph”
- Rename the new sheet “Formulas”
- Enter the following information
- Insert one row above row 1 and enter title “Bills” in the new A1 cell
- “AutoSum” the values in the B9 cell
- In cell B12 type what a colon means in a formula
- In cell B13 answer the following: Is a parenthesis needed after an operation function like SUM? Yes or NO
Assignment 1-6: Basic Tasks in Excel
- Add a new sheet after “Formulas” and name it “Basic Tasks”
- Change the column width to “20” and the row height to “25”
- Enter the following data beginning in cell A1
- “Sum” the values in cell B4
- In cell A6 type the formula for “3+3”
- Apply “Accounting” format to all numbers through row 5. *Note: Accounting format with TWO decimals will be used for all assignments
- Enter the following data beginning in cell D1
- Center all data in columns D and E
- “Autofill” information in columns D and E through row 10
- Use “Quick Analysis” to find the average of D and E and create a table.
- Using the table, “Sort” the rain column from “Largest to Smallest”
*Remember these operations are found under the “Home” tab in addition to “Quick Analysis”
Assignment 1-7: Basic Formatting (No Sheet Creation)
Be able to accomplish the following in excel.
- Change color of cells
- Change border of cells
- Clear selected cells
- Change row and column height and width
- Select whole sheet
- Merge and center cells
- Middle, top, and bottom Align
- Be able to make whole sheet into accounting format
- Increase decimals. WE WILL USE TWO DECIMALS ALWAYS
- Right click and bring up “Format Cells” menu
Assignment 1-8: Number Formatting
- Add a sheet behind “Basic Tasks”. Rename this sheet “Numbers”
- Enter “15.66” into cell B2
- Apply accounting format to this cell with two decimal places
*NOTE: All numbers will be in this format when doing excel
- In B5 enter “99.99” and in cell B6 enter “9.9”
- Format B5 and B6 in accounting format with NO dollar signs.
- IN B8 enter “11.29”. In B9 enter 0.88.
- Format B8 and B9 as percentages.
- In cell A1 find 1129% of 2000 using a formula. In cell A2 find 88% of 2000 using a formula.
Assignment 1-9: Force Printing One Page
- Create a new sheet behind “Numbers” and rename it “Print”.
- Enter the following data
- “Auto Fill” from A1 and A2 through A37
- “Auto Fill” from A1 and B1 through Q1
- Change view to “Page Layout” than back to “Normal”
- Under “File” “Print” be able to find the option to “Fit Sheet on One Page”. Select this option. Do NOT print.
- Be aware of the use of “CTL + Arrows”
Assignment 1-10: Practice Basic Formulas
- Create a new sheet after “Print” and rename it “Practice”
- Enter the following information with accounting formatting beginning in A1
- In B8 use a formula to average the salary of all professors.
- In C8 use a formula to find the median salary of all professors.
- Beginning in A10 enter the following data with all prices in accounting format:
- Find the amount paid in tax for cells B11 through B14. Assume a tax of 5%. Use a formula in B11 then autofill the formula through B14
- Figure price with Tax in cells C11 through C14 by entering a formula in C11 and auto filling it through C14. There are multiple correct formulas.
Assignment 1-11: Referencing Worksheets in Formulas
- Create two sheets after “Practice”. Rename the first “Overview” and rename the second “Business A”.
- Change the tab color of “Business A” to Red and the color of “Overview” to blue.
- On the “Business A” sheet enter to following data beginning in cell A1. Make sure it is in accounting formatting.
- Auto Sum the totals of column B in B4.
- Create a copy of worksheet A using the “Move or Copy” option when right clicking the sheet. DO NOT RENAME IT. (If it places the new sheet before business A this is fine you don’t have to move it)
*Useful when duplicating blank balance sheets and other statement sheets.
- Change the dollar values on the new sheet you just created to:
January: 1,000,000
February: 10
March: 10
- On the “Overview” sheet enter the following data starting in A1:
- On the “Overview” sheet in column B reference the total dollars made for each business in the months of January- March from sheets Business A and Business A (2).
- Change the March total of Business A (2) to $1,000,000 and observe how it changes the total on the overview sheet.
When you are all completed, check your excel file with the copy of my completed file that I will post to BlackBoard > 7. Excel Basics > Data Set #1 - Excel Basics – Answers
Excel Lesson 2
Excel Basics Lesson 2 Packet
Assignment 2.1 – 2.5 will be done in one excel workbook. When complete with Excel Basics Lesson 1 & 2 Packet Submit to Blackboard > Excel Basics > Excel Basics Lesson 1 & 2 Package Submit – There are videos that walk you through each assignment. Watch each section, pause the video, complete the task.
Navigate to Blackboard > 1. Excel Basics > Lesson 2 > Excel Basics Lesson 2 Video – This is the video for this unit.
Navigate to Blackboard > 1. Excel Basics > Lesson 2 > Excel Basics Lesson 2 Packet– Open all documents and excel files. This is what you will need to start with.
Assignment 2-1: Freeze Panes
- When you open “Excel Basics Lesson 2 Packet Data” go to the first sheet called “Cell Ref 1”
- Click “View” at the top of the page
- Click “Freeze Panes”
- Freeze the top row
- Now scroll down
Assignment 2-2: Absolute Cell Referencing
- Display sheet “Cell Ref 1”
- We will calculate the Revenue (how much money was made) for each calf if sold at weaning weight using the provided price.
- To do this we need to take the weaning weight in pounds multiplied times the price in $/lb. NOTE: Price for cattle is given in cwt or hundred weight. This is the price for 100 pounds of a calf. To find the price per pound we need to divide the cwt price by 100. Do this in cell L2. (It may round to $1.50 but this is just what is displayed. Excel keeps the real number)
- Repeat this step in L5 for the price of calves if fed to gain 200 pounds above weaning weight (around 725 lb average)
- Now we will calculate the revenue. To do this we enter the formula “=E2*$L$2”. The dollar signs lock the column L and the row 2 values so if we drag the formula to fill other cells the L2 cell is locked and does not move. The best way to enter formulas is to type the “=” sign then click the cells you wish to multiple rather than typing the letters. Try reentering the formula doing this. When you click L2 hit the F4 button once to automatically insert $ signs.
- Now we will drag the formula down to the calculate all values for all calves (called “auto filling” the formula. To do this place your cursor in the lower right-hand corner of the cell until you get a black + sign. Then left click and hold and drag to the end of the table. Values will calculate. If you click on any random “Revenue at WW” value, then click in the formula box up top, you will be able to see what cells are used in the formula. This is helpful to check to see if your formulas auto filled correctly.
- Next, we need to calculate the “WW + 200” to do this we will add 200 pounds to the weaning weight. This can be done by entering a formula in cell “G2” that is “= E2+200”. This can then be auto filled for all values repeating methods in step 6.
- Next we need to calculate “Revenue of WW + 200”. Repeat procedures from step 5 and 6 with the appropriate values to complete this.
Assignment 2-3: Cell Referencing (Not Absolute)
In assignment 2-2 you completed absolute cell referencing. This involves locking both the column (letters) and the row (numbers). However, you do not have to lock both. At times you may only want to lock the row or column using one $ sign.
- Display sheet “Cell Ref 2”
- We care going to calculate the revenue if we sold calves at WW (weaning weight) for 2020, 2021, 2022. To do this we use some of the steps from assignment 2-2. Please complete letter a using operations done in assignment 2-2.
- Find the price average per pound from the price average per cwt
- To calculate revenues for multiple years we need to autofill formulas down AND over. Because of this we do not lock the row and column of the price – we will enter a formula in cell “J2” and fill over to “K2” and “L2” so we do not need to lock the columns. However, when we fill down, we do not want the price to move down so we must lock the row. Based on this the formula entered in “J2” will read “=G2*O$3”
- Now auto fill the formula over to columns “K” and “L” and down to the end of the data.
Assignment 1-4: Basic Graph
Any of you that have cattle know that often calves are sorted and solid in different groups based on weight. To calculate the revenues in this type of scenario we can use the “IFS” formula in excel.
- Display Sheet “If”
- In this sheet we want to calculate the revenue of calves sold at pricing, but we have three prices. The first step is to convert the prices to $/lb instead of cwt. Do this in column H
- In order to calculate revenue quickly with this data type “=IFS(“ in cell “J2” and then click the “fx” symbol in the formula bar.
- In an “IFS” formula a test is conducted on the selected cell (Logical_test1). If the test is passed then it can calculate the value (Value_if_true1). If the value doesn’t pass the test it goes on to a second test. If this second test is passed, the value is calculated. If the second test is failed, it goes to the third and so on. There is another function called “IF” that is used if there is only two values, however we have three prices so we must use “IFS”
- The first logical test enter “D2<501” and in value if true1 enter “D2*$H$3” This is saying that if the weight of the calf is less that 501 pounds then use the price for calves from 400-500 pounds to find the formula.
- The second logical test enter “D2<601” and in value if true1 enter “D2*$H$4” This is saying that if the weight of the calf is less that 601 pounds then use the price for calves from 500-600 pounds to find the formula. (Note that “IFS” tests one at a time, so if it passes the first test it will calculate an answer and never get to test two)
- The third logical test enter “D2>=601” and in value if true1 enter “D2*$H$5” This is saying that if the weight of the calf is greater than or equal to 601 pounds then use the price for calves from 600-700 pounds to find the formula.
- The final formula reads “=IFS(D2<501,D2*$G$3/100,D2<601,D2*$G$4/100,D2>=601,D2*$G$5/100)” without the “fx” function this is super hard to enter unless you have done this formula a lot.
- Now drag down the formula to calculate revenue for all the calves.