Exceljet

Quick, clean, and to the point

Excel Table Options

In this video, we'll look at the options available for Excel Tables.

Excel provides four settings for Tables you should be aware of.

Include new rows and columns in tables

The first option controls whether a table range will automatically adjust as data changes. 

By default, this option is enabled, since this feature is a benefit of tables.

For example, if I enter a new row in this table, the table expands to include it.

The same is true when I add a new column header.

In Windows, the setting that controls this behavior is in the proofing area, under Autocorrect settings.

On a Mac, the setting is in the Tables and Filters area.

Windows: Options > Proofing, Autocorrect settings > Autoformat > include new rows and columns in tables.

Mac: Tables and Filters > Automatically expand tables

Notice with the option disabled, the table does not automatically expand.

Though I can still adjust the table range with the sizing handle.

Automatically fill formulas

Tables also have a setting to automatically fill formulas.

This setting will create Calculated Columns when a formula is entered in a column.

For example, if I add a column for tax, and enter a formula to calculate tax at 7%, the formula is automatically filled to the bottom of the table.

If I change the tax rate to 6%, that change is propagated throughout the entire column, even if I change the formula in another cell.

In Windows, you'll find this setting in the Proofing area, under Autocorrect options.

On a Mac, the setting is in the Tables and Filters pane.

Windows: Options > Proofing, autocorrect settings > Autoformat > Fill formulas to create calculated columns.

Mac: Tables and Filters > Automatically fill formulas

Use table names in formulas

The next table option is called "Use table names in formulas"

This setting controls whether formulas use structured references automatically.

For example, if I use the SUM function to calculate the sum of tax in another cell, notice the table and column name is automatically used.

In Windows, you can disable the setting at in the Formulas area of Excel Options

Options > Formulas > Working with Formulas > Use Table names in formulas

On a Mac, the setting is at Tables and Filters

With this setting is disabled, formulas will use normal references.

Regardless of the setting, you're free to use either normal cell references or structured references.

The setting only controls wether structured references are used by default.

Group dates when filtering

The last setting is related to filtering dates.

When Excel recognizes dates in a table column, it can optionally group those dates by year and month in the filter.

To illustrate how this works, I'll first disable the setting.

In Windows, you'll find this option in the advanced area, under under display options for this workbook.

On a mac, the setting is under Tables and Filters

Windows: Options > Advanced > Display options for this workbook > Group Dates in the AutoFilter menu

Mac: Tables and Filters

Next I'll create a table with this data, which has a date in the first column, and contains over 300 rows.

When I click the Date filter, notice I see all individual dates. It's possible group by month and year, but you'll need to click a lot of checkboxes.

I'll reenable the date group setting.

Back in the table, the filter now groups all dates neatly by year and month.

Course 
Excel Tables

Related shortcuts

ShiftTab
Tab
Enter
Return
CtrlZ
Z
CtrlT
T
AltFT
,
Author 
Dave Bruns