The Format as Table feature in Excel 2016 is so automatic that the
cell cursor just has to be within the table of data prior to you
clicking the Format as Table command button in the Styles group on the
Home tab.
Clicking the Format as Table command button opens its rather extensive Table Styles gallery with the formatting thumbnails divided into three sections — Light, Medium, and Dark — each of which describes the intensity of the colors used by its various formats.
As soon as you click one of the table formatting thumbnails in this Table Styles gallery, Excel makes its best guess as to the cell range of the data table to apply it to (indicated by the marquee around its perimeter) and the Format As Table dialog box, similar to the one shown here, appears.
This dialog box contains a Where Is the Data for Your Table text box that shows the address of the cell range currently selected by the marquee and a My Table Has Headers check box.
If Excel does not correctly guess the range of the data table you want to format, drag through the cell range to adjust the marquee and the range address in the Where Is the Data for Your Table text box. If your data table doesn't use column headers or, if the table has them, but you still don't want Excel to add Filter drop-down buttons to each column heading, deselect the My Table Has Headers check box before you click the OK button.
Clicking the Format as Table command button opens its rather extensive Table Styles gallery with the formatting thumbnails divided into three sections — Light, Medium, and Dark — each of which describes the intensity of the colors used by its various formats.
As soon as you click one of the table formatting thumbnails in this Table Styles gallery, Excel makes its best guess as to the cell range of the data table to apply it to (indicated by the marquee around its perimeter) and the Format As Table dialog box, similar to the one shown here, appears.
 | ||||
| Selecting a format from the Table Styles gallery and indicating its range in the Format As Table dialog box. |
This dialog box contains a Where Is the Data for Your Table text box that shows the address of the cell range currently selected by the marquee and a My Table Has Headers check box.
If Excel does not correctly guess the range of the data table you want to format, drag through the cell range to adjust the marquee and the range address in the Where Is the Data for Your Table text box. If your data table doesn't use column headers or, if the table has them, but you still don't want Excel to add Filter drop-down buttons to each column heading, deselect the My Table Has Headers check box before you click the OK button.
