Let's talk about the characteristics of Excel 'table”

This article brings you relevant knowledge about excel, which mainly organizes issues related to the characteristics of l "table". By creating "table", you can easily group and group data. Analysis, and can filter, sort and other operations on the data in the "table" independently of the data in other rows and columns in the worksheet. Let's take a look at it together. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

In Excel, you can easily group and group data by creating "tables" Analysis, and can filter, sort and other operations on the data in the "table" independently of the data in other rows and columns in the worksheet. In addition, "Table" also has some features that regular tables do not have, such as fixed title rows, automatic expansion of table areas, automatic filling of formulas, etc. Add-ins such as Power Query and Power Pivot also rely on "Table".

Next, the editor will lead you to learn about the "table" function of Excel 2019. In order to facilitate the distinction, except for the button names in the ribbon, other parts of the article are referred to as "super tables" to refer to this special "table".

Creating a "Super Table"

The steps to create a "Super Table" are as follows.

Select the object, which is the cell area to be generated as a "super table", such as the A1:B9 cell area shown in Figure 1-1. If there are no empty rows or columns in the entire data area, you can select any non-empty cell (such as A4) as the selected object.

Click the [Format Table] drop-down button under the [Home] tab, and select any table style in the extended menu, as shown in Figure 1-2 Show.

You can also click the [Table] button under the [Insert] tab, as shown in Figure 1-2. Or press or .

Retain the default settings in the pop-up [Create Table] dialog box, and then click the [OK] button to generate a "super table", as shown in Figure 1-3.

Features of "Super Table"

Super table has the following characteristics.

There is one and only one title row. The content of the title row is in text format and has no repetitions. The original field titles are repeated. Titles that appear multiple times will be distinguished by numbers.

Automatically apply table styles.

All merged cells are automatically unmerged, and the original content is displayed in the first cell in the upper left corner of the original merged area.

Select any cell in the "Super Table" and scroll down the worksheet, the table title automatically replaces the column label of the worksheet, as shown in Figure 1-4.

The title row automatically adds a [Filter] button, and you can also insert a [Slicer] on the basis of the "Super Table" to quickly filter the data, such as As shown in Figure 1-5.

Changes in the application scope of "Super Table"

There is an application scope mark in the lower right corner of "Super Table", as shown by the arrow in Figure 1-6 At the pointed position, drag and drop this mark with the mouse to adjust the application range of the "Super Table".

Another adjustment method is to click the [Adjust Table Size] button under the [Table Tools] [Design] tab, and in the pop-up [Reset Table Size] Respecify the table range in the dialog box, as shown in Figure 1-7.

The application scope of "Super Table" can be automatically expanded. Enter content in any cell adjacent to "Super Table" on the right side or below of "Super Table" , the size of the SuperTable automatically expands to include cells with new input content. The newly expanded column will automatically add a title. If the original title content is [Custom Sequence], the new title content will be automatically generated according to the sequence rules. Otherwise, the default title of "Column Number" will be automatically generated.

Clearing the contents of the entire row or column will not cause the scope of the "Super Table" to be automatically reduced. If you need to reduce the scope of the "Super Table", in addition to dragging and dropping the application range mark with the mouse and adjusting the table size, You can also use the [Delete Column] or [Delete Row] function.

Calculation of "Super Table"

1. Calculated Column

"Super Table" enables the calculated column function by default.

If you enter a formula in any cell in the adjacent column on the right side of the "Super Table", the "Super Table" area will not only automatically expand, but also automatically apply the formula to all cells in the column, as shown in Figure 1-8. Show.

For the newly added calculated column, a smart mark of [AutoCorrect Options] will appear. Users can modify the settings as needed, as shown in Figure 1-9

The table size of "Super Table" can automatically expand with the data. For example, if you create a PivotTable and a chart using SuperTable as the data source, when you add data to the SuperTable, the data source range of the chart and PivotTable will automatically expand accordingly.

If this function fails due to some misoperation, you can click [File] → [Options] to open the [Excel Options] dialog box. Then click the [Proofing] → [AutoCorrect Options] command to open the [AutoCorrect] dialog box. Under the [Autoformat as you type] tab, select the [Fill formulas into table to create calculated columns] check box. , and finally click the [OK] button, as shown in Figure 1-10.

2. Summary row

You can use the [Summary row] function in "Super Table".

Select any cell in the "Super Table", select the [Summary Row] checkbox under the [Design] tab of [Table Tools], and the "Super Table" will automatically add a "Summary" row. The default summary method is summation, as shown in Figure 1-11.

Click the cell in the summary row, and a drop-down button will appear. You can select different summary methods in the drop-down list, and Excel will automatically generate the corresponding formula, as shown in Figure 1-12.

After adding a summary row, if you enter content in the lower cell adjacent to the "Super Table", it will not automatically Expand the application scope of "Super Table". At this time, you can click the cell of the last data record in the "Super Table" (the row above the summary row), such as cell C9 in Figure 1-12, press the button to add a new row to the table, and summarize the formula in the row The reference scope is also automatically extended.

Related learning recommendations: excel tutorial

The above is the detailed content of Let's talk about the characteristics of Excel 'table”. For more information, please follow other related articles on the PHP Chinese website!