Structured tables
All the tables we make have some structure. However, in order to connect with external data sources, Excel requires the structure of the table to be strictly formal. The structured table (Table) represents the named range with a clearly defined header and an applied autofilter. In addition to facilitating connection with other data sources, structured tables allow easy addition of new columns and formulas based on their names …
In order to create a structured table, we first need to have an ordinary table with a clearly defined form. We have to select such table, so to do that we position on the Insert ribbon and click on the Table option. Also, we can create a structured table by pressing the combination of the CTRL + T key. After this action, a dialog window will appear on the screen where it is necessary to indicate the range where the table is located (if we have previously selected a certain range, the dimensions of the table will be selected area). Also, it is necessary to mark the field My table has Headers, which emphasizes that the first line in the table is its header. This action is not required if the table that we create does not have a header. The newly created tables will get automatic filters and will be displayed using a specific formatting style.
When we create a structured table, by clicking inside it, the Design ribbon will be activated, which includes a number of options that allow us to change the look of the table. First, we can change the formatting style or some of its elements. Then, some other options will be offered: to change the name, to create a Pivot table, to remove duplicates, to convert the table into a string, to add a slider …
Each new table has a unique name within the workbook, and the elements in its columns are accessed by their name, which consists of the name of the table and the name of the column in square brackets. For example, the columns with a retail price (“RT PRICE”) in the table we have called “Table” is accessed by its name “Table [RT PRICE]”. This can be used to easily create additional columns in the table that contain the formulas in which they are used as argument data from some other columns in the table. For example, if we want to create an additional column “AMOUNT”, we will click on the field that is located outside the table, to the right of the first row with the data, and then enter the formula:
=Table[RT PRICE]*Table[QTY]
a new column will automatically be added, the name begins with the prefix Column, and ends with a ordering number. The lines will automatically be filled with formula values for all row values in the columns that we have specified as arguments. Now it is only necessary to change the name of the column in “AMOUNT” (or some other name, depending on what we wanted to achieve with the formula).
When making the formulas within a structured table, we can specify an entire column as an argument. For example, if we write the formula:
=AVERAGE([QTY])
all rows of newly created columns will be filled with an identical value, which is the average value of the given column.
The possibilities of applying structured tables are numerous, and the best effects are given when they are used to connect to databases. It is then possible to add new columns with the formulas to analyze these data in the framework of them, and then use them to fill in static reports (with the appropriate data collection function) or as a source for Pivot tables and/or charts.
When working with structured tables, we can use a special data aggregation function called SUBTOTAL. This function calculates the interval for the given values from the table, and differs from the classical sum that we do not need to change if we add new records to the table, and can also be used to add values and using some other functions other than the sum. The syntax of this function is:
SUBTOTAL (<function>,<range1>,…,<rangen>)
If the first argument is number 9 the sum will be made. However, if we specify another number, another function will be used to calculate the interval. The range is usually one, but it can be more columns in the table that we want to summarize.
