Excel Training | The Company 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

Share and Enjoy:

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

Share and Enjoy:

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

Share and Enjoy:

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

Share and Enjoy:

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 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 July 16, 2012 1 Comment

How to Create a Series of 30 Minute Time Intervals in Excel

One of my readers tracks data points at 30 minute (half-hour) periods throughout the day. He asked me for some help in streamlining this process.

AutoFill the Step-value for a Series

You can use AutoFill in Excel to create this series. Here are the steps to take:

In two adjacent cells, enter the beginning time value and the time that is 30 minutes later than the beginning time value.
Select BOTH cells and AutoFill the cells in the range that you want to “populate.”

Calculate Decimal Number that Represents 30 Minutes

You can calculate the numeric value for a half-hour (30 minute) period:

Use a Formula to subtract the Start Time from the End Time.
Format the result as a Number with 6 Decimal Places.

In this case, the result is: 0.020833 which you can use as a Constant Value in a Formula.

Keyboard Shortcut to Enter the Current Time

Ctrl + Shift + : (Colon) will “time stamp” a cell.

How Excel Stores Time

While you can Format Time in a variety of ways, Excel must be able to recognize and store Time entries as Numeric entries! This means that correctly entered times align to the right-side of the cell. Time values are stored as a “decimal part of one” day. For example, 12:00 PM (Noon) is stored as 0.50 – 50% of the day has elapsed

Shop at My Secure Online Website

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to view all of the resources that I offer you to improve your Excel skills.

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 Create a Series of 30 Minute Intervals in Excel [ 6:22 ] Play Now | Play in Popup | Download (2689)

Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Tips and Time Savers Tagged: AutoFill Options, Calculate Lapsed Time in Excel, Calculate Time in Excel, Excel Video Training, Formatting Cells with Time Calculations, How Excel stores time values, Keyboard Shortcuts in Excel, Tips and Time Savers, Video lessons for Excel

Danny July 8, 2012 1 Comment

How to Calculate the Calendar Year Quarter for a Date in Excel

Formulas to Calculate a Calendar Year Quarter

After watching my previous video tutorial, “How to Place Dates in a Fiscal Year Quarter,” several viewers requested that I demonstrate how to calculate the calendar year quarter for a date.

This requires “nesting” the MONTH() /3 Function inside either the ROUNDUP() or the CEILING() Function. I break these formulas down step-by-step in this video so that you can see how they work.

Formatting Numbers to Include Text

As a bonus, I demonstrate how to format the formula result cells to include the text “Quarter.” This is really simple to create as a custom format.

Download this Video Podcast from iTunes

You can watch and download this – and many of my – video tutorials for free at iTunes. Here is the link to my iTunes video podcast, “Danny Rocks Tips and Timesavers.”

Watch Video in High Definition

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

Visit my Secure Online Shopping Website

I invite you to visit my online shopping site – http://shop.thecompanyrocks.com – to preview and purchase my training resources. I guarantee your satisfaction 100%. I will refund your purchase if, for any reason, you are not satisfied with my products!

Share and Enjoy:

How to Calculate the Calendar Year Quarter for a Date [ 5:19 ] Play Now | Play in Popup | Download (1131)

Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Functions, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: CEILING Function in Excel, Custom Formats for Numbers in Excel, Excel Video Training, MONTH Function, Nested Functions, Nesting Formulas and Functions, ROUNDUP Function, Video lessons for Excel

Danny July 1, 2012 1 Comment

How to Place Dates in a Fiscal Year Quarter

Many organizations operate in a Fiscal Year – for example, April 1 through March 31 of the next calendar year. I get many requests from viewers for help with placing calendar dates into the proper Quarter for their Fiscal Year.

While researching a question from one viewer, I cam across a very interesting formula that nests the “undocumented” DATEDIF() Function inside the CEILING() Function. I found this formula in the book, “Formulas and Functions with MS Excel 2003,” written by Paul McFedries. It is a very efficient formula.

What is Covered on the Video

Watch this video tutorial as I break down each step. As a Best Practice, I recommend that you break down a complex formula by working from the “inside out.” I start with the “nested” DATEDIF() function and then demonstrate the results of each formula detail.

Learn More About the “Undocumented” DATEDIF() Function

For various reasons, Microsoft does not offer any help or documentation for the very useful DATEDIF() Function. In fact, it is not even listed as a Function! However, it is very useful and often found in VBA (Visual Basic for Applications) coding. Here, are links to two videos that I created to help you understand how to use DATEDIF():

Learn How to Calculate a Calendar Year Quarter

In response to viewer requests, I created a companion video tutorial -” How to Calculate a Calendar Year Quarter in Excel.”

Resources Available at The Company Rocks

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview the many resources that I offer for helping you to improve your software skills.

Watch Video Tutorial in High Definition

If you would like to watch my video tutorial for “Finding the Fiscal Year Quarter for any Date,” click on this link. I have posted it on my YouTube Channel – DannyRocksExcels

Watch this Video Now

Share and Enjoy:

Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Formulas, Excel Functions, Excel Training Tagged: CEILING Function in Excel, DATEDIF Function in Excel, Excel Video Training, Fiscal Year Quarter, Nested Functions, Nesting Formulas and Formulas, Place Dates in Fiscal Year Quarter, Video lessons for Excel

Danny June 27, 2012 1 Comment

How to Add Summary Information for Excel Workbook Properties

Almost everyone has experienced the frustration of being unable to locate an Excel Workbook on their computer. You can’t remember the name or the location of the Workbook. You waste valuable time searching in vain.

Tags for Document Search

One way to minimize this frustration is to add Excel Workbook Properties that Summarize the content and purpose of the document. By adding Tags, Keywords, Client Names or Project Titles in the Properties Summary you make it easier for your computer to Index and Find your documents.

Fortunately, beginning with Microsoft Office 2007, it is a lot easier to add these properties to an Excel Workbook. In this video tutorial, I show you how to do this.

Displaying Recent Documents

Did you know that you can display up to 50 Recent Documents? Even better, you can “Pin” important documents so that they remain on the list. This is a valuable tool when you need to access important files, for example, once a month! I show you how to do this on the video.

Shop for Additional Resources

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – where you can purchase all of the training materials that I have developed.

Watch Video in High Definition

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

Watch this Video Now

Share and Enjoy:

Filed Under: Excel Training, New in Excel 2007, New in Excel 2010, Workbooks and Worksheets in Excel Tagged: Add new tag, Display Recent Excel Workbooks, Excel Video Training, Index and Search for Excel Workbooks, Pin Documents to Recent Workbook List, Properties Summary for Excel Workbook, Search for Workbook Tags and Keywords, Tag Keywords in Excel Workbook, Tips and Time Savers, Video lessons for Excel

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Tips Covered in this Video

Watch this Video in High Definition

Learn About My Training Resources

Watch Tutorial Now

Excel’s Analysis ToolPak “Add-in”

Advantages of Using Excel’s Histogram Tool

What is a Pareto Chart?

Links to All Videos in Frequency Report Series

Improve your Excel Skills – My Video Tutorials

Follow The Company Rocks on Facebook

Top 10 Most Viewed Videos – January thru July, 2012

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

The 50 Best Tips, Tricks & Techniques Series

100% Satisfaction Guaranteed!

Previous Tutorial on The Company Rocks Website

How to Use the FREQUENCY() Function in Excel

My Secure Shopping Website

Watch Tutorial in High Definition

View My Tutorial Now on YouTube

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

AutoFill the Step-value for a Series

Calculate Decimal Number that Represents 30 Minutes

Keyboard Shortcut to Enter the Current Time

How Excel Stores Time

Shop at My Secure Online Website

Watch Tutorial in High Definition

Formatting Numbers to Include Text

Download this Video Podcast from iTunes

Watch Video in High Definition

Visit my Secure Online Shopping Website

What is Covered on the Video

Learn More About the “Undocumented” DATEDIF() Function

Learn How to Calculate a Calendar Year Quarter

Resources Available at The Company Rocks

Watch Video Tutorial in High Definition

Watch this Video Now

Displaying Recent Documents

Shop for Additional Resources

Watch Video in High Definition

Watch this Video 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

Tags