Convert the data to a table, name the table supportcalls, and apply the gold, table style medium 12.

 Exp19_Excel_Ch04_Cap_Tech_Support 

Exp19 Excel Ch04 Cap Tech Support 

Project Description:

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 departments 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 prepare the worksheet for printing

Download   and open the file named EXP19_Ch04_Cap_TechSupport.xlsx. Grader has automatically added   your last name to the beginning of the filename.

Freeze Panes so the first row   containing column headings (Row 5) on the SupportCalls worksheet will remain   static when scrolling. Ensure that Rows 1-4 are visible.

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

Remove duplicate records.

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).

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

Sort the table by Agent Name in   alphabetical order, add a second level to sort by Description, and create a   custom sort order as follows: Will not power on, Virus, Printing Issues, Software Update, Forgotten Password. Add a third level to sort by Duration smallest to   largest.
 

  Mac users, to create the custom list, from the Excel menu, click Preferences.   In the dialog box, click Custom Lists.

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

Use Quick Analysis to apply the   default Data Bars conditional formatting (Solid Blue Fill) to the Duration   column (range I6:I85). Mac users, on the Home tab, click Conditional   Formatting, point to Data Bars, and under Solid Fill, click Blue Data Bar.

Create a new conditional format   that applies Red fill and bold font to incident (range A6:A85) that required 30 or more days to resolve. Be   sure to use relative cell references in the conditional format formula.

Change page breaks so page 2   begins with the Computer ID column (column E).

Set the print scale to 85%.

Add a custom 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 workbook.   Submit the workbook as directed.

The tutorial explains how you can quickly apply or change table styles and remove table formatting keeping all features of an Excel table.

After you have created a table in Excel, what's the first thing you would like to do with it? Make it look exactly the way you want!

Luckily, Microsoft Excel provides a variety of predefined table styles that let you apply or change the table formatting in a click. If none of the built-in styles meets your needs, you can quickly create your own table style. In addition, you can show or hide the main table elements, such as header row, banded rows, total row, and so on. This tutorial will show you how to leverage these useful features and where to get started.

Excel table styles

Excel tables make it a lot easier to view and manage data by providing a handful of special features such as integrated filter and sort options, calculated columns, structured references, total row, etc.

By converting data to an Excel table, you also get a head start on the formatting. A newly inserted table comes already formatted with font and background colors, banded rows, borders, and so on. If you don't like the default table format, you can easily change it by selecting any of the inbuilt Table Styles on the Design tab.

The Design tab is the starting point to work with Excel table styles. It appears under the Table Tools contextual tab, as soon as you click any cell within a table.

As you can see on the screenshot above, the Table Styles gallery provides a collection of 50+ inbuilt styles grouped into Light, Medium, and Dark categories.

You can think of an Excel table style as a formatting template that automatically applies certain formats to table rows and columns, headers and totals row.

Apart from table formatting, you can use the Table Style Options to format the following table elements:

• Header row - display or hide the table headers.
• Total row - add the totals row at the end of the table with a list of functions for each total row cell.
• Banded rows and banded columns - show alternate row or column shading, respectively.
• First column and last column - apply special formatting for the first and last column of the table.
• Filter button - display or hide the filter arrows in the header row.

The following screenshot demonstrates the default Table Style options:

How to choose a table style when creating a table

To create a table formatted with a specific style, do the following:

1. Select the range of cells that you want to convert to a table.
2. On the Home tab, in the Styles group, click Format as Table.
   
   
3. In the Table Styles gallery, click the style that you want to apply. Done!

How to change table style in Excel

To apply a different style to an existing table, perform these steps:

1. Click any cell within the table whose style you want to change.
2. On the Design tab, in the Table Styles group, click the More button
   to show all available Excel Table styles.
3. Hover your mouse over the style you want to apply, and Excel will show you a life preview. To apply the new style, just click on it.

Tip. If you have applied any formatting to the table manually, e.g. highlighed certain cells in bold or with a different font color, selecting another Excel style will keep the manually applied formats in place. To apply a new style and remove any existing formatting, right-click on the style, and then click Apply and Clear Formatting.

How to change the default table style in Excel

To set a new default table style for a given workbook, right-click that style in the Table Styles gallery and select Set As Default:

And now, whenever you click Table on the Insert tab or press the table shortcut Ctrl+T, a new table will be created with the selected default format.

How to create a custom table style

If you are not quite happy with any of the built-in Excel table styles, you can create your own table style in this way:

1. On the Home tab, in the Styles group, click Format as Table. Or, select an existing table to display the Design tab, and click the More button
   .
2. Underneath the predefined styles, click New Table Style.
3. In the New Table Style window, type a name for your custom table style in the Name box.
   
4. Under Table Elements, select the element you want to format and click the Format button. The Format Cells dialog will open, and you select the desired formatting options on the Font, Border, and Fill tabs.

   To remove existing formatting, click the element, and then click the Clear button.

   Tips:

   • The formatted table elements are highlighted in bold in the Table Element box.
   • The formatting changes are shown in the Preview section on the right.
   • To use the newly created table style as the default style in the current workbook, select the Set as default table quick style for this document box.

5. Click OK to save your custom table style.

As soon as a custom style is created, it is automatically added to the Table Styles gallery:

To modify a custom table style, go to the Table Styles gallery, right-click on the style, and click Modify…

To delete a custom table style, right-click on it, and select Delete.

The built-in Excel table styles can neither be modified or deleted.

Tip. A custom table style is available only in the workbook where it is created. If you want to use it in another workbook, the fastest way is to copy the table with the custom style to that workbook. You can delete the copied table later and the custom style will remain in the Table Styles gallery.

How to apply a table style without creating an Excel table

If you want to quickly format the worksheet data with any of the inbuilt Excel table styles, but you don't want to convert a regular range to an Excel table, you can use the following workaround:

1. Select a range of cells to which you'd like to apply a table style.
2. On the Home tab, in the Styles group, click Format as Table, and then click the desired table style.
3. Select any cell within a newly created table, go to the Design tab > Tools group, and click Convert to Range.
   

   Or, right-click the table, point to Table, and click Convert to Range.
   

How to remove table formatting

If you want to keep all features of an Excel table and remove only the formatting such as banded rows, shading and borders, you can clear the table format in this way:

1. Select any cell in the table.
2. On the Design tab, in the Table Styles group, click the More button.
3. Underneath the table style templates, click Clear.
   

Tip. To remove a table but keep data and formatting, go to the Design tab Tools group, and click Convert to Range. Or, right-click anywhere within the table, and select Table > Convert to Range.

You may also be interested in

How do I convert data to a table in Excel?

How do you convert a cell range to a table using a table style?

How do I convert Excel data to Word table?

How do I style a table in Excel?