Excel Quiz

1. Start Excel 2016 and download and open the file Excel Quiz1F18.

2. Save the workbook as FirstName_LastName_Excel_Quiz1 where FirstName is your own First Name and LastName is your Surname (for example Roger_de_Peiza_Excel_Quiz1).

3. On the Debt worksheetselect the range A1:I1, fill with Colour Purple Accent 4, and then Merge and Center.

4. Change the width of columns B to I to 90 pixels.

5. Wrap text in cells H2:I2.

6. In the range B8:H8, insert the column totals. Ensure that all the totals are properly seen.

7. Apply the Total cell style to range B8:H8only and then display no decimals.

8. In cell I3, calculate the Percent of Total Debt. In your formula, use an absolute cell reference when referring to cell H8.

9. AutoFill the formula down through cell I7, and then format the results as percentages with one decimal place.

10. In the range B10:G10, insert a function to calculate the highest monthly debt. In the range B11:G11, insert a function to calculate the lowest monthly debt. In the range B12:G12, insert a function to calculate the average monthly debt.

11. Insert a 3D Pie chart based on the nonadjacent ranges A2:A7 and H2:H7. Move the pie chart to a chart sheet with the sheet name Debt Chart.

12. For the pie chart, apply Layout 6, and then apply the Chart Style 8.

13. Change the data label Font Size to 12. Fill the chart background with blue, accent 1, lighter 80%.

14. Pull out slightly the largest piece of the pie.

15. Go back to the Debt worksheet, insert a 3D Clustered Column chart based on the range A2:G7.

16. Move the chart below the data, and then resize the chart to approximately the range A15:I38. Apply the chart Style 3.

17. Change the title of the chart to Aspen City Debt.

18. Insert three sheet rows at the top of the worksheet (before row 1).

19. Insert WordArt, using the style Gradient Fill - Purple, Accent 4, Outline - Accent 4.

20. Change the WordArt text to Aspen Falls and then change the FontSize to 36.

21. Move the WordArt to the top of the worksheet, centering it above the text "City Debt" on row 4.

22. Save your workbook.

23. Group the1st Quarter, 2nd Quarter, 3rd Quarter and 4th Quarter worksheets.

24. In cell E5, construct a formula to compute Net Income = Income - (Indirect Costs + Direct Costs).

25. AutoFill the formula down through cell E7.

26. In the nonadjacent ranges B5:E5 and B8:E8, apply the Currency [0] cell style.

27. Insert a new worksheet.

28. Rename the new Worksheet tabSummary and apply the worksheet tab color Orange, Accent 6.

29. Move the new worksheet tab to before the 1stQuarter worksheet.

30. Copy the range A1:E4 from any of the detail worksheets, and then on the Summary sheet, click cell A1. Paste the range using the Keep Source Column Widths paste option.

31. Change the subtitle of cell A2to City Center Annual Revenue and then change the label in cell A4 to Quarter.

32. In cell A5, type 1st Quarter and then AutoFill the labels in the range A6:A8. In cell A9, type Total

33. In the Summary worksheet, enter a formula in cell B5 setting the cell to equal cell B8 in the 1st Quarter worksheet.

34. Enter the Income total from the 2nd Quarter, the 3rd Quarter, and the 4th Quarter worksheets in the range B6:B8.

35. Select the range B5:B8, and then use the Quick Analysis Lens button to insert the column total.

36. AutoFill the range B5:B9 to the right through column E.

37. In row 9, apply the Total cell style.

38. Set the cell style of ranges B5:E5 and cells B9:E9to currency[0] and cell style of the range B6:E8to comma [0].

39. Autofit columns A to E.

40. Insert a Clustered Bar chart using the nonadjacent ranges A4:A8 and C4:D8 as the source data.

41. Move the chart to a chart sheet with the sheet name City Cost Chart.

42. Apply the Style 10 chart style.

43. Change the Chart Title to City Center Costs.

44. Save your workbook.

45. On the Recycling worksheetclick on cell E3 and insert the NOW function.

46. Select A5:G5, and apply a BottomBorder.

47. In F6:F27, insert Line Sparklines using the data in columns B:E.

48. Show the Low Point on the Sparklines.

49. In G6, insert the IF function. For the logical test, check whether theFY 2017 result is greater than the FY 2016 value in the same row.If the logical test is TRUE, Yesshould be displayed, and if it is FALSE,Needs Workshould be displayed.

50. Center the results, and then AutoFillG6 down through G27.

51. Select G6:G27. Apply a Text Contains conditional format that will display any cells that contain Needs Work formatted with Light Red Fill.

52. Save your workbook.

53. Create a copy of the Recycling sheet, and move the copy to the end of theworkbook. Rename the new worksheet tab Improvements.

54. On the Improvementssheet, format A5:G27 as an Excel table, usingthe Table Style Light 16.

55. Filter column G to display only the rowsthat improved from the previous year.

56. Display the Total row, and then display the four FY sums. In G28,select None.

57. Sort the FY 2016column from the smallest to the largest value.

58. Hide column B.

59. Save your workbook.

60. Group all the worksheets. Change the page orientation to Landscape.

61. Add the file name in the left footer and the sheet name in the rightfooter.

62. Enter your name in the Center section of the Header

63. Adjust the page width to one page.

64. Ensure that you are viewing your worksheets in Normal View.

65. Ungroup the sheets.

66. Save your workbook.

67. Upload your completed workbook to the Excel Quiz 1 dropbox.

Attachment:- Excel Quiz.zip