Excel Core: Assignment 4
1
Assignment Description: This assignment will require you to review the terms, concepts and
fundamentals primarily covered in Lessons 10 through 12, and you should also still be comfortable
with the concepts in the earlier lessons. You will be completing one file for this assignment on your
computer and then submitting it online through Blackboard.
Obtaining the files needed to complete the assignment:
1. Open the file link in the Assignment 4 folder entitled Assignment 4 – Student Data Files
2. Right click on the file Assignment 4_Document to open the context menu.
3. Depending on the browser you are using, select the option Save link as or Save target as to open the save dialog box.
4. When the save dialog box opens, save the file to a location on your computer where you will be able to locate it again.
5. Repeat steps 2 through 4 for the files MusicCollection and Seagull.
All the instructions required to complete this assignment are listed below in a step‐by‐step
format. Save your work frequently.
Excel Core: Assignment 4
2
Assignment 4 Instructions:
1. Open the Assignment 4_Document file in Excel. In the File tab, save the file as an Excel Macro-Enabled Workbook, with the name Firstname_Lastname_Assignment4, where Firstname and Lastname are replaced with your first and last names. Note that when you save a workbook as an Excel Macro-Enabled workbook, the file extension will be xlsm, rather than xlsx.
Note that if you do not save your workbook in this format, then the macro you will be completing in steps 6, 7, and 8 will not be included in your assignment and therefore cannot be graded, resulting in a 0 for these steps.
Go to the Student Grades worksheet to complete steps 2 through 4.
2. Apply data validation over the cell range C3 through G11 that allows only decimal numbers in the range 0 through 1 to be entered. Note that 1 represents 100%. The data validation must include the input message Enter student grade. Additionally, set an error alert of style Stop, with the error message Grade must be a decimal number between 0 and 100%. Do not include a title for either the input message or error alert.
Make sure to test that your data validation rejects invalid data by attempting to enter a negative number (for example -1%), a number greater than 100% (for example 101%). Also try re-entering the original value in one of the cells to make sure that it is accepted. Don’t forget to type the % sign when entering a number. Make sure that all of the data is left at the original values when done.
3. Apply the 4 Traffic Lights icon set conditional formatting over the cell range C3 through G11.
4. Modify the conditional formatting rule you applied in the previous step as follows:
• a Green Circle icon of type Number is displayed for values greater than or equal to 0.8 (representing 80%). Note: Set the type box to Number before setting the values for the icons, just like you did in the learning activity.
• a Gray Circle icon of type Number is displayed for values less than 0.8 and greater than or equal to 0.6 (60%). Do not confuse gray with black – make use of the tool tips to help you.
• a Yellow Circle icon is displayed for values less than 0.6 and greater than or equal to 0.5 (50%).
• a Red Circle With Border icon is displayed for values less than 0.5.
When done you should see that grades less than 50% have a red stop light icon, grades in the 50’s have a yellow stop light icon, grades greater than or equal to 60 and less than 80 have a gray stop light icon, and grades 80 and above green stop light icon.
Go to the Music Collection worksheet to complete step 5.
5. Import the MusicCollection csv file beginning at cell A2 in the Music Collection worksheet. Note that the csv file uses a comma delimiter and it includes a header row.
Go to the Macro worksheet to complete steps 6 and 7.
6. Create a macro with the name SalesFormatting and has the shortcut key Ctrl+m. Do not begin recording the macro until the next step.
7. Record the macro so that it performs only the following tasks:
Excel Core: Assignment 4
3
• Merge and centre cells A1 through D1.
• Apply the Total cell style to cells A11 through D11.
Make sure that you are in the Macro worksheet before starting the recording, as you must not include switching between worksheets in your macro.
Go to the Macro Test worksheet to complete step 8.
8. Run the SalesFormatting macro in the Macro Test worksheet to ensure that it performs the required tasks.
Go to the Home Inventory worksheet to complete steps 9 through 11.
9. Sort the cell range A3 through E33 alphabetically by the type of room.
10. Using the Subtotal feature, apply a subtotal to the cell range A2 through E33, such that it groups by room type, includes a Sum for only the Qty and Total columns, applies a page break between groups, and sets the total summary so that it displays above the data.
11. Adjust the outline level so that only the room totals and grand total are displayed.
Go to the Gull Corporation worksheet to complete steps 12 through 25.
12. Insert the picture file named Seagull that you downloaded previously, such that the upper left corner of the picture is located in cell B3.
13. Change the height of the seagull picture to be 4 inches (or 10.2 cm). Make sure to use a technique that keeps the height to width ratio the same, so that the picture does not become distorted.
14. Apply the picture effect Soft Edges with a setting of 50 Point to the seagull picture.
15. Adjust the colour of the seagull picture to a recolor of Grayscale.
16. Apply the Crisscross Etching artistic effect to the seagull picture.
17. Insert a WordArt with the WordArt style Fill: Blue, Accent color 5; Outline: White, Background color 1; Hard Shadow: Blue, Accent color 5. Do not adjust the size of the WordArt.
18. Set the text of the WordArt to Gull Corp. (including the period).
19. Place the WordArt so that it is just covering the seagull's feet (approximately).
20. Insert a Name and Title Organization Chart SmartArt graphic to the right of the seagull picture.
21. Alter the organization chart so that it has four boxes on the third row instead of three.
22. Insert text in each box (name and title) of the organization chart as follows: top row – C. (Charlie) Gull is President, second row – Izzy Gull is Admin, third row – Henry Gull is Left Wing, Madi Gull is Drumstick, Grant Gull is Giblets, and Zach Gull is Right Wing. Make sure to put the titles (e.g. President) in the white title boxes. If you do not see the white title boxes, then you selected the wrong SmartArt graphic type.
23. Change the SmartArt style to Polished.
24. Change the SmartArt colours to Colored Outline – Accent 5.
25. Turn off display of the gridlines.
Save the workbook. Do not forget to double-check that the workbook is saved as an Excel Macro-Enabled Workbook.
Excel Core: Assignment 4
4
Rubric: Assignment 4
Rubric Marks
Workbook Management 2
Data Validation 8
Conditional Formatting 14
Import and Append Data 5
Macros 10
Worksheet Formatting 3
Subtotals 10
Insert and Format Pictures
11
Insert and Format WordArt
6
Insert and Format SmartArt
11
Total 80
File to Submit: Firstname_Lastname_Assignment4
Total marks available for this assignment = 80 marks worth 15% of your final grade.
How to Submit Files
Once you have completed the file for this assignment on your computer and saved the file with the
corresponding filename:
1. In Blackboard, go to the assignment area and select Assignment 4.
Excel Core: Assignment 4
5
2. Click on Assignment 4 File Upload link. Scroll down the page and click the Browse My Computer button.
3. A window will open asking you to select your file. Locate your assignment file Firstname_Lastname_Assignment4 on your computer and then click the OPEN button.
4. You should now see the file for submission in the area File Name. 5. Click the Submit button. The Save Draft button does NOT submit assignments to the facilitator.
If you encounter any problems with the assignment or have questions, email your facilitator – include
your name, the course title – Excel Core, and the assignment number – Assignment 4 in the email
subject line.
