Introduction to Excel Tables – Data beyond just formatting

Many people think converting data into table is basically a formatting options giving it colours and other eye candies like giving data “zebra lines”. But there is much more to it beside data formatting and face lift that comes in terms of functionality and features that are available if data is maintained in tables.

In Excel 2003 version it was known as lists. In 2007 lists were upgraded to tables and different enhancements were introduced.

In Excel table is:

A collection of related rows and columns that user want to manage independently from the data in other rows and columns i.e. once data is converted to table it is an independent unit in itself that has its own identity.

Consider yourself for example. You have a name and has certain events in your life that records your progress. Similarly everything you do gets added in your account as part of your life that is moving forward every second and every moment something new is happening which is becoming part of record.

Similarly in Excel every table has its own unique identity that can grow, reduce or expand in anyway and all the data enclosed in table will be part of that table and can be identified by calling upon table.

Inserting or Converting data into tables

Step 1: Select the data that you want to convert to table. It is better if you select only related rows and columns, but don’t worry you can later add or delete the rows and columns.

Step 2: Click insert tab>Table button in Table group or simply press a shortcut Ctrl+T. T for table. Easy to remember 

Step 3: A “Create table” dialogue box will appear. In the range field the range you selected in Step 1 will be shown with cell references. If you think that your previous selection is not appropriate you can make new selection. To do so click in the range field or click collapse button. Select the cells and click dialogue expander button.

Step 4:

Case – 1: Ideally data should have headers that help you identify what each column represent. If you have headers tick “my data has headers”. Having this option checked the first row of selected range will be converted to range headers and will be given different colour than the rest of table with arrows pointing downwards. I will explain what these arrows mean later in the article.

Case – 2: If the first row of selection is empty and you checked the option of data has headers then excel on its own put Column 1, Column 2, Column 3 and so on in the header row of table.

Case – 3: If the option is unchecked then excel will put a new row above the selection with names Column 1, Column 2, Column 3 and so on. Because you told Excel that data does not have headers and so Excel will put them on its own. In short tables always have headers! May be each of the table is unique and keeps its “head” high!???

In our case our data has headers so we will go with Case – 1

Look! It comes with its own tab – Tabulous!

When you successfully convert the range to a table. Then each table get its own tab in the ribbon full of features and this tab is exclusively available just for tables. It has several options in different groups just like normal tabs. You can access this tab only if you have active cell within the table and due to this reason such tabs are called contextual tabs.

Contextual tabs are such tabs that are feature specific and are enabled and available only if related feature is applied. In case of tables a contextual tab named Table Tools springs to action.

Properties

In this group we have to important options:

Name: This is the name of the table. If you have not named your table yet then it has generic name as Table1, Table2, Table3 and so on if you have multiple tables in your workbook. Name of excel plays pivotal role especially in formula writing which we will understand in detail when we discuss use of structured references.

You can name the table as you wish that best represent the data. For example; SalesData, UnitsSold or SalesVariances etc.

To name a table go to table contextual table and in properties group in the name field punch in the name you like and press Enter. Its important to press enter key. Table’s name won’t change until enter is pressed.

Note however, that name cannot have spaces in it otherwise Excel will give an error message if you try. This is the rule for naming throughout Excel, if you name anything using Name manager even you cannot have spaces in the name. You can use numbers in the name but name cannot start with a number. And yes one name cannot repeat again.

Those who are keen follower and reader of my articles they might know already that once something is named, you can access that item via name box drop down as well. Similarly, when you can make a table excel give it a name automatically which you can change later. The same will appear in the name box. You can quickly jump and select the item from name box by simply clicking its name.

Resize table: This let you add or delete rows and/or columns from existing table. On clicking this button you will be navigated to concerned table where you can make a fresh selection which may be bigger or smaller than the previous table size.

One key thing to remember is that you need to make new selection from the top left cell of the table. If you don’t excel will give an error of invalid selection.

Tools

Summarize with pivot table: Use the data in the table in pivot table. Those who are not familiar with pivot table then consider it a big brother of tables that let you enter into completely different realm of data analysis.

Remove Duplicates: This let you remove duplicates with in the table. Although this option is available in Excel under Data tab but having this button contextual tab means that it lets you remove duplicates from the table ignoring rest of the data.

Convert to range: Not finding any good reason to keep the table? Well you can always convert it back to simple range and stripping off all the table features from it. Simply have the active cell within table and click this button and now it is same old general range of data. However, the formatting will stay in place but table related features will be no more there. And that means all of them!

External table data

Skipping the details, you can export the data in table to shared server facility where a software can draw the data from specific table and these options let you fine tune that.

Table style options:

Header row: Show/hide table header row

Total row: A special row available only to tables. You can turn it on or off. By default it is off. This row let you insert or apply formula on table’s columns usual formula like sum, average and other if you like by choosing one from the list. Its like a ready made list of formula for you. By default SUM formula is applied.

Banded rows/columns: Apply “zebra lines” to columns and rows. This makes reading data a lot easier as one does not mingle data of one row or column with the data of the other.

First / Last column: This let you format the first or last columns of table differently from the rest of the table. For example last column of table gives total of values in each row. To make it stand out you can select “Last column” and have it formatted differently.

Table styles

With 61 colour or style designs you can select and apply the colour scheme you like to make your table not just appealing in numbers but also loved by eye balls. Even before you apply you can just hover your mouse above each style to have a live preview how your table will look once style is applied make things super fast. Alas! ladies don’t have such facility in beauty parlour! 😀

If somehow your pursuit of beautification is not fulfilled by the given style sets you can design your own. So there are endless possibilities for you to make your data looks good. And it is so easy that you are only clicking with your mouse most of the time.