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.
There are very few simple requirements for creating a table.
- data must be in columns
- each column must have a name
- each column name must be unique within the table
- column name cannot be blank
- there cannot be any merged cells
- 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.
In the next article, we will see how to create tables in greater detail.
This is a very interesting topic Doc. Looking forward to the rest of the posts in this series.
I’ve been using Excel Tables since the past 2 – 3 years, and found them very useful, especially the structured formulas, and the auto-expand features.
About the limitations / disadvantages of using Excel tables, I can think of a couple more.
1. If you have a Table anywhere in the workbook, you cannot setup Custom Views in that workbook. I find
2. A Table cannot be linked to an external data source.
Both these are with reference to Excel 2010, so if they have been fixed in Excel 2013, I stand (happily) corrected 🙂
Thanks Khushnood. External Data source connections automatically create a Table.
Does this behaviour occur in Excel 2013, Doc ?
Because, in Excel 2010, when I import data from an external source it shows a ‘regular’ data range (not a Table).
After that, if I try to format it as a Table, Excel warns me that “Your selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connections?”
Conversely, once a range is formatted as an Excel Table, all the commands in the Get External Data group on the Data tab are greyed out.
Thanks for sharing doc. To add to the drawbacks, you can not use structured references in conditional formatting formulas
Thanks Sumit. Good one.
Reblogged this on SutoCom Solutions.