Chapter 17 · 17.8
A formatted table: column names in formulas, a total row and slicers
An ordinary list in a spreadsheet doesn't know where it starts and ends. If you type a row below it, you have to extend formulas and pivot tables yourself. If you turn the list into a formatted table, Euro-Office knows it is a single unit: the table grows automatically, gets filter buttons and a total row, and you can add slicers to it.
As an example, we use the product list of Bloemisterij Het Zonnetje with the columns Product, Category, Price, Number and Total.
Step 1: from list to table
- Click anywhere in the list, or select it including the header row.
- On the Home tab, click Format as table template and choose a style.
- The Create table window shows the range. Leave My table has headers ticked if the first row contains headings, and click OK.
A Table Design tab appears. This is where you switch on the Total row, add a Slicer and use Convert to range to turn the table back into an ordinary list, among other things.

Step 2: give the table a good name straight away
Euro-Office calls the first table Table1. A recognisable name is much more convenient in formulas. You change the name via the Formula tab, Named ranges, Name manager: choose Table1, click Edit and type, for example, Products. Do this before you create any formulas.

Step 3: formulas with column names
In a table, you can refer to columns by their name instead of by cell addresses. In the Total column, type in the first row, E2:
=Products[@Price]*Products[@Number]
[@Price] means: the price in this row. A formula like this is easier to read than =C2*D2. Outside the table, you refer to an entire column, for example =COUNTIF(Products[Category],"Plants"): here that gives 2.
Please note: in Excel, a formula in the first row of a table automatically fills the whole column. Euro-Office doesn't do this: the formula is only in E2. Drag it down to the last row yourself.
Step 4: adding a row
Type directly below the last row. The table automatically expands to include that row, along with the formatting and the filter buttons; in Name manager you can see the range of Products grow. Formulas elsewhere that refer to a table column, such as the COUNTIF above, include the new row straight away. A formula in the Total column is not added automatically: drag that down to the new row as well.

Step 5: total row and slicers
- On Table Design, tick Total row. An extra row appears below the table. Euro-Office puts the word Summary in it; you can type over it. The last column contains a formula with SUBTOTAL(109,...), which only counts the rows you see after filtering (see Add up only the filtered rows, even with an extra condition).
- Click Slicer, tick a column (for example Category) in the Insert slicers window and click OK. You get buttons for quick filtering. See also A small dashboard with sparklines and slicers.

What doesn't work: a drop-down list via a name on a table column
In Excel, you often create a name that points to a table column, for example ProductNames with the range =Products[Product], and use it as the source of a drop-down list. In Euro-Office you can create that name, but data validation then reports: "A named range you specified cannot be found." Instead, use an ordinary range as the source of the drop-down list, such as =$A$2:$A$9, and adjust it when the table grows (see Data validation and drop-down lists).

Pitfalls
- Convert to range on the Table Design tab turns the table back into an ordinary list.
- A pivot table based on a table doesn't refresh automatically: right-click in the pivot table and choose Refresh (see A pivot table by month, quarter or week).
