Excel Tables: Manage your data like a pro

Excel is all about tables (the two dimensional sheets), and we deal with tabular form data all the time. Due to this, a specific table format was introduced from excel 2007 onwards. Excel table feature help us in several ways, but the crucial ones are:

i. Cell ranges are dynamic. This means that we can add data to the tables (like new records) and all the functions/charts/ pivot tables that refer to this data will auto update themselves. This is a huge improvement in terms of improving accuracy & efficiency of your day to day data management.

ii. Sorting and filtering can be made directly from the table

iii. Named structures are created by default for various table fields

Other improvements relate to formatting, viewing etc

Now let’s see how to create a table and a quick overview of above discussed features.

As an example, let’s say we have the following sales data in an excel sheet.

Raw table data

To define this data as an excel table, we can select any cell within this data, go to Insert->Table as shown below:

Creating an excel table

Alternatively, we can use the Ctrl+T shortcut also (after selecting any cell of the data range).

A confirmation window will pop-up for you to confirm on the data range an headers:

Table creation confirmation

Once you click OK, the data table will be created:

Excel Table inserted

The underlying data should be contiguous(without gaps), should be surrounded by blank cells and should have meaningful headers in the first row. These three things will help in this one click table insertion.

preferred data layout for excel table

Now that we have a data table in place, let’s see some of its functionalities:

Sort/Filter: Excel table comes with an inbuilt sorting & filtering options. The drop down menu buttons that are embedded in each header row will help you in doing this.

Sorting and Filtering operation on excel table

The good thing is that this sort will work on entire table (no more un-intentional jumbling up of data while sorting), also, no other data outside the table will be impacted by any of this sorting.

Add totals: It is very easy to add totals when we work with excel tables. Simply click on any cell inside the table and go to DESIGN and select ‘Total Row’

Adding Totals

This will add a total row at the end of the table. For each column you may add/alter the total (to be a sum/average/count/maximum etc)

For example in this case, while sum makes sense for all other columns, average is perhaps more appropriate for %margins.

Quick Formatting: Excel applies a banded row format to new tables by default. There are also options to select from a set of predefined formats in the Table Styles gallery on the Design tab. To do this, click on any cell in the table and then select the DESIGN tab and preview various styles as shown below:

Calculated columns: Another benefit of using excel tables is that you may avoid direct cell references (like C2:D2) for referring to any data in the table and use provided names to access the data. Excel uses the data headers to create names for us to refer to the data inside table.

To access these names, press opening bracket ’[‘ to get the list of all valid names.

For example, if we want to calculate Profit in the below given table, we start entering formula (by pressing =) and then press [ and select the required name, then we close the bracket by pressing ] and go on to complete the formula in the similar way. This process is shown below.

Using Named fields for formulas

Excel will automatically fill the whole column with the same formula.

You can also use these names outside the table, but to do that we will need to name the table first. This can be done easily through the DESIGN menu as shown below.

To give a desired name to an excel table, select any cell of the table, then go to DESIGN-> edit the table name. (table name has been entered as 'SALES' in this example)

Excel Table Naming

Once the table is named, the cells can be referred as following:

Access Table data through Names

Please note that while we can still use the traditional cell reference way of writing this formula, one big advantage of using these names is that even if the columns/rows are moved, the formula will still be correct and a potential error can be avoided this way.

There are some other benefits of using excel tables that we are not capturing here in detail:

-          You can select an option to only print an excel table

-          Table headers will be visible even when you scroll down

While all these are very good improvements for tables, you can still do most of this through other standard operations(although with more steps). In our next tutorial we will touch upon the most critical benefit of using the excel tables. That is : Dynamic cell ranges through excel tables. We decided to dedicate a seperate tutorial to this due to its importance.

 

Quick Tip:Most of the table related options can be quickly accessed by selecting the table, & then clicking on the menu icon that becomes visible at the bottom-right corner of the table. This will expose the quick access menu as shown below:

Quick Access Table menu

TutorialFiles: 

Section: