Convert Tabular Format into Excel Data Table

The Excel Table is a 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.

Converting a data range into a table makes its usage 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, then go to the Format as Table icon.

tabular format - 1

3. As you can see in the picture, many pre-set arrangements and color themes are available. Choose the one you like!

tabular format - 2

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 we want to convert into Table format. We always 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.” Excel will automatically insert headers into the first row if your data doesn’t have headers.

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, you will immediately see the “Format as Table” dialogue box; no need to look for it on the ribbon.

tabular format - 3

When your tabular data is converted into Excel Table, your data will look as shown below.

The most important changes are: the header got a darker shade, and the color of the rows is different. Data filters are enabled on the header. Note that the data hasn’t changed, only the design.

tabular format - 4

How to convert data from the table into a tabular format back?

Sometimes we would like to convert the Excel Table 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.

tabular format - 5

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.

tabular format - 6

• If our list or range is long and stretches beyond the visible screen, the header remains at the top when scrolling. It works similarly to the “Freeze Top Row” function.

tabular format - 7

• And now let’s see one of our favorite functions! When we reach the bottom of our range but want to add additional rows, use the TAB button. This adds new rows to the table.

tabular format - 8

• Now, let’s examine the case when we need to do calculations with the table. When new rows are added, Excel automatically figures them into the calculation.

tabular format - 9

Table Design – Overview

If you are 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, several options are available.

tabular format - 10

• Properties: Properties are 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.

tabular format - 11

Tools: The following options are available:

  • Summarize with Pivot Table: This creates a pivot table using the Excel data.
  • Remove Duplicates: Our article about data cleansing detailed how to remove duplicates quickly.
  • Convert to Range: With this option, we can convert an Excel Table into a regular range.
  • Insert Slicer (only in Excel 2013 and 2016): Slicer can be familiar with our Pivot tables subject. Its function is similar here. Very useful in filtering and arranging data using a single click.
tabular format - 12

External Table Data

This menu exports and refreshes data.

tabular format - 13

Table Style Options

These are design elements. There are more variants to customize the created table.

tabular format - 14

Table Styles

When we don’t have the time to create personal styles, here we have many pre-defined themes at our disposal. The variety is wide; everyone can find the perfect one. But also we can make our style by selecting the “New Table Style” option. It is the fastest method if you want to highlight every other row.

tabular format - 15

Conclusion

In summary, 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 additional calculations. We recommend its use.