 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada. The tip in this show is from Excel 2007 Miracles Made Easy. Viewers can download a chapter-a-week from this book by following the previous link.
In Excel 2003, Microsoft introduced List functionality (Ctrl+L). They've improved and replaced this in Excel 2007
with table functionality. Most spreadsheets contain data in a tabular format - headings across the top and each
row containing a new record. If you have data in a tabular format, select a cell and press Ctrl+T to convert
the range to a table.
Once you have a table defined, you can use the tools in the Table Tools Design ribbon:
1) Add totals with a single click by choosing the Total Row checkbox in the Table Style Options group.
2) Add a new record in the blank row under the table and the table will expand to include the new row.
3) Format with one of 60 different color schemes in the Table Styles gallery. Note that the 6 checkboxes in the Table Style Options group will modify look of each style. (This gallery supports Live Preview - just hover over a style to preview it in the worksheet.
4) Enter a formula in the first row of a table and have the formula automatically copy to every row. Tip: format cell I2 as a percentage before
you enter the formula in I2. Here is the spreadsheet just before I press Enter:
Here is the spreadsheet after I click Enter. Excel has filled in the formulas to the bottom of the table.
Notice in the last image that Excel uses a new table nomenclature for formulas that point to a table. This replaced the old Natural Language Formulas that have been in Excel for several versions.
One advantage of tables is that any charts or pivot tables that are built based on a table will automatically expand to include new data when the table expands.
For the BEST TV show on technology, check out Call for Help.
This tip was originally published on February 23, 2007 and aired on TechTV in Canada and Australia on February 23, 2007.
The permanent URL for this page is http://www.mrexcel.com/tip141.shtml.
If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.