Use Different Types of Tables in Excel & Google Sheets

This tutorial demonstrates how to use different types of tables in Excel and Google Sheets.

 

table types intro

 

Ranged Tables

You can create a table from a range of cells in Excel, and similarly, you can convert a table back 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, select 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

 

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

 

table types ribbon convert to range

 

  1. 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.

 

tabletypes 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:

tabletypes table 1

 

Notice that in the names box, the name of the table is Table1.

Click in a separate part of your worksheet, and type the following formula:

=Table1

Notice that the data from the table is now placed in your worksheet at your selected location. Only the data is placed, not the column headers.

 

tabletypes dynamic table

 

HTML Tables

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

  1. In the Ribbon, select Data > Get & Transform Data > From Web.
  1. Type in the URL where the data is stored on the web.

 

importweb url

 

  1. Click OK and then click on Table 0 and then Transform Data to bring your HTML table into your Excel sheet.
  2. You can also embed a table you have 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, select Insert > Pivot Table.

 

tabletypes insert pivot table

 

  1. You can select 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

 

Use Different Types of Tables in Google Sheets

Google Sheets does not have a table function like Excel does. It does however have the ability to insert a Pivot table and an HTML table.

  1. Click within the data you want to create the Pivot table from, and then, in the Menu, select 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

 

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

 

tabletypes gs html