Maximizing Connectivity & Productivity

Excel table advantages

So why use Excel tables?  Isn’t a spreadsheet just like a table?  Well, there are benefits to using an excel table..

First, here’s how to create one.

Simply select your data and go to Home-> Styles-> Format As Table, pick the table style from the selection that pops up, confirm the location of your data, and select the Header row checkbox if you want to show the headers.

Now, here’s the fun part.

  1. For starters, it looks pretty already with the format that you picked.  If it doesn’t – select another style. 
  2. You now have those header rows which show the dropdowns without you having to use the auto-filter and so on.  It has integrated auto-filter and auto-sort functionality.
  3. If you add a new row, that row happily continues with the formulas for that table.
  4. If you have multiple tables on the spreadsheet, you can select all the data for that table, just by clicking in the top left cell of the table.
  5. For long tables, the header rows remain visible when scrolling while within the table.
  6. The new rows get the alternate shading automatically.
  7. Referencing a table is easier.  Instead of using: [WorkbookName.xls]!TableName as you would reference a range, you use  WorkbookName!TableName.  This is because range name points only to the data rows of the table. The header row is NOT included.
  8. In the Tools group, you can
    • Create a pivot table from this data.  If you add a row, Excel automatically updates the range, though you will have to refresh the pivot table.
    • Convert to a range (but if you revert, your pivot table reference is removed, and the pivot will not refresh),
    • and Remove duplicates.
  9. You can use the options in the External Tables Data tab in the ribbon.  For example, you can convert the table to a Sharepoint List, and then Open the table in a browser window.
  10. With the Styles feature, you can remove the header row, add a totals row, have banded rows or columns, format the first or last column differently, or simply choose one of the quick styles.


Leave a Reply

You must be logged in to post a comment.

Mississauga, ON, Canada 905.607.3500