| Grade |
Available |
| |
2 |
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. |
| |
|
Student Grades worksheet |
| |
8 |
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. Make sure that all of the data is left at the original values when done. |
| |
2 |
3. Apply the 4 Traffic Lights icon set conditional formatting over the cell range C3 through G11. |
| |
12 |
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 have a green stop light icon. |
| |
|
Music Collection worksheet |
| |
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. |
| |
|
Macro worksheet |
| |
2 |
6. Create a macro with the name SalesFormatting and has the shortcut key Ctrl+m. Do not begin recording the macrountil the next step. |
| |
6 |
7. Record the macro so that it performs only the following tasks: • 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. |
| |
|
Macro Test worksheet |
| |
2 |
8. Run the SalesFormatting macro in the Macro Test worksheet to ensure that it performs the required tasks. |
| |
|
Home Inventory worksheet |
| |
2 |
9. Sort the cell range A3 through E33 alphabetically by the type of room. |
| |
8 |
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. |
| |
2 |
11. Adjust the outline level so that only the room totals and grand total are displayed. |
| |
|
Gull Corporation worksheet |
| |
3 |
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. |
| |
2 |
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. |
| |
2 |
14. Apply the picture effect Soft Edges with a setting of 50 Point to the seagull picture. |
| |
2 |
15. Adjust the colour of the seagull picture to a recolor of Grayscale. |
| |
2 |
16. Apply the Crisscross Etching artistic effect to the seagull picture. |
| |
2 |
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. |
| |
2 |
18. Set the text of the WordArt to Gull Corp. (including the period). |
| |
2 |
19. Place the WordArt so that it is just covering the seagull's feet (approximately). |
| |
2 |
20. Insert a Name and Title Organization Chart SmartArt graphic to the right of the seagull picture. |
| |
2 |
21. Alter the organization chart so that it has four boxes on the third row instead of three. |
| |
3 |
22. Insert text in each box (name and title) of the organization chart as follows: top row – C. (Charlie) Gull is President, secondrow – Izzy Gull is Admin, third row – Henry Gull is Left Wing, Madi Gull is Drumstick, Grant Gull is Giblets, and Zach Gull isRight 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 youselected the wrong SmartArt graphic type. |
| |
2 |
23. Change the SmartArt style to Polished. |
| |
2 |
24. Change the SmartArt colours to Colored Outline – Accent 5. |
| |
1 |
25. Turn off display of the gridlines. |
| 0.0 |
80 |
| 0.0% |
