Excel 2016 Tips and Tricks

Excel 2016

Lesson 2 – Working with Tables

NOTE When you select any of the eight aggregate functions in the drop-down list, Excel uses the column name as the second argument for the SUBTOTAL function. This structured referencing means that when data is added to the table, it is automatically included in the calculation. You can use any Excel function in the total row by selecting More Functions from the drop-down list to open the Insert Function dialog box. To use structured referencing in these functions instead of ordinary cell references, type the column name enclosed in square brackets. This will ensure that new data added to the table will be included in your calculations automatically. SUBTOTAL is the only function that recalculates its result when you filter a table.



P ROCEDURES

1. Select a cell in the table. 2. Select the Design tab. 3. Select the

option in the Table Style Options group. 4. Select the cell in the total row for the field you want to calculate. 5. Select the list arrow. 6. Select the desired function.



S TEP - BY -S TEP

Create a total row.

Use the Year Filter arrow to display only data for the year 2006 .

Steps

Practice Data

1. Select a cell in the table.

Click cell B4

The cell is selected and the Design tab appears on the Ribbon .

2. Select the Design tab.

Click Design

The Design tab is displayed.

3. Select the Total Row option in the Table Style Options group. A total row appears at the bottom of the table.

Click

Total

Row to select it

OFFICEPRO, Inc.

Page 31