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.
The table formats in the Table Styles gallery are not available if you select multiple nonadjacent cells before you click the Format as Table command button on the Home tab.
After you click the OK button in the Format As Table dialog box, Excel applies the formatting of the thumbnail you clicked in the gallery to the data table. Additionally, the Design tab appears under the Table Tools contextual tab at the end of the Ribbon, and the table is selected with the Quick Analysis tool appearing in the lower-right corner, as shown here.
The Design tab enables you to use Live Preview to see how your table would appear. Simply click the Quick Styles button on the Ribbon and then position the mouse pointer over any of the format thumbnails in the Table Styles group to see the data in your table appear in that table format. Click the button with the triangle pointing downward to scroll up new rows of table formats in the Table Styles group; click the button with the triangle pointing upward to scroll down rows without opening the Table Styles gallery and possibly obscuring the actual data table in the Worksheet area.
Click the More button (the one with the horizontal bar above the downward-pointing triangle) to redisplay the Table gallery and then mouse over the thumbnails in the Light, Medium, and Dark sections to have Live Preview apply them to the table.
Whenever you assign a format in the Table Styles gallery to one of the data tables in your workbook, Excel automatically assigns that table a generic range name (Table1, Table2, and so on). You can use the Table Name text box in the Properties group on the Design tab to rename the data table to give it a more descriptive range name.
You can also use the Tables option on the Quick Analysis tool to format your worksheet data as a table. Simply select the table data (including headings) as a cell range in the worksheet and then click the Tables option on the Quick Analysis tool, followed by the Table option below at the very beginning of the Tables' options. Excel then assigns the Table Style Medium 9 style to your table while at same time selecting the Design tab on the Ribbon. Then if you're not too keen on this table style, you can use Live Preview in the Tables Styles gallery on this tab to find the table formatting that you do want to use.
