Home Archives for Danny
June 2, 2011 1 Comment

Don’t Subtotal Excel Data, Use Subtotal Function Instead

Subtotal Function

Subtotal Function Numbers

I used to love creating Subtotaled Reports. They are useful. They are easy to create. But they are also “clunky.” In my opinion, there are too many steps to take when you wish to see a Subtotal for a different field or to use a different function in your Subtotals.

Let me introduce you to the Subtotal Function in Excel. Here are several ways to take advantage of this function:

  • You can place the Subtotal Function in any cell on your worksheet – it does not have to reside directly below your data field.
  • You can use the Subtotal Function in connection with Data Filters – to get the subtotal for the visible cells in a filter.
  • You can use any of the 11 functions available to the Subtotal Function (Sum, Average, Count, etc.)

Watch This Video in High Definition on YouTube

This file size for this video is a little bigger than usual. So, to watch it, click on this link to view it in High Definition Mode on YouTube.

Subtotal Function Part Two

I have decided to film a second video lesson on the topic of the Subtotal Function – Using Subtotal Function in Excel Tables and Lists. Click on this link to watch my second video on this topic.

Watch or Download My 24 minute Introduction to Pivot Tables Video Recording

I have started to posted a series of “extended length” video tutorials online at: http://thecompanyrocks.webex.com – Follow this link to get more information about viewing or downloading my “free” Introduction to Pivot Tables.”

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

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Functions, Excel Training, Subtotals and Outlines in Excel, YouTube Videos by Danny Rocks 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: , , , , , , , ,
May 26, 2011 Leave a Comment

Two Ways to Turn Excel Data On Its Side – Transpose the Data!

Transpose Excel Data

Transpose Excel Data

How many times have you spent an hour or more creating an Excel worksheet only to discover – or be told – that you have the wrong structure? The values that you entered run vertically down in Column A. And  now, you need to have your values run horizontally across in Row 1.

Well, don’t despair! And, most important, do NOT retype your entries on another worksheet. Rather,watch this video to see how to “Turn Excel Data On Its Side”:

  1. Select Transpose from the Paste Select dialog box or the right-click shortcut menu
  2. Use the =TRANSPOSE() Function which is an Array Function

Watch as I demonstrate the advantages of each approach – they are different! If you are “curious” about how an Array Function works, the TRANSPOSE Function is a good one to practice with.

Click this link to watch this video in High Definition on YouTube.

Now Using Excel 2010

I filmed this video using Excel 2010. I will use Excel 2010 for all future video lessons unless there is a particular need to use another version to illustrate a point.

Download Workbook Used in This Video

Click on this link to download the Excel Workbook that I used in this video tutorial. It is stored on my “SkyDrive” at www.office.live.com

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

Link to this post!
Filed Under: Download Files from Video Lessons, Excel Functions, Excel Training, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , ,
May 24, 2011 Leave a Comment

My Extended Length Pivot Tables Video Recordings Available for Excel 2003, 2007 and Excel 2010

On my WebEx site – http://thecompanyrocks.webex.com – I have posted three, one-hour-plus video recordings to help you to learn how to create and customize Excel Pivot Tables:

Download “Free of Charge” Resources for Each Recording

Click on the hyperlinks above to go to a special landing page for each Excel version. From the landing page, you will find links to download the resources that accompany each version:

  • The Excel Workbook – in each version of Excel (2003, 2007, 2010) that I use during the video recording.
  • The Step-by-Step Instructional Manual that I created for each Excel version of my recordings.
    • Available as an Adobe Acrobat PDF or as a Word Document

Very Attractive Pricing for Recordings

I have decided to price each of these recordings – which you can download to your computer – at US$9.95

  • Imagine what you would have to pay to hire a trainer – per hour –  to teach you these Pivot Table lessons!
  • Imagine how many pages in a book you would have to wade through to read about Pivot Tables!
  • Now, imagine being able to:
    • Watch a video demonstration of the steps to take to create or customize a Pivot Table.
    • Read – and make notes in – a written document that follows the steps that I demonstrate.
    • Practice the techniqes that I demonstrate on the video with the actual Excel worksheets that I use.
    • Skip back and forth to the chapter that you want to watch or review on the video recording.
    • Download the recording for the exact version of Excel that you are using.
    • Get all of this for only US$9.95

What You Will Learn on My Video Recordings

I have taken care to organize each of these topics “by chapter” – with starting points and ending points – on the recordings:

  • Create a Pivot Table in the Excel version that you are using.
    • Spotlight the uniqe features of each version
  • Filter and Sort the Fields in your Pivot Table
  • Change the Data Source & Refresh your Pivot Table
  • Use Multiple Functions and Views to Calculate your Pivot Table
  • Create a Calculated (“virtual”) Field in your Pivot Table
  • Group Fields – e.g. turn daily transactions into a Month, Quarter, Year Pivot Table Report
  • Create and Filter Pivot Charts
Download WebEx Recording

Download WebEx Recording

Download My Recordings at WebEx

  • Go to http://thecompanyrocks.webex.com and on the left side of the screen click on “Attend a Session – Recorded Sessions” to find the recording that you wish to download.
  • Register and enter your payment information – this is a secure shopping site.
  • Download the recording – also download the WebEx Recording Player so that you can play the ARF recording.
    • I recommend that you “Save” your recording on the download page. Play it from your directory of choice after it has downloaded
    • From the ARF Player for the WebEx Recording, you can convert the recording to another video format (MP4, AVI, WMV or SWF)

I welcome your feedback. Send me your questions, comments and suggestions for future recordings.

Link to this post!
Filed Under: Excel Training, MS Excel Training, Pivot Tables, Video Training Courses, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , , , , ,
May 22, 2011 2 Comments

Still Using Excel 2003? Download My Video Tutorial Pivot Tables 2003 to Summarize, Analyze and Present Data

Video Tutorial for Excel 2003 Pivot Tables

Excel 2003 PivotTables Video

By popular demand, I have created an “Extended Length” ( 69 minutes) video tutorial in Excel 2003 that you can download – for US$9.95  – “Create Pivot Tables in Excel 2003 toe Summarize, Analyze and Present the Information in Your Data.”

Quite a few of my customers and viewers are still using Excel 2003. Are you one of them? If so, this is welcome news for you! If you can even find a video tutorial for Excel 2003, chances are it was created “ages ago.” I incorporate current video technology to view online or to download  69 minute video tutorial that will teach you everything that you need to know to create, modify and present a PivotTable or PivotChart Report using Excel 2003.

What You Will Learn from this Video Recording

Here are the topics that I cover – each as an individual chapter on the recording:

  • Creating a PivotTable in Excel 2003
  • Filtering and Sorting Fields in an Excel 2003 Pivot Table
  • Refreshing your PivotTable; Changing the Source Data for your Excel 2003 PivotTable
  • Changing the Functions and Type of Calculations in your Excel 2003 PivotTable
  • Creating a Calculated Field in an Excel 2003 PivotTable
  • Grouping an Excel 2003 PivotTable Field (e.g. Month, Quarter, Year from a series of Daily Transactional Data)
  • Creating and Modifying and Excel 2003 PivotChart

Skip to Individual Chapters on the Video Recording

If you have ever purchased a recording of a “live” Excel Webinar, you know that one frustration is: “How do I get to the specific part of the recording that I want to learn or repeat?”

The last thing that I want you to worry about is “getting stuck” with watching my video recording from start to finish! NO! NO! NO!

That is why I recorded this video webinar with you in mind – I created an easy to follow Table of Contents on the recording so tht you can skip to or repeat an indivual chapter or starting point!

I am confident that you will enjoy this feature!

How to Get Started

  1. Go to http://thecompanyrocks.webex.com and click on “Recorded Sessions” to see the list of all of my Recorded Video Lessons on WebEx by Cisco.
  2. Click on this link to open the Video Recording, “Excel 2003 Pivot Tables to Summarize and Analyze Data”
  3. Register for this recording (First Name, Last Name and email address) so that I can assist you quickly if you need help with downloading the recording or accessing the support materials.
  4. Enter your payment information US$9.95 using PayPal or a Credit Card – This is a SECURE WEBSITE.
  5. Download this video recording. I recommend that you: 
    1. Download the WebEx by Cisco ARF Recorder/ Player. Once you have opend the Video Recording, you can convert it to a different format (e.g. WMV (Windows Media), SWF (Flash Format) or MPEG4 (MP4 Format)
    2. Save the File to a Folder on your hard disk drive.
  6. Watch the video recording.

Download the Support Material for this Video Recording

Here is a link to the web page that I created for this video recording. Follow the hyperlinks to view online or download the Excel Workbook that I used in this recording as well as the Step-by-Step Instructional Manual that accompanies this recording.

Give Me Your Feedback

As I develop these “Extended Length” video recordings, I seek your feedback. Tell me what you liked. Tell me what you did not like – any why. Send me your suggestions for future topics for my video tutorial recordings.

Link to this post!
Filed Under: Excel 2003 Video Lessons, Excel Training, Pivot Tables, WebEx by Cisco, WebEx Recordings & Webinars 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 13, 2011 5 Comments

How to Apply Conditional Formatting to a Pivot Table in Excel

A Pivot Table is a great way for you to summarize and present the information contained in your data set. When you apply conditional formatting to your Pivot Table, you can “visually” spot important trends or anomolies in your data.

Improvements to Conditional Formatting

Conditional Formatting in Pivot Table

Condition Formatting for Above Average

Beginning with Excel 2007, Conditional Formatting has been dramatically improved. In addtion to pre-built formula rules, you can also apply Data Visualizations (Icon Sets, Data Bars and Color Scales).

Conditional Formatting for Above Average

In this lesson, I apply the pre-built “Above Average” rule. I want Excel to dynamically format the summary values that answer, “TRUE” to this question: “Is this value “Above the Average” in the range of cells selected?

This is the key to understanding how Conditional Formatting works. Special Formatting is applied only when the answer to your CONDITION (a rule or a formula) is TRUE.

Dynamically Filter Conditional Formatting

In this lesson, I use the Report Filter zone in my Pivot Table to see how conditions change when I change the time period in my report. Values that meet the Condition, “Above the Average,” receive the formatting that I established in my rule. Try this on your Pivot Table. It is a wonderful tool to use in an Excel Dashboard.

View This Video Tutorial in High Definition

Follow this link to watch this lesson in High Definition on my YouTube Channel, DannyRocksExcels.

My Extended Length Pivot Table Video Tutorials

I offer “free of charge,” a 24 minute in-depth video tutorial for creating your first Pivot Table in Excel. Follow this link to learn how to view it online or to download it to your computer.

Vist My New Online Shopping Site

My new, secure online shopping website – http://shop.thecompanyrocks.com/ – is now open. I invite you to visit and learn more about the new products that I have added.

How to Apply Conditional Formatting to a Pivot Table [ 6:01 ] Play Now | Play in Popup | Download (1642)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , ,
May 11, 2011 Leave a Comment

How to Create an Interactive Pivot Chart in Excel

While I have created many Excel Video Tutorials demonstrating the power of interactive Pivot Tables during the past four years, I have NEVER created a video lesson focused on creating interactive Pivot Charts in Excel – until now!

Interactive Pivot Table Chart

Interactive Pivot Table Chart

Pivot Charts in Excel 2007

In this lesson, I show you how to create a Pivot Chart in Excel 2007. Starting with Excel 2007, there are several major changes:

  • By default, Pivot Charts are embedded on the same worksheet as the Pivot Table Report that is linked to the chart.
  • A new, PivotTable Chart Filter Dialog box is available – this works “in tandem” with the filters available in the PivotTable Field List” Dialog box.
  • It is very easy to “Move” a Pivot Chart to its own worksheet tab – or vice versa – to move the Pivot Chart to an existing worksheet.

 Pivot Charts and Pivot Table Reports are Connected

A Pivot Chart MUST be connected to a Pivot Table Report. Any changes that you make to the chart are reflected in the report and vice versa. If you filter the Pivot Table Report, the filter is automatically applied to the Pivot Chart. This is an important concept to understand. This is the reason why it is so easy to make BOTH Pivot Table Reports and Pivot Charts “interactive!”

Explore My Index of Free Excel Video Lessons

I was surprised to discover that I had not yet created a video tutorial for Excel Pivot Charts when I looked at my list of “free” Excel Video Lessons. This became the impetus for creating this lesson. I invite you to explore all of my free Excel Video Tutorials. Click on this link to go to my Index of Excel Video Lessons. I have organized the free video lessons by topic. Simply click on the title of any lesson and you will arrive at the posting and the video for that lesson.

Download Excel File for This Lesson

View this Lesson in High Definition

I invite you to view this Excel Video Tutorial in High Definition, Full-screen Mode on my YouTube Channel – DannyRocksExcel

Watch Video Now

 

Feedback Welcome

I always welcome your feedback. Let me know what you think about this – or any other – video lesson. Add your comments below!

Extended Length Video Training Resource for Pivot Tables

I have created 90-minutes video training resources for Pivot Tables. Follow this link to learn more about my “Master Excel in Minutes” series.

How to Create an Interactive Pivot Chart in Excel [ 9:12 ] Play Now | Play in Popup | Download (1587)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Most Viewed Excel Videos, Pivot Tables, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
May 9, 2011 2 Comments

How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

  1. Do not know how to create or use Pivot Tables
  2. Have no desire to learn how to create and use Pivot Tables
Convert Pivot Table to Standard List

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

  • Add the “Date” field to the grid and then select a single date.
  • Group the Date field by Month, Quarter and Year.
  • Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

  1. Remove both the Subtotals and Grand Totals from the Pivot Table Report.
  2. In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
  3. Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
  4. Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
  5. Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

  1. Select all of the values and all of the blanks in Column A.
  2. Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
  3. From the Go To Special choices choose “Blanks.”
  4. With all of the Blank Cells selected use this formula: = ↑
  5. Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
  6. Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video:   http://youtu.be/IwReVDJ7BAI?hd=1

Download This Excel Workbook

Link to this post!
Filed Under: Excel Formulas, Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
« Previous Page
Next Page »