Excel Project Chapter 9-12 Exam
- Halsey
- Rating : 15
- Grade : A+
- Questions : 0
- Solutions : 335
- Blog : 0
- Earned : $5956.25
Chapter 9-12 Exam Instructions
Project Description:
For this project, you will update a workbook. You will format grouped worksheets, set up validation rules, and create functions. Additionally, you will import a text file and XML data and manipulate the imported text. Finally, you will modify the document properties, insert comments, and mart the workbook as final.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Open wake-final-Exam1.xlsx and save the workbook as Wake_LastName_FirstName.xlsx (Substituting your first and last name.)
Make sure the Invoice sheet is active.
0.000
2 Unlock the following cells: E4, B14:E33, E35, E37 7.000
3 In cell E4 put in the date: 6/1/2018
Then put in the following:
B14: Insulation in walls and attic
D14: T
E14: 935.25
B15: Labor for insulation
E15: 210
B16: Mud for wall texture
D16: T
E16: 57.84 8.000
4 Fix the subtotal in cell E34 to calculate sum the above cells (E14:E33)
Set the tax rate to 6.5% in cell E35
Have to total amount show up in cell D2
Protect the worksheet so locked cells cannot be modified (Set the password to: Wak3Invoice), and make sure that “Select locked cells” and “Select unlocked cells” is checked.
<hint> Please make sure you put in a THREE for the password. 9.000
5 Group the following worksheets (Qtr1, Qtr2, Qtr3, Qtr4).
Fill the contents AND formats for cell A1 across the worksheets.
While still grouped, AutoSum the Dept. Totals AND the Monthly Totals at the same time.
Lastly, fill the formatting from A2:E8 across the worksheets and auto adjust the column A width. 8.000
6 Ungroup the worksheets and on the Qtr1 worksheet only, add the following data validations
B3: D3
Values need to be a decimal value less than or equal to 300,000
Add a Stop Error Alert
Title: Value Error
Error message: The value you entered was too high. Please check the value and re-enter. If you feel this value is correct, please contact your supervisor.
B6:D6
Values need to be a whole number between 0 and 200,000
Add an Input Message
Title: Enter Value
Input message: Please enter a value that is between 0 and 200,000. (Include the period)
Add a Warning Error Alert
Title: Invalid Entry
Input message: The value you entered was too high. Are you sure the value is correct? 10.000
7 Change the value in cell D6 to 255,235. Accept the value as a correct entry.
Check to see if the values entered are correct. To do this, circle the invalid data. 8.000
8 In the Yearly Totals worksheet, create a 3D formula to calculate the totals for the year
Change the formatting to accounting with 0 decimal places. 8.000
9 On the Stock Investments worksheet, pull in the current stock prices from finance.google.com.
On the right side of the page, you will find the World markets section. Select that data to be imported into the worksheet.
Make sure you place it in the Stock Investments worksheet in cell A2. 8.000
10 Modify the connection you just made with the Connection name World_Markets.
Next make the connection update every 2 minutes and when the file opens.
Set the description to: World markets on finance.google.com 10.000
11 Download the Names.txt so import all of the customer names.
Import the text file.
Make sure you select that your data has headers, and that you want to start importing at row 2.
Select that you want to separate the data based on the “Tab” AND “Space” delimiters
Place the data in cell B2 of the existing worksheet.
10.000
12 Use the CONCATENATE function to combine the first and last name with a space in-between.
(Auto size the column to fit the largest name)
Use the flash fill to show ONLY the first initials for the first and last name. 8.000
13 In the properties, add the following:
Title: Wake Workbooks
Tags: Invoice, Quarterly Totals, Investments
Add another author: Student 6.000
14 Save the workbook 0.000
15 Submit it to MyItLab 0.000
Total Points 100.000
[Solved] Excel Project Chapter 9-12 Exam
- This solution is not purchased yet.
- Submitted On 29 Nov, 2017 01:34:11
- Halsey
- Rating : 15
- Grade : A+
- Questions : 0
- Solutions : 335
- Blog : 0
- Earned : $5956.25