Instructor for this course
more

Microsoft Excel is considered one of the most useful tools in the workplace. However, if Excel is used incorrectly, it can seriously drag down productivity. This course guides you through shortcuts and functions to enable you to boost your productivity and shave valuable time from repetitive tasks. You'll learn:

Selected Shortcuts

Keep your hands on your keyboard to improve your speed. This online Excel course explores 5 shortcuts that assist when writing formulas and functions. The Excel lesson includes F9, fill down and right, Ctrl+Home, row shortcuts and column shortcuts. It also follows up on a discussion from Volume 1 Chapter 6, and expands the utility of F2.

Conditional Summing Basics

Have you explored the conditional summing functions in Excel? The SUMIFS function is absolutely a game changer.  This online Excel course introduces the multiple condition sum function SUMIFS. The Excel lesson works through the syntax of the function, and provides several practical examples so you can work hands-on.

Remove Duplicates

This online Excel course explores how to remove duplicate values from lists and tables. This Excel lesson demonstrates how to use the remove duplicates feature to generate a unique list of report labels.

Lookup Basics with VLOOKUP

Are you comfortable performing lookups in Excel? They are the keys that unlock significant efficiency in recurring-use workbooks. This online Excel course covers the cornerstone of all Excel lookup functions, VLOOKUP. This Excel lesson covers the function syntax, and provides numerous exercises so you can work hands-on to develop your skill.

Improve VLOOKUP with MATCH

This online Excel course demonstrates how to address a serious limitation of the VLOOKUP function. The VLOOKUP function will break when a user inserts a new column between the lookup and return columns. This Excel lesson explores the MATCH function, and illustrates how to use it to bypass this VLOOKUP limitation. We work through the syntax of the function, and provide several practical examples so you can work hands-on.

Improve VLOOKUP with TEXT and VALUE

This online Excel course addresses a serious limitation of the VLOOKUP function, the fact that equivalent values stored as different data types won't match. That is, the number 100 won't match 100 in the lookup range if stored as a text string. This lesson demonstrates how to bypass this limitation by using the TEXT and VALUE functions. We work through the syntax of the function, and provide several practical examples so you can work hands-on.

Moving Beyond VLOOKUP with INDEX

This online Excel course addresses a major limitation of the VLOOKUP function, the inability to go left. That is, the VLOOKUP function is not designed to return a value that lies to the left of the lookup column. This Excel lesson demonstrates how to bypass this limitation by using the INDEX function. We work through the syntax of the function, and provide several practical examples so you can work hands-on.

Course Series

This course is included in the following series:

5 CoursesJeff Lenning's Excellence in Excel Training

  1. Excel Shortcuts Training: 5 Top Productivity Boosting Shortcuts
  2. Excel Training: Skills for Better Workbook Design
  3. Excel Training: Boosting Proficiency with Selected Shortcuts (PC version)
  4. Excel Training: Mastering Fundamental Functions - IFERROR, IF, List Comparisons and More
  5. Excel Training: Tips for Improving Data Validation, Error Checking, Reporting and Other Performance Obstructions

Learning Objectives

  • Increase your Excel speed with selected shortcuts
  • Remove duplicates from lists and tables
  • Look up values in a table using VLOOKUP
  • Utilize multiple lookup functions such as VLOOKUP, MATCH, and INDEX
Last updated/reviewed: March 13, 2018

Included In Certifications

This course is included in the following Expert Certifications:

9 CoursesExcel for Professional Applications Certification

  1. Excel Shortcuts Training: 5 Top Productivity Boosting Shortcuts
  2. Excel Training: Skills for Better Workbook Design
  3. Excel Training: Boosting Proficiency with Selected Shortcuts (PC version)
  4. Excel Training: Mastering Fundamental Functions - IFERROR, IF, List Comparisons and More
  5. Excel Training: Tips for Improving Data Validation, Error Checking, Reporting and Other Performance Obstructions
  6. PivotTable Essentials
  7. How to Use PivotTables instead of Formula-Based Reports
  8. PivotTable Conclusion and External Data Introduction
  9. Using PivotTables and PivotCharts to Prepare External Data

110 Reviews (274 ratings)Reviews

5
Anonymous Author
Another excellent, informative course from this instructor. I was already familiar with Vlookup, but this course introduced me to quite a few workarounds that I was not aware of. The keyboard shortcuts were also useful. There are plenty of opportunities to practice and learn through experience in the workbooks, which helped me retain the information much more than simply listening to the lecture. I highly recommend the course to anyone that wants to improve their Excel skills.
5
Member's Profile
Once again, Jeff Lenning is clear in presenting sometimes difficult ideas. My only suggestion is that he glides over shortcuts that he has already taught rather than saying every step of what he is doing. For example, I remember double clicking the bottom right to fill down and F9 for seeing the value, but I do not remember the F key to use when he quickly scrolls through locking down cells or which key strokes to use when filling in the whole column for cell references.
5
Member's Profile
Jeff knows his stuff and explains it really well. It's fast paced.I had to go in and out of it and it took a while. It's easier with an extension monitor, not a laptop. Give yourself time to figure out the differences on a mac vs. a PC. I like the worksheets as we go through and I plan to revisit and finish up.
5
Member's Profile
Even though I considered myself proficient in lookups, I learned a ton about how to optimize my workbooks with lookup functions, particularly using the Index/Match combo. I am very glad I took the time to review this content - as I actually ended up learning a lot of new material!
5
Member's Profile
I liked the pacing of the course, it starts simple and builds to the most complex. By the time you get to the complex you feel ready for it. I had not taken an earlier course so I was lost on a few of the shortcuts that were heavily referenced, but that was a minor problem.
4
Anonymous Author
I'm not a fan of VLOOKUP, because if you state the column number and a column is added or deleted, you have to find every instance where you've referenced the table using VLOOKUP. Match/Index never has this problem, and you don't have to count how many columns there are.
5
Anonymous Author
I like that this course solved for very specific issues that are encountered by many Excel users. The course flowed very well and the worksheets provided the hands-on experience required to understand and retain the information presented.
4
Member's Profile
Great course, I had to pause frequently to make sure I understood the topic. He really flew through the formulas and it was difficult to keep up at times. Not a big problem but definitely changes the timing of the course for me
5
Member's Profile
Excellent course! Very much to learn in Excel which can only be done through repetition. You will need to keep looking at all these tricks and shortcuts to remember them and know when to use them. Again...excellent course.
4
Member's Profile
Course was excellent but notes need to be more comprehensive to ensure material is retained and can be referenced down the road. The video would be a helpful tool to keep to refer to or a deck that represents the video.
5
Member's Profile
I really appreciated the detailed explanations and alternate ways to use each of these more advanced Excel functions in spreadsheets, and the presenters application of each function to common business reports.
5
Member's Profile
A lot of material - might have obtained a better understanding of the topics had they been broken down into smaller presentations. 1.5 hours is insufficient time for this course. Jeff was stellar as usual
5
Member's Profile
I really liked the workarounds for the vlookup formula and learning about the Index formula. I will definitely start utilizing these methods in my spreadsheets. The worksheets were very helpful.
4
Anonymous Author
This course is very useful and easy to understand. It clearly explains the importance as well as the functions of some shortcuts. It also give examples and exercises to explain further.
4
Member's Profile
This was a great course. I likes that I could stop it at any time and review my material and that I could go back if I was unsure. The instructor took his time and explained everything.
5
Member's Profile
Mr. Lenning is a good instructor. His jokes are a little corny, but that's OK. I have already used some of his methods I learned in previous courses to improve my efficiency at work.
5
Member's Profile
Lots of examples were provided, and I appreciated the extra credit exercises which were useful in practicing the concepts from this module along with others learned in previous models.
5
Member's Profile
A lot of great information in this course. As other reviewers have commented, it is fairly fast-paced. I had to stop the video lesson, try the formula and then continue on many times.
5
Anonymous Author
Awesome. This course covered a lot and was informative with relevant examples. Mr. Lenning was very clear and thorough. There was a lot of very useful information. Very good course!
5
Member's Profile
The format for these lessons is perfect for the intermediate user. The demonstrations of complex strings are demonstrated in steps, and "unpacks" each part. Great lessons!
5
Member's Profile
This course is very helpful for Excel users. The lessons learned make calculations of complex formulas easy, enhance basic reporting tool and increase productivity.
5
Anonymous Author
This instructor always does a really nice job within the Excel subject area he's presenting. His style and presentation works with all learning types.
5
Member's Profile
Very good course. I'm a fairly experienced Excel user, and I definitely learned some very helpful tools. The materials will serve as good references.
4
Member's Profile
A lot of information in this course. I sometimes felt the instructor was going too fast when explaining formulas. Overall an excellent course.
5
Member's Profile
Step by step escalation of of formula logic, I shunned use of index and match but instructor made it so easy and tempting, I would love to use again
5
Anonymous Author
Best excel class that I have taken so far! I actually learned something new and can actually used some of the examples in my own workbook.
5
Anonymous Author
A great review of VLOOKUP and intro to power of INDEX with which I was not familiar. Love Jeff's presentation and hands on teaching method.
4
Anonymous Author
I have been a long time user of VLOOKUPS. However using them with Match, Index, Text and Value is new to me. Thanks for the great tips!
5
Anonymous Author
Thank you!! Really useful info. I have not used Match/Index before but now find a lot of ways to use them in my spreadsheet.
5
Anonymous Author
I thought this was a very helpful course. I will use all of the tips that were taught in the accounting workpapers I prepare.
5
Anonymous Author
Another very helpful and informative course by the presenter. A great precursor to have an in depth knowledge on V- lookups.
5
Member's Profile
I very much enjoy Jeff Lenning's teaching approach, I hope you offer more of his classes that are not Premium! Thank you!
5
Member's Profile
I learned many new shortcuts during this course and most importantly i learned the concept of Index and Match properly.
5
Member's Profile
I learned a lot in a relatively short period of time due to the exercises provided as part of the workshop. Thanks.
5
Member's Profile
i'm familiar with vlookups and index/match, but the text/value formualas are entierly new to me. very helpful! thanks
5
Member's Profile
Uses good examples and breaks down the steps so that you can then apply it with the excel problems provided.
4
Member's Profile
much more information than in previous and relied on previous courses so they need to be taken in order.
5
Member's Profile
Solid insights to improve command of key excel functions that can be readily applied to real problems.
3
Anonymous Author
Liked the additional short-cuts and different ways upon which to improve upon the look-up function.
4
Anonymous Author
It moves very quickly. A lot of information. Hard to take notes and would like a summary sheet.
4
Anonymous Author
Good pointers to improve and analyze spreadsheets if your not familiar with these functions
5
Member's Profile
The Author kept the course interesting and easy to follow. I like the practice workbooks.
5
Anonymous Author
Some great tips to improve my efficiency in my spreadsheets and to remove my manual steps.
5
Member's Profile
It's great to learn improve Vlookup with Match and improve Vlookup with Value and Text.
4
Anonymous Author
Helpful demo of index/match, which seems to confuse many looking to go beyond VLOOKUP
4
Member's Profile
This is a great course for someone who is new to using excel and formatting data.
5
Member's Profile
I liked the course and the worksheets to work along with the video. Very helpful.
5
Member's Profile
I like that there are worksheets that allow you to follow along with the course.
5
Member's Profile
Intense training on V-lookups, Index and Match. I can do anything in Excel now!
5
Member's Profile
Quick and easy lessons that will help you increase your efficiency with Excel.
5
Anonymous Author
The instruction is very easy to follow. I have mastered the subject matters!
5
Member's Profile
Excellent Excel presentation from Jeff!! He makes Excel looks easy-pessy !!!
5
Member's Profile
Good course and some surprising applications that can come from the material
4
Member's Profile
Helpful to know how to use vlookup when your data includes text and valules
5
Anonymous Author
The examples helped to confirm understanding of the material presented.
4
Member's Profile
I like how Jeff breaks down the pros and cons of different functions.
3
Member's Profile
I would love to know which one I got wrong so I can review the area
4
Anonymous Author
I liked the examples to learn each section, they were very helpful.
4
Anonymous Author
Course presented in a good format covering all stated objectives.
4
Member's Profile
sometimes it goes a little too fast but I like I can back it up
5
Anonymous Author
very useful functions to use in excel, and very well presented.
5
Anonymous Author
Good examples and interesting solutions to VLOOKUP limitations!
5
Member's Profile
Class goes at a fair clip, and much is learned in short order.
5
Anonymous Author
Great explanation of function compatibility and substitution.
3
Member's Profile
There was too much material to cover in a short period of time
5
Member's Profile
Great intro to a few key functions for saving time in excel.
5
Member's Profile
This takes more than 1.5 hours and covers a lot of material.
5
Member's Profile
Thanks for the quick overview of the referenced materials.
4
Member's Profile
Great course that I hope will save me time down the road.
5
Anonymous Author
Great, relevant techniques with working examples.
5
Member's Profile
I really like this instructor, he does a great job.
4
Anonymous Author
Good review of functions I haven't used in awhile.
4
Member's Profile
Lots of info. May need to go over more than once.
5
Member's Profile
Excellent Course! Love the VLOOKUP function...!
5
Member's Profile
Awesome tips to quickly overcome the limitations
5
Member's Profile
Great simple but on point examples and practice
5
Member's Profile
Lots of good tips on using built in formulas.
5
Member's Profile
A good review of how to use lookup functions.
4
Anonymous Author
Great examples of how to nest functions!
5
Anonymous Author
I liked the challenge in the worksheets
4
Member's Profile
The Index Match usage was good to see.
5
Member's Profile
Very useful in practical applications
5
Member's Profile
Some excellent tips in this course!
4
Member's Profile
index/match is the only way to go!
5
Member's Profile
Great instructor excellent class!
4
Member's Profile
Very useful. A little fast paced.
3
Member's Profile
I thought it was pretty good.
5
Member's Profile
Will make the job very effective
5
Member's Profile
Great course! Very informative.
5
Member's Profile
love the F2 and F9 buttons now.
4
Member's Profile
Very useful tips and tricks.
5
Member's Profile
The tricks in Match and Index
4
Member's Profile
the exercises are awesome
5
Member's Profile
Good class, very useful.
5
Anonymous Author
Very helpful guidance.
5
Member's Profile
Worthwhile for sure!
5
Member's Profile
great information
4
Member's Profile
Well presented.
5
Member's Profile
Great course!
4
Anonymous Author
clear concepts
4
Anonymous Author
It was helpful
5
Member's Profile
Very helpful!!
5
Member's Profile
great course!
5
Anonymous Author
Very helpful
5
Member's Profile
Great course
5
Anonymous Author
Excellent.
5
Anonymous Author
Excellent!
4
Anonymous Author
Loved it!
4
Anonymous Author
Good
3
Member's Profile
no

Prerequisites

Course Complexity: Foundational

No Advanced Preparation or Prerequisites are needed for this course. However, it is recommended to take the other courses in the series prior to completing this one.

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
Training
  3:53Overview
  7:12Selected Shortcuts
  4:31Conditional Summing Basics
  3:38Remove Duplicates
  11:21Lookup Basics with VLOOKUP (Part 1)
  9:41Lookup Basics with VLOOKUP (Part 2)
  9:01Improve VLOOKUP with MATCH
  7:10Improve VLOOKUP with TEXT and VALUE
  8:30Moving Beyond VLOOKUP with INDEX
CONTINUOUS PLAY
  1:01:07Excel Training: Boosting Proficiency with Selected Shortcuts (PC version)
SUPPORTING MATERIALS
  ZIP FILEWorksheets
  PDFBoosting Proficiency with Selected Shortcuts Glossary/Index
REVIEW & TEST
  quizReview Questions
 examFinal Exam