If I have to teach ONE thing to the world, it would be Excel Tables.
I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.
Outdated formulas in Excel are a big operational risk
Tables eliminate that risk proactively.
Additional Benefits of Excel Tables
This in itself can become a series. But here is a short description of the benefits
Auto-update of formulas and references
All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.
Automatic formatting of tabular data
If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.
Automatic copying of calculated column formulas
Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.
This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.
If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.
Automatic copying of calculations when more data is added
This is very useful. If more data is added, calculated columns are auto-filled.
To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.
Automatic display of headings
Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.
Automatic copying of formatting, conditional formatting and validations
All attributes of cells are auto-copied when you add more rows in a Table.
Ability to use Table and Column names in formulas
Ability to integrate with SharePoint and Visio
You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.
In Visio you can create a visualization based upon a Visio based Pivot Table functionality.
2 Responses
Reblogged this on Office Corner Software Productivity Blog and commented:
One of the features of Excel which deserves to be known and used better.
Thanks François Auerbach for sharing and feedback. Just posted another article on this issue – about Background Error Checking. Have a look. http://wp.me/p43KFU-13t