Home Archives for Excel 2007 Tables
October 24, 2011 1 Comment

How to Use Structured Formula References for Tables in Excel 2007 and Excel 2010

Structured Formula Reference - Excel 2007

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.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
September 19, 2011 2 Comments

How to Create a Summary Report from an Excel Table

Functions for Executive Summary

Functions for Executive Summary

I created this Excel Video Tutorial in response to a viewer who, in her company, is not permitted to use a Pivot Table to create a report. Unfortunately, this happens more often than you might think. Nevertheless, I promised to share my tips and advice. I think that many of this tips will help Excel users at all levels.

Tips Covered in this Video Tutorial

  • Use Advanced Filter to Extract a list of unique customer names from a range with @ 4,300 records.
  • Later in the lesson, I show you how to extract this list to a different worksheet – a tip that will save you time when preparing your reports!
  • How and why to convert a normal range of data into a TABLE in Excel 2007 / 2010 or into a LIST in Excel 2003.
  • How to create “Named Ranges” for your key data cells and to use them in your Formulas and Functions.
  • How to use the SUMIF, AVERAGEIF and COUNTIF Functions in your summary report.

Keyboard Shortcuts Used in this Tutorial

  • Ctrl + T to convert to a TABLE in Excel 2007 / Excel 2010
  • Ctrl + L to convert to a LIST in EXCEL 2003
  • Ctrl + A to open up the Function Arguments Dialog Box
  • Ctrl + Shift + F3 to open the Create Names Dialog Box
  • F3 to open the Paste Names Dialog Box

I decided that I will only make this video available on YouTube – I think that you will enjoy the clarity that you get from the High-Definition / Full Screen Mode.

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition on my YouTubeChannel – DannyRocksExcels

Visit my new secure online shopping website

I invite you to visit http://shop.thecompanyrocks.com – my new online shopping website.

Watch Video Now

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Functions, Excel Training, Keyboard Shortcuts, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
June 12, 2011 Leave a Comment

How to Take Advantage of Excel 2007 – 2010 Tables and Excel 2003 Lists

Excel Table Styles

Excel Table Styles

In this video tutorial, I cover multiple versions of Excel. I demonstrate how to take advantage of the Table Tools that were introduced in Excel 2007 and I also show you how to create Excel 2003 Lists.

Topics Covered on Video Tutorial

  • Convert a range of data to an Excel 2007, Excel 2010 Table.
  • Convert a range of data to an Excel 2003 List.
  • View and change the Total Row in each version of Excel.
  • Append new records to Tables and Lists.
  • Add a new field to Tables and Lists.
  • Write a formula in one cell of an Excel Table and have it automatically copy down for each record.
  • Change the Table Styles in Excel 2007 and 2010.
  • Use Conditional Formatting to add alternate row shading to an Excel 2003 List

Watch Video in High Definition on YouTube

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

Download This Excel Workbook

Click on this link to download the Excel Workbook that I used in this video tutorial.

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $39.95!

Link to this post!
Filed Under: Conditional Formatting, Download Files from Video Lessons, Excel Data Tables and Lists, Excel Training, New in Excel 2007, New in Excel 2010, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
June 8, 2011 Leave a Comment

Refer to an Excel Table Field When Writing Formulas

Refer to Excel Table in Formula

Refer to Excel Table in Formula

While I strongly recommend creating “Named Ranges” that you can refer to in an Excel Formula, there is one drawback. By default, “named ranges” refer to “Absolute” Cells – e.g. $A$1:$A$15

However, what happens when your data set expands? For example, when you add records beyond row 15 in this case.  Now, your formulas do not refer to the additional data in each field. You need to find a way to automatically expand the range of cells that your formula refers to.

Tables in Excel 2007 and Excel 2010

Fortunately, if you are using either Excel 2007 or Excel 2010, you can “format” your data set as an EXCEL TABLE and take advantage of many great options:

  • When you append records (rows) , the definition of your Table automatically expands.
  • You can “name” your table to reflect its contents. For example, in this video I name my Table – DRTable –  and I refer to it in my formulas.

Writing Formulas that Refer to Excel Table Field

In this video tutorial I use the SUMIF Function. Remember that I have “named”my table DRTable. Here is how I begin my formula. =SUMIF(DRTable[Customer]. Notice the use of brackets ([ ]) around the [Customer] Field in the formula. This is the gotcha step in this lesson.

Watch This Video in High Definition on YouTube

I created this video tutorial in High Definition Mode. Click on this link to view it on my YouTube Channel – DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” – Visit my Online Bookstore for more information.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, SUMIF and SUMIFS Functions, YouTube Videos by Danny Rocks Tagged: , , , , , ,
May 18, 2011 Leave a Comment

Download My 75 Minute Video Tutorial on Excel 2007 Pivot Tables

Topics Covered

Table of Contents for Recording

I have just recorded and published a 75 Minute Video Tutorial titled, “How to Create and Modify a Pivot Table in Excel 2007 to Summarize, Analyze and Present the Information in your Data Set.”

It is published on my WebEx by Cisco site: http://thecompanyrocks.webex.com Follow this link to go directly to this recording.

There is a charge for this recording – US$9.95 In addition to the recording you get:

  • A Step-by-step instructional manual
  • Access to the Excel workbook files that I used for this recording

Not to brag , but I think that this video tutorial is excellent! I spent many hours planning out the exercises, working through the timing of each chapter of the video and practicing my delivery. I am happy with the result. And, more important, I think that you will also be satisfied with what you will learn from this recording!

What You Will Learn on My Recording

My goal is to give you a comprehensive understanding of Excel 2007 Pivot Tables. But to give you the opportunity to learn this in “bite-size chunks” that can replay when necessary. I created realistic exercises to illustrate each topic.  Also notice that I have created a Table of Contents for the recording so that you can go to a specific chapter on the recording. You can stop and start at any point.

How to Download this Recording

  1. Go to http://thecompanyrocks.webex.com or click on this link to go to the main menu.
  2. Click on “Recorded Sessions.”
  3. Click the link for the recording, “Excel 2007 Pivot Tables to Summarize Data.”
  4. Register for this session (First Name, Last Name and email address).
  5. Enter payment information (PayPal or Credit Card). This is a secure site!
  6. Follow the instructions to Download my recording.
  7. Recommended – you can also download the ARF Recorder to view the recording.
  8. At this point you can decide to convert the recording to a different format. E.g. WMV (Windows Media Player), SWF (Flash) or MP4 (MPEG4)
  9. At the end of the recording, you are taken to a web page with links to download the Instructional Manual and to download the Excel Workbook that I used during this recoring.

Give Me Your Feedback

I welcome your comments and suggestions for future video tutorial recordings. You can add your comments below or send them to me via email.

Link to this post!
Filed Under: Excel Training, Pivot Tables, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , , , , , , ,
May 31, 2010 3 Comments

Dynamically Expand the Source Data for Pivot Tables in Excel 2007

Without question, the new Tables Tools is my favorite feature  in Excel 2007 – this feature, alone, is worth the price to upgrade!

When you format a Data Range as a Table in Excel 2007, you can “append” new data to the table and the Table definition automatically expands to incorporate the new cells! If you create Pivot Tables based on a “Named Range” for your source data, this is GREAT NEWS!

Now, after appending the new data to your “Named Table: in Excel 2007, when you refresh your Pivot Table, you automatically get updated data in your Pivot Table report.

This can save you hours each month – at what – $50.00 per hour? $100.00 per hour? Watch these savings add up each month of the year

(Note: This is a re-post so that I can feed this video to my Podcast at the iTunes store.)

Follow this link, to view and download – for free – the episodes on my Podcast, “Danny Rocks Tips and Timesavers” at the iTunes store.

Learn how to “Quickly Create Excel Pivot Tables and Charts.”

Save hundreds of dollars each month when you use a Pivot Table Play Now | Play in Popup | Download (902)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, Pivot Tables, Podcast Tips & Timesavers, Tips and Time Savers Tagged: , , , , ,
May 24, 2010 Leave a Comment

How to Save Hours Updating Pivot Tables in Excel 2007

This Excel 2007 Training Video could – conservatively – save you at least one hour each month when you need to update your Pivot Tables!

What is that worth to you and your company? $100.00? $500.00? – Multiply that by 12 for each month in the year…

Have I got your attention yet?

Watch this short – 3 minute and 20 second video lesson – and you can thank me – or learn more ways to save time and money by purchasing my DVD – “The 50 Best Tips for Excel 2007”

If you watched this short – 3 minute 20 second video lesson, you can tell that I am excited about this topic. Would you like to trade 3 1/2 minutes to watch this video in order to save hundreds of $$$ each month to get an updated Pivot Table?  If your answer is anything other than “Yes!”, please send me an email – I would like to know what I am missing.”

Enjoy this video – and enjoy spending or investing the savings that you reap each month by applying this technique!

Want to save even more money? – Purchase my DVD – “The 50 Best Tips for Excel 2007″ – Click here to shop at my secure website.

Want to find a specific – and FREE – Excel Video Training Lesson? Click here to visit my Index of Excel Video Training Lessons.

You can watch this Excel video on YouTube. I have almost 200 videos on my YouTube channel – DannyRocksExcels

Learn how to “Quickly Create Pivot Tables” in Excel

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, MS Excel Tips, MS Excel Training, Pivot Tables Tagged: , , , , , , , ,
May 21, 2010 Leave a Comment

How to Take Advantage of Excel 2007 Tables

One of the major improvements in Excel 2007 is working with Tables. In this lesson I demonstrate Five Benefits for Working with Excel 2007 Tables:

  1. Automatically expand in size to add Columns (Fields) and Rows (Records)
  2. Use Natural Language Formulas – Copied down the column automatically!
  3. Total Rows Tool – great for seeing the results in filtered lists
  4. Easy to use Filters for Dates (Last Week, Next Quarter, etc.), Text and Numbers (Above Average, Top 10, etc.)
  5. Improved Formatting – Use Live Preview to see what style options look like before you select them

You can view and download this Excel video lesson – for free – on iTunes. Click here to visit my Podcast, Danny Rocks Tips and Timesavers at the iTunes store.

If you enjoyed this Excel Video Lesson, I invite you to purchase my DVD, “The 50 Best Tips for Excel 2007” – You can shop with confidence at my secure web store.

I help you to find the Excel Training Video Lesson that you want – Visit my Index of Excel Video Lessons

You can watch – and download – this Excel Video Training Lesson on You Tube. Subscribe to my YouTube Channel – DannyRocksExcels

Learn how to “Quickly Create Pivot Tables in Excel”

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Formulas, Excel Training, MS Excel Tips Tagged: , , , , ,