Home Archives for New in Excel 2010
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: , , , , , , ,
July 31, 2011 Leave a Comment

Use the Show Values As Formulas in Excel Pivot Tables

Show Values As

Show Values As in Pivot Table

I participate in a number of Group Discussion Boards on LinkedIn. In this video tutorial, I address one question that was recently asked:

“Is it possible to show a Percentage of Running Total formula in a Pivot Table?”

The answer is, “Yes, if you are using Excel 2010. If you are using earlier versions of Excel, you can use a work-around outside the Pivot Table.”

New in Excel 2010

A great new feature introduced in Excel 2010 is the drop-down menu for the “Show Values As’ in a Pivot Table Calculation. In earlier versions of Excel, the Show Values As option was not so obvious. And, I found that many of my clients did not know how to use it – or could not locate the drop-down menu.

Work-Around for Show % of Running Total

While the “Running Total in…” calculation has been available for quite some time, the “% of Running Total in …” formula is brand new in Excel 2010. The work-around that I demonstrate is to first, create the Show As “Running Total in …” formula. Then change the Font and background of these cells to “White.” Next, write a standard formula “outside of the Pivot Table” that references these cells. Be sure to turn off the “GetPivotData” Formula when you write this formula.

Download Extended Length Pivot Table Video Tutorials

Click on this link to learn about my new “extended length” video tutorials for Pivot Tables. They are availabe for Excel 2003, Excel 2007 and Excel 2010.

Watch Video in High Definition on YouTube

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

Master Excel Pivot Tables in 90 Minutes

Follow this link to learn about my new “Extended Length” – 90 Minutes – Video Tutorials

 

 

How to Use the Show As Values Formulas in Excel Pivot Tables [ 8:13 ] Play Now | Play in Popup | Download (1325)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
May 30, 2011 5 Comments

New Keyboard Shortcuts for Paste Special in Excel 2010

Content Key on Keyboard

Content Key on Keyboard

In Excel 2010, you can now use special keyboard shortcuts to control your Paste Special Options – e.g. Paste Values Only, Transpose, Paste Link, Paste Formatting, etc.).  There is a new technique to learn, however, before you can become proficient with these keyboard shortcuts in Excel 2010. Let me demonstrate.

Live Preview for Paste Special in Excel 2010

In my previous video tutorial, I demonstrated how to take advantage of this great new feature – “Live Preview for Pasting” – in Excel 2010. Click here to watch that video.

Two Ways to Use Keyboard Shortcuts to Paste Special in Excel 2010

  1. Press and Release the Ctrl Key, then press the “letter key” for the shortcut.
  2. Press the “Context Key” on your keyboard (to the right of the Space-bar; between the Alt & Ctrl keys) and then press the “letter key” for the shortcut.

Each of these techniques is easier to explain through a video demonstration than to write out the instructions. So, I encourage you to watch my video if you want to learn these new techniques for Excel 2010!

Learn More Paste Special Options

Main Menu Essential Skills for Excel

Main Menu for “9 Essential Excel Skills”

On my latest DVD-ROM, “Nine Essential Skills for Excel 2010,” I go into greater detail about the many ways that you can use Paste Special. This IS one of the 9 Essential Skills that I have identified. Follow this link to learn more about the 4 hour training video. The DVD-ROM includes 25 individual video tutorials, a 29-page instructional manual,  and the Excel 2010 Practice Files that I used while filming the videos.

Learn about all of the training resources that I offer at my secure online shopping website – http://shop.thecompanyrocks.com

Watch Video in High Definition on YouTube

Follow this link to watch this video tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

New Keyboard Shortcuts for Paste Special in Excel 2010 [ 8:39 ] Play Now | Play in Popup | Download (2790)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
May 28, 2011 1 Comment

Introducing Live Preview for Paste Special in Excel 2010

Paste Special Gallery

Gallery of Paste Special Options

I have always loved the many “Paste Special Options” available in Excel. Now, in Excel 2010, there is a great new tool available – “Live Preview of a Gallery of Paste Special Options” – to help you to discover or to choose the best way to paste items copied to the clipboard.

From either the Paste Menu on the Home Tab of the Excel 2010 Ribbon or from the right-click shortcut menu, you can hover your mouse over one of the Gallery Icons to see a “Live Preview” of your Paste Special Option Selection.

When I demonstrate this feature  to my clients, they are amazed to discover the many options that are available! Of course, some of these same clients are also “overwhelmed” by all of the choices on the menu!

That is why I created this video – to help you to discover and to select the best Paste Special Option for your needs.

Paste Between MS Office Programs

Later in this video tutorial, I also show you how to use the Microsoft Office Clipboard and the Gallery of Paste Special Options to copy a data set from Excel and Paste it as a Picture in a Word document. With the Office Clipboard, you can cut, copy and paste up to 24 items between Excel, PowerPoint and Word. However, you must first open up the Office Clipboard for this tool to be available to use.

Additional Paste Special Features in Excel 2010

I have decided to produce an addition video tutorial to demonstrate how to use some new Keyboard Shortcuts in Excel 2010 to take advantage of the Paste Special Options.

Watch Video in High Definition Mode

Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Introducing Live Preview for Paste Special in Excel 2010 [ 7:01 ] Play Now | Play in Popup | Download (2181)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , ,