Excel Functions | The Company Rocks

Danny October 21, 2012 1 Comment

Essential Excel Skill #3: Creating and Using Named Cell Ranges in Excel

Essential Skill #3 - Using Named Cell Ranges

Essential Excel Skill 33 – Using Named Cell Ranges

I believe that there are two main reasons to create and use Named Cell Ranges in Excel”

They make great Navigational Bookmarks – to quickly move to a specific location in your workbook
Using Named Cell Ranges in Excel Formulas make the formula:
- Easier to Write
- Easier to Explain – to others
- Easier to Understand – when you need to edit it or copy it.

9 Essential Excel Skills

My latest Excel Video Training Resource: “9 Essential Excel Skills,” contains 4 hours of focused video training. There are a total of 25 individual video tutorials in the resource.

Here, in this lesson, I demonstrate several of the tips and techniques that I present in Essential Skill # 3. I show you how easy it is to create and use Named Cell Ranges in Excel Formulas and Functions.

Secure Online Shopping at The Company Rocks

I invite you to visit my secure, online shopping website – http://shop.thecompanyrocks.com – to explore all of the training resources that I offer you.

Watch My Tutorial in High Definition

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

Share and Enjoy:

How to Create and Use Nammed Cell Ranges in Excel [ 8:13 ] Play Now | Play in Popup | Download (4615)

Filed Under: Essential Excel Skills, Excel Formulas, Excel Functions, Excel Training, Names for Cells and Ranges Tagged: Create Names from Selection in Excel, Create Names in Excel, Define Names in Excel, Excel Functions, Excel Keyboard Shortcuts, Excel Video Training, Named Cell in a formula, Named Cells and Ranges, Paste Names Dialog Box, Paste Names in Formulas, 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 4, 2012 Leave a Comment

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

Essential Functions – VLOOKUP()
Text Functions – LEN() and TRIM()
Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

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

Share and Enjoy:

How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)

Filed Under: Excel Basics, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, Text Functions Tagged: #N/A Error Message in Excel, Exact Match for VLOOKUP, Fixed Width Fields in Excel, Formula Errors, Len() Function, Paste Special Values, Trim() Function, VLOOKUP Function

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

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

Share and Enjoy:

Filed Under: Excel Basics, Excel Functions, Excel Training, Lookup Functions in Excel, MS Excel 2007 DVD, Names for Cells and Ranges, Training Tagged: Absolute cell reference, Approximate Match for VLOOKUP, Exact Match for VLOOKUP, Excel Functions, Excel Video Training, HLOOKUP Function in Excel, Lookup Functions in Excel, MS Excel Training, Named Cells and Ranges, Video lessons for Excel, VLOOKUP Function

Danny September 20, 2012 Leave a Comment

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

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

Add this product to your secure shopping cart at my website.
During Checkout, you can choose to either Register or Shop as a Guest
You will need to provide a valid email address in order to receive the actual link to download my Instructional Guide.
Even though you will be downloading this product, I had to set it up as a physical product with “Free Shipping.”
Shortly after you complete the checkout process you will receive an email that contains a hyperlink to the file that you will download.
You will be downloading a “zipped” file. So, once the download is completed, be sure to “unzip” this file!
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 Video Tutorial

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

Entering Data Efficiently in Excel 2010
Selecting Cell Ranges Efficiently in Excel 2010
Creating and Using Named Cell Ranges in Excel 2010
Applying Styles and Formatting to Excel 2010 Worksheets
Working with Structured Data Sets in Excel 2010
Working with Excel 2010 Formulas and Functions
Using Paste Special Options in Excel 2010
Using Data Validation in Excel 2010
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!

Share and Enjoy:

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: "What-if" Analysis in Excel, Absolute cell reference, AutoFill Options, COUNTIF Function, Create Subtotals in Excel, Data Validation, Excel Functions, Excel Video Training, Go To Special dialog box, IF() Function, Keyboard Shortcuts in Excel, Learn to use AutoFill in Excel, Lookup Functions in Excel, Named Cell in a formula, Paste Special, SUMIF Function, Video lessons for Excel, VLOOKUP Function

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 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 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 Rocks “Training You to Excel”

Follow Danny on Social Media

9 Essential Excel Skills

Secure Online Shopping at The Company Rocks

Watch My Tutorial in High Definition

“Nine Essential Skills for Excel 2010”

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

Learn More About My Video Training Resources

Use the TRIM() Function to Remove Extra Spaces

The #N/A Error Message for VLOOKUP()

Paste Special Values

Essential Excel Skills

Watch Video in High Definition

Top 10 YouTube Excel Videos

Thanks to my 4,500 Subscriber on YouTube!

Learn More About my Extended Length Excel Video Training Resources

Lookup Functions in Excel 2007

Learn More About My Video Training Resources

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

Watch this Lesson in High Definition

Watch Tutorial Now

Link to The Company Rocks Free Resources Page

Instructions for Downloading the Free Instructional Guide

Read the Instructional Guide and then Purchase my Video!

List of the “9 Essential Excel 2010 Skills”

Excel 2010 Practice Files Included

Tips Covered in this Video

Watch this Video in High Definition

Learn About My Training Resources

Watch Tutorial Now

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

Formatting Numbers to Include Text

Download this Video Podcast from iTunes

Watch Video in High Definition

Visit my Secure Online Shopping Website

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