Archives for August 2012

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.

Danny August 23, 2012 1 Comment

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

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)

Filed Under: Excel Training, Keyboard Shortcuts, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: Compare lists in Excel, Excel Keyboard Shortcuts, Excel Video Training, Filter by Color in Excel, Go To Special dialog box, Remove Filters in Excel, Sort by Color in Excel, Video lessons for Excel

Danny August 17, 2012 1 Comment

DannyRocksExcels on YouTube Recognized as a Top 10 Channel for MS Excel Tutorials

See the Complete List – Top 10 YouTube Channels

Click here to see the list of the Top 10 YouTube Channels for Excel Tutorials. I am honored to be included in this list!

Visit My YouTube Channel – DannyRocksExcels

I invite you to visit my DannyRocksExcels Channel on YouTube.

DannyRocksExcels on YouTube

As of today, I have published 250 Video Tutorials on YouTube. Many of my videos can be viewed in High Definition.

My videos have been viewed nearly 1.9 million times and my list of subscribers is close to 4,000.

My Top 10 Videos – The Last 7 Days

Here is a list of the 10 Most Viewed Excel Tutorials at DannyRocksExcels over the last 7 days – dating back from August 17, 2012. I have created Hyperlinks for each video. Just click on the title that you want to watch on YouTube:

Shop at My Secure Online Website

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the Training Resources that I offer you.

Filed Under: Excel Training, Most Viewed Excel Videos, MS Excel Tips, MS Excel Training, YouTube Videos by Danny Rocks

Danny August 13, 2012 3 Comments

How to Combine 2 Excel Workbooks Using VLOOKUP Function

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

Filed Under: Excel Functions, Excel Training, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: Exact Match for VLOOKUP, Excel Keyboard Shortcuts, Excel Video Training, Formula Errors, IFERROR Function, Mixed Cell Reference, Move or Copy Sheet, Named Cell in a formula, Named Cells and Ranges, Video lessons for Excel, VLOOKUP Function

Danny August 9, 2012 1 Comment

How to Use Excel’s Histogram Tool and The 80/20 Rule of Analysis

Excel Histogram Charts

This is the Third – and final – episode in my “Creating Frequency Distribution Reports in Excel” series of tutorials. In this episode, I demonstrate a very powerful – and very easy to use – analysis tool – The Histogram Tool.

Excel’s Analysis ToolPak “Add-in”

This tool is included in the Analysis ToolPak which is an “Add-in” program within Excel. Beginning with Excel 2007, the Analysis ToolPak is automatically included in a basic installation of Excel. However, if you are using an earlier version of Excel or if, somehow, this Add-in has been disabled, I show you how to “activate it.”

Advantages of Using Excel’s Histogram Tool

No Formulas to write
Results are Numeric Values – not Formulas
Chart(s) can be added automatically
Multiple Chart Options
Pareto Chart Option

What is a Pareto Chart?

Pareto Analysis is commonly known as “The 80 / 20 Rule.” A brief explanation is: Roughly 80% of your sales come from only 20% of your customers or 20% of your products. I like to say, “You can focus on the Vital Few and not on the Trivial Many,” when you use Pareto Charts. As you will see in my video, when you choose the Pareto Chart option, the Histogram automatically sorts the Frequency Field in descending order and charts this field.

Links to All Videos in Frequency Report Series

Group by Age Bracket in an Excel Pivot Table
- Watch in High Definition on YouTube
Use an Array Function – Frequency – to Generate Frequency Report
- Watch in High Definition on YouTube
Use Excel’s Histogram Tool to Generate Frequency Report and Chart
- Watch in High Definition on YouTube

Improve your Excel Skills – My Video Tutorials

I invite you to visit my secure online shopping website to see the many training resources that I offer. For example, click here to learn about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Follow The Company Rocks on Facebook

You can now follow me on Facebook – www.facebook.com/TheCompanyRocks

Use the Histogram Tool in Excel [ 7:46 ] Play Now | Play in Popup | Download (2696)

Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: Analysis ToolPak for Excel, Bins for Frequency Report, Excel Add-ins, Frequency Report in Excel, Histogram Charts, Histograms in Excel, Pareto Chart in Excel, The 80/20 Rule

Danny August 6, 2012 3 Comments

My Top 10 Most Viewed Excel Tutorials on YouTube Year-to-Date in 2012

DannyRocksExcels on YouTube

Later this month, my YouTube Channel – DannyRocksExcels – will surpass 1.8 million views! Thanks to everyone who has visited, watched, and commented on my Excel video tutorials these past four years!

Learn More Tips, Tricks, and Techniques for MS Office Programs

The 50 Best Tips Series of DVD-ROM’s

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase my video training resources.

The 50 Best Tips, Tricks & Techniques Series

Click on the link to go directly to the information page for each title. You will see a list of the 50 Tips and Ru Times for each title:

100% Satisfaction Guaranteed!

I stand 100% behind the quality of my products. I will refund your purchase price if, for any reason, you are not 100% satisfied – with no questions asked!

Thank you for your support through the years!

Danny Rocks

The Company Rocks

Filed Under: Excel Training, Most Viewed Excel Videos, Online Store for The Company Rocks, Video Training Courses, YouTube Videos by Danny Rocks Tagged: "What-if" Analysis in Excel, Advanced filter an Excel data list, Consolidate Excel data, DannyRocksExcels YouTube Channel, Excel Forms, Excel Pivot Tables, Excel Training Videos on YouTube, Lookup Functions in Excel, Top Rated Excel Videos on YouTube, VLOOKUP Function

Danny August 3, 2012 1 Comment

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

In 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

Create your “Bin Array.” In this example, enter – in ascending order – the ages that you want to find the “frequency of occurrences” between
Select the “Array of Cells” that will contain your FREQUENCY() Function results.
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!”
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.
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

Filed Under: Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: Array Formulas in Excel, Bin Array for Frequency Function, Ctrl + Shift + Enter for Array Formulas, Curly Braces for Array Formulas, Excel Functions, Excel Video Training, FREQUENCY Function in Excel, Video lessons for Excel

Danny Rocks “Training You to Excel”

Follow Danny on Social Media