Home Archives for Video lessons for Excel
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: , , , , , , ,
August 13, 2012 3 Comments

How to Combine 2 Excel Workbooks Using VLOOKUP Function

VLOOKUP Function Arguments

VLOOKUP Function Arguments

I created this video tutorial to assist one of my viewers. He had 2 Excel Workbooks that he needed to combine. Because he had a MemberID Field in each workbook, I decided that the VLOOKUP() Function would be the fastest way to complete this task for my viewer.

Tips Covered in this Video

  • Move or Copy a Worksheet to another Excel Workbook
  • Use a Mixed Cell Reference – e.g. $A4 – so that column “A” reference is “frozen” when copying formula
  • Create “Named Range” to use as the “Table_Array” argument in VLOOKUP
  • Use FALSE as 4th (optional) argument in VLOOKUP to produce an “exact match”
  • Use IFERROR to prevent “error messages” from displaying

Watch this Video in High Definition

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

Learn About My Training Resources

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

Watch Tutorial Now

 

 

Link to this post!
Filed Under: Excel Functions, Excel Training, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
August 3, 2012 1 Comment

How to Use an Array Function in Excel – Learn How to Use the Frequency Function

Key Points for Frequency FunctionIn my experience, most of the people who I train have never heard of – let alone use – Array Formulas and Functions in Excel. However, once I demonstrate how they work, they become “hooked” and want to learn more about these “Magic Formulas!” In this tutorial, I demonstrate, “step-by-step” how to use the FREQUENCY() Function in Excel.

Previous Tutorial on The Company Rocks Website

If you watched my previous tutorial, I used the same data set and a Pivot Table to Group these 500 plus Sample Records by Age Bracket. Using a Pivot Table, you can quickly “group”  ages into brackets – without writing a single formula! However, on the down side, you must use a consistent “step-value” for these groupings – in this case by 10 years. When you need more flexibility in organizing your Age Brackets, use the Frequency Function in Excel.

How to Use the FREQUENCY() Function in Excel

  1. Create your “Bin Array.” In this example, enter – in ascending order – the ages that you want to find the “frequency of occurrences” between
  2. Select the “Array of Cells” that will contain your FREQUENCY() Function results.
  3. Write the FREQUENCY() Function –  1st argument is the  “Data_array”. In this case, I created a Named Cell Range called “Age.” this is mu “Best Practice Tip!”
  4. The 2nd argument is the “Bin_array.” Choose the vertical range of cells that you created in step 1 of this list. Make sure that the “size” of this Array matches the “size” of your Array Formula Selection.
  5. Complete the Array Function with the keyboard combination of “Ctrl + Shift + Enter.” This “CSE” combination is essential when entering all Array Formulas and Functions!

My Secure Shopping Website

I invite you to visit my secure shopping website – http://shop.thecompanyrocks.com – where you can preview all of the training resources that I offer. If you want to learn more about formulas and function, I offer a great resource: “The 50 Best Tips for Excel 2007.” Regardless of the version of Excel that you are currently using, you will pick up many great tips to improve your Excel skills!

Watch Tutorial in High Definition

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

View My Tutorial Now on YouTube

 

 

Link to this post!
Filed Under: Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
July 30, 2012 6 Comments

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

Pivot Table for Age Brackets

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

 

How to Group People by Age Brackets with an Excel Pivot Table [ 8:12 ] Play Now | Play in Popup | Download (3400)
Link to this post!
Filed Under: Conditional Formatting, Excel Date and Time Functions, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,
July 22, 2012 1 Comment

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

Pivot Table Summary Report By Hour

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

 

How to Group an Excel Pivot Table Field to Group by Hours [ 6:32 ] Play Now | Play in Popup | Download (4608)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, Pivot Tables, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , , ,
« Previous Page
Next Page »