Table features and how to use it.(Microsoft Excel)
List of names, ledgers, and other tables can be conveniently converted to tables.
Steps
Setting
Select the cells you want to convert to a table and "Home", Format as Table" and select the color scheme you want.
This color scheme can be changed later.
The cell range is automatically set and adjusted if necessary, then click "OK.
Table conversion is complete. The color scheme will change and filters will be set.
When you select a cell that has been converted to a table, "Table Design" will appear in the menu.
On the left side of the table is the table name, which is initially "TableN".
This is the name of the table when it is referenced from outside the table.
As an example, it is changed to "SalesList".
Items in the "Design" menu
This section describes each item that appears in the menu when a cell in a table is selected.
Properties
Table Name
Specifies the name of an item in a table when it is referenced from outside the table.
Details are explained in "Structured References" below.
This name is also used for access from PowerAutomate.
Resize Table
You can change the cell range of a table.
Tools
Summarize with PivotTable
Create a pivot table based on the data in the table.
Remove Duplicates
If there are multiple rows with identical data, make them into one row.
Convert to Range
Untable and return to a normal cell range.
Incert Slicer
Specify a column and display a button-style filter.
Table Style Options
Header Row
If unchecked, the heading line will not be visible.Unchecking the checkbox makes the heading line invisible, but does not make the item name disappear.
Total Row
If checked, a tally line is added at the end.
Details of the summary rows are described below.
Banded Rows/Columns
Specifies whether or not to use a background color for each row and column.
First/Last column
Check each to apply the specific formatting.
Table Styles
You can change the color scheme of the entire table.
Table Specific Features
Totals Row
"Table" has been added to the right-click menu of table cells.
Selecting the tally row will add a "Totals Row" at the end.
The summary method can be changed by selecting a summary cell.
Note that this summary row is a SUBTOTAL function.
Therefore, it does not include values that are hidden by the filter.
Structured references
If "My table has headers" is checked, structured references will function.
The formula can be set by item (field) name, not by cell address.
The formula will look like this
=TableName[@FieldName]
When formulas are created in the same table, the table name can be omitted.
Specifying within the same table
Specifying with the mouse as usual results in a structured reference.
Because formulas are specified with meaningful item names instead of alphabetical ones, it is easier to understand the meaning when you look at the formula later.
It also makes the sheet more resistant to adding columns to the table.
Furthermore, the advantage of tables is that once you create a formula, it is automatically applied to all rows.
Specify from outside the table
Create a SUM function from outside the table.
When selected, it will automatically become a structured reference.
Because the number of rows is not specified, the function does not forget to extend the range of the function when rows are added to the table.
Right-Click Menu
When you right-click on a cell that has been converted to a table, a specific menu item will appear.
This is more efficient than using normal cell manipulation, such as taking over formatting and formulas when inserting.
