See all How-To Articles

How to Create an Excel Table

This tutorial demonstrates how to create a table in Excel.

 

createtable intro

Create an Excel Table

You can either create an Excel table using existing data, or you can create a blank table and fill it with data afterwards.

You can either create an Excel table using existing data, or you can create a blank table and fill it with data afterwards.

  1. To create a table using existing data, ensure that your data is laid out in a way that is compatible with creating a table, e.g., each column should have a header row that describes the contents of that column and no blank rows or columns should exist in the middle or the data.

 

createtable data

 

  1. Then, in the Ribbon, go to Insert > Table.

 

createtable ribbon insert

 

  1. The entire range of data is selected. Make sure My table has headers is ticked, and then click OK.

 

createtable create table

 

  1. Your table will automatically be created as far down as the next blank row and as far across as the next blank column.

Alternate Shading in a Table

When a table is automatically created, the table is formatted according to the default table style that exists in Excel. This means that the top row is formatted with a blue background and white writing, while the rows below are formatted alternatively with a blue or white background.

  1. To amend this format, ensure that you are clicked in your table and then, in the Ribbon, go to Table Design > Table Styles and then click on the style you wish to use.

 

create table styles

 

  1. The format changes according to the style you choose. There are many built-in styles available. To access a few more styles, click on the “more” button as shown below.

 

createtable styles more

 

  1. The list of styles is expanded to show a variety of different table styles. Choose one.

 

createtable styles more expanded

 

Convert Table Back to a Range

If you have formatted your data as a table, and then wish to remove the filter options and convert your table back to a range, you can use the Table Design tab to do this.

  1. Click in your table, and then, in the Ribbon, go to Table Design > Tools > Convert to Range.

 

createtable convert to range

 

  1. Click OK to convert your table to a range.

 

createtable convert

 

While the formatting is preserved, the filters are removed from the column headers indicating that the data is now a normal range and no longer a table.

 

createtable shaded

 

Link Tables: Relationships

If you have data in two different ranges in Excel, but the data is linked together by a common column name, you can create a relationship between these two sets of data as long as both sets of data are tables in Excel.
Consider the following two tables in Excel:

createtable twotables

 

One table contains the salesperson‘s name and their Sales Target, while the other table contains their order amounts. In one table, the salesperson appears only once while in the other table the salesperson can appear multiple times.

Note: the data does not have to be on one sheet and may be much larger than the example above.

For example, to create a pivot table that contains information from both tables, you can link these tables together by creating a relationship between them.

  1. In the Ribbon, go to Data > Data Tools > Relationships.

 

createtable ribbon

 

  1. In the Relationships window, click New.

 

createtable relationships new

 

  1. In the Table drop down, choose the first table and then in the drop down below that, choose the second table.

 

createtable select table

 

  1. In the Column table, first choose the Foreign column (in this case, Salesperson as it can appear multiple times); and then in the Related Column, choose the Salesperson field from the second table. This is the Primary column where the salesperson will only appear once in that table.

 

createtable select column

 

  1. Click OK to create the relationship.

 

 create table create relationship

 

  1. Click Close to return to Excel.

 

createtable show relationship

 

Benefits of Using a Table

Add Totals Automatically

Adding a total row to a table is incredibly easy.

Click in your table, and then, in the Ribbon, go to Table Design > Table Style Options > Total Row

createtable totals

 

The default function use for the Total Row is the sum function. You can, however, amend this function if you want to use a different function.

 

createtable total function

 

Notice that the Header Row, Banded Rows, and Filter Button are also ticked in this group of options.

  • If you switch off the Header Row, then the Filter Button option is no longer available.
  • If you switch off the filter option, you can’t use the filter option in your table.
  • you switch off the Banded Rows option, the rows are no longer alternatively shaded.

Automatically Add Rows With Tab Key

One of the benefits of using at table is that a table will automatically expand if you enter more data into the table. Notice that in the bottom row of the table, a small, backward-L-shaped handle exists.

 

createtable add data icon

 

If you then click in this cell and press the TAB key, a new row in the table is created, and your cursor is moved to the first cell in the new row. The table has therefore automatically expanded to include this row.

 

createtable add row

 

This is useful since some of the benefits of using a table are the sorting and filtering options that are built into the table. If you then wanted to filter on the Salesperson, for example, the new record would be included in that filter. Similarly, if you sort the data, the new row(s) are included in the sort.

Consistent Formulas

If you add two more columns to the table, these would automatically be included in the table, as the new rows were above.

 

createtable add columns

 

Then, add a formula to work out the total by multiplying the Order Amount by the Quantity. The formula is created using the field names (column headers).

 

createtable formula

 

When you press ENTER, the formula is copied down to all the other rows in the table.

 

createtable formula press enter

 

Multiple Filters on One Sheet

In the example below, a table has been created for each year of orders, and then, each table has been filtered by region to show the orders for a single region in each individual year.

 

createtable multiple tables

 

Combine Tables Into One Pivot Table

If you have created a relationship between two tables, you can create a pivot table using fields from both tables.

  1. In the Ribbon, go to Insert > Pivot Table > From Data Model.

 

createtable pivot two tables

 

  1. Select New Worksheet, and then click OK.

 

createtable pivot new

 

  1. You now have the field available from both your tables to use in your pivot table where the linked fields (Salesperson) will show up identical data.

 

createtable fields

 

Tables
Add a Column and Extend a Table
Add a Total or Subtotal Row to a Table
Compare Two Tables
Convert a Table to a Normal Range
Display Data With Banded Rows
Remove a Table or Table Formatting
Rename a Table
Rotate Data Tables
Conditional Formattingyes
Highlight Every Other Line In Excel
Copy & Pasteyes
Copy Every Other Row
Databaseyes
Create a Searchable Database
Filtersyes
Filter Rows
Find & Selectyes
Select Every Other Row
Format Cellsyes
Alternate Row Color
Insert & Deleteyes
Delete Every Other Row