Excel - Creating a Table

Creating a Table

When you input data into Excel it is initially a 'data array' and although it is perfectly fine to leave data in this format it is sometimes easier or better to convert it to Table for the following reasons;

  • Table data can be sorted and filtered faster and at the table headers themselves. 
  • Any new data that is added is automatically formatted like the rest of the data within the table, so expanding the data's formatting will occur across all new rows and columns
  • Any cell references will be displayed as a named Row or column rather than an alphabetic reference. e.g (Column B on the data array would be referenced as "B:B", in the table this would be referenced as "Table1[Awesomeness] In the examples provided below. 
  • Formulas and Autofills will be extended automatically
  • Any Charts or graphs created using the table data will automatically expand to include the new data added to the table.

 

2.png

Illustrative image
what a table looks like

1.png

Illustrative image
A standard Data array

 

How to create a table

Click into any cell within the data array and press "Ctrl + T", this will try to guess the references of the Table by displaying a dotted line around the suggested table and will confirm if you would like headers of not: Illustrative image

If this is the not the correct selection you can manually amend the reference to reflect what is the correct selection.  You can also use quick selection techniques mentioned in another help article or by clicking and dragging around the data before using the button to create a table in the ribbon under the home tab:

Once you have selected the data you may also find the 'format as table' button in the ribbon instead of pressing Ctrl + T:  Illustrative image

Quick sorting and filtering

On each of the headers you will find a small drop down list that will allow you sort and filter directly from the column, rather than having to select it first and then find the buttons in the ribbon  Illustrative image

What are Headers?

headers are the term given to a column's category, usually displayed at the top of column. If this is marked as a header then it will not be counted as a data but rather the column's name.

Extra Table Options

There are also more options for tables found in the contextual formatting options when a cell in the table is selected Illustrative image

  • Renaming a Table can be found on the far left
  • Converting the Table back to a data range (as it was before)
  • Showing and hiding:
  1. Header rows 
  2. Total rows
  3. Banded Rows and columns
  4. or even to hide the filter button itself. 

Still need help?