See all How-To Articles

How to Use Table Tools in Excel

This tutorial demonstrates how to use table tools in Excel.

tabletools ribbon

 

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.

tabletools new tab

  1. Click on this tab in the Ribbon to see the Table Design tab functions.

tabletools table design tab

Table Tools

Summarize With Pivot Table

1. In the Tools group of the Table Design tab, click Summarize with Pivot Table.

tabletools summarize pivot table

2. Your table will already be selected as the Table/Range. Make sure New Worksheet is selected, and then click OK.

tabletools insert pivot table

3. A pivot table is placed in a new sheet. Select the Row, Column, and Value fields as required.

tabletools pivot table

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.

tabletools tools remove duplicates

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

 

tabletools tools select duplicates

3. Click OK to remove the duplicate rows.

tabletools tools removed duplicates

 

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.

tabletools tools convert to range

2. Click Yes to convert your table to a range.

tabletools tools range

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

tabletools tools ribbon

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.

tablestyles

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.

headertotalfilter

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.

bandedrowscolumns

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.

tabletools tools rename

 

Resize a Table

1. To resize your table, in the Ribbon, go to Table Design > Properties > Resize.

tabletools tools resize ribbon

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.

tabletools tools resize

 

3. The size of your table will then be adjusted.

tabletools tools resized

 

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.

tabletools tools resize drag

See all How-To Articles