Shulou(Shulou.com)11/24 Report--

In our work, we can't avoid using Excel to make tables-organize data-beautify tables.

Generally speaking, after we have just sorted out the data, the table is roughly this sub-sub:

Needless to say, as we all know, such forms have not been beautified and will definitely be withheld 🍗 ~

If you have learned a little bit of Excel, you will think that using [Ctrl+T] to convert a table into a "super table" can quickly beautify the data. Here, this is the picture below:

After being converted to a super table, the data not only becomes more beautiful, but also easy to view, so it is not suitable for wrong rows.

But the function of the super watch is much more than that. Xiao an will take you to learn its 9 functions!

Automatic filling formula

Entering a formula in the super table automatically populates a column, eliminating the drop-down fill step (you can also undo this fill operation).

Automatically update serial number

Enter the formula in cell A2:

The = ROW ()-1 formula is automatically populated to the last row of the super table to generate a sequence number starting from 1, and the sequence number can be updated automatically after adding or deleting rows.

Of course, if you do not apply the super table, directly use the ROW function, insert a new row sequence number can also be updated.

The difference is that new rows inserted by a super table are automatically numbered, while new rows inserted by a normal table do not automatically generate serial numbers.

Show title line

After converting to a super table, scroll down to view the data, and the header row will always be displayed on the column label, especially when there are many rows of data, it is very convenient for us to view the data!

Think about it, is this very similar to the freeze pane? But it's automatic.

Automatically extend the data area

If we continue to enter data in the last row of the super table, the new data will be automatically nested into the super table.

This function also lays the foundation for the linkage change of PivotTable and chart.

PS: linkage change means that PivotTable and charts automatically aggregate new data when you enter new data into a super table.

Linked PivotTable

After the new data is entered, the super table format is automatically inserted, based on which the PivotTable report is inserted.

In this way, the PivotTable also has the function of linkage update, that is, after the PivotTable is refreshed, the new data is automatically entered into the PivotTable.

Linkage chart

In addition to the PivotTable can be linked to update, the same is true of charts!

Enter new data into the table and the chart will be updated automatically:

Built-in filtering function

When converted to a super table, the data region automatically generates a filter button that can filter the data directly.

If you want to turn off filtering, you can use the shortcut key [Ctrl+Shift+L].

Rapid screening of slicer

As we all know, slicers can be used in charts or PivotTables to make it easier for us to display data.

But in Office 2010 and above, the slicer will also take effect on super tables, allowing you to filter quickly!

First of all, let's insert the slicer according to the department: [insert]-- [slicer]-- "Department"

Click the options in the slice to see that the super table will filter out the corresponding content according to the options of the slice, as shown below:

Does this screening method look more classy?

A variety of summary methods

After converting the table to a super table, position the cursor in the table, and you can see that there is a Table Design tab at the top.

Check "Summary Line" to show the summary line below the original data, as shown below:

You can choose the summary method here at will:

Of course, this summary is calculated by function, and as we can see in the edit bar, this is the SUBTOTAL function.

Therefore, the summary line only summarizes the visible cell data, which is very convenient to use with filtering.

If you want to include hidden cells for summary, you can refer to the following article ↓↓↓

Hide the line summary, why can't you remember it when you use it? You will know it after reading this article! This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Chang Xiaoan Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.