Wednesday, July 15, 2009

Put it in a table

Microsoft Office Excel 2007

Image via Wikipedia

Excel 2007 has enhanced the object formerly known as Lists (in 2003) – they’re now called Tables. Tables are now super cool, the greatest thing in Excel since, oh, AutoFiltering.

How do you make a Table?

I guess the obvious way is to Insert > Table.

But I much prefer placing your cell cursor on a cell in the midst of a dense rectangular shaped block of data cells and doing the Home > Format As Table action.

What can Tables do for you?

Let me count the ways

  1. A Table can have banded rows and/or columns. This colouring is independent of Direct Formatting or Cell Styles
  2. A Table auto-extends in columns if you type a new heading
  3. A Table auto-extends if you insert a new column or row in the midst of the cells
  4. An auto-extending table also extends any range names that you have assigned to column blocks or row blocks
  5. A table has a TableName and has ColumnNames which can be used in new Structured Formulae.
  6. If you type a formula in an empty cell in a column of the table, Excel 2007 tries to give you a Structured Formula (MyTable[ThisRow][Salary] * 9% instead of the classic A2*9%
  7. Completing a formula in an empty cell in a column of the table will cause Excel 2007 to fill the rest of the cells in that column with relevant formulae.
  8. A table does not mind you inserting a new row at the bottom of the table and it auto extends the banded shading as well as the Table’s cell addresses to that new row.
  9. A table acts as a a suggestion to be the source for a Pivot Table.
  10. A table overcomes the guessing glitch Excel 2007 has with regard column titles which look like numbers. For example if your column titles were CountryName, 2008, 2009, when you select the whole block to make an Excel chart, Excel 2007 thinks 2008 is a Datapoint and not a Data Series Name. So you then have to unconvince it by excluding the column titles of your block and manually editing the Data Series Name. Or maybe you have to apply Numeric Formatting as Text.
  11. A table supports a Totals Row which can have the aggregate statistic, normally SUM, AVERAGE, COUNT, etc…
  12. You can refer to totals of Table Columns by using the Expression fragment – SUM(TableName[ColumnName]) – the Table Name and Column Name appear in the autocomplete list when you progressively type the formula into the cell.
  13. A Table has the Remove Duplicates Command
  14. A Table has the AutoFilter drop down arrows in the column titles row – you can do all the AutoFilter activities

What you can’t do with a Table

  1. You can’t carry out the Data > Subtotals command in a Table. It’s greyed out. You can convert the Table back to a Range and then apply AutoFilters.
  2. You can’t use the TableName syntax in a Data Validation of a List. Is there a workaround?

Curious things about a Table

  1. If you change the Accent1, 2, 3… colours in a Theme, that affects Table Colours in an unexpected manner.
  2. If you want to use the Table smarts but don’t like the coloured banding and coloured borders in a Table, you can clear this colouring. There is one choice in the gallery of Table Formats called “None” but this choice is not visible on the Home Tab of the Ribbon. It is visible in the (Table Tools) Design Tab of the Ribbon – as well, there is a Clear All text item in that Gallery of Table Formats.
Reblog this post [with Zemanta] Tags: ,

No comments: