Simple Data Conversion to Excel Table


How many times have you started to use Excel to analyze some data given to you, and decided that you needed to filter it to summarize it quickly for you or for someone else.

Typically you would click in a cell within the data and then insert a filter. Excel is usually able to automatically add the filters to the correct columns.

Starting with Excel 2010, however, data can also be converted into an Excel table.

Why You Want a Table

By converting to a table, you get these benefits:

  1. Banded row colors are added
  2. Header row color changes to stand out from the data rows
  3. Header text is always visible as you scroll down through your data
  4. A data filter is automatically added
  5. Dynamic total row available
  6. Dynamic named range available for PivotTables

Creating a Table

Below is a sample of data before being converted to a table.

Table

To convert the data into an Excel table, click a cell within the data.

Then select Insert, and click Table. A form will pop up for you to select the range of data. The range defaults to what Excel think is correct. You can change the range of data if you want to.

If your data has headers, make sure to tick the check box. If you do not tick it, Excel creates its own header row with unique titles for each column, but will also include the first row as part of the data. This may not be what you intended.

Excel table

To convert the data to a table as shown below, click OK. The data can now be sorted or filtered.

Excel table

Column Headers Are Always Visible

If a table has more rows of data than the screen can show, the header names will always visible when scrolling down.

The column letters are replaced by the header text in the header, until you scroll back up to the top of table’s data. The screenshot below shows what happens.

Excel table

Automatically Calculated Columns

If you insert more columns in between columns already in the table, or a column directly the right of the table, that column then becomes part of the table of data. Also, any formulas entered in a cell in a column are automatically applied to all the cells in that column.

Total Row

A total row can be added to the bottom of the table. This can do summing, counting, averaging, and a variety of functions to columns in the Excel table.

Excel table

Leave a Reply