Excel 2013 Exam
Part 2 – Practical

  1. Open the file “Excel 2013 Exam” from my blog.
  2. Save the file in your personal file.
  3. Add a header with your name, period, and filename
  4. Insert three new rows at the top.
  5. In the first row, type “Primary Votes by County”
  6. In the second row, type “Rhode Island”
  7. In the third row, type “November, 2016”
  8. Merge and center each row from columns A-G
  9. Change the font in row 1 to Arial Black, size 20.
  10. Change row 2 and 3 to italics
  11. Change the width of columns A-G to 11.
  12. In column G, sum the totals for each county. Autofill down.
  13. Insert a new column before column A.
  14. In A4, type “Candidates”
  15. Center it both horizontally and vertically.
  16. Highlight A4:A10 and merge and center
  17. Rotate the text up
  18. Highlight C5:C10. Set the conditional formatting so that the highest number is shaded light red. (Hint: When creating a new rule, search for a rule type that has to do with top rankings. You want only the Top 1, not the Top 10.)
  19. Do the same for each county. Using Format Painter will speed up the process! There should be ONE red shaded box in each county column.
  20. In B11:B15, enter the text as shown in the image to the right:
  21. In C11, figure the average for each county. Autofill across to column G.
  22. In C12, figure the maximum for each county. Autofill across to column G.
  23. In C13, figure the minimum for each county. Autofill across to column G.
  24. In C15, figure the total number of votes for each county. Autofill across to column H.
  25. In C14, figure the Maximum percentage—this is a formula that takes the highest number of votes for that county (now in red) and the total number of votes possible (row 15) and calculates what percentage of the total votes went to that leading candidate.
  26. In A 11, type “Totals.”
  27. Highlight A11:A15.
  28. Merge and center
  29. Center vertically and horizontally
  30. Rotate the text up
  31. Highlight A4:H15 and add all borders.
  32. Highlight A1:H15 and add a thick box border
  33. Highlight A4:H10 and add a Thick BOTTOM border
  34. Highlight B4:H4 and shade it gray.
  35. Highlight B15:H15, shade it gray.
  36. Highlight A4:A15, shade it a lighter gray.
  37. Create the graph shown to below.
  38. Do not include the total column—just the counties!
  39. Use a 100% stacked column chart
  40. Switch the Row/Column
  41. Change at least two of the default colors
  42. Use the same style as shown
  43. Add data labels
  44. Back in your data in column B, change the fill for each candidate’s name to match their color in this graph.
  1. Create a pie chart with the candidates and the total votes for all counties, showing what percentage of votes they got overall.
  2. Title it “Overall Votes by Candidate”
  3. Choose Quick Layout #6
  4. Ensure that both graphs and the data fit on one page.
  5. Send to my e-mail.