Data Table to Normal Range: How to Convert an Excel Table

data, table, data table, excel, convert, normal range

Excel has a data table format, called “Data Tables” (or “Excel Tables”) were introduced in Excel 2007. They were supposed to simplify the work with data in Excel. I like to use them quite a lot because they often make the work with functions (especially complex SUMIFS functions), quite simple. In this article I describe, how to convert an Excel table to normal cell range.

In a hurry? Click once on a cell in your data table, go to the table tools ribbon and click on “Convert to Range“.

What is an Excel table in?

First of all: We are not talking about the data table that are part of the What-If Analysis functions on the data ribbon. For the analyse function also called data table please refer to this article.

A table (which is also called “Excel table”) offers a structured way to organize your data. It has some advantages and disadvantages towards normal cell ranges:

  • A table (or “Excel Table”) offers some built-in filtering, sorting, row shading and so on.
  • It (by default) uses the same formula for the complete row. So, it’s not possible to use a different formula in a single cell within the data table.

Steps for converting an Excel Table into a normal table

Method 1: Convert table to range via the ribbon

data, table, data table, excel, table, convert
Convert a data table (Excel table) to a normal cell range in Excel.

There are two necessary steps for changing your table to a normal range (the numbers are corresponding to the picture):

  1. Click on a cell within the Data Table.
  2. The yellow Table Tools (“Design”) ribbon is now visible. Click on it.
  3. A little bit hidden: “Convert to Range” is located under the “Tools” group. Click on it and confirm with YES.

Please note that the formatting (for example the row background pattern) might be lost.

Method 2: Right-click on the table to convert it to range

data, table, excel, table, convert, transform, normal, range
The right-click method for converting a data table to a normal cell range might be slightly faster.

The second method is even faster.

  1. Right-click on a cell within the Data Table.
  2. Scroll down to “Table” and click on “Convert to Range”. Confirm with YES.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

Insert an Excel table

insert, table, data, table, excel
Inserting a data table is quite simple: Click no Insert and then on Table.

Is also not difficult to convert a normal table into a data table (or Excel table).

  1. Select the cells you want to convert. Usually it’s also fine to just select one cell and Excel detects the complete table range. But the safer way is to select the entire cell range.
  2. Go to the “Insert” ribbon and click on “Table”.
  3. Check if the selected cell range is correct and click on OK.

There is also a keyboard shortcut for inserting Excel tables: Press Ctrl + T on the keyboard.

Image by Pexels from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *