Use formatted tables

Create a new formatted table

To make it easier for you to work with data, the Spreadsheet Editor allows you to apply a table template to the selected cell range and automatically enable the filter. To do that,

1. Select a range of cells you need to format,

2. Click the Format as table template icon situated on the Home tab of the top toolbar.

3. Select the required template in the gallery,

4. In the opened pop-up window, check the cell range to be formatted as a table,

5. Check the Title if you wish the table headers to be included in the selected cell range, otherwise, the header row will be added at the top while the selected cell range will be moved one row down,

6. Click the OK button to apply the selected template.

The template will be applied to the selected range of cells, and you will be able to edit the table headers and apply the filter to work with your data.

It's also possible to insert a formatted table using the Table button on the Insert tab. In this case, the default table template is applied.

If you enter a new value in the cell below the last row of the table (if the table does not have the Total row) or in the cell to the right of the last column of the table, the formatted table will be automatically extended to include a new row or column. If you do not want to expand the table, click the Paste special button that will appear and select the Undo table autoexpansion option. Once you undo this action, the Redo table autoexpansion option will be available in this menu.

Select rows and columns

To select an entire row in the formatted table, move the mouse cursor over the left border of the table row until it turns into the black arrow , then left-click.

To select an entire column in the formatted table, move the mouse cursor over the top edge of the column header until it turns into the black arrow , then left-click. If you click once, the column data will be selected (as it is shown on the image below); if you click twice, the entire column including the header will be selected.

To select an entire formatted table, move the mouse cursor over the upper left corner of the formatted table until it turns into the diagonal black arrow , then left-click.

Edit formatted tables

Some of the table settings can be changed using the Table settings tab of the right sidebar that will open if you select at least one cell within the table with the mouse and click the Table settings icon on the right.

The Rows and Columns sections on the top allow you to emphasize certain rows/columns applying specific formatting to them, or highlight different rows/columns with different background colors to clearly distinguish them. The following options are available:

• Header: allows you to display the header row.

• Total: adds the Summary row at the bottom of the table.

  Note: If this option is selected, you can also select a function to calculate the summary values. Once you select a cell in the Summary row, the button will be available to the right of the cell. Click it and choose the necessary function from the list: Average, Count, Max, Min, Sum, StdDev, or Var. The More functions option allows you to open the Insert Function window and choose any other function. If you choose the None option, the currently selected cell in the Summary row will not display a summary value for this column.

• Banded: enables the background color alternation for odd and even rows.

• Filter button: allows you to display the dropdown arrows in each cell of the header row. This option is only available when the Header option is selected.

• First: emphasizes the leftmost column in the table with special formatting.

• Last: emphasizes the rightmost column in the table with special formatting.

• Banded: enables the background color alternation for odd and even columns.

The Select From Template section allows you to choose one of the predefined tables styles. Each template combines certain formatting parameters, such as a background color, border style, row/column banding, etc. Depending on the options checked in the Rows and/or Columns sections above, the templates set will be displayed differently. For example, if you've checked the Header option in the Rows section and the Banded option in the Columns section, the displayed templates list will include only templates with the header row and banded columns enabled:

If you want to remove the current table style (background color, borders, etc.) without removing the table itself, apply the None template from the template list:

The Resize table section allows you to change the cell range the table formatting is applied to. Click the Select Data button - a new pop-up window will open. Change the link to the cell range in the entry field or select the necessary cell range in the worksheet with the mouse and click the OK button.

The Rows & Columns section allows you to perform the following operations:

• Select a row, column, all columns data excluding the header row, or the entire table including the header row.

• Insert a new row above or below the selected one as well as a new column to the left or the right of the selected one.

• Delete a row, column (depending on the cursor position or the selection), or the entire table.

The Remove duplicates option can be used if you want to remove duplicate values from the formatted table.

The Convert to range option can be used if you want to transform the table into a regular data range removing the filter but preserving the table style (i.e. cell and font colors, etc.). Once you apply this option, the Table settings tab on the right sidebar will be unavailable.

The Insert slicer option is used to create a slicer for the formatted table.

The Insert pivot table option is used to create a pivot table on the base of the formatted table.

Adjust formatted table advanced settings

To change the advanced table properties, use the Show advanced settings link on the right sidebar. The 'Table - Advanced Settings' window will open:

The Alternative Text tab allows you to specify the Title and the Description which will be read to people with vision or cognitive impairments to help them better understand what information the table contains.