{"id":290,"date":"2025-10-31T22:40:22","date_gmt":"2025-10-31T22:40:22","guid":{"rendered":"https:\/\/blog.callmelazy.eu\/?p=290"},"modified":"2025-10-31T22:41:59","modified_gmt":"2025-10-31T22:41:59","slug":"excels-format-as-table-button","status":"publish","type":"post","link":"https:\/\/blog.callmelazy.eu\/?p=290","title":{"rendered":"Excel’s “Format as Table” button"},"content":{"rendered":"\n

In the Home Ribbon, between the Conditional Formatting and Cells, you can find an often misunderstood button. It’s called Format as Table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

It’s not hard to assume that this button changes the range only visually. This illusion is often due to the button’s name itself: the word “Format” implies that the change is only visual.<\/p>\n\n\n\n

The reality is very different. Once you format a set of cells (called a Range in Excel language) as a table, many things change, in addition to their appearance.<\/p>\n\n\n\n

Creating a table<\/h2>\n\n\n\n

Start by creating a table and you will notice immediately that every column will have a header above it. Also, if you had duplicate column names, they will change automatically to be unique, as I illustrate in the screenshots below:<\/p>\n\n\n\n

\n
\n
\"\"<\/figure>\n<\/div>\n\n\n\n
\n
\"\"<\/figure>\n<\/div>\n<\/div>\n\n\n\n

When you create a table, it also gets a name. Every table, just like every worksheet, must have a name. You can see it, and change it if you want, by clicking on the Table Design <\/em>ribbon which appears once you create the table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Let’s now start to see tables in action. The first important change is when composing formulas.<\/p>\n\n\n\n

Formulas inside tables<\/h2>\n\n\n\n

Excel is very much about writing a formula once and applying it to many inputs. Challenges start when you need to go back and read your formulas again. To change them, to debug them, whatever. Then, suddenly, that A2*G2, which was obvious when you first entered the formula, is not so obvious anymore. The more complex a formula is, the worse it gets.<\/p>\n\n\n\n

Tables change that completely. They enable easy references to columns using their headers (titles). And that’s the reason Excel was forcing them to be unique. This is a game changer because, as soon as you choose meaningful headers for your tables, your formulas become readable. Let’s see an example:<\/p>\n\n\n\n

\n
\n
\n
\"\"<\/figure>\n<\/div>\n\n\n\n
\n
\"\"<\/figure>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n

On the left, you have a simple formula calculating the total cost from the unit cost and the quantity. It’s an easy formula, but if the cells B2 and C3 didn’t happen to be right next to it, it could be hard to find them when writing the formula in the first place, and even harder to read the formula after some time.<\/p>\n\n\n\n

Compose the same formula inside a Table and, instead of the usual coordinates, you get references using the table’s headers. This helps already, but it gets even better.<\/p>\n\n\n\n

While typing a formula inside the table, start by typing =[@<\/em> and you will get a list of the table’s headers, from which you can choose the one you need:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Start typing and the list includes only the headers starting from what you type, which makes it even easier to select the right one:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

References within tables<\/h2>\n\n\n\n

Let’s now see how we refer to other cells inside a table.<\/p>\n\n\n\n

In the examples above, you probably noticed the @<\/em> sign. This has a special meaning inside a Table: it means you refer to a cell in the same row as the cell you are editing. Keep in mind that this extends even outside of the specific table you are editing, which can sometimes be handy, but can be dangerous too.<\/p>\n\n\n\n

There are several ways to reference cells inside a table, and the main ones are:<\/p>\n\n\n\n