Excel provides four settings for Tables you should be aware of. 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 key 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. 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 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 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 When this setting is disabled, formulas will use normal cell references. Regardless of the setting, you’re free to use either normal cell references or structured references. The setting only controls whether structured references are used by default. 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 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 to 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
