The Format as Table tool in Excel 2021 allows you to declare an entire set of data as a table and style all of its data in one step. After you’ve defined a cell range as a table, try clicking a new style icon in the Table Styles collection to change its layout. This table design — and, as a result, its table style — is immediately extended to all new rows you insert within the table and add at the bottom and any new columns you create within the table and add to either the left or left-right end.

To utilize the Format as Table feature, move the cell pointer somewhere inside the data table before pressing the Format as Table command button on the Home tab of the Ribbon. When you click the Format as Table command button, it opens the Table Styles gallery, separated into three sections: Light, Medium, and Dark, each of which indicates the intensity of the colors utilized by its various styles.

When you press one of your table configuring thumbnails in your Table Styles arcade, Excel takes an educated guess about which cell array of the data table to apply it to (as indicated by the marquee around its perimeter), and the Format As Table dialogue box appears, similar to the one shown in Figure below.

A text box labeled Where Is the Data for Your Table? Displays the address of the cell range now selected by the marquee, and a check box labeled My Table Has Headers appears in this dialogue box (selected by default).

Drag through the cell range to alter the highlight, and the area address in the Where Is the Data for Your Table? Text box if Excel does not correctly guess the range of the data table you want to build. If your data table doesn’t have column headers, deselect the My Table Has Headers check box before clicking OK; Excel will then add its column heads (Column1, Column2, Column3, and so on) to the new table’s top row.

Excel applies the format of the thumbnail you clicked in the gallery to the data table once you click the OK button in the Format as Table dialogue box, and the command icons on the Design tab of the Table Tools contextual section appear on the Ribbon.

You can utilize the Live Preview function on the Design contextual tab to check how your table data might look in different table styles. Click the Quick Styles button, then use the mouse or Touch pointer to select any of the format thumbnails in the Table Style group to see the data in your table appear in that table format. Use the vertical scroll bar to scroll through the styles in the Dark area of the gallery.

The Design tab features a Table Style Options group that you can use to personalize the look of the selected format further and allow you to select a new format from the Table gallery in the Table Styles group. The following checkboxes are found in the Table Style Options group:

Filter buttons should be added to each of the column heads in the table’s first row.

Total Row:  At the bottom of the table, add a Total row that shows the table’s last column (assuming that it contains values). Click the cell in that column’s Total row to apply a Statistical function other than a sum to the values in that column’s new Total row.

This brings up your drop-down menu where you can choose from None, StdDev (Standard Deviation), Count, Average, Max, Count Numbers, Sum, Min, or Var (Variation).

Add coloring to every other row on the table in a banded pattern.

First Column:  Make the first row of the table’s row headings bold.

Last Column:  Make the row headings in the table’s last row bold.

Apply shade to every other column in the table with banded columns.

By admin