Best Microsoft Excel Bloggers

Tuesday, February 23, 2010

Smart Tables in Excel 2007

Excel 2007 offers a wonderful feature that not many people are aware of.  It is called a Smart Table and, in my mind, is basically an advanced version of Excel 2003’s List feature.

Excel considers a list or table to consist of data that is adjacent and does not contain a totally empty column or a totally empty row.
With the Table feature, you can have new data formatted as you enter it, shade alternate rows, instantly sum up the right column and quickly name the table.

The most exciting feature however is that tables are dynamic. As you add or delete data, Excel automatically updates and formats the table. So, what does this really mean for you? It means that if you base a chart, a pivot table or a formula on a table, as data changes in the table, it will automatically update the chart or whatever it is based on. This ensures that you are working with the most current data.

To create an Excel table

  • Type your data into the worksheet as you normally would
    • Make sure that you include column headings
    • Do not leave any empty rows or columns
  • Click in the data and press CTRL+T.
The Create Table dialog box will pop up so that you can double-check the data range and to ensure there is a checkmark in My Table has headers.

  • Click OK.

The table feature automatically bands and shades every second row and it applies filter arrows to the column headings. A definite timesaver.
When the table is created, a contextual toolbar also appears.
It allows you, among other things, to quickly sum a column, name the table, change formatting styles as well as remove duplicates.
Adding data into the spreadsheet is not any different than what you have done before – you simply select a cell in the row immediately below the last row and type the value in.

-If you enter a formula in the adjacent column, Excel will automatically copy it down the entire table and format it.
-If you enter data in the adjacent row, Excel will automatically format it so that it matches the table.

To me, the exciting part of the table is that when you add the total row, Excel automatically calculates the values in the rightmost column. You can change that summary operation or add a total to another column easily.

To Create a Total Row
-Make sure your cursor is in the table
-Click on the Design tab under the new Table Tools tab
-Click Total Row

The last row of your total now displays the sum of the right most column.

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek