Excel has a handy feature called Excel Tables, which makes data management and analysis a lot easier.
If you’re an Excel beginner, you may think an Excel Table is data in a tabular format. But that’s not the case.
When data is arranged in rows and columns in the worksheet, it’s just plain regular data. It may look like a table, but it’s not the Excel table we will discuss in this article. You need to click a few buttons to convert this data in rows and columns into an Excel table.
This Tutorial Covers:
ToggleCreating a Table in Excel
Below I have this data set that I want to convert into an Excel table.
While you may think it is already in a table because data is arranged neatly in rows and columns, it’s not an Excel table yet.
When we convert this data into an Excel Table, it gives us access to some additional features that come with the Excel table functionality.
Here are the steps to convert this data into an Excel table:
- Select the range you want to convert into an Excel Table. If there are no blank rows or columns in your data set, you can select any cell instead of the entire dataset.
- Click the Insert tab in the ribbon.
- Click on the Table option (it’s in the Tables group). This will open the ‘Create Table’ dialog box.
- Confirm the range mentioned in the dialog box. In case it has picked up the wrong range, or you want to change it manually, you can do it.
- If your data has a header row (like I have in my example dataset), make sure the ‘My table has headers’ option is checked.
- Click OK
As soon as you click on the Ok button, your data will now be converted into an Excel table.
Pro Tip: You can use the keyboard shortcut Control + T To open the ‘Create Table’ dialog box. To use a shortcut, select the range that you want to convert into an Excel table, then hold the Control key and then press the T key. If you’re using a Mac, you can use Command + T.
When you create an Excel table, by default, it applies some formatting to the table.
Here are some of the changes that you will notice once you convert your data into an Excel Table:
- Cell coloring is applied to the table header as well as the alternate rows.
- A filter is applied to the header row, and you can see the filter icon in each cell in the header row.
- When you select any cell in the Excel table, an additional contextual tab called ‘Table Design’ will appear in the ribbon.
While these are some things that Excel does by default, in case you do not like the formatting of the Excel Table, you have complete control over changing it. I will cover how to change the formatting of an Excel table later in this article.
Also read: How to Create Named Ranges in Excel
Creating Excel Table with a Specific Style
Excel has some already inbuilt table formatting styles that you can use while creating an Excel table.
So instead of getting the default table formatting, you can first choose the table style you want and then convert your data into an Excel table using that formatting style.
Let me show you how it works.
Below I have a data set that I want to convert into an Excel table.
Here are the steps to first choose one of the prebuilt Excel table styles, and then use it to convert the data into an Excel table:
- Select the range you want to convert into an Excel Table. If there are no blank rows or columns in your data set, you can select any cell instead of the entire dataset.
- Click the Home tab.
- In the Styles group, click on the ‘Format as Table’ option.
- Select from any of the pre-made formatting styles that appear. When you click on any of the existing formatting styles, it will open the Create Table dialog box.
- Confirm the range mentioned in the dialog box (change the range if needed).
- If your data has a header row (like I have in my example dataset), make sure the ‘My table has headers’ option is checked.
- Click OK
The above steps would convert your data into an Excel table and also apply the selected style.
Note: If you do not like the existing table styles and want to make some customizations to them, you can click on the New Table Style option that appears when you click on the ‘Format as Table’ option in Step 3. It will open the ‘New Table Style’ dialog box where you can customize the formatting of each element of an Excel table
Customizing the Table in Excel
In this section, I will take you through some of the common customizations that can be made to an Excel table.
Renaming the Excel Table
Whenever you insert an Excel table, it is assigned a name (such as Table1 or Table2).
This is an important feature as it allows you to refer to that Excel table using that name instead of the reference.
You can rename the Excel table to something more meaningful using the steps below.
- Select any cell in the Excel Table.
- Click on the Table Design tab in the ribbon. Note that this tab will only appear when you select any cell in the Excel table.
- Manually change the Table name in the Properties group.
Note that you cannot have spaces in between the name of the table (i.e., it needs to be a single word). You can, however, use an underscore in between words. So I can name my table Sales_Data.
Apply Table Formatting Style to an Existing Table
Once you have inserted an Excel table, you can also change the look and feel of that table by changing the formatting style.
Below I have an Excel table created using the default table style, and I want to change it (say I want to make it blue to match my brand colors).
Here are the steps to change the formatting style of an existing Excel table:
- Select any cell in the existing Excel Table.
- Click on the Table Design tab in the ribbon.
- In the Table Styles group, click on the Quick Styles expand icon. This will show you all the pre-made styles that you can apply to the table.
- Select from any of the premade styles or click on the New Table Style option to create your own table formatting style.
Note: When you hover your cursor over the different table styles, you would be able to see how it would look in your data set in real time.
Removing the Table Style Format
If you do not want any formatting style to be applied to your Excel Table, you can easily remove the existing style format.
Here are the steps to do this:
- Select any cell in the existing Excel Table.
- Click on the Table Design tab in the ribbon.
- In the Table Styles group, click on the Quick Styles expand icon.
- Click on the Clear Option
When you remove the existing table style format, it only removes the formatting, but the Excel table continues to work as expected.
Also read: Merge Tables in Excel Using Power Query
Using the Excel Table Features
As I mentioned at the beginning of this article, when you convert your data into an Excel Table, you get access to some additional functionalities that could be pretty useful.
In this section, I will take you through some of these additional functionalities that you can access in an Excel Table.
Filtering in Excel Table
By default, the header row of an Excel table has the Filters applied to it. You can access these filters by clicking on the drop-down filter arrow in each column header.
This lets you quickly filter your text, numeric, or date data (just like a regular Excel filter).
Apart from filtering your data based on the cell value, you can also filter based on the color (In case we have manually colored cells in the data set)
Sorting in Excel Table
Just like filtering, you can also use the same drop-down in the column header to sort your data set.
If it is a numeric data set, you can sort from smallest to largest or largest to smallest, and if it is a text dataset, you can sort from A to Z or Z to A.
And in case you have a date dataset, you can sort based on Oldest to Newest or Newest to Oldest.
Removing Duplicates from the Table
Another useful feature you can access in the Table Design format is the ability to remove duplicates from your table.
To use this feature:
- Select any cell in the Excel table
- Click on the Table Design tab in the ribbon
- Click on the Remove Duplicates option. This will open the Remove Duplicates dialog box.
- Select the columns from which you want to remove the duplicates. In case you want to remove duplicate rows, select all the columns
- Click OK
Structured References in Excel Tables
One unique feature that you can access only with an Excel table is the ability to use structured references.
When you create an Excel table, you can reference the entire table or parts of that table, such as a specific column or the header row, or a specific cell by using a structured reference.
For example, if I want to reference the entire data set in my Excel table (that I have named Sales_Data), I can use =Sales_Data in a cell (instead of using the entire range reference)
And in case I want the entire table, including the headers, I can use =Sales_Data[#All] in a cell.
Below are all the structured references you can use (here I am taking the example where the table is named Sales_Data)
Structured Reference | What it Will Give Us? |
---|---|
=Sales_Data | The entire table, except for the header row |
=Sales_Data[#All] | The entire table, including the header row |
=Sales_Data[Column Name] | The entire column whose name is used in the square brackets. For example, =Sales_Data[Q1] would give me the data from the Q1 column |
=Sales_Data[#Data] | All the data in the table, excluding the header row. This is similar to =Sales_Data |
=Sales_Data[#Headers] | Gives only the header row |
=Sales_Data[#Totals] | Gives the Total row if it has been added to the table. Else it returns a #REF! error. |
Now let me talk about some fantastic benefits of using structured references of Excel tables:
- Easy to Write and Read Formulas – When you have named your Excel table, it is much easier to refer to that table and the data in that table using the name instead of the range reference. For example, it is easier to remember =Sales_Data is the table instead of the reference A1:E11.
- Update Automatically – When you update your Excel table (by adding rows or columns or deleting rows and columns), you don’t need to adjust the structured references in the formulas where you have already used them. The structured reference would automatically update and reflect it in the formula. Had you been using range cell references, you would have to manually change the reference every time your table expands or constricts.
Adding New Rows/Columns to an Existing Excel Table
Another helpful feature is that when you add a new row or column to the existing Excel Table, it would automatically expand and consider that as part of the table.
For example, below, I have an Excel table that has data till Store 10.
Now if I add another row with data for Store 11, as soon as I enter the data, the table will automatically expand and include this newly added row as part of the table.
And if I have used structured references in formulas (that refer to the data in the table), those formulas would automatically update and reflect this newly added data in the result.
Add Total Row
You can add a total row at the end of the table with a single click. This could be useful when you want to show calculations such as the sum or count of the column in a separate total row at the bottom.
Here are the steps to insert a Total Row in an Excel table:
- Select any cell in the Excel table.
- Click on the Table Design tab in the ribbon.
- In the Table Styles option group, check on the Total Row check box
You will now see that a new total row has been added, and you see the sum of all the values in the last column.
With this option, Excel automatically inserts a subtotal formula that gives you the sum of the values in the last column.
If you want any other measure instead of sum (such as COUNT or MAX or MIN, or Standard Deviation), you can select the cell, click on the drop-down and select the calculation metric you want to use.
While Excel only gives you the value in the last column, only you can apply this formula for any cell in the total row (just select the cell, click on the drop-down icon, and then select the calculation that you want)
Using Slicers in Excel Table
When working with large Excel tables, a slicer can be pretty useful to filter data based on a click.
While earlier, slicers were only part of Pivot Tables in Excel, it has recently been introduced in Excel tables as well.
Let me show you how it works.
Below I have a small data set in an Excel table, and I want to create a slicer that would allow me to quickly filter the data based on the region.
Here are the steps to insert a slicer for this Excel table:
- Select any cell in the Excel table
- Click on the Table Design tab in the ribbon
- Click on the Insert Slicer option. This will open the Insert Slicers dialog box.
- Select the column based on which you want to insert a slicer. In our example, I want a slicer that would allow us to filter the data based on the region column, I would check the Region option.
- Click OK
The above steps would insert a slicer on your worksheet where you can click on the options in the slicer, and as soon as you do that, it is going to filter your Excel table.
For example, if I click on the East option, it will filter my Excel table and only show the records for the stores in the East.
You can also customize the Slicer box by placing it anywhere in the worksheet, changing the size of the Slicer, enabling multi-select, and changing the color of the slicer.
Also read: How to Insert Checkbox in Excel
Convert Excel Table Back to Range
While an Excel table has a lot of benefits, there might be some cases where you want to convert your Excel table back into a regular range without all the acceptable functionalities.
Below I have an Excel table, and I want to convert this table back into range.
Here are the steps to do this:
- Right-click on any cell in the Excel table.
- In the menu that opens, go to the Table option.
- Click on the ‘Convert to Range’ option.
As soon as you do this, Excel will convert this Excel table into a normal range, and all the functionalities that come with an Excel table will be gone.
For example, the filters in the header rows would be removed, and if you have the slicer for the table, it would also be removed.
However, the table style (i.e., the header and cell colors) may remain.
In this article, I’ve covered how to create an Excel table and all the customization options available. I’ve also covered the benefits of using an Excel table, such as structured references and automatically expanding tables when new data is added.
Other articles you may also like:
5 thoughts on “How to Create Excel Table?”
I’ve only just discovered tables and I can’t believe how useful they are going to be. I love them. Thanks = I found them by mistake and now I’m reading your tutorials to fully understand them.
Is Excel Table as you’re using it, available in excel 2008 for Mac? I can’t seem to figure it out on my version (table seems to only be used for making charts) nor find the answer to this question elsewhere. Thank-you. And thank-you for your other blog posts, they’ve been very helpful.
Searchable drop down list working for one row but how can something in below rows
This is helpful but i cant understand how I can add footer?
Please help me.
Have a look at this tutorials for headers and footers: http://trumpexcel.com/2014/05/insert-watermark-in-excel/