Excel 2019 Formatting: Using the Format as Table Gallery

Here’s a formatting technique for formatting in Excel2019 that doesn’t require you to do any prior cell selecting. (Kinda figures, doesn’t it?) The Format as Table feature 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 in Excel 20129 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 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 in Excel 2019, 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.

The Design tab enables you to use Live Preview to see how your table would appear. Simply click the Table Styles continuation button on the Design tab of the Ribbon (or Quick Styles button, if it’s displayed) and then position the mouse pointer over any of the format thumbnails in the Table Styles drop-down palette 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 2019 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 Excel 2019 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.

Customizing table formats in Excel 2019

Excel 2019 has a ton of great features. In addition to enabling you to select a new format from the Table Styles gallery, the Design tab contains a Table Style Options group containing a bunch of check boxes that enable you to customize the look of the selected table format even further in Excel 2019:

• Header Row to display the top row with the labels that identify the type of data in each column as well as the Filter buttons for sorting and filtering data in the first row of the table. (This check box is selected by default.)
• Total Row to have Excel add a Total Row to the bottom of the table that displays the sums of each column that contains values. To apply another Statistical function to the values in a particular column, click the cell in that column’s Total Row to display a drop-down list button and then select the function — Average, Count, Count Numbers, Max, Min, Sum, StdDev (Standard Deviation), or Var (Variance).
• Banded Rows to have Excel apply shading to every other row in the table.
• First Column to have Excel display the row headings in the first column of the table in bold.
• Last Column to have Excel display the row headings in the last column of the table in bold.
• Banded Columns to have Excel apply shading to every other column in the table.
• Filter Button to have Excel display drop-down buttons to the right of the entries when the table’s Header Row is displayed that you can use to sort and filter the data in that column. (This check box is selected by default.)

When you finish selecting and/or customizing the formatting of your data table, click a cell outside of the table to remove the Table Tools contextual tab (with its Design tab) from the Ribbon. If later, you decide that you want to further experiment with the table’s formatting, click any of the table’s cells to redisplay the Table Tools’ Design tab at the end of the Ribbon.

Creating a new custom Table Style in Excel 2019

Excel 2019 lets you create your own custom styles to add to the Tables Styles gallery and use in formatting your worksheet tables. Once created, a custom Table Style not only applies just the kind of formatting you want for your worksheet tables but can also be reused on tables of data in any worksheet you create or edit in the future. You can even designate one of the custom styles you create as the new Table Style default for your workbook so that it’s automatically applied when you later format a data table in its worksheets with the Tables option on the Quick Analysis toolbar.

To create a custom Table Style in Excel 2019, you follow these steps:

1. Format the data in your worksheet as a table using one of the existing styles.
2. On the Design contextual tab of the Tables Tool tab, click the Table Styles More drop-down button and then select the New Table Style option near the bottom of the gallery.

   The New Table Style dialog box appears.

   Use the options in the New Table Style dialog box to create a new custom table style to add to the Table Styles gallery.
3. Replace the generic, table style name, Table Style 1, with a more descriptive name in the Name text box.
4. Modify each of the individual table components in the Table Elements list box (from Whole Table through Last Total Cell) with the custom formatting you want included in your new custom table style.

   To customize the formatting for a table element, select its name in the Table Element list box. After you select the element, click the Format button to open the Format Cells dialog box where you can change that element’s font style and/or color on its Font tab, the border style and/or color on its Border tab, or the fill effect and/or color on its Fill tab.

   Note that when customizing a First or Second Column or Row Stripe element (that controls the shading or banding of table’s column or row), in addition to changing the fill for the banding on the Fill tab of the Format Cells dialog box, you can also increase how many columns or rows are banded by increasing the number in the Stripe Size drop-down list that appears when you select one of the Stripe elements.

   As you assign new formatting to a particular table element, Excel displays a description of the formatting change below the Element Formatting heading of New Table Style dialog box, as long as that table element remains selected in the Table Element list box. When you add a new fill color to a particular element, this color appears in the Preview area of this dialog box regardless of which component is selected in the Table Element list box.

5. (Optional) If you want your new custom table style to become the default table style for all the data tables you format in your workbook, select the Set as Default Table Style for This Document check box.
6. Click the OK button to save the settings for your new custom table style and close the New Table Style dialog box.

As soon as you close the New Table Style dialog box, a thumbnail of the new custom table style you just created appears at the very beginning of the truncated Table Styles gallery that appears on the Design tab of the Table Tools font. You can then apply the newly created style to the currently selected table of data (the sample table you formatting in order to create the new style) by clicking this thumbnail. When you next open the entire Table Styles gallery to format other tables of data that you add to your worksheets, the thumbnail of the custom style you created will appear in its own Custom section at the very top of the gallery.

If you made changes to the fill formatting for the First or Second Column Stripe element in the custom table style, don’t forget to select the Banded Rows check box in Table Styles Options section on the Table Tools Design tab to make it appear in the formatted data table. So, too, if you made changes to the First or Second Row Stripe, you need to select the Banded Rows check box to make these formats appear.

To further modify, copy (in order to use its settings as the basis for a new custom style), delete, or add a custom style to the Quick Analysis toolbar, right-click its thumbnail image in the Table Styles gallery and then choose the Modify, Duplicate, Delete, or Add Gallery to Quick Access Toolbar option on its context menu. Check here to find out how to share Excel tables from OneDrive.