Home Archives for 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

Top 10 YouTube Excel Videos

  1. Compare Two Excel Lists to Spot the Differences– 5,304 views in September
    1. Go to this Blog Post on my website
  2. How to Merge Multiple Excel Workbooks to a Master Budget – 4,915 views in September
    1. Go to this Blog Post on my website
  3. How to Use Advanced Filters in Excel– 4,660 views in September
    1. Go to this Blog Post on my website
  4. How to Add a Check Box Control to an Excel Form– 4,301 views in September
    1. Go to this Blog Post on my website
  5. Use the Built-in Data Form in Excel to View and Filter Records– 3,948 views in September
    1. Go to this Blog Post on my website
  6. How to Create a Summary Report from an Excel Table-3,932 views in September
    1. Go to this Blog Post on my website
  7. How to Create an Interactive Excel Pivot Chart– 3,696 views in September
    1. Go to this Blog Post on my website
  8. Consolidating Data from Multiple Excel Worksheets by Position– 3,618 views in September
    1. Go to this Blog Post on my website
  9. How to Use the Solver Tool in Excel– 3,532 views in September
    1. Go to this Blog Post on my website
  10. How to Use an Excel Data Table for “What-if” Analysis– 3,513 views in September
    1. Go to this Blog Post on my website

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

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.

Link to this post!
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: , , , , ,
September 30, 2012 1 Comment

How to Clear Cell Formatting In Excel

Numbers Stored as Text

Numbers Stored as Text

In my previous tutorial, I demonstrated how to clear all text formatting from all slides in a PowerPoint Presentation. Several viewers contacted me about publishing a tutorial that demonstrates how to clear formatting from ranges of cells in Excel.

Use the Clear Formatting Command

From my experience, I had found that a great many Excel users expect that formatting as well as contents are cleared when you press the Delete Key. This is not the case. The Delete Key only clears the contents of the cells in the selected range.

Use the Clear Formatting Command in the Editing Group on the Home Tab of the Ribbon. If you are using Excel 2003 or earlier, you will find this on the Edit Menu.

Numbers Stored as Text

As a Best Practice, I strongly recommend that you verify that the numeric values that you will be using in formulas are truly numbers and not numbers stored as text. This, unfortunately is a common case whenever you are working with data that has been imported from a main frame computer or other external sources. In this lesson, I demonstrate how to use Paste Special Operators to quickly convert numbers stored as text to true numeric values.

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

Nine Essential Skills for Excel

I cover, in-depth,  Entering and formatting data as well as using Paste Special Options on my new video resource, “Nine Essential Skills for Excel.”

  • Four hours of video training
  • 25 individual video tutorials
  • Step-by-Step Instructional Guide
  • Excel Practice Files included

Download It or Order My DVD-ROM

Watch My Tutorial in High Definition

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

How to Clear Formatting from Cells in Excel [ 6:03 ] Play Now | Play in Popup | Download (2881)
Link to this post!
Filed Under: Excel Basics, Excel Training, Format Cells in Excel, MS Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
September 27, 2012 Leave a Comment

How to Quickly Clear All Text Formatting from PowerPoint Slides

Control Slide Formatting

Use SlideMaster to Control Formatting

One of my clients inherited a “Ransom Note” PowerPoint Presentation. I think that you can visualize what a “Ransom Note” slide looks like: Multiple Fonts at various sizes with multiple colors and effects thrown in for good measure! Ugly and, certainly, not professional.

Steps to Clear All Text Formatting

In my video tutorial, I demonstrate:

  • How to work in PowerPoint’s Outline View
  • How to use Keyboard Shortcuts to expand and collapse the text for each slide
  • How to clear all of the text formatting

Use the SlideMaster to Set Default Settings for Text

In my experience, most of the people who use PowerPoint are either unaware of the SlideMaster or they do not know how to use it. In my tutorial, I demonstrate how Microsoft Office Themes and the SlideMaster work together to set the default setting for formatting your presentation.

Minimize Local Formatting

My goal, in creating this tutorial, is to impress upon you the importance of limiting your use of “local formatting.” The reason that “ransom note” formatting survives is a direct result of applying local formatting rather than using the SlideMaster to help you to maintain a consistent look.

Additional Resources for Learning PowerPoint

I am the author of the best-selling DVD-ROM, “The 50 Best Tips for PowerPoint 2007.” Click on this link to learn more about the 7 1/2 hours of focused video training that I offer on my DVD-ROM.

Watch My Tutorial in High Definition

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

 

 

How to remove all text frormatting from PowerPoint Slides [ 9:07 ] Play Now | Play in Popup | Download (4099)
Link to this post!
Filed Under: Podcast Tips & Timesavers, PowerPoint, Powerpoint Techniques, PowerPoint Video Training Tagged: , , , , ,
September 24, 2012 1 Comment

How to Use Lookup Functions in Excel – Take a Free Lesson from My Latest Video Training Resource

Video Lesson - Lookup Functions in Excel 2007

Video Lesson – Lookup Functions in Excel 2007

I have just published the Excel 2007 version of my latest video training resource, “Nine Essential Excel Skills.” And, I want to offer you the opportunity to watch a complete episode. This is one of the 25 video tutorials included on my video training resource. The complete package contains almost four-hours of focused Excel 2007 Training.

Lookup Functions in Excel 2007

Watch this complete 11 minutes and 30 second lesson, as I demonstrate how to use both the VLOOKUP() and HLOOKUP() Functions in Excel. I demonstrate how to return an “exact” match as well as how to return an “approximate” match. I use “plain language” to describe how to use Lookup Functions.

Learn More About My Video Training Resources

Here are the links to the specific product information pages for my latest video training resources:

Download My Step-by-Step Instructional Guide – for Free!

You can download a PDF of my Step-by-Step Instructional Guide for the “9 Essential Excel 2007 Skills” video training resource. I am offering this for free so that you can see the scope and detail of the training that I offer on my 4-hour video. Click on this link to begin the downloading process for my free Instructional Guide.

Watch this Lesson in High Definition

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

Watch Tutorial Now

 

Link to this post!
Filed Under: Excel Basics, Excel Functions, Excel Training, Lookup Functions in Excel, MS Excel 2007 DVD, Names for Cells and Ranges, Training Tagged: , , , , , , , , , ,
September 20, 2012 Leave a Comment

Instructional Guide “9 Essential Excel 2010 Skills” – Download for Free

Instructional Guide "9 Essential Excel 2010 Skills"

Instructional Guide “9 Essential Excel 2010 Skills”

I want you to discover the scope of the Excel Training that I offer on my new video tutorial, “9 Essential Excel 2010 Skills.” So, I am offering the PDF of the Step-by-Step Instructional Guide that accompanies the video tutorial.

Link to The Company Rocks Free Resources Page

Click on this link to begin the download process for the Instructional Guide.

Instructions for Downloading the Free Instructional Guide

  1. Add this product to your secure shopping cart at my website.
  2. During Checkout, you can choose to either Register or Shop as a Guest
  3. You will need to provide a valid email address in order to receive the actual link to download my Instructional Guide.
  4. Even though you will be downloading this product, I had to set it up as a physical product with “Free Shipping.”
  5. Shortly after you complete the checkout process you will receive an email that contains a hyperlink to the file that you will download.
  6. You will be downloading a “zipped” file. So, once the download is completed, be sure to “unzip” this file!
  7. I created my Instructional Guide using the free Adobe Acrobat Reader.

Read the Instructional Guide and then Purchase my Video!

I am convinced that once you see the scope of the training in Excel 2010 that I offer, you will want to purchase the video tutorial. I offer my video in tow versions:

List of the “9 Essential Excel 2010 Skills”

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

These are the 9 Essential Skills that I have identified for my video tutorial:

  1. Entering Data Efficiently in Excel 2010
  2. Selecting Cell Ranges Efficiently in Excel 2010
  3. Creating and Using Named Cell Ranges in Excel 2010
  4. Applying Styles and Formatting to Excel 2010 Worksheets
  5. Working with Structured Data Sets in Excel 2010
  6. Working with Excel 2010 Formulas and Functions
  7. Using Paste Special Options in Excel 2010
  8. Using Data Validation in Excel 2010
  9. Using Excel 2010 “What-if” Analysis Tools

Excel 2010 Practice Files Included

When you purchase my video tutorial, you receive the same Excel 2010 Worksheets that I used while filming each of the 25 video tutorials. Using the same files as you view my videos, you will be able to practice your new skills. And, of course, you also receive the same Instructional Guide that I am offering for free!

I welcome your feedback! Please send me your comments via email – danny@thecompanyrocks.com – or by adding a comment below.

Thank you!

 

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Data Validation, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Formulas, Excel Functions, Excel Training, Format Cells in Excel, Goal Seek in Excel, MS Excel Tips, MS Excel Training, Names for Cells and Ranges, New in Excel 2010, Online Store for The Company Rocks, Subtotals and Outlines in Excel, SUMIF and SUMIFS Functions, Video Training Courses, What-if Analysis Tagged: , , , , , , , , , , , , , , , , ,
September 13, 2012 2 Comments

How to Use a Check Box to “Toggle” Conditional Formatting On or Off in Excel

Sort by Color in Excel

Sort by Color in Excel

In my previous Excel Tutorial – “Conditional Formatting to Dynamically Format Dates” – I demonstrated how to  apply Conditional  Formatting based upon the number of days have passed since last contact with a customer. Now I will extend what you just learned.

Three Techniques in this Video:

  1. How to extend  Conditional Formatting Rules to additional cell ranges.
  2. How to use the “Sort by Colors” feature introduced in Excel 2007.
  3. How to add a Check Box Form Control that will “toggle” the Conditional Formatting On or Off.

Paste Special “Live Preview”

I demonstrate a great new feature introduced in Excel 2010, “Live Preview” for Paste Special. You will use Paste Special – Formats to extend the rules for Conditional Formatting to a new range of Cells

Sort by Color

Beginning with Excel 2007, you can now sort fields based upon the color of a font or a cell background. I show how this works.

The Check Box Form Control

The “key concept” to understand when using Excel’s Form Controls is the “Cell Link.” With a Check Box Control, the value in the Cell Link is TRUE when the box is “checked” while the value is FALSE when it is Unchecked. We can use this information to create a “Conditional Format based upon a Formula.”

Conditional Formatting Rules

When you base Conditional Formatting upon a Formula, the result of that formula must return TRUE in order apply this formatting. Setting the background cell color to “White” effectively “hides” the previous background cell colors.

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

Resources Offered for Sale

I encourage you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview the many training resources that I offer you. Check out my latest Excel Training Videos:

Watch this Video in High Definition

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

 

How to Use a Check Box to "Toggle" Conditional Formatting On or Off in Excel [ 8:29 ] Play Now | Play in Popup | Download (3175)
Link to this post!
Filed Under: Conditional Formatting, Excel Forms, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
September 11, 2012 1 Comment

How to Dynamically Color-Code Dates with Conditional Formatting in Excel

Conditional Formatting for Dates

Conditional Formatting for Dates

One of my viewers wants to learn how to “dynamically” color-code cells that contain dates. She is monitoring the frequency of contact with her clients and she wants Excel to automatically apply:

  • One background color to cells where it has been less than 60 days since last contact
  • A different color when the time frame is between 60 and 90 days
  • A third color when it has been over 90 days since the last contact with a client

Here, I demonstrate how she can do this using Conditional Formatting Rules. However, this will only work if you are using Excel 2007 or Excel 2010.

Understanding Dynamic Date Functions

In order to make this visualization “dynamic,” I make references to cells that contain the =TODAY() Function. This is a “volatile” function that updates automatically based upon the date in your computer system’s clock. I use this to classify the last date of contact with her clients.

Improvements in Conditional Formatting

Beginning with Excel 2007, you can now apply multiple rules that use formulas. Remember that when you use a formula, it must evaluate as “TRUE” in order to apply the formatting that you specify.

Formulas Used to Apply Conditional Formatting

Conditional Formatting Rules

Rules for Conditional Formatting

To make it easier for you to practice this skill, I have posted this table that details the formulas that I used for each of the three rules in my video tutorial.Note that with the middle condition, I use the =AND() Function. All logical tests must answer TRUE for the AND() Function to return TRUE.

It will take a little bit of practice for you to become comfortable with these formulas. As a Best Practice, I recommend that you create a table of formulas so that you can see how a “tweak” in your formula can turn a FALSE answer into a TRUE answer.

Related Video

Follow this link to Part Two in this Series. I demonstrate how to “toggle” Conditional Formatting On or Off by using a Check Box Control in Excel.

Watch this Video in High Definition

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

Get the Best Tips and Training for Excel

Click on this link to learn more about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” This link will take you to my secure online shopping website –  http://shop.thecompanyrocks.com

Subscribe to my Video Podcast on iTunes

Here is the link to go to my free Video Podcast, “Danny Rocks Tips and Timesavers” on iTunes.

 

 

How to use Conditional Formatting to Color-Code Dates in Excel [ 8:49 ] Play Now | Play in Popup | Download (3143)
Link to this post!
Filed Under: Conditional Formatting, Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Formulas, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
September 8, 2012 Leave a Comment

Review for my Latest Publication – “9 Essential Skills for Excel 2010”

Main Menu Essential Skills for Excel

Main Menu for “9 Essential Excel Skills”

I just received a fabulous review, from one of my viewers, for my video tutorial, “9 Essential Skills for Excel 2010.”

Read this Review

Product Reviews

It’s really a master piece!!

Posted by Tamoghna on 8th Sep 2012

“I was a beta tester of “9 Essential Excel Skills- Excel 2010” by Danny Rocks. In one word this is an excellent resource for anyone who wants to master those essential skills which are required to use excel professionally. There are total 9 chapters which consist of a series of dense but brief video lessons.

The lessons have been planned in a careful way so that the viewers are introduced from simple to more complex topics.

Surely you are going to be amazed by the incredible picture and sound quality of the videos. I had a feeling as if I was watching an excel movie while putting my head phone. Danny has several qualities as an instructor. His pace of delivery and voice modulation is just fantastic and if you are not an absolute excel-newbie watching a video just one time is enough. Before starting each lesson he gives a brief introduction so that you can connect what you learned in the previous lesson. He also repeats and stresses some part which you will find really helpful.

Another great take away from this video tutorial is a bunch of great keyboard shortcuts and best excel practices which you can expect only from an excel veteran. Among so many other things I was left with surprise why I didn’t use “page layout view” and “vertical alignment formatting” before! Even if you are an advanced excel user surely you are going to learn a lot of useful tricks including some commonly encountered gotchas and how to avoid them.

I won’t be taken aback If this product goes every corner of the excel user community and becomes best seller in this field.”

Learn More About My Video Tutorial

I have produced the “9 Essential Skills for Excel 2010” in two formats:

Both versions include 4 hours of video instruction. 25 individual video tutorials. The Excel Practice files that I used while filming the video tutorials. A PDF of the Step-by-step Instructional Guide that I created for these video lessons.

Version for Excel 2007 Now Available!

DVD-ROM, "9 Essential Excel 2007 Skills"

DVD-ROM, “9 Essential Excel 2007 Skills”

I have just published “9 Essential Excel 2007 Skills” for DVD-ROM. Click to to get more information about my latest publication.

Secure Shopping at The Company Rocks

You can use a secure shopping cart to purchase my products at my online shopping website – http://shop.thecompanyrocks.com

 

 

Link to this post!
Filed Under: Excel Training, Online Store for The Company Rocks, Reviews, Video Training Courses Tagged: , , , , , , , , , , , , , , , , , , , , ,
August 30, 2012 Leave a Comment

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

9 Essential Excel 2010 Skills

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”:

  1. Entering Data Efficiently – 4 video tutorials Run Time: 28:42
  2. Selecting Cell Ranges Efficiently – 2 video tutorials Run Time 13:28
  3. Creating & Using Named Cell Ranges – 2 video tutorials Run Time 16:00
  4. Applying Styles & Formatting – 3 video tutorials Run Time 22:49
  5. Working with Structured Data Sets – 4 video tutorials Run Time 32:00
  6. Working with Formulas & Functions – 4 video tutorials Run Time 52:16
  7. Using Paste Special Options – 2 video tutorials Run Time 17:18
  8. Using Data Validation – 2 video tutorials Run Time 12:34
  9. 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

 

Link to this post!
Filed Under: Excel Training, Online Store for The Company Rocks, Pivot Tables, Subtotals and Outlines in Excel Tagged: , , , , , , , , ,
August 23, 2012 1 Comment

How to Highlight Different Values in a Row with Excel’s Go To Special

Go To Special Dialog Box

Go To Special – Row Differences

I have published several video tutorials on the topic of Highlighting and Deleting Duplicate Records in Excel. However, a viewer recently asked me how to highlight cells that contain different values in the same Row. This is not as “crazy” as it first seems.

Scenario for this Tutorial

I am trying to determine accurate inventory counts for my product line. I have the count that the computer shows. I also have records from three different auditors who have performed manual counts. I need to easily highlight the cells where the inventory count “is off.” That is where there is a discrepancy between what the computer shows and what a manual count shows.

Go To Special Dialog Box

The commands in the Go To Special Dialog Box are some of my favorite tools. In my experience, I have found that most Excel users have never explored this dialog box.  I think that will change after you watch this video tutorial. For the Row Differences tool, you first select the range of cells that contain your data – beginning with the left-most column. The Row Differences will use this left-most column as the “baseline” and highlight each cell in that row that contains a value that is different from this baseline.

Sorting & Filtering by Color

Beginning with Excel 2007, you can Sort by Color and you can also Filter by Color. After the cells were highlighted with the Row Differences command, I applied a background cell color to each highlighted cell.

Use one of my favorite Keyboard Shortcuts, Ctrl + Shift + L, when working with Filters in Excel 2007 or Excel 2010.

Watch this Excel Tutorial in High Definition

You can watch this tutorial in High Definition on my YouTube Channel – DannyRocksExcels.  Click on this link to watch this video now.

Video Tutorials for Highlighting Duplicate Records

I mentioned that I have published many Excel Tutorials on the Topic of Highlighting & Removing Duplicate Records. Here are links to my tutorials:

Shop for Excel Training Resources

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

Watch My Video Podcasts on iTunes

Click on this link to watch my “Danny Rocks Tips and Timesavers” video podcast on iTunes. – It is free of charge!

 Note: The original Podcast Video did not have the correct video settings. This video has now been updated.

 

How to Highlight Different Values in a Row in Excel [ 4:34 ] Play Now | Play in Popup | Download (2169)
Link to this post!
Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , ,
« Previous Page
Next Page »