Create and Manage Tables in Excel

A table is a special configuration that allows you to manipulate and analyze your data with maximum efficiency. Converting your data set into a table allows you to quickly format, sort, filter, sum, and analyze the data.

Sections:

Create a Table

Format a Table

Add Data to a Table

Add Columns to a Table

Sort a Table

Filter a Table

Add a Total Row with Automated Functions

Static Headers

Notes

Create a Table

We must turn our data set into a table before we can use its features.

1. Setup your data. Make sure that it has headers for each column.  Though this isn't actually necessary, the table feature works much better with headers.
   
2. Select a cell within your data set, or select the entire data set and go to the Insert tab and click Table.
   
3. Make sure that Excel selected the correct range for your data and that My table has headers is selected if your data already has its own headers. Then click OK.
   
4. Now, you've got a nice table.
   

All the things you need to manage this table are located on the Design tab that appears when you select any cell within the table.

There are many nice features that we can now use so keep reading.

Format a Table

You can change the format of the table to fit your design needs.

1. Select any cell in the table and go to the Design tab.
   
2. Look to the Table Styles section on the right and click the More button to expand it.
   
3. Here, you can see lots of pre-made styles and hovering over them will show you what it would look like with your data.
   
4. If you don't like one of the pre-made styles, click New Table Style... located at the bottom of the window.
   
5. A window will open that allows you to edit the formatting for each individual element of the table.
   
   You select the desired element from the Table Element section and then click the Format button to select the format and when you are done hit the OK button
6. You can then select the new format from the Custom section that will now be present at the top of the Table Styles section.
   

Formatting is not the most important feature of tables but it does allow you to make nice looking data sets with ease and which are easy to read.

Add Data to a Table

Adding data to tables is very easy because everything updates for you.

1. Simply select the first cell below the current data set and begin typing.
   

The moment you finish entering a value into the cell the table formatting will expand to encompass the new data as well as allow all table features to automatically work for this new row of data. If you had a column of formulas, those would also automatically copy down to the new row.

Add Columns to a Table

If you need to add a new column to a table, this works like adding a row.

1. Select the first cell to the right of the current table and begin typing a header or a data point anywhere along the current data table.
   

When you finish entering data in the new column the table automatically expands to encompass the new column.

Sort a Table

Tables have built-in sorting features enabled by default and they are easy to access.

1. Click the small arrow on the right side of any header cell and you get access to all sorting options.
   
   At the top of the menu that appears you have Sort A to Z, Sort Z to A, and the Custom Sort option when you hover your mouse over Sort by Color.
2. Clicking Custom Sort will get you to the advanced sort window:
   
3. From there you can sort however you like.

As you can see, sorting is easy.

Filter a Table

Filtering data in a table is as easy as sorting it.

1. Click the drop down arrow in one of the header cells to filter that column and you get all of your normal filter options.
   

Basically, the table feature sets-up your data for filtering by default.

Add a Total Row with Automated Functions

You can add a row that has automated functions that appear when you select them from a list.  This makes adding "total" functions, which sum up or average or do something with the data in the table, very easy.

1. Click anywhere in your table and then go to the Design tab and put a check mark next to the option Total Row.  Or, select the table and hit Ctrl + Shift + T.
   
2. There is now a row at the bottom of the table that says Total.
   
3. Click a cell in that row and you will see a small arrow appear; click that arrow and a list of functions that you can use appears. Select the desired function or click More Functions to access all of the possible functions.
   I want to find out the average sales for my stores so I click Average.
   
4. Excel automatically enters the Average function and applies it to every row in the Sales column within the data table; I didn't have to enter a single thing and that is the beauty of tables in Excel.
   

To remove a function from the totals row, click the desired cell and then the arrow and select None from the list of possible functions.

Static Headers

Another great feature of tables is that the headers of the columns are always visible. When you scroll down they take the place of the column headers and the Sort and Filter option arrows also appear there so you can sort and filter from anywhere in the table without having to scroll back to the top.

Notes

Tables in Excel are very helpful and make life a lot easier when dealing with large sets of data.  They don't do anything that you couldn't do on your own, they just make doing that easier.

There are many more features available for tables in Excel so don't stop here, check out our other tutorials to learn more.

Make sure to download the workbook that accompanies this tutorial so you can see everything I talked about in this tutorial.