2.8 The Excel Table

The table is the touchstone of a more advanced use of Excel. What exactly is this feature? It is a means to format and most importantly list data in Excel. To do this, select the range of data you are working with (on a single sheet), preferably having already included headers and on the Insert tab click Table.

The following confirmation window should then appear:

Make sure that the selected range is correct, that it includes the headers (otherwise uncheck the option and add later) and click OK.

Concretely, creating a table in Excel allows you to replace, within your formulas, the syntax A1:E21 in a single reference “Table1” (that you can rename any way you want by clicking on the Table Design tab, far right when you clicked in your table).

The fundamental advantage of this is that when you add data to your interval. For example, by adding an inspection visit to an infrastructure, such as in the attached example, this value will automatically be included in any formula that refers to your “Table1”. It will not be necessary to manually update A1:E22, this operation is automatic (as long as the entry is added to the last row of the created table).

Referencing is also easier when writing your formulas, referring for instance to your table by its name is possible (and you will find it directly in Excel by typing the first letters of the column name when writing a formula, for example Table1 in our example) as follows:

To refer to a column in your table, it’s just as easy: once you’ve written Table1, you append an open bracket (AltGr+5 on a French keyboard) and then select the column to reference, for example “target” in this case.

There are also two ways to reference the values in your table. Either by referring to an entire range of values (for example, when you want to make a search formula in the entire column), in this case you simply write the column name in square brackets, without @!) as in the following case:

Either you refer to a cell (because you want to add a formula in column F for example), and therefore use the column name in square brackets with an @ (the basic notation that Excel will offer you when you click on your table upon writing your formula):

You can also add automatic formulas at the bottom of the column that allow you to perform the most common operations in terms of data processing, which saves you from having to enter the formula (and therefore avoid typos, or errors of ranges and intervals).

You can also automatically change the formatting of your table in the Table Design tab.