How to Use Table Tools in Excel
This tutorial demonstrates how to use table tools in Excel.
Table Design Tab
The Ribbon in Excel is dynamic. This means that when you insert a table, chart, or pivot table into your worksheet, a new tab appears on your Ribbon which relates to the object that you have inserted.
1. To view the table tab, click in your table and you will notice that in the Ribbon, after the Help tab, a tab called Table Design is shown.
- Click on this tab in the Ribbon to see the Table Design tab functions.
Table Tools
Summarize With Pivot Table
1. In the Tools group of the Table Design tab, click Summarize with Pivot Table.
2. Your table will already be selected as the Table/Range. Make sure New Worksheet is selected, and then click OK.
3. A pivot table is placed in a new sheet. Select the Row, Column, and Value fields as required.
Remove Duplicates
You can remove any duplicate information from your table but using the Remove Duplicates tool.
1. In the Tools group of the Table Design tab, click Remove Duplicates.
- Your table will already be selected as the Table/Range. Select the columns in which to look for duplicates, or if you wish to remove duplicate rows entirely, make sure Select All is selected.
3. Click OK to remove the duplicate rows.
Convert to Range
If you wish to convert your table to a range (and lose the Table Design tab), You can Convert to Range.
1. In the Tools group of the Table Design tab, click Convert to Range.
2. Click Yes to convert your table to a range.
- Your table is converted to a standard range. Notice that the filter buttons have now been removed and the Table Design tab is no longer available.
Insert Slicer
As with a Pivot table, you are able to create Slicers when your data is formatted as a table.
To filter on more than one option in your slicer, you can hold down the CTRL key (for nonconsecutive values) or the SHIFT key (for consecutive values). Also, try using some shortcuts when you’re working with pivot tables.
Table Styles
You can change the style of your table by using the Table Styles in the Table Design tab on the Ribbon.
Table Style Options
Header Row, Total Row, and Filter Buttons
The Header Row, Total Row and Filter buttons are all toggle buttons in the Table Style Options group. You can switch these options on or off by clicking in your table, and then ticking the relevant button.
First Column, Last Column, Banded Rows, and Banded Columns
Similarly, you can do the same thing to format the first column and last column and to set banded rows and columns in your table.
Table Properties
Rename and Resize a Table.
The table properties in your Table Design Ribbon allow you to rename and resize a table either by adding rows or columns to the table
Rename a Table
To rename your table, in the Ribbon, go to Table Design > Properties and then type the name of your table in the name box.
Resize a Table
1. To resize your table, in the Ribbon, go to Table Design > Properties > Resize.
2. Amend the table range in the range box by either typing in the new range or dragging in your worksheet and then click OK.
3. The size of your table will then be adjusted.
4. You can also resize your table by (1) clicking the small handle at the bottom-right corner of the table, and (2) when your mouse turns to a black cross, drag either up or down to add or delete rows, or left or right to add or delete columns.
