[pconline skills] excel is very familiar to professionals. Draw charts, do statistics, and occasionally play visual analysis. However, there is a function that many people may not know. It does not exist significantly in Excel, but it solves the pain points of many traditional tables. Automatically expand the table, add edging, freeze the first row, support slicer… Greatly improve the efficiency of daily table production. It is precisely because of its so many magic skills that it is named super watch!
1. Make “Super Table”
There are many ways to make a “Super Table”, but the most commonly used one click conversion of an ordinary table to a “Super Table”. The specific method is: select the table area to be converted, press Ctrl + T, and then check “table contains title” according to the actual situation. After entering, a “super watch” was born.
Making a super table
2. Form intelligent expansion
Automatic expansion is an important attribute of super table. No matter how exquisite the traditional table is, there will be a physical boundary. Once the data exceeds the limit, it is necessary to reset the formula and format. The automatic expansion of super table can ensure that when a new record is added (regardless of row and column), it can automatically bring the record into the management category of the original table, and automatically inherit the previous formula and format, greatly reducing the pressure of later table maintenance.
Table intelligent extension
3. One click beautification
When a table becomes a super table, it will automatically have a new look. Like ordinary tables, we can also quickly adjust the appearance of super tables through “table tool” → “design” → “table style”. Of course, if your table already has a format and you don’t want to be disturbed by the super table style, just click “table style” → “None” to restore the previous table style.
Adjust supertable appearance
4. Built in filtering
The super table has built-in filtering function, that is, when we need to filter and classify, we just need to select directly in the super table. Moreover, compared with ordinary tables, Super Table filtering has another advantage: it can create multiple filters at the same time.
Built in filtering function
5. Automatic range
In daily use of formulas, it is often necessary to fix the value range (converted to absolute reference) by F4 key. But in the super table, this operation does not exist. The super table can automatically fix the value range. Taking the ranking as an example, the ordinary table needs to enter the formula “= rank (M6, M6: M19)”, and then fix “M6: M19” with F4 key to fill in the formula.
In the super table, M6: M19 will automatically change to a region reference value such as “[average score]”. The user only needs to complete the formula writing and hit enter to complete the whole column formula writing. At the same time, due to the intelligent expansion characteristics of the super table, when new records are added in the later stage, the value field will be expanded automatically, and there is no need to modify the formula frequently.
Formula automatically fixed range
6. One click summary
One click summary is also one of the functions of super table. When we need to perform summary operation on data, just check “summary row” in “table tool” → “table design”. Super table supports many summary commands. In addition to the most common “average”, “count”, “sum”, “maximum”, “minimum”, “standard deviation” and “variance”, users can also write their own complex formulas.
One click summary
7. Edging row / column
The super table supports the function of auto edge row / column. You can see this option by opening “table tool” → “design” → “table style options”. The edge bar (column) of super table is added intelligently. No matter how to add or delete the column, the normal display of color bar will not be affected. In the long table making, this function can greatly reduce the probability of users reading wrong lines.
Automatically add border lines
8. Rapid analysis
The super table has built-in quick analysis function. Just select the data field to be processed and click “quick analysis”. You can complete data formatting, chart making, value summary, pivot table, mini chart and other operations in one stop.
Quick analysis function
Super table supports slicer, and users can filter and view data from multiple dimensions. For example, to see which grade’s competition results, just click “table tool” → “tool” → “insert slicer”, and then check “grade”.
Write at the end
Super watch can solve a lot of problems in daily work, especially some of the operations that are usually done with the help of super watch can be easily completed. If you no longer need a super table, just click “table tool” → “tool” → “convert to area” to turn it back easily.
One click to turn the super table back to the normal table