Excel Table

The Excel Table allows us to analyze the data quickly and easily. Today’s post explains the advantages of its use.

The tabular format means that every data, data point lives in one cell, for example, the name of the city, address, or phone number. If the data is in a tabular format, you should convert it into an Excel table.

Using Excel tables provides us with many advantages, and with its help, we can try to avoid errors. We’ll show you what kind of benefits do you get by using a table when creating a dashboard in Excel.

How to create an Excel Table

In the example, the data is in a tabular format. We’ll show you how to convert it into an Excel Table.

1. Select the range which contains data.

2. Go to the Home tab, then click Format as Table icon.

home tab format as table

3. Choose a preferred table style from the built-in styles.

format excel table

4. A new dialogue box appears. We have to add data to our table. Apply the ‘My Table has headers’ option if the range has a header. If the range does not contain a header, it will be inserted automatically.

5. Click OK.

Tip: apply the Ctrl+T keyboard shortcut to immediately insert a table without any table style.

create table using keyboard shortcut

Okay, the tabular data is converted. Let’s see the most important differences. We have a zebra-stripe data set, and data filters are enabled by default on the header. The data is the same as the above-mentioned range.

new excel table has been created

Convert data back into a tabular format

Are you want to convert the excel table into a tabular format? No problem, it’s easy.

  1. Highlight the range
  2. Right-click
  3. Click the ‘Convert to Range’ icon from the Table menu.
convert table to range

Advantages of using Excel Table

Sorting and filtering data: Let’s begin with our favorite function. The new table has a header and enables us to sort and filter data in seconds.

sorting and filtering table

Freeze top row: Similar to the excel freeze top row feature, the first row (header) remains on the top. This is very useful if we are working with large data sets.

Data entry without any troubles. To add a new record to the table, it’s not necessary to insert a new row. Locate the last cell in a range and press ‘Tab’ to add a new row to the range.

press tab to insert a new row

Calculations. If we are working with data, we have to apply calculations too. Just add a new row by clicking Tab. For example, if the scenario changes further down the line (you need to insert more data), expand the table. Excel will keep the formulas.

Table Design

Take a closer look at the ‘Table Tools Design’ tab. This is hidden by default; you’ll see the tab only when you working on tables.

Click on the Design tab. Multiple options are available.

excel table design

Properties: add a unique name to the table or resize it if you want.

Tools: The following options are available:

  • Summarize with Pivot Table
  • Remove Duplicates
  • Convert to Range
  • Insert Slicer

External Table Data

Final Thoughts

Excel table is a great tool if you want to transform your tabular data into an easy-to-use format. If you expand the initial data range and append it with new data, no additional calculations are necessary.