The tutorial explains the essentials of the table format, shows how to make a table in Excel and leverage its powerful features.
At the surface, an Excel table just sounds like a way to organize data. In truth, this generic name covers a ton of useful features. Tables containing hundreds or even thousands of rows and columns can be instantly recalculated and totaled, sorted and filtered, updated with new information and reformatted, summarized with pivot tables and exported.
You might be under the impression that the data in your worksheet is already in a table simply because it's organized in rows and columns. However, the data in a tabular format is not a true "table" unless you've specifically made it such.
Excel table is a special object that works as a whole and allows you to manage the table's contents independently from the rest of the worksheet data.
The screenshot below contrasts a regular range and the table format:
The most obvious difference is that the table is styled. However, an Excel table is far more than a range of formatted data with headings. There are many powerful features inside:
For more information, please see 10 most useful features of Excel tables.
With the source data organized in rows and columns, carry out the below steps to covert a range of cells into a table:
As the result, Excel converts your range of data into a true table with the default style:
Many wonderful features are now just a click away and, in a moment, you will learn how to use them. But first, we'll look at how to make a table with a specific style.
Tips and notes:
The previous example showed the fastest way to create a table in Excel, but it always uses the default style. To draw a table with the style of your choosing, perform these steps:
Tip. To apply the selected style and remove all existing formatting, right-click the style and choose Apply and Clear Formatting from the context menu.
Every time you make a table in Excel, it automatically gets a default name such asTable1, Table2, etc. When you deal with multiple tables, changing the default names to something more meaningful and descriptive can make your work a lot easier.
To rename a table, just do the following:
Tip. To view the names of all tables in the current workbook, press Ctrl + F3 to open the Name Manager.
Excel tables have many awesome features that simply calculating, manipulating and updating data in your worksheets. Most of these features are intuitive and straightforward. Below you will find a quick overview of the most important ones.
All tables get the auto-filter capabilities by default. To filter the table's data, this is what you need to do:
If you don't need the auto-filter feature, you can remove the arrows by unchecking the Filter Button box on the Design tab, in the Table Style Options group. Or you can toggle the filter buttons on and off with the Ctrl + Shift + L shortcut.
Additionally, you can create a visual filter for your table by adding a slicer. For this, click Insert Slicer on the Table Design tab, in the Tools group.
To sort a table by a specific column, just click the drop-down arrow in the heading cell, and pick the required sorting option:
For calculating the table data, Excel uses a special formula syntax called structured references. Compared to regular formulas, they have a number of advantages:
The screenshot below shows an example of a structured reference that sums data in each row:
Another great feature of an Excel table is the ability to summarize data without formulas. This option is called Total Row.
To sum a table's data, this is what you need to do:
The Total row is inserted at the bottom of the table and shows the total in the last column:
To sum data in other columns, click in the Total cell, then click the drop-down arrow and choose the SUM function. To calculate data in a different way, e.g. count or average, select the corresponding function.
Whatever operation you choose, Excel would use the SUBTOTAL function that calculates data only in visible rows:
Tip. To toggle the Total Row on and off, use the Ctrl + Shift + T shortcut.
When you type anything in an adjacent cell, an Excel table expands automatically to include the new data. Combined with structured references, this creates a dynamic range for your formulas without any effort from your side. If you don't mean the new data to be part of the table, press Ctrl + Z. This will undo the table expansion but keep the data that you typed.
You can also extend a table manually by dragging a little handle at the bottom-right corner.
You can also add and remove columns and rows by using the Resize Table command. Here's how:
Tables are very easily formatted due to a predefined gallery of styles. Additionally, you can create a custom style with your own formatting.
When you insert a table in Excel, the default style is automatically applied to it. To change a table style, do the following:
When you format a table with any predefined style, Excel preserves the formatting you already have. To remove any existing formatting, right-click the style and choose Apply and Clear formatting:
To add or remove banded rows and columns as well as apply special formatting for the first or last column, simply tick or untick the corresponding checkbox on the Design tab in the Table Style Options group:
For more information, please see How to alternate row / column colors in Excel.
If you'd like to have all the functionality of an Excel table but do not want any formatting such as banded rows, table borders and the like, you can remove formatting in this way:
Note. This method only removes the inbuilt table formatting, your custom formatting is preserved. To remove absolutely all formatting in a table, go to the Home tab > Formats group, and click Clear > Clear formats.
For more information, see How to remove table formatting in Excel.
Removing a table is as easy as inserting it. To convert a table back to a range, just do the following:
This will remove the table but retain all the data and formatting. To keep only the data, remove table formatting before converting your table to a range.
This is how you create, edit and remove a table in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents