Excel Tables 1 – What and Why?

This is a series about Excel Tables. I have covered this topic in the past but now we need more details. So let us start at the beginning. What are Excel Tables and why do we need them…

Excel allows us to use various types of data. Usually data is large and difficult to grasp. Therefore we need to summarize and analyze the data to make any sense out of it. In order to do that powerful features like Pivot table, Power Pivot, Power View, Power map or Power BI stack.

For all analytical tools to work the data must be in tabular form. What does tabular mean? It is like a table – each piece of information is in a column. Each column can have as many rows as necessary.

Once data is in tabular form you can analyze it easily. In order to formalize the data range, Excel tables are used. In older days we used to use Named Ranges for this purpose. Unfortunately, when more data is added Named Range does not extend itself. That creates lot of problems. Excel Tables, on the other hand, extend automatically when you add more data.

Once data is converted to Excel tables, you get lots of unbelievably great benefits. In the next few articles, we will explore all aspects of Excel tables.

The primary purpose

The primary purpose of people is to freeze the data range of tabular data. You can create a table even if there is one cell of data. Unlike what most people think, adding a table does not increase the file size drastically.

Prerequisites

There are very few simple requirements for creating a table.

1. data must be in columns
2. each column must have a name
3. each column name must be unique within the table
4. column name cannot be blank
5. there cannot be any merged cells
6. the column header cannot contain formulas

Creating a Table

This is quite easy.  Just select the data and choose Insert – Table. The table is created. Table is automatically formatted using the default formatting.

The Table Tools menu

Once you create a table, the Table Tools menu is shown. By default only a Design tab is visible. In some cases you will see a Query tab as well.

Checking whether there is a Table Tools menu is the most reliable way of checking whether the current cell is inside a table.

Naming a Table

Like every column has a name, the Table also has a name. This name is visible in the Table Tools-Design menu. Excel automatically generates names like Table 1, Table 3, Table 27 and so on.

It is best to specify a more useful and legible name for each Table as soon as you create it.

Names must start with a character (not a number) and cannot contain spaces. Table names must be unique within a workbook.

Disadvantages of tables

Before we discuss benefits of tables in detail, let us understand the disadvantages of using tables. Disadvantages are a few and specific.

The first disadvantage is that if an Excel workbook has a table in it, you cannot share the workbook.

Second disadvantage is if you have an Excel table in a sheet, you cannot multi-select sheets for copy or more operations. These operations must be done one sheet at a time.

If you know of any other issues or shortcomings, please post those as comments.

What next?

In the next article, we will see how to create tables in greater detail.