It makes it easy for a user to manage data if it is in a tabular format. Just by converting a data range into a table, you are able to use a variety of features that will make your work super easy (I cover these features in detail below in this article).
Creating an Excel Table
If you have the data in the tabular format in a worksheet, here are the steps to convert it into an Excel table
Select the entire data set. Go to Home –> Format as Table.
Select any one of the pre-defined formats.
In the Format as Table dialogue box, ensure that the right range is selected. If your table has headers, keep the check box checked. In case your data does not have headers, excel automatically inserts headers with generic name Column 1, Column 2, and so on..
Click OK.
TIP: A much faster way to do this is to select any cell within the data set and use the keyboard shortcut – Control + T. It would directly open the Format as Table dialogue box. When your tabular data is converted into Excel Table, your data would look as shown below: Note that some changes automatically happen with your data:
The data now has banded rows The header is shaded in a different color Data Filters are enabled (note the arrow pointing down at the right of the headers).
Note that when you convert tabular data into an Excel table, only the design changes. Nothing happens to the data.
Converting Excel Table into Regular Tabular Data
If you want to convert the Excel Table back to the normal tabular range, right-click anywhere on the table, go to Table and select Convert to Range. Note that when you convert the Excel Table to a range, it no longer has the properties of an Excel Table (discussed below), however, it retains the formatting.
Useful Excel Table Features
Now that you have learned how to create a table, here are some features of Excel Table that make it useful:
An Excel Table automatically creates headers that have the option to sort or filter.
If the Excel Table is long and stretches beyond the visible screen, the headers remain at the top when you scroll down. This would work only if you have selected the table and scrolling down.
If you type anything in the cells adjacent to the Excel table, it automatically expands the selection to include this data. If not required, this can be switched off.
If you are using the data from the table in any calculation, and if there are any additions to it (say you add one row of data), it automatically gets figured into the calculations
Excel Table Designing
Excel Table is cool and fashionable. You can dress it the way you want. When you click on any cell within the Excel Table, an additional tab TABLE TOOLS DESIGN appears in the Excel Ribbon area. When you click on the Design tab, there are several options that you can access:
Properties: Here you can change the existing name of the Table (yes! every table gets a name and it is very useful for referencing, as you will see later in this article), or you can re-size the table.
Tools: This has four options: Summarize with Pivot Table: This simply creates a pivot table using the data in the Excel Table. Remove Duplicates: This can be used to remove duplicates. Convert to Range: To convert an Excel Table into regular range. Insert Slicer (in Excel 2013 only): This works exactly like Pivot Table slicers, and could be helpful in filtering data using a single click.
External Table Data: This section can be used to export data or refresh data.
Table Style Options: This is the fashion section where you dress up your table: Header Row: If unchecked, it removes the header from the table. Total Row: If checked, it inserts a total row at the end. Banded Rows: If unchecked, it removes the alternate band in rows. Banded Columns: If checked, it applies alternate band in columns. First Column: If checked, it makes the first column font bold. Last Column: If checked, it makes the last column font bold. Filter Button: If unchecked, it removes the filter drop down from headers.
Table Styles: Give you multiple options to change the styling of the tables. You can also create your own table style by selecting New Table Style option
Structured Referencing in Excel Tables
Another benefit of using Excel Tables is the ease to reference data once a table has been created. There is a specific format that you can use to refer to data in an Excel Table:
If you have given a specific name to the Excel Table, then that name would be used instead of Table1. This structured referencing has many benefits:
You can refer to data using names instead of cell references. Imagine you have the Excel Table in one sheet and you are working in some other worksheet. You need not go back, again and again, to refer to the data in the table. All you need is to type the name of the table and excel will show you the option of using that table. Something as shown below: Similarly, if you want to use the data from a specific column, simply have the entire name of the table and put a [ (square bracket). It will show you the column names and other options that you can use. Something as shown below: If you add more data to the table, it automatically gets figured in if you have used structured references.
NOTE: Structured references do not work within conditional formatting custom formula.
Using Named Ranges in Excel. Creating a KPI Dashboard. The Art of Creating An Excel Dashboard.
