Creating Tables in Excel

Excel Tables is one of the powerful options that help to handle and organize large sets of data effectively. Organizing/ Formatting the data in a structured format always makes the user’s job much easier to manage the data. By converting raw data into tables you can use multiple options which makes work easier and saves a lot of time. In this post we are going to see how to create a table in excel and explore the different features/ options available with Tables in Excel.

Lists feature in Excel 2003 was succeeded by the Table Feature. From Excel 2007 version, the concept of tables has been introduced with additional options.

What is an Excel Table?

Excel Table is a data set organized in a series of rows and columns. Interesting thing is you can also add data to the existing table without worrying about the customizations made by you i.e., formulas, references, formatting remains the same and are also applied to newly added data. Also, Excel Tables helps in creating pivots easily as the data is already organized and formatted.

To do’s before Creating a Table

Please make a note of the below points before creating the table.

  1. Headings: Make sure that in the 1st row, each column contains Unique Heading regarding the data present in the column. If there is any column without headings Excel automatically names it as column 1, column 2 e.t.c. This will make identifying the columns difficult while analyzing the data.
  2. Data Types: Organize the data in such a way that same data type is present in the same column.
  3. Blank Rows and Columns: There should not be any row or column which is completely bank in the data. Excel treats the data after blank row or column as separate data and will not be a part of your table. (In case you have different sets of data in the same spreadsheet you can use a blank row/ column to separate them).

How to Create a Table from Data?

To create a table from an existing data,

  1. Select any cell in your data.
  2. Go to Insert tab in the Ribbon and click on the TABLE option.
  3. You’ll now see a Create Table Dialog Box with the complete data range selected (make sure the data range is correct). By default “My Table has Headers” is checked (In case if it is unchecked check it).
  4. Click OK and you can see your data is formatted into a table.

See the below image on creating a table from a data range

Creating tables in excel

Alternatively you can use the Key Board Shortcut “CONTROL + T” for Creating a Table.

Now you can see

  • The Header is changed into a different Colour.
  • All are rows and columns are banded.
  • Data Filters are enabled in the top row.
  • The header remains on the top if the table stretches beyond the visible screen and incase if you need to scroll down (works like freezing the top row).
  • Upon clicking any cell in the table a new tab with name “Design” can be seen on the ribbon.

Customizing the Table – Design Tab

Excel provides various options to customize the excel table in the way you want to.

  1. Naming the Table: In the, Design Tab, in the properties group (at the extreme left) you can see an option to change the name of the table. By default it will be as “Table 1”. Delete the “Table 1” and type the name which you want to give the table.
  2. Adjusting the Table Range:Also, you can adjust the range of the table by using the “Resize Table” option in the same group. Click on Resize Table and select the new range for the table in the dialog box.
  3. Table Styling Options: In the Design Tab, in the Table Style Options group there are different options available with check boxes against each option.
    1. Header Row: To show/hide the Header.
    2. First Column and Last Column: To Bold the 1st and last columns.
    3. Banded Rows and Columns: Helps to differentiate between alternate rows/columns with a different pattern. You don’t need any Conditional Formatting for this.
    4. Filter Button: To hide/show filter on the top row.
    5. Styles: There are different pre-designed styles inbuilt in excel available to change the color and appearance of tables. To do this,
      1. Go to the “Home Tab” and Select the option “Format as Table”. Choose the style which you like.
      2. Alternatively, Click on any cell in the table and choose the style which you need from the “Design Tab“.
  4. Summarize with Pivot Table: To create a pivot table from the table data.
  5. Remove Duplicates: To identify and remove the duplicate items from the table.
  6. Convert to Range: To change the table to normal range.
  7. Insert Slicer: To create a slicer to represent the data in an interactive way. Available from Excel 2013.
  8. By using the Export option the entire table can be exported directly to SharePoint.

Creating and customizing tables in excel

Total the values in Table without writing Formulae

One of the best features of Excel Tables is that you don’t need to struggle or fiddle with formulae to show the total for a column/ multiple columns. Tables has a built in feature to directly update your totals in the columns.

To see the totals in table

  1. Select any cell in the table.
  2. Go to the Design Tab and in the Table Styling Options group put a check mark for the “Total Row” option.
  3. At the bottom of the table (after the last row of data) you can see a new row inserted with name “Total” and one or more columns might show totals.

The interesting feature of the “Total Row” is, you can change the summary type from “Sum” to other options like “Average”/ “count”. Click on any cell on the “Total Row” and you can see the drop down with options count, max, min etc. Select the desired option from the drop down. A SubTotal formula is added in the cell which shows the calculation based on the visible cells of the table.

Create and Use total row in excel tables

Structured References

Structured References makes your job much easier. Instead of using cell references, you can structured references that reference table names in a formula. As and when a formula refers to a part of a named excel table a structured reference is created. There is specific format to refer to the data in a table using structured references. The structured reference will show the name of the column in between “[ ]”and might include the table name before “[ ]”.

For Example “=Table Name[Column1]”

If a Name is given to the Table and Column then the same should be used instead of Table Name and Column1

If you are working with multiple sheets, you need not go back to the master sheet to refer the data in the table. Just type the name of the table and excel will provide you the option of using the data from the table. See the below image

Similarly, if you want to refer to the data in a specific column from a table2 (from the above image), type the name of the entire table and put a “square bracket” after the table name and excel will show you the list of columns in the table to use. See the image below.

Structured references can also be used directly in the formulae as well.

Limitation: Please note structured references won’t work with conditional formatting

Calculated Columns are much easier

Inserting or using a calculated column becomes easy with tables and structured references. When you write a formula in one cell, excel fills the formula in the rest of the cells in the column making your job much easier.

Excel Tables

Table do not adjust on adding/removing data

Tables are dynamic in nature. The range expands as and when new data (rows/ columns) is added to the table. In case if you see that the table range is not expanding please follow the below steps.

  1. Go to File tab and click on Options.
  2. In the Options window select Proofing. Click Auto Correct Options.
  3. In the Auto Correct Window, Go to “Auto Format as you type” tab (2nd tab).
  4. Add a check mark to the 2nd and 3rd options. i.e. “Include New Rows and Columns in Table” and “Fill Formulas in Table to create calculated colums”
  5. Click on “OK” twice.
  6. Now you can see the table range adjusting automatically upon adding/ removing data.

Create and Update tables.

What are your thoughts on Excel Tables?

I feel they are powerful and easy to use without much complexity. They can be of great use in your day to day work. Please do share your thoughts on working with excel tables in the comments.

7 Replies to “Creating Tables in Excel”

  1. […] the image below to create a Table for the data using the Shortcut. The icon should be added to the QAT before you use the […]

  2. Very useful info. Thanks!

    1. Hi Ananth,

      Thanks for dropping by. Please do share about the blog with your friends

  3. […] Excel Tables: Tables serve as a better alternative to offset as they are dynamic in nature and can be used in formulas. Click to know more about tables and their usages. […]

  4. […] The limitations are auto filter is a basic /simple filter and also cannot be applied directly on Excel Tables. […]

  5. […] which the lookup_value needs to be matched. This can be a single column/ more than one column or a Table or a Named […]

Leave a Reply