Home Archives for Lookup Functions in Excel
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 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 6, 2012 3 Comments

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

DannyRocksExcels on YouTube

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!

Top 10 Most Viewed Videos – January thru July, 2012

According to my YouTube statistics, these are the 10 most viewed Excel video tutorials on my channel. Click on the Links to watch any video on YouTube :

  1. How to Merge Multiple Excel Workbooks to a Master Budget – views year-to-date 39,338
  2. How to Use Advanced Filters in Excel – views year-to-date 29,700
  3. How to Use an Excel Data Table for “What-if” Analysis – views year-to-date 27,301
  4. How to Add a Check Box Control to an Excel Form – views year-to-date 27,171
  5. Consolidating Data from Multiple Excel Worksheets by Position – views year-to-date 26,517
  6. How to Create an Interactive Pivot Chart – views year-to-date 24,858
  7. Compare Two Excel Lists to Spot the Differences – views year-to-date 23,826
  8. Create Interactive Excel Forms by Including Option Boxes – views year-to-date 20,665
  9. How to Use the VLOOKUP Function in Excel – views year-to-date 20,574
  10. Import Excel Data Into MS Access – views year-to-date 18,288

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

The 50 Best Tips DVD-ROMs

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

Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, Online Store for The Company Rocks, Video Training Courses, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
December 2, 2011 Leave a Comment

How to Return Either an Approximate or Exact Match for VLOOKUP Function

Matches with VLOOKUP

Matches with VLOOKUP

One of my viewers asked me to explain – with examples – how and when to choose either an Approximate or an Exact Match using the VLOOKUP Function in Excel. .

Approximate Match for VLOOKUP

The Approximate Match is the Default setting for Vlookup. This means that you can either omit the 4th, optional, argument or use the word TRUE. However, as you will see in this video tutorial, there are other considerations to consider in order to get the correct result that you are looking for.

Exact Match for VLOOKUP

In the example that I use on the Video Tutorial, I am setting up an Employee Payroll worksheet. I definitely want to have an Exact Match for each employee to ensure that they are getting the correct amount of compensation for the hours that they worked during this period. Watch the tutorial to see how I set this up.

Watch Tutorial in High Definition

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

Get More Excel Tips

I invite you to visit my online shopping website – http://shop.thecompanyrocks.com – to preview the many resources that I offer. Here is the link to go directly to the information page for my DVD-ROM, “The 50 Best TIps for Excel 2007.”

 

Link to this post!
Filed Under: Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
November 5, 2011 Leave a Comment

How to Use the Offset Function Inside a VLookup in Excel

Vlookup in Excel

VLOOKUP in Excel

Each month, I get 5 to 6 emails of phone calls from viewers who who are having trouble using the VLOOKUP Function in Excel. In the majority of cases, the problem is that their Data Table is setup so that the “Key” field that they need to search in is NOT the Left-most or first field in the table. It is not always practical – nor desirable – to use “Cut and Paste” to rearrange the fields in the data set. So, what techniques can you use to fix this?

Use the OFFSET Function Inside a VLOOKUP Function

In this scenario, our IT Department sends us a daily report of the products that we sell including fields for the current cost and quantity on-hand. We use this report to generate additional reports and filters. This report gets saved automatically as an Excel 2010 Table with the name “Inventory.” The problem is, whenusing a VLOOKUP, that the first – or Left-most – field is NOT the “Key” field that we want VLOOKUP to use to search for a Match in order to return the current price or inventory for individual product.

The OFFSET() Function in Excel, makes it easy to reference a “starting field” that, in this case, is one column to the Right. This is perfect for our situation. We can continue to use our “named range” with the VLOOKUP!

Watch Video in High Definition

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

Visit My New Secure Online Shopping Website

I invite you to visit my new online shopping website at: http://shop.thecompanyrocks.com I have created new, extended length video tutorials that you can either purchase to download immediately or order them on a DVD-ROM. In addition, I have published versions of my 90 minute Pivot Tables Video Tutorials for Excel 2003, Excel 2007 and Excel 2010. So, I have the tutorials that you want for the Excel version that you are currently using!

Click to Play Excel Video Tutorial

Link to this post!
Filed Under: Data Validation, Excel Data Tables and Lists, Excel Training, Lookup Functions in Excel, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
September 19, 2011 2 Comments

How to Create a Summary Report from an Excel Table

Functions for Executive Summary

Functions for Executive Summary

I created this Excel Video Tutorial in response to a viewer who, in her company, is not permitted to use a Pivot Table to create a report. Unfortunately, this happens more often than you might think. Nevertheless, I promised to share my tips and advice. I think that many of this tips will help Excel users at all levels.

Tips Covered in this Video Tutorial

  • Use Advanced Filter to Extract a list of unique customer names from a range with @ 4,300 records.
  • Later in the lesson, I show you how to extract this list to a different worksheet – a tip that will save you time when preparing your reports!
  • How and why to convert a normal range of data into a TABLE in Excel 2007 / 2010 or into a LIST in Excel 2003.
  • How to create “Named Ranges” for your key data cells and to use them in your Formulas and Functions.
  • How to use the SUMIF, AVERAGEIF and COUNTIF Functions in your summary report.

Keyboard Shortcuts Used in this Tutorial

  • Ctrl + T to convert to a TABLE in Excel 2007 / Excel 2010
  • Ctrl + L to convert to a LIST in EXCEL 2003
  • Ctrl + A to open up the Function Arguments Dialog Box
  • Ctrl + Shift + F3 to open the Create Names Dialog Box
  • F3 to open the Paste Names Dialog Box

I decided that I will only make this video available on YouTube – I think that you will enjoy the clarity that you get from the High-Definition / Full Screen Mode.

Watch Tutorial in High Definition on YouTube

Follow this link to view this Excel Tutorial in High Definition on my YouTubeChannel – DannyRocksExcels

Visit my new secure online shopping website

I invite you to visit http://shop.thecompanyrocks.com – my new online shopping website.

Watch Video Now

Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Data Tables and Lists, Excel Functions, Excel Training, Keyboard Shortcuts, Names for Cells and Ranges, YouTube Videos by Danny Rocks Tagged: , , , , , , , , , ,
January 30, 2011 3 Comments

How to Use Conditional Formatting to Compare Two Excel Lists

Clients and viewers frequently ask me to help them to compare two differeny lists in Excel. They want to find – or highlight – the values that are different in each list. For example, which customers appear in the 1st list but NOT in the 2nd list. So, I created this video lesson to demonstrate how to do this with Conditional Formatting.

In an earlier Excel Video Lesson, I demonstrated how to compare two Excel lists using either the MATCH() Function or the VLOOKUP() Function.

Conditional Formatting Rule

Conditional Formatting Rule

Use Conditional Formatting

I demonstrate how to use Conditional Formatting to Highlight the Cell Values that are different when you compare two Excel Lists. I will use a “New Rule with a Formula” that must return the answer TRUE, to trigger the special formatting.

In Conditional Formatting, you first establish a “condition” that can be answered as either TRUE or FALSE. Then, for those cells where the answer to the condition is TRUE. the special “cell formatting” that you chose will apply.

In this lesson we will be using this Formula: =COUNTIF(List 2, 1st cell in List 1) = 0.

Steps to follow:

  1. Select the cells that you want the Conditional Formatting to apply to- in our example List 1.
  2. On the Home Tab of the Ribbon, click the Conditional Formatting arrow and select New Rule.
  3. Select New Rule – “Use a Formula to determine which cells to format.”
  4. Enter the formula – e.g. =COUNTIF(Range, Criteria) = 0 where the “Range” is the list of values in List 2 (Absolute Reference) and the “Criteria” is the 1st cell reference in List 1 (Relative Reference).
  5. Choose the Format for the cells when the condition is met – the result is TRUE. In this example, I choose to “FILL” the cells with a Blue background color.

Want to watch this video in High Definition, Full-Screen Mode? Click here to go to my YouTube Channel, DannyRocksExcels

Learn to “Master Excel in Minutes – Not Months!”

How to Use Conditional Formatting to Compare Two Excel Lists [ 8:33 ] Play Now | Play in Popup | Download (1495)
Link to this post!
Filed Under: Conditional Formatting, COUNTIF and SUMIF Functions, Excel Formulas, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
January 1, 2011 Leave a Comment

The 10 Most Watched Videos at The Company Rocks During 2010

Reminder Note

Reminder Note

I like to look back to review the results of the past year. Which posts, which videos were the most popular, which videos were downloaded most often? As I look at this list of 10 videos, the clear winner – by category – is merging data from multiple worksheets. Three of the top 10 videos that were watch the most fall into this category!

Here is a listing of the 10 Video Lessons that were watched most frequently on my website – www.thecompanyrocks.com – during 2010:

1- Summarize Multiple Excel Worksheets – Consolidate by Position

2- Build an Accounts Receivable Aging Report

3- Use Pivot Tables to Summarize by Year, Quarter and Month

4- Simplify Data Lookups in Excel

5- Perform Break-Even Analysis with Excel’s Goal Seek Tool

6- How to Display Numbers During a PowerPoint Presentation

7- Make Excel Data Come Alive with Visualization Tools

8-  Excel 2003 Basics – Data Entry

9- How to Merge Multiple Excel Workbooks into a Master Budget

10- Consolidate Data from Multiple Excel Worksheets – Part 2 – by Category

If you enjoy these videos, you will enjoy my DVD-ROMs, “The 50 Best Tips for Excel 2007” and “The 50 Best Tips for PowerPoint 2007.” You can use my secure shopping cart to purchase them now.

I have reduced the purchase price of my Individual DVD-ROMs to $29.97 USD. You do not need a coupon to receive this special price. Simply, go to my online store – http://shop.thecompanyrocks.com

You can also watch my videos on iTunes. Click here to go to my Video Podcast, “Danny Rocks Tips and Timesavers” at the iTunes Store.

Learn how to “Master Excel in Minutes – Not Months!”

My most recent videos can be viewed in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Consolidate Excel Data, Excel Basics, Excel Financial Tools, Excel Training, Goal Seek in Excel, Lookup Functions in Excel, Most Viewed Excel Videos, Pivot Tables, Powerpoint Techniques Tagged: , , , , , , , ,
November 27, 2010 2 Comments

How to Use the Indirect Function to Create Excel Dashboards

You can use the Indirect Function to create an Excel Dashboard. A Dashboard gives you an overview of your data – in this case, an interactive view of the information hidden in our data set. I will create Drop-Down lists using Data Validation in the Dashboard. The Indirect Function takes a little bit of time to understand. Here are some key points:

  • The Indirect Function works best with Named Cell References
  • The syntax is =INDIRECT(reference text). In our example the “reference text” will be the label for a Named Range of cells.
  • You can “nest” the Indirect Function inside another function – e.g. =SUM(INDIRECT())

Later in the lesson, I show you how you can quickly  look up any cell at the “intersection” of a Named Row and a Named Column – e.g. April East. You use the SPACE BAR as the “Intersection Operator” to point to the cell where “April” meets “East.”

50 Best Tips for Excel 2007

Excel Tips

Some of the Keyboard Shortcuts that I use in this lesson are:

  • Ctrl + A to select all of the cells in a Data Set
  • F3 to bring up a list of all the Named Ranges that have been created in the current workbook
  • Ctrl + Shift + F3 to create Named Ranges for the cells in the selection – using as Names, the Labels in the Top Row / Left Column

Here is a link to the 1st lesson in this series on the INDIRECT Function in Excel.

Follow this link to watch this lesson in High Definition, Full Screen Mode on YouTube.

You can learn how to “Master Excel in Minutes – Not Months!”

How to Use the Indirect Function to Create Excel Dashboards [ 7:20 ] Play Now | Play in Popup | Download (1168)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
Next Page »