Pivot Tables | The Company Rocks

Danny March 20, 2013 4 Comments

2.9 Million Views for My YouTube Channel – DannyRocksExcels

This morning, I passed the 2.9 Million views milestone for my DannyRocksExcel’s YouTube Channel!

Thank you to all who have supported my Excel tutorials since I began posting them in August, 2008.

Quickly Improve Your Excel Skills

I have created many Video Tutorial Packages that I offer for sale at my secure online shopping website – http://shop.thecompanyrocks.com I invite you to visit and preview my training resources for MS Excel and the other programs in the Microsoft Office Suite.

The “Special Bundles” of videos are very attractively priced!

Share and Enjoy:

Filed Under: Access Tips and Timesavers, Conditional Formatting, Excel Data Tables and Lists, Excel Forms, Excel Training, Most Viewed Excel Videos, Pivot Tables, What-if Analysis, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: "What-if" Analysis in Excel, Conditional formatting in Excel, Consolidate Excel data, Excel Form Controls, Excel Pivot Tables, Excel Video Training, MS Excel Training, Video lessons for Excel

Danny January 4, 2013 Leave a Comment

How to Work with Numeric Values in an Excel Pivot Table

Value Field Settings for Pivot Table

One of my clients contacted me with a question about Excel Pivot Tables. Specifically, about how to control a field – Employee ID – that contains numeric values.

Beginning with Excel 2007, when you click – to add – a field that contains numeric values, it is automatically placed in the Values Zone of the Pivot Table and the Sum Function is used to Subtotal it. That is what was troubling my client.

It is really easy to change the location for any field in the Pivot Table. In this case, I moved the Employee ID to the Row Labels area and everything worked out nicely.

Value Field Settings for Pivot Table

In my experience, I use the commands in the Value Field Setting Dialog Box more than any other commands when working with Pivot Tables. Learning how to use the Value Field Settings Commands is an Essential Skill when working with Pivot Tables. In this video tutorial, I demonstrate how to:

Change the function used to subtotal a field
Change the name of the subtotaled field
Change the numeric formatting for the subtotaled field
Move fields to different drop zones in a Pivot Table
Expand and collapse entire Pivot Table fields
Remove fields from the Pivot Table

Video Training Resources for Sale

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see the many video training resources that I make available.

Watch Video Tutorial in High Definition

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

Share and Enjoy:

Podcast Video [ 5:51 ] Play Now | Play in Popup | Download (7806)

Filed Under: Excel Training, Pivot Tables Tagged: Change Subtotal Function in Pivot Table, Excel Video Training, Expand and Collapse Pivot Table Fields, Fomat Numeric Valies for Pivot Table Field, Value Field Settings for Pivot Tables, Video lessons for Excel

Danny December 13, 2012 1 Comment

Excel Pivot Tables Tutorials: My YouTube Playlist

I have been organizing the video tutorials on my YouTube Channel – DannyRocksExcels – into Playlists.

I quickly realized that I have more videos (70 +) that focus on Excel Pivot Tables than on any other topic. So, having a playlist for all 70 (and growing) Pivot Table Tutorials is a great help when you need to learn about Pivot Tables. Or, simply to refresh your memory about how to use a specific Pivot Table Tool.

Scope of My Pivot Table Tutorials

I posted my first Pivot Table Tutorial on YouTube back on August 1, 2008. So far, that 7 minute Introduction to Pivot Tables in Excel 2003 has received over 90,000 views! Since then, I have created Pivot Table videos for Excel 2007 and Excel 2010.

In-Depth Focused Training for Excel Pivot Tables

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn about the many training resources that I offer you.

Link to My Excel Pivot Tables Playlist

Follow this link to go to my Excel Pivot Tables playlist on YouTube. If you like this Playlist, I encourage you to share it with your friends and colleagues.

Excel Pivot Tables Playlist on YouTube

Share and Enjoy:

Filed Under: Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: Calculated Fields in Pivot Tables, DannyRocksExcels YouTube Channel, Excel Pivot Tables, Excel Video Training, Group Dates in Pivot Tables, Hide Details in Pivot Tables, MS Excel Training, Video lessons for Excel

Danny December 11, 2012 Leave a Comment

How to Prevent Empty Cells from Displaying in a Pivot Table

In response to one of my viewers, I demonstrate how to prevent “empty cells” from displaying in a Pivot Table Report. In my experience, I have found that clients and colleagues who are unfamiliar with Pivot Tables tend to “doubt the numbers” when they see these “empty cells.”

What You Will Learn

In my video, I demonstrate:

How to use Pivot Table Options to show a Zero instead of “blanks.”
How to “drill down” to reveal the details for any Subtotal.
How to use a great Keyboard Shortcut to apply and clear Filters.

Learn More About Pivot Tables

Pivot Tables Tutorial

I have published a 90 minute video tutorial focused exclusively on Pivot Tables and Pivot Charts. The package includes:

9 Video Tutorials – total of 90 minutes of video instruction.
Step-by-Step Instructional Manual as a PDF for you to use as you watch the videos.
The Excel Worksheets that I use in each video lesson so that you can practice your new skills.

I make this video training resource available both on DVD-ROM and as a Downloadable Product. Versions are published for Excel 2010, Excel 2007, and Excel 2003.

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn more about all of my training resources.

Watch Video in High Definition

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

Watch Video Now

Share and Enjoy:

Filed Under: Excel Training, Pivot Tables Tagged: Drill Down for Details in a Pivot Table, Excel Keyboard Shortcuts, Excel Pivot Tables, Excel Video Training, Filter Excel Data, Video lessons for Excel

Danny October 8, 2012 Leave a Comment

Two Glowing Reviews for my Excel Video Tutorials

Today, two of my customers posted glowing reviews for my Excel Video Tutorials. Thank you! Thank you! Thank you!

9 Essential Skills for Excel 2010 Video Tutorial

“Nine Essential Skills for Excel 2010”

“In my opinion the 9 Essential Skills for Excel 2010 , is an outstanding training video. It covers a lot of the required intermediate requirements. One thing that really stands out for me is the method of presentation. Danny always:

a) explains what he is going to do in simple language

b)demonstrates what he has just explained

c)shows you where to find the icons on the ribbon

d)shows you the keyboard shortcuts to use(they actually appear on the video as they are used.

The video also had a good introduction to the value of using tables. Like all of Danny’s videos this should be a fantastic success.

– John J, Canada

Pivot Tables for Excel 2003

“Excel 2003 Pivot Tables to Summarize, Analyze, and Present Information”

“A mere few hours with your Excel 2003 course and I was able to
perform pivot table operations which had eluded me for over a year
using multiple other publications totaling nearly 1,500 pages: while those sources are terrific, each of us learns differently, your courses certainly fill that learning gap for me.

I deal with mostly with cumulative metrics, that is, incremental, week to week.

After two evenings of your course, I was able to build tables presenting the year, month, quarter & weekly totals of the multiple tools deployed, and, amazingly, display the incremental changes down to a week-to-week basis.

You had mentioned that you hoped I enjoyed the course, of course, I enjoyed the course – indeed, but after a year-plus struggle with several other sources, the *learning* from your course was the true reward!”

– Paul B., Colorado

Learn More About My Video Training Resources

I am confident that you will learn many valuable tips and techniques from my extended length Excel Video Training Resources.My Motto is:

“I work hard to make it easy for you to get the most out of Microsoft Excel”

Follow these links to learn more about my video training resources:

Excel Pivot Tables to Summarize, Analyze, and Present Your Data
- Available for Excel 2010, Excel 2007, and Excel 2003
- Available on DVD-ROM and also for Instant Downloading
Nine Essential Skills for Excel
- Also available for Excel 2007
- Available on DVD-ROM or for Instant Downloading

Share and Enjoy:

Filed Under: Excel 2003 Video Lessons, Excel Basics, Excel Formulas, Excel Functions, Excel Training, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Video Training Courses Tagged: Excel Pivot Tables, Excel Video Training, Tips and Time Savers, Video lessons for Excel

Danny October 2, 2012 Leave a Comment

The 10 Most Watched Excel YouTube Videos for September 2012

My YouTube Channel – DannyRocksExcels – had 141,586 individual views during the month of September 2012:

Here is a list – with Hyperlinks – to the 10 Most Watched Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

I extend my heartfelt thanks to my many loyal viewers and subscribers to my Excel video tutorials. Your feedback and requests for solutions have formed the foundation for my Excel Training Videos.

9 Essential Excel 2010 Skills

Learn More About my Extended Length Excel Video Training Resources

I have created a series of Extended Length Video Training Resources in the category of “Master Excel in Minutes.” I invite you to follow this link to learn more about my new series of videos.

Share and Enjoy:

Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Forms, Excel Formulas, Excel Functions, Excel Training, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, SUMIF and SUMIFS Functions, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: "What-if" Analysis in Excel, Excel Pivot Charts, Excel Pivot Tables, Excel Video Training, Solver Tool in Excel, Video lessons for Excel

Danny August 30, 2012 Leave a Comment

Take a Complete,10 Minute, Video Lesson from my Latest Publication

9 Essential Excel 2010 Skills

I am very proud to announce my latest publication – “Nine Essential Skills for Excel 2010.” I have made my publication available in two formats:

What You Get with this Preview

Here, in this preview of my publication, I offer you one, complete, ten-minute, video tutorial from my video publication. This video tutorial demonstrates how to use Subtotals and Pivot Tables in Excel 2010. On my video publication, you will find 24 additional video tutorials just like this. You will get @ 4 hours of focused video instruction to help you to master the Essential Skills in Excel 2010!

The Nine Essential Skills for Excel 2010

Here are – in my opinion – The “Nine Essential Skills in Excel 2010”:

Entering Data Efficiently – 4 video tutorials Run Time: 28:42
Selecting Cell Ranges Efficiently – 2 video tutorials Run Time 13:28
Creating & Using Named Cell Ranges – 2 video tutorials Run Time 16:00
Applying Styles & Formatting – 3 video tutorials Run Time 22:49
Working with Structured Data Sets – 4 video tutorials Run Time 32:00
Working with Formulas & Functions – 4 video tutorials Run Time 52:16
Using Paste Special Options – 2 video tutorials Run Time 17:18
Using Data Validation – 2 video tutorials Run Time 12:34
Using “What-if” Analysis Tools – 2 video tutorials Run Time 15:09

I have tried to offer a range of Video Tutorials that appeal to both the beginning Level and the Intermediate Level Excel user. That is why, I decided to separate each chapter into multiple episodes. In this manner, I can offer the Beginning Level Excel User the foundational instruction that they require without “boring” a more experienced user.

Supporting Materials Included in My Publication

In addition to the 25 individual video tutorials included in my package, you will also receive:

A PDF Instructional Booklet that you can print out to use as you follow each video lesson.
The actual Excel Workbook files that I used while filming these videos that you can use to practice your new Excel 2010 skills.

Learn More About this Publication

I invite you to click on this link to learn more about the “Nine Essential Excel 2010 Skills” Video Publication

Watch this Video Tutorial in High Definition

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

View this Tutorial Now

Share and Enjoy:

Filed Under: Excel Training, Online Store for The Company Rocks, Pivot Tables, Subtotals and Outlines in Excel Tagged: Apply Filters in Excel, Create Subtotals in Excel, Excel Pivot Tables, Excel Subtotals, Excel Video Training, Filter on Cell Value in Excel, Go To Special dialog box, Remove Filters in Excel, Video lessons for Excel, Visible Cells Only in Excel

Danny July 30, 2012 6 Comments

How to Group People by Age Bracket Using an Excel Pivot Table

Pivot Table to Group Age Brackets

In my previous Excel Tutorial, I demonstrated how to use the Group Field command in a Pivot Table to summarize time periods by the Hour. Several viewers wanted to know what other grouping possibilities are available in Pivot Tables. One viewer wanted to know how to group people who responded to a survey by Age Brackets. That is why I created this tutorial!

Use DATEDIF Function to Calculate Age

From my perspective, the DATEDIF() is an invaluable function. However, it is not documented in Excel. Watch how I use its three arguments to calculate the age of each person in the data set.

Group Field by Age

Select a single value in the “Age” Field and then select the “Group Field” Command. In the dialog box, choose the “step-value” for your groups. In this case, I chose the Default Setting of 10 years.

Show Pivot Table Values As

Some of the most powerful Pivot Table tools are found on the “Show Values As” tab of the “Value Field Settings” Dialog Box. Watch me demonstrate how to show each Age Bracket as a Percentage of the Column.

Apply Conditional Formatting to Pivot Table

Take advantage of the greatly improved Conditional Formatting Commands in Excel 2007 and Excel 2010 to focus attention on the key information in your Pivot Table. In this case, I use the “Top 10 Items” rule to apply Conditional Formatting to the two highest Age Brackets- by percentage – for each gender.

Learn Excel Pivot Tables Quickly

Follow this link to learn about the focused 90 minute video tutorials that I have published to help you to really learn how to get the most out of Pivot Tables. Available in versions for Excel 2003, Excel 2007, and Excel 2010.

Watch Tutorial in High Definition

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

Share and Enjoy:

How to Group People by Age Brackets with an Excel Pivot Table [ 8:12 ] Play Now | Play in Popup | Download (3400)

Filed Under: Conditional Formatting, Excel Date and Time Functions, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: Conditional formatting in Excel, DATEDIF Function in Excel, Excel Pivot Tables, Excel Video Training, Group Fields in Excel Pivot Tables, Show Values As in Pivot Tables, Video lessons for Excel

Danny July 22, 2012 1 Comment

How to Group an Excel Pivot Table Field to Summarize By Hours

Pivot Table Summary Report By Hour

After viewing my previous video tutorial, one of my subscribers asked me to demonstrate how to actually summarize a minute-by-minute Excel report into an hour-by-hour report.

My viewer was unfamiliar with Pivot Tables – as are many Excel users – so I created this video to show him – and you how to do this.

Group a Pivot Table Field

Begin by right-clicking a single cell in the Time Field and choosing Group Field. In the dialog box, deselect Month and choose Hour. That’s it – it’s that simple!

Apply Conditional Formatting to the Top 10%

Beginning with Excel 2007, Conditional Formatting has been tremendously improved. Watch how I apply a “Top 10%” Conditional Format Rule with three mouse clicks!

Purchase My 90 Minute Focused Pivot Table Tutorial

Whether you are a novice or an experienced user of Excel Pivot Tables, you will learn some great Tips and Techniques when you purchase my 90 minute video tutorial: “Summarize, Analyze and Present Information with Excel Pivot Tables.”

Follow this link to learn more about my video tutorial for Pivot Tables. Available for ALL versions of Excel as either a DVD-ROM or as a Downloadable Product.

Watch Video Tutorial in High Definition

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

Share and Enjoy:

How to Group an Excel Pivot Table Field to Group by Hours [ 6:32 ] Play Now | Play in Popup | Download (4608)

Filed Under: Conditional Formatting, Excel Training, Pivot Tables, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: Conditional formatting in Excel, Conditional Formatting Rules in Excel, Excel Pivot Tables, Excel Video Training, Group Fields in Excel Pivot Tables, Top 10 Percentage Rule for Conditional Formatting, Video lessons for Excel

Danny April 9, 2012 Leave a Comment

How to Roll Up a Summary by Month to Filter an Excel Pivot Table

Filter with a Roll Up by Month Summary

In this Excel tutorial, I respond to a viewer request. He likes the new “Roll Up Summary by Month” feature for filtering a field in an Excel 2007 – 2010 Field. What he finds frustrating – there seems to be no natural way to accomplish this with an Excel Pivot Table.

Natural Language Date Filters in Excel

Before I solve my readers dilemma, I demonstrate how to take advantage of the new “Natural Language” Date Filters that were introduced in Excel 2007. Date Filters allow you to filter records from “Today,” “Last Week,” “Next Month,” etc. They are available for Excel Tables and Excel Pivot Tables. These “Natural Language” Date Filters are a major improvement in Excel!

Group a Field for Pivot Tables

To solve my viewers question, I “Grouped” the original Date Field in his Pivot Table to produce “virtual” fields for “Month,” and “Year.” Now, it is a simple step to filter the “virtual” Month Field to obtain a “roll up” filter for individual months in the Pivot Table. Just select a single cell in the Pivot Table Date Field and choose Group Field. Make your choices in the Grouping Dialog Box and you are “good to go!”

I also show you how to take advantage of the Expand and Collapse Field Commands in a Pivot Table.

In-Depth Video Tutorial for Excel Pivot Tables

At my secure, online shopping website, you can purchase my 90-minute Video tutorial for Excel Pivot Tables. Available for immediate downloading or on a DVD-ROM. Version specific editions for Excel 2003, 2007, and 2010.

Watch Video Tutorial in High Definition

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

Share and Enjoy:

How to Create a Roll Up by Month Filter for an Excel Pivot Table [ 5:42 ] Play Now | Play in Popup | Download (1696)

Filed Under: Excel Data Tables and Lists, Excel Training, Pivot Tables, Video Response to Viewers Tagged: Collapse and Expand Fields in Pivot Tables, Date Filters in Excel, Excel Pivot Tables, Excel Video Training, FIlter a List in Excel, Filter by Time Period, Group Dates in Pivot Tables, Natural Language Date Filters in Excel, Video lessons for Excel

Danny Rocks “Training You to Excel”

2.9 Million Views for My YouTube Channel – DannyRocksExcels

Top 10 Excel YouTube Videos Year-to-Date in 2013

Quickly Improve Your Excel Skills

How to Work with Numeric Values in an Excel Pivot Table

Value Field Settings for Pivot Table

Video Training Resources for Sale

Watch Video Tutorial in High Definition

How to Prevent Empty Cells from Displaying in a Pivot Table

What You Will Learn

Learn More About Pivot Tables

Watch Video in High Definition

Watch Video Now

Two Glowing Reviews for my Excel Video Tutorials

“Nine Essential Skills for Excel 2010”

“Excel 2003 Pivot Tables to Summarize, Analyze, and Present Information”

Learn More About My Video Training Resources

The 10 Most Watched Excel YouTube Videos for September 2012

Top 10 YouTube Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

Learn More About my Extended Length Excel Video Training Resources

Take a Complete,10 Minute, Video Lesson from my Latest Publication

What You Get with this Preview

The Nine Essential Skills for Excel 2010

Supporting Materials Included in My Publication

Learn More About this Publication

Watch this Video Tutorial in High Definition

View this Tutorial Now

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Top 10 Excel YouTube Videos Year-to-Date in 2013

Quickly Improve Your Excel Skills

Value Field Settings for Pivot Table

Video Training Resources for Sale

Watch Video Tutorial in High Definition

Scope of My Pivot Table Tutorials

In-Depth Focused Training for Excel Pivot Tables

Link to My Excel Pivot Tables Playlist

Excel Pivot Tables Playlist on YouTube

What You Will Learn

Learn More About Pivot Tables

Watch Video in High Definition

Watch Video Now

“Nine Essential Skills for Excel 2010”

“Excel 2003 Pivot Tables to Summarize, Analyze, and Present Information”

Learn More About My Video Training Resources

Top 10 YouTube Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

Learn More About my Extended Length Excel Video Training Resources

What You Get with this Preview

The Nine Essential Skills for Excel 2010

Supporting Materials Included in My Publication

Learn More About this Publication

Watch this Video Tutorial in High Definition

View this Tutorial Now

Use DATEDIF Function to Calculate Age

Group Field by Age

Show Pivot Table Values As

Apply Conditional Formatting to Pivot Table

Learn Excel Pivot Tables Quickly

Watch Tutorial in High Definition

Group a Pivot Table Field

Apply Conditional Formatting to the Top 10%

Purchase My 90 Minute Focused Pivot Table Tutorial

Watch Video Tutorial in High Definition

Natural Language Date Filters in Excel

Group a Field for Pivot Tables

In-Depth Video Tutorial for Excel Pivot Tables

Watch Video Tutorial in High Definition

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags