MICe ASSIGN #2: Basic Tables/Inserts
I’ll be grading your work based on what you upload to the CANVAS tool.. Name the file exactly like I require (below) or you will lose points.
This assignment will focus on basic formulas and tables, in Excel 2013.
Work through a two (2) sheet workbook in Excel 2013 using the context I provide in the Original Text link below as a starter. Use your best guess for colors to Format and personalize the page to look like the Required Outcome. DUE Thursday, February 19, by 11:55pm on the CANVAS tool. 25 points.
- Open the Required Outcome in order to see the example of how this assignment is to be revised.
- Download the Original Text, and save it as a new file called assign02firstinitiallastname.xlsx in your USB Flash drive's SCHCOOL/MIC102e/Excel folder. Example: assign02lbothell.xlsx.
- If your workbook opened with more than one worksheet, Delete the extra worksheets.
- Name the one remaining worksheet Contact Info.
- Set the worksheet margins to Narrow.
- Set the worksheet orientation to Landscape.
- Set worksheet size to Legal.
- Change worksheet's theme to ION.
- Change worksheet's color palette to ORANGE.
- Make up names, addresses, and other information needed in this worksheet to ADD to Rows 15-20, Cells A-L of each.
- Run a spell check, and correct any misspellings.
- Select Cells A3-L20 and change them into an Excel Table format. Make sure to choose "My Table Has Headers".
- Use the Table Tools contextual menu/ribbon to change the Table Style to "Table Style Light 10".
- Select Cells G4-G20 and change the cell format to Special, Phone Number.
- IF any of the email addresses in Cells H4-H20 are active hyperlinks, Remove the "active hyperlinks".
- Add the word TOTAL in Cell H21.
- Change Cells H21-K21 to Bold.
- Select Cell I21 and Use Autosum to get the total number of guests.
- Select Cells J4-J20 and change the cell format to Currency, No decimal points.
- Select Cell J21 and Use the formula =SUM(J4:J20) in the Formula Bar to get the total donations. IF the total does NOT display as Currency, No decimal points, change the format so it does. NOTE: do J4:J20, DO NOT just select the cells, or the formula wil be different and will not work in a step below.
- Select Cells K4-K20 and change the cell format to Currency, 2 decimal points.
- Copy the formula contents of Cell J21 into Cell K21. See how the formula autocalculates the total for column K.
- IF the Cell K21 total does not display as Currency, 2 decimal points, Copy the style of Cell K20 to Cell 21 so that it does. IF Cell K21 is no longer bold, Change it to bold.
- Use Conditional Formatting to change Cells L4-L20 so that "yes" has a light green background.
- Choose Insert Online Pictures/Office.com Clipart, and search for the Ice Cream graphic you see in the Required Outcome (or something very similar). Choose it and insert it.
- Use the Picture Tools contextual menu/ribbon to change the Picture Style to whatever color you like.
- Manually Resize the image and drag it to where you see it in the Required Outcome.
- Select Cells A3-L21, and increase the font size to 12 points.
- Sort the table of Cells A3-L20 alphabetically by LastName. DO NOT select anything from Row 21.
- Change to the Page Layout view.
- Select Cells A1-L23 and Set Print Area.
- Click "Click to add Header", and type ITC102e ASSIGN 2. Format it to be 16 point, and a dark Orange color.
- Click "Click to add Footer", and type your full name. Then, using the Header & Footer Tools contextual ribbon, also Insert the Current Date. Finally, Format it to be 9 point, medium gray.
PART 2 - DON'T Forget to do this TOO! :0)
- Make a copy of the Contact Info worksheet and move this copy to the end of the workbook. Change that new copy's worksheet title to Parking (filtered).
- In the Parking (filtered) worksheet, Change the title in Cell A1 to "Block Party Parking Info.
- In the Parking (filtered) worksheet, Hide Columns H-K. Do NOT delete them.
- In the Parking (filtered) worksheet, Filter the Parking column to show only the Yes selection.
- In the Parking (filtered) worksheet, Choose Cell L4, and insert a comment that reads "Parking Locations to be determined".
- Spell check the assignment.
- Save regularly. Get used to using Control-S to do this.
- Upload to CANVAS tool by the end of Thursday, February 19. At the Upload page, upload your assignment file. Verify that the correct file appears after you finish uploading.
Does your work include ALL the assignment requirements?
Have you double-checked that your assignment has all the requirements I listed in this assignment?
Have you uploaded your assignment before the required due date?
Have you emailed me at least 1 full day before the due date if you had serious problems completing the assignment?