MICe ASSIGN #2: Basic Tables/Inserts

 

REQUIREMENTS

 

Summary
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.

 

Click for ORIGINAL TEXT

 

Click for REQUIRED OUTCOME

 

 

To Do

 

PART 1

  • 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.

 

 

 

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

 

CHECKLIST

  • 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?

 

BACK TO TOP