Conditional Formatting (a) Create a copy of the Empleyee Salary worksheet and rename the woeksheet as Formatting. (b) Using the Fermatting worksheet, apply a conditional formatting rule to the Net Incorne column (Column J) to highlight values that are below the average Net Income. (c) Apply conditional formatting rale to highlight the entire row for those records wita an Excellent Performance or Salary greater than $70,000. Hint: Use the OR function. (d) Apply conditional formatting rule to biphlight the Frist Name, Last Name, and Location columns with employees located at Honten and Good Pcrformance. (c) Save your workbook: Filter and Sort (a) Create a eopy of the Kmployee Salary workheet and rename He workibect as Top Empioyees. (b) Acrly fileri to your Table to show recotds with an Ercellent perfuemusce wich a Net Inceme greater than 965,000 , Sort the result by the Net Inceme colurne (ascending ceder). (c) Russ the total row 10 yoer table and show the number of recards as well as the average Net Incuene for the fitiered dive (d) Save your workbook. Pivot Tables and Chart (a) Using the Employee Salary woektheet, ciease a Pivottable and place it in a new workshoet. Rename the workaheet as Ferformance Couat. (b) Drag and trop appropriate fields in the exmurary arca to show tho total number of eneployees for each Perfermance scale. your Piechart. (d) Uving the Employee Salary workaheet, creve a Pivectable and place it in a new wothatoot Resaine the worksoct as Tax by Lecatien. (i) Using the Employee Salary worksheet, create a PivotTable and place it in a new woekst Rename the wokkheet as Min Max Net Income. (j) Deag and drop appropriate fields itt the sammary area to show the highest and the lowest Inceme for cach location. Fonmat your table. (k) Format your workbook as desired (Heatings, Font, Fill coloc, Nuraber format ..) (1) Save and close your workbook: 2. Durham Technical Services (50 Points) Data File needed for this excrcise: IT Support-xks Durham Technical Services (DTS) provides IT suppect services for several compenies in the Draham. region. The company has been in cperation since 2010 and has grown to become one of the lending technical senice companics in the area. DTS tas prided itself on providing grat service at reasonable costs and time. Open and Save the Workboek (a) Open the workbook called IT Suppert.xisx and save it as Duralan IT Support.xlsx. (b) Insert a Doeumentation worksbeet and abd an appropriale title for the workbook in cell Al. In cell B3, enter your name, In cell B4, enter your stident number. In cell B5, enser loday's date, and in celi 86 , enser a sentence to describe the purpose of the woekbook. (c) Save yeur workbook. Yeur Task The IT Supportulux the coestins all II suppot services provided by DTS in the year 2021. Your task is 10 create an interactive dabboard uxing Excel PiverTables and Charis lo suminsarize the compuey's performance in 2021. For your reference, you can refer to a file nimed PivotTable (Dashbeard). alsx posied on Camvas Modules under lecture 7. Your sachboant - Sbonld be pleasiag to vicu. - Sheald commusicitehummarize key informarion on the company's performust in 202t. - The inficmation thould be condensed into key metugevitioding., making it easier for twekebolden to enderiand the conchany's buviness performance Open and Save the Werhhook (a) Opes the workhok cafled II Sepportalex and une it at Durham IT Suppertalks. (b) loict a Documeataties woekhect and ad4 an apgropriact titic for the woeklook in cell AI. In cell B3, snicr your name, In cell B4, enter yost inydert number, In cell ins, meter today'r dase, asd in cell P6, crter a senticroe ta deteribe the papose of the werlbosk. (c) Seve your worlbork. Your Task The IT Suppartalsx thie contains all IT suppert ucmoes provided to DTS in the yeat 2021. Your laik is perforesase in 2021 . Fer your reference, you eas refer to a file nunsed PiverTable (Dachbeardj-alis pested en Cavas Modules under lostue 7 Vour dechboent: - Sooda bo pleasirg so viewi itakehoiders to underitand the cocrouny't betiness performance. Your dasthoard abculd conhain: - Five os cight charte. Submassion Cuideline: Conditional Formatting (a) Create a copy of the Empleyee Salary worksheet and rename the woeksheet as Formatting. (b) Using the Fermatting worksheet, apply a conditional formatting rule to the Net Incorne column (Column J) to highlight values that are below the average Net Income. (c) Apply conditional formatting rale to highlight the entire row for those records wita an Excellent Performance or Salary greater than $70,000. Hint: Use the OR function. (d) Apply conditional formatting rule to biphlight the Frist Name, Last Name, and Location columns with employees located at Honten and Good Pcrformance. (c) Save your workbook: Filter and Sort (a) Create a eopy of the Kmployee Salary workheet and rename He workibect as Top Empioyees. (b) Acrly fileri to your Table to show recotds with an Ercellent perfuemusce wich a Net Inceme greater than 965,000 , Sort the result by the Net Inceme colurne (ascending ceder). (c) Russ the total row 10 yoer table and show the number of recards as well as the average Net Incuene for the fitiered dive (d) Save your workbook. Pivot Tables and Chart (a) Using the Employee Salary woektheet, ciease a Pivottable and place it in a new workshoet. Rename the workaheet as Ferformance Couat. (b) Drag and trop appropriate fields in the exmurary arca to show tho total number of eneployees for each Perfermance scale. your Piechart. (d) Uving the Employee Salary workaheet, creve a Pivectable and place it in a new wothatoot Resaine the worksoct as Tax by Lecatien. (i) Using the Employee Salary worksheet, create a PivotTable and place it in a new woekst Rename the wokkheet as Min Max Net Income. (j) Deag and drop appropriate fields itt the sammary area to show the highest and the lowest Inceme for cach location. Fonmat your table. (k) Format your workbook as desired (Heatings, Font, Fill coloc, Nuraber format ..) (1) Save and close your workbook: 2. Durham Technical Services (50 Points) Data File needed for this excrcise: IT Support-xks Durham Technical Services (DTS) provides IT suppect services for several compenies in the Draham. region. The company has been in cperation since 2010 and has grown to become one of the lending technical senice companics in the area. DTS tas prided itself on providing grat service at reasonable costs and time. Open and Save the Workboek (a) Open the workbook called IT Suppert.xisx and save it as Duralan IT Support.xlsx. (b) Insert a Doeumentation worksbeet and abd an appropriale title for the workbook in cell Al. In cell B3, enter your name, In cell B4, enter your stident number. In cell B5, enser loday's date, and in celi 86 , enser a sentence to describe the purpose of the woekbook. (c) Save yeur workbook. Yeur Task The IT Supportulux the coestins all II suppot services provided by DTS in the year 2021. Your task is 10 create an interactive dabboard uxing Excel PiverTables and Charis lo suminsarize the compuey's performance in 2021. For your reference, you can refer to a file nimed PivotTable (Dashbeard). alsx posied on Camvas Modules under lecture 7. Your sachboant - Sbonld be pleasiag to vicu. - Sheald commusicitehummarize key informarion on the company's performust in 202t. - The inficmation thould be condensed into key metugevitioding., making it easier for twekebolden to enderiand the conchany's buviness performance Open and Save the Werhhook (a) Opes the workhok cafled II Sepportalex and une it at Durham IT Suppertalks. (b) loict a Documeataties woekhect and ad4 an apgropriact titic for the woeklook in cell AI. In cell B3, snicr your name, In cell B4, enter yost inydert number, In cell ins, meter today'r dase, asd in cell P6, crter a senticroe ta deteribe the papose of the werlbosk. (c) Seve your worlbork. Your Task The IT Suppartalsx thie contains all IT suppert ucmoes provided to DTS in the yeat 2021. Your laik is perforesase in 2021 . Fer your reference, you eas refer to a file nunsed PiverTable (Dachbeardj-alis pested en Cavas Modules under lostue 7 Vour dechboent: - Sooda bo pleasirg so viewi itakehoiders to underitand the cocrouny't betiness performance. Your dasthoard abculd conhain: - Five os cight charte. Submassion Cuideline