Archives for October 2011

Structured Formula Reference - Excel 2007

Beginning with Excel 2007, you can – and should – convert a Standard Data Range to an Excel Table. Doing so offers several advantages:

• You get a selection of Table Styles – including Alternate Row Shading – that make it easier to read your tables.
• You can automatically extend the Scope of your Table – by adding additional Fields (Columns) or Records (Rows).
• You can give your Table a “Name” to reflect the purpose of the Table.
• You can add a Total Row to Subtotal each Field (Column) and you can change the Function used to Subtotal from a drop-down menu.
• You can apply Structured Formula References in the Table – Enter the Formula in a Single Cell and it is Automatically copied down for each record in the Table.

“In This Row” Formula Styles

Structured Formula References use a new style of formulas. Rather than referring to a specific Cell, in an Excel Table, you refer to a Field “in this row.” This type of formula is easy to create “inside the table.” It is also fairly easy to create “outside the Table” using the new Formula AutoComplete tool. You will be using [ ] (Left & Right Brackets) for these formulas. I go over these details, in-depth, in this video tutorial.

There are also distinct differences in how Structured Formula References are created between Excel 2007 and Excel 2010. These Structured Formula References are greatly streamlined in Excel 2010 – in my opinion.

The SUBTOTAL() Function and Excel Tables

I strongly recommend that you become familiar with how to use the valuable SUBTOTAL() Function in Excel when referring to the data in a Table. With the SUBTOTAL Function, you can produce a wide range of summaries anywhere on your Excel Workbook. And… the Subtotal Results reflect the totals for any “Filters” that you apply to your Table.

Play this Video in High Definition

Watch this Video in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Go to Part 2 in this Series of Tutorials

Follow this link to go to Part 2 in this series of tutorials about using Structured Formula References in Excel Tables.

Announcing 6 New Products on My New Online Secure Shopping Website

I invite you to visit my new online shopping site. I have added 6 new products – Extended Length Video tutorials that you can either “download now” or purchase as a DVD-ROM. Each product contains over 90 minutes of in-depth video instruction for Pivot Tables. You can choose the product to fit your version of Excel – Separate products for Excel 2003, Excel 2007 and Excel 2010.

Click here to view my new products.