The Excel Table is relatively seldom used and underrated feature in Excel. But in today’s article we’ll show you the advantages of its use. It makes it easy for a user to manage data if it is in a tabular format.
Just by converting a data range into a table its usage is even easier. After this little intro, let’s see the step by step tutorial!
Convert tabular format into excel table
Suppose our data is in tabular format. In the next few steps we’ll show you how to convert them into an Excel Table.
1. Select the worksheet range that contains the data set.
2. After this choose the Home tab, than go to Format as Table icon.
3. As you can see on the picture there are many pre-set arrangements and color themes are available. Choose the one you like!
4. The Format as Table dialogue box contains the following information. First “Where is the data for your table?”
The answer is obvious, we can see the range here that we would like to convert into Table format. We always need to check this before we start the conversion, to ensure the right range!
If our data table has headers we need to check the box “My Table has headers.” In case your data doesn’t have headers, Excel will automatically insert headers into the first row.
5. Click OK.
Shortcut tips: use the Ctrl+T keyboard shortcut, this makes the conversion a lot faster. If you highlight the data table, as an effect of the shortcut, immediately you will see the “Format as Table” dialogue box, no need to look for it on the ribbon.
When your tabular data is converted into Excel Table, your data would look as shown below.
Most important changes are: the header got a darker shade and the color of the rows are different. Data filters are enabled on the header. Note that the data hasn’t changed, only the design.
How to convert data from table into a tabular format back?
Sometimes we would like to convert the Excel Table back to its original tabular range format. Let’s see what we have to do! Highlight the range, or easier to click anywhere on the table. Right click and from the Table menu click on the “Convert to Range” icon.
Note that when you convert the Excel Table to a range, it no longer has the properties of an Excel Table (discussed below), however, it retains the formatting.
Useful Table Features in Excel
So we have seen how to create a table. Now let’s see the useful functions it enables.
• The created table automatically makes a header that enables the sort and filter options.
• If our list or range is long and stretches beyond the visible screen, the header remains at the top when scrolling. It works similar to the “Freeze Top Row” function.
• And now let’s see one of our favorite functions! When we have reached the bottom of our range but would like to add additional rows, simply use the TAB button. This adds new rows to the table.
• Now let’s examine the case when we need to do calculations with the table. When new rows added, Excel automatically figures them into the calculation.
Table Design – Overview
If you involved with Excel, you know it is easy to customize. We can dress it any way we want to. Click on any cell in the Table and an additional button “Table Tools Design” will appear on the ribbon. This is optional and can be seen only when working with tables.
When you click on the Design tab, there are several options available for you.
• Properties: Properties is one of the most useful tools of the group, we can give a unique name to our table. This is in the Table name box. We can use it as a reference. Re-size it and the name – of course – will remain the same.
Tools: The following options are available:
- Summarize with Pivot Table: This simply creates a pivot table using the data in the Excel Table.
- Remove Duplicates: We detailed in our article about data cleansing how to quickly remove duplicates.
- Convert to Range: With the use of this option we can convert an Excel Table into a regular range.
- Insert Slicer (only in Excel 2013 and 2016): Slicer can be familiar from our Pivot tables subject. Its function is similar here. Very useful in filtering and arranging data using a single click. Splendid addition to a dashboard.
External Table Data
This menu exports and refreshes data.
Table Style Options
These are design elements. There are more variants to customize the created table.
When we don’t have the time to create personal styles, here we have many pre-defined themes at our disposal. Variety is wide, everyone can find the perfect one. But also we can make our own style by selecting the “New Table Style” option. It is the fastest method if you want to highlight every other row.
As a summery we can say that with the use of Excel Table we can have our data in the appropriate structure. Further work is very simple with it. Its biggest advantage is that we can easily expand the original list / range without any additional calculation. We definitely recommend its use.