Page 4 / 20

The Table Concept

Note

### A message from a student

As an entrepreneur, my family and I need a lot of Excel for budgeting and various calculations. This is where Excel tables are a real discovery for me. They are really handy for cost accounting and, for example, for comparing offers.

Mari Kuosmanen, farmer, Jokilehto farm

Although a range of cells can be treated as such, it is worth converting it into a table. Atable fills a rectangular part of a spreadsheet. Usually the first row of such a table contains headers for the columns. A table saves time in the future, and for some operations, a range must be a table in order to perform the operation.

When you convert a range into a table, you get benefits such as the following:

get a number of usability improvements
the data will be dynamic, so that e.g. diagrams and formulas will be updated automatically
the data becomes structurally more secure than a simple range, as individual cells cannot be removed from the table
some of the new features work better than in unconverted data (e.g. Power View and PowerPivot, which are discussed in their own separate sections)
you can use visual slicers to filter the table. We'll look at the dividers in more detail a little later.

The Objective

The range has been converted into a table and its layout has been reformatted. The table is named “Sales”. Model:

Task: Convert the range to a table

Let's start by turning the range into a table. Model:

Task: View the table

Click on the table (basically any cell in the table) and scroll down the table. Do you see the column headers appear above the table? Do you see the Table Design tab?

Mallitaulukon päällä on otsikot, jotka pysyvät mukana taulukkoa selatessa

Task: Change the appearance of the table

The color scheme used in the table is first and foremost a matter of taste. But we know that blue is the easiest color scheme for the eye. It is also possible to choose a coloring where every second line is highlighted with a different coloured background. This also helps to clarify the table.

Change the layout of the table. Model:

Task: Name the table as “Sales”

A table is automatically named (Table1, Table2 etc.), but it is a good and useful habit to give the table a name that describes its contents.

This is particularly useful if you have several tables in your workbook at some point and you refer to a table in a formula.

Let's name the table “Sales”.

Info: Converting a table into a range

The table can be converted back to a normal range. If you want to practise this, create a small exercise sheet and don't use the table you made above. The reason to this is that it is difficult to cancel a change.

Valitaan Vaalea-otsikon alta Ei mitään -vaihtoehto

If you want to convert the range back to a table later, you should remove the table styles with the first option of the **Table Styles** group (**None**) before converting the table to a range. Otherwise, the range will be left with designs that cannot be changed.

A table is converted to a range using the **Convert to Range** button in the **Tools** group on the **Table Design** tab. (The name of the tab in previous versions of Excel is **Table Formatting**.)

Are you ready?

Mark as complete: