To help the IT manager analyze the effectiveness of each support technician,create a custom sort to display Agents in alphabetic order, then by problem description, and then by incident duration.

Description

Capstone Exercise

IT Department Analysis You have been hired as a student assistant in the IT department of your university. As part of your responsibilities, you have been asked to enhance the Excel workbook used to analyze the department’s performance. The workbook contains records of all support issues resolved over the past year. You will convert the data to a table, format the table, sort and filter the table, insert calculations to evaluate key performance indicators, and then prepare the worksheet for printing

Prepare the Large Worksheet as a Table

You will freeze the panes so that labels remain onscreen. You also want to convert the data to a table so that you can apply table options.

Open the e04c1TechSupport workbook and save it as e04c1TechSupport_LastFirst.

Freeze Panes so the first row containing column headings (Row 5) on the SupportCalls worksheet will remain static when scrolling.

Convert the data to a table, name the table SupportCalls, and then apply the Gold, Table Style Medium 12.

Remove duplicate records.

Add a Structured Reference and a Total Row

To help the IT analyze productivity, you will use unqualified structured references to add a calculation to the table. You will also add a total row to provide basic summary data.

Add a new column to the table named Duration.

Create a formula using unqualified structured references to calculate the days required to resolve the incident (Date Resolved – Date created) and apply General Number Format.

Add a total row to display the Average days required to resolve an issue.

Sort and Filter the Table

To help the IT manager analyze the effectiveness of each support technician, you will create a custom sort to display Agents in alphabetic order, then by problem description, and then by incident duration.

Sort the table by Agent Name in alphabetic order, add a second level to sort by description, and then create a custom sort order as follows: Won’t power on, Virus, Printing Issues, Software Update, Forgotten Password. Add a third level to sort by duration smallest to largest.

Filter the table to only display closed incidents as indicated in the status column.

Apply Conditional Formatting

The IT department has a 30-day threshold to resolve all technical incidents. You will use conditional formatting to identify issues that lasted or exceeded 30 days to resolve.

Use Quick Analysis to apply Data Bars conditional formatting to the column that contains duration. (On a Mac, click the Home tab and use Conditional Formatting to apply Data Bars to the column that contains the data.)

Create a conditional format that applies Red fill and White Background 1 font color to the incidents (column A) that required 30 or more days to resolve.

Prepare the Worksheet for Printing

The final report will be distributed in print for your end-of-the-year meeting. You will set page breaks and repeating column headings before printing.

Select Landscape orientation for all sheets and set appropriate margins so that the data will print on one page. Set the print scale to 85%.

Change page breaks so agent information is not split between pages.

Set row 5 to repeat on each page that is printed.

Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

Save and close the file. Based on your instructor’s directions, submit e04c1TechSupport_LastFirst.

Last Completed Projects