' i need the steps on how to do these with functions to insert. '

Question

Manu · Accepted Answer

Answer: Download and open the file: Ensure the file named EXP19_Ch04_Cap_TechSupport.xlsx is open in Excel. Freeze Panes: Go to the View tab. Click Freeze Panes and select Freeze Panes again to keep the first row containing column headings visible. Convert data to a table: Select the data range (A5). Go to the Insert tab and click Table . Name the table SupportCalls . Apply the Gold, Table Style Medium 12. Remove duplicate records: With the table selected, go to the Table Design tab. Click Remove Duplicates and ensure all columns are selected, then click OK . Add a new column named Duration: Click on the cell in the next column after the last column of your table (likely column I). Name this column Duration . Create a formula for Duration: In the first cell under the Duration column, enter the formula =[@[Date Resolved]]-[@[Date created]] Press Enter to calculate the number of days to resolve each incident. Ensure the formula is applied to the entire column. Add a total row to display the average days to resolve an issue: Go to the Table Design tab and check the Total Row option. In the Duration column of the total row, select Average from the dropdown. Sort the table: Sort by Agent Name in alphabetical order. Add a second level sort by Description with the custom order: "Will not power on", "Virus", "Printing Issues", "Software Update", "Forgotten Password". Add a third level sort by Duration smallest to largest. Filter the table to show only closed incidents: Click the filter dropdown in the Status column. Select only Closed . Apply Data Bars conditional formatting to the Duration column: Select the Duration column. Go to the Home tab, click Conditional Formatting , select Data Bars , and choose the Blue Fill option. Create a conditional format for incidents resolved in 30 or more days: Select the range A6:A85 . Go to Conditional Formatting , select New Rule , choose Use a formula to determine which cells to format , and enter: =[@Duration]>=30 Set the format to red fill and bold font. Change page breaks: Insert a page break before the Computer ID column. Go to Page Layout , select Breaks , and then Insert Page Break . Set print scale to 85%: Go to Page Layout , set Scale to Fit options to 85%. Add footer: Go to Insert , select Header & Footer , and add the required footer with your name on the left side, sheet name code in the center, and file name on the right side. Save and submit the workbook: Save your workbook. Follow the instructions to submit your workbook as directed

AI tools

Study help

Free tools

Earn & more

Computer science

Computer science

Manu

Answer

Extracted Transcript from Images

AI

Study help

Earn & more

Company