i need help with the excel worksheet listed

2. Click the Billable Hours sheet lab and select cell A4 3. Click the Format as Table button [Home tab, Styles group] use Light Gray, Table Style Medium 11 4. Select cell C6 and type the formula =C5+1 to add one to the date in the previous cell. 5. Autofill the formula to copy it to row 51 6 Type. 5 Add On in cell E4 and press Enter 7 Build a formula in cell E 6 to add. 5 to cetl D5 and press Enter to copy the formula Format results to display two decmal places and apply All Borders 8. Copy dates as values a. Select cell C5 Cell CS is a reference to the TODAY function in cell F2 b. Sort the column by date in newest to oldest orden Because the dales are calculated, the formulas are wrong The date that was originally in cell C5 sorted to row 51 and now has a relative relerence to cell F48. (Figure 4-113) c. Click the Undo button fUndo group. Home tabl and select cell C5 The formulas are restored a. Select cells C5:C51 You can copy and paste to replace. the formulas with the actual dates d Select cells C5:C51 You can copy and paste to replace the formulas with the actual dates Eller Systems Billable Hours To Date e. Press Ctrl+C to copy the range to the Clipboard Select cell C5 and click the arrow with the Paste button [Home tab, Clipboard group] 9. Choose Values (Figure 4-114) h Press Esc to remove the moving border and click cell C5 Formulas are replaced with the date Your dates will be different from figures in this text. 9 Sort the table by date in newest to oldest order. Then sort by Figure 4.113 Enors result when product/service in ascending order 10. Use Power Query to transform data. a Select the Contracts sheet tab and select cell A4. b. Use the From Text/CSV command to preview the: EllerText-04.trt file downloaded from the Resources link in the import Data window Use Power Query to transform data. a Select the Contracts sheet tab and select cell A4 b. Use the From TexuCSV command to preview the EllerText-04. ixt file downloaded from the Resources link in the import Data window Click Transform Data in the preview window to open Power Query. d Click the Address column label, pross Ctri and click the Phone Number label Click the Remove Columns button IHome tab; Manage Columns groupl: Select and remove these columns Zip, ID and First Contact (Figure 4-116) Scrolt the window as needed 10 see rows 28:43 There are 16 blank rows incorporated in the data The nuff text string in the Hours to Date column is a clat: Click the arrow with the Remove Rows button IHome tah, Reduce Rows groupl and choose Remove Bottom Rows Type 16 in the Number of rows box and click OK There are now 27 rows. Click the arrow with the Close \& Load button IHome tab. Giose groupl and load the data to cell A4 in the existing worksheet 11. Place a fomula in a table a Select cell F4, type. Fees as the header and gress Enter b Select cell F5 and build a formula to maltipiy the value in cell E 5 by 225 (c) Format the values in column F as Currency with zero decimal places d Close the Queries \& Connections pane; 12 Sort the data by Fees and then by City bothin ascending order: 13 Change the font size for cells A1:A2 to 20pt and set libe fonk style to bold 14 Select cells A1:F2 and center them across the soloction 4. Select cells A1:F2 and center them across the selection 15. Copy the Billable Hours sheet to the end and name the copy Data 16. Create a Pivotiable: a Select cell A5 and click the Table Name box [Table Design tab, Properties group] b. Name the table tblHours. c. Click the Summarize with PivotTable button [Table Design tab, Tools group]. The range is identified as tblHours d. Verity that New Worksheet is selected and click OK e. Name the sheet pivotTable 17. Manage fields in a PivotTable. a Show the Productservice and Billable fields in the Pivorfable b. Drag the Billable fieid from the Choose fields to add to report area below the Sum of Billable field Values area so that it appears twice in the report layout and the pane c. Select cell C4 and open the Value Fleld Sertings dialog box Tvoe Averake Hours as the Custom Name choose Averace as the calculation and set the Number format Select cell C4 and open the Value Field Settings dialog box Type Average Hours as the. Custom Name, choose Average as the calculation, and set the Number Format to Number with two decimal places Open the Value Field Settings dialog box for cell B4 and set its Custom Name to Total Hours and the number format to Number with two decimal places 1 Apply Dark Gray, Pivot Style Dark 11 with banded rows and columns. Select the Data sheet tab and copy cells A1:A2 to cell A1 on the PivotTable sheet Left align cells A1:A2 on the PivotTable sheel (Figure 4-116) d Select cells A4:E4 and change the font color to Black, Text 1 e. Use the Subtotal command to show a sum for billable and add on amounts for each product/service. 1. Apply All Borders to cells A66:E67 9. Display only the product/service and grand totals: Save and close the workbook (Figure 4-117) 2. Click the Billable Hours sheet lab and select cell A4 3. Click the Format as Table button [Home tab, Styles group] use Light Gray, Table Style Medium 11 4. Select cell C6 and type the formula =C5+1 to add one to the date in the previous cell. 5. Autofill the formula to copy it to row 51 6 Type. 5 Add On in cell E4 and press Enter 7 Build a formula in cell E 6 to add. 5 to cetl D5 and press Enter to copy the formula Format results to display two decmal places and apply All Borders 8. Copy dates as values a. Select cell C5 Cell CS is a reference to the TODAY function in cell F2 b. Sort the column by date in newest to oldest orden Because the dales are calculated, the formulas are wrong The date that was originally in cell C5 sorted to row 51 and now has a relative relerence to cell F48. (Figure 4-113) c. Click the Undo button fUndo group. Home tabl and select cell C5 The formulas are restored a. Select cells C5:C51 You can copy and paste to replace. the formulas with the actual dates d Select cells C5:C51 You can copy and paste to replace the formulas with the actual dates Eller Systems Billable Hours To Date e. Press Ctrl+C to copy the range to the Clipboard Select cell C5 and click the arrow with the Paste button [Home tab, Clipboard group] 9. Choose Values (Figure 4-114) h Press Esc to remove the moving border and click cell C5 Formulas are replaced with the date Your dates will be different from figures in this text. 9 Sort the table by date in newest to oldest order. Then sort by Figure 4.113 Enors result when product/service in ascending order 10. Use Power Query to transform data. a Select the Contracts sheet tab and select cell A4. b. Use the From Text/CSV command to preview the: EllerText-04.trt file downloaded from the Resources link in the import Data window Use Power Query to transform data. a Select the Contracts sheet tab and select cell A4 b. Use the From TexuCSV command to preview the EllerText-04. ixt file downloaded from the Resources link in the import Data window Click Transform Data in the preview window to open Power Query. d Click the Address column label, pross Ctri and click the Phone Number label Click the Remove Columns button IHome tab; Manage Columns groupl: Select and remove these columns Zip, ID and First Contact (Figure 4-116) Scrolt the window as needed 10 see rows 28:43 There are 16 blank rows incorporated in the data The nuff text string in the Hours to Date column is a clat: Click the arrow with the Remove Rows button IHome tah, Reduce Rows groupl and choose Remove Bottom Rows Type 16 in the Number of rows box and click OK There are now 27 rows. Click the arrow with the Close \& Load button IHome tab. Giose groupl and load the data to cell A4 in the existing worksheet 11. Place a fomula in a table a Select cell F4, type. Fees as the header and gress Enter b Select cell F5 and build a formula to maltipiy the value in cell E 5 by 225 (c) Format the values in column F as Currency with zero decimal places d Close the Queries \& Connections pane; 12 Sort the data by Fees and then by City bothin ascending order: 13 Change the font size for cells A1:A2 to 20pt and set libe fonk style to bold 14 Select cells A1:F2 and center them across the soloction 4. Select cells A1:F2 and center them across the selection 15. Copy the Billable Hours sheet to the end and name the copy Data 16. Create a Pivotiable: a Select cell A5 and click the Table Name box [Table Design tab, Properties group] b. Name the table tblHours. c. Click the Summarize with PivotTable button [Table Design tab, Tools group]. The range is identified as tblHours d. Verity that New Worksheet is selected and click OK e. Name the sheet pivotTable 17. Manage fields in a PivotTable. a Show the Productservice and Billable fields in the Pivorfable b. Drag the Billable fieid from the Choose fields to add to report area below the Sum of Billable field Values area so that it appears twice in the report layout and the pane c. Select cell C4 and open the Value Fleld Sertings dialog box Tvoe Averake Hours as the Custom Name choose Averace as the calculation and set the Number format Select cell C4 and open the Value Field Settings dialog box Type Average Hours as the. Custom Name, choose Average as the calculation, and set the Number Format to Number with two decimal places Open the Value Field Settings dialog box for cell B4 and set its Custom Name to Total Hours and the number format to Number with two decimal places 1 Apply Dark Gray, Pivot Style Dark 11 with banded rows and columns. Select the Data sheet tab and copy cells A1:A2 to cell A1 on the PivotTable sheet Left align cells A1:A2 on the PivotTable sheel (Figure 4-116) d Select cells A4:E4 and change the font color to Black, Text 1 e. Use the Subtotal command to show a sum for billable and add on amounts for each product/service. 1. Apply All Borders to cells A66:E67 9. Display only the product/service and grand totals: Save and close the workbook (Figure 4-117)