Use Different Types of Tables in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on March 29, 2023

This tutorial describes different types of tables in Excel and Google Sheets.

 

table types intro

 

Generally, when referring to “an Excel table,” a user is talking about the Excel-specific table feature (see How to Create an Excel Table). But “table” in Excel can mean different things. It might be raw data with fields in rows, or it could be any of the types below.

Ranged Tables

You can create a table from a range of cells in Excel and similarly, you can convert a table into a simple range of cells.

  1. To create a table from a range, highlight the range of cells with your data.

 

tabletypes select cells

 

  1. In the Ribbon, go to Insert > Tables > Table.

 

table types insert table

 

  1. Your data is now converted to a table and formatted with a default style.
  2. You can use the filter buttons at the top of each column to filter your data, as well as use the Table Design tab on the Ribbon to format and adjust the way the table is displayed.

 

table types ribbon design tab

 

Convert to Range

To convert your data back to a range, in the Ribbon, go to Table Design > Tools > Convert to Range.

 

table types ribbon convert to range

 

This keeps the formatting for your table but removes the ability to filter on each column and the Table Design tab is no longer displayed.

 

table types table formatted range

 

Dynamic Tables

A dynamic table is a table created from an original table and inserted into your data by using the name of the table in your formula.

For example, say you have the table below:

 

table types table 1

 

  1. Look at the name box; the name of the table is Table1.
  2. Click in a separate part of your worksheet, and type the following formula:
=Table1

 

table types dynamic table
The data from the table is now placed in your worksheet at your selected location. Only the data is placed, not the column headers. It will change whenever the original table is changed.

HTML Tables

An HMTL table is a table that has data that has been imported from the internet.

  1. In the Ribbon, go to Data > Get & Transform Data > From Web.
  2. Type in the URL where the data is stored on the web.
  3. Then, Transform Data to bring your HTML table into Excel.

 

importweb transform data

 

You can embed a table you created in Excel into an HTML table by saving your Excel file as an HTML file.

Pivot Tables

A pivot table is a table that is a summary table of information that comes originally from a bigger table.

  1. Click in your original table, and then, in the Ribbon, go to Insert > Pivot Table.

 

tabletypes insert pivot table

 

  1. Choose whether to create the pivot table in a new sheet, and you are then able to add the fields and values to your new pivot table.

 

tabletypes pivot table

 

Pivot tables have a robust range of features and are very customizable. Try using some shortcuts when you’re working with pivot tables.

Types of Tables in Google Sheets

Google Sheets does not have anything equivalent to Excel tables. You can, however, use pivot tables and HTML tables.

  1. Click within the data you want to create the pivot table from, and then, in the Menu, go to Insert > Pivot table.

 

tabletypes gs insert pivot table

 

  1. Insert the pivot table into a new sheet and then add the row, column, and value fields as needed.

 

tabletypes gs pivot table

 

To insert an HTML table, you can type the URL you wish to get the data from in the formula bar in your sheet.

 

tabletypes gs html

AI Formula Generator

Try for Free

See all How-To Articles