This article will demonstrate how to create a table in Excel.
Creating 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.
1. To create a table using existing data, ensure that your data is laid out in a way that is complatible with creating a table eg: 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.
2. Then, in the Ribbon, select Insert > Table.
3. The entire range of data will be selected. Make sure “My table has headers” is selected and then click OK.
4. 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 will be formatted with a blue background and white writing, while the rows below will be 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, select Table Design > Table Styles and then click on the style you wish to use.
2. The format will change according to the style you have selected. There are many in-built styles available. To access a few more styles, click on the “More” button as shown below.
3. The list of styles will be expanded to show a variety of different table styles. Select the one you wish to use.
Converting a 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.
Click in your table, and then, in the Ribbon, select Table Design > Tools > Convert to Range.
Click OK to convert your table to a range.
While the formatting will be preserved, the filters will be removed from the column headers indicating that the data is now a normal range and no longer a table.
Linking Tables with Relationships
If you have data in two different ranges in Excel, but the data is linked together by a commen column name, you can create a relationship between these 2 sets of data as long as both sets of data are tables in Excel.
Consider the following 2 tables in Excel:
One table contains the Sales Person’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.
If we wished to create a Pivot Table for example, that contains information from BOTH tables, we can link these tables together by creating a RELATIONSHIP between these 2 tables.
1. In the Ribbon, select Data > Data Tools > Relationships.
2. In the Relationships window, click New.
3. In the Table drop down, select the first table and then in the drop down below that, select the second table.
4. In the Column table, first select the Foreign column (in this case Salesperson as it can appear multiple times); and then in the Related Column, select the Salesperson field from the second table. This is the Primary column where the Salesperson will only appear once in that table.
5. Click OK to create the relationship.
6. Click Close to return to Excel.
Benefits of Using a Table
Adding Totals Automatically
Adding a total row to a table is incredibly easy.
Click in your table, and then, in the Ribbon, select Table Design > Table Style Options > Total Row
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.
You will notice that the Header Row, Banded Rows and Filter Button are also selected in this group of options.
- If you switch off the Header Row, then the Filter Button option will no longer be available.
- If you switch off the filter option, you will not be able to use the filter option in your table.
- If you switch off the Banded Rows option, the rows will not longer be alternatively shaded.
Automatically Adding Rows by Pressing the 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. You will notice that in the bottom row of the table, as small handle in the shape of an L exists.
If you then click in this cell and press the tab key, a new row in the table will be created and your mouse pointer will be moved to the first cell in the new row. The table has therefore automatically expanded to include this row.
This is useful due to the fact that 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 sorted the data, the new row or rows would be included in the sort.
If we were to add 2 more columns to our table, these would automatically be included in the table, as the new rows were above.
We can 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).
When you press enter, the formula is copied down to all the other rows in the table.
Multiple Filters one 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.
Create a Pivot using 2 tables
If you have created a relationship between 2 tables, you can create a Pivot table using fields from both tables.
1. In the Ribbon, select Insert > Pivot Table > From Data Model.
2. Select New Worksheet and then click OK.
3. You will now have the field available from both your tables to use in your Pivot table where the linked fields (SalesPerson) will show up indentical data.
|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|
|Highlight Every Other Line In Excel|
|Copy & Paste||yes|
|Copy Every Other Row|
|Create a Searchable Database|
|Find & Select||yes|
|Select Every Other Row|
|Alternate Row Color|
|Insert & Delete||yes|
|Delete Every Other Row|