This course provides practical guidance to help you rapidly master a few fundamental functions of Excel. Upon completing the training, you will be able to use:

The IFERROR Function

This online Excel course explores the idea of error trapping, and demonstrates how to control formula results when an error is encountered. This Excel lesson introduces the IFERROR function, works through the syntax of the function, and provides several practical examples so you can work hands-on.

The IF Function

This online Excel course reviews the IF function. The lesson includes the syntax of the IF function, how to implement it into your workbooks, and explores the benefits.

List Comparisons

This online Excel course demonstrates how to perform list comparisons with Excel. What's a list comparison? Let's say you have two Excel sheets, and you have a question: does anything on this list appear on that list? Or, what appears on this list but not on that list? These are examples of list comparisons. This lessons applies Excel's lookup functions to perform list comparisons. Homework activities and Excel files are provided so you can work hands-on.

The ISERROR Function

This online Excel course explores the ISERROR function, and illustrates how to use it in conjunction with the IF function to control the output of formulas when errors are encountered. This combination of functions enables us to perform tasks that are not possible with the IFERROR function, and expands our ability to trap errors. Sample Excel files are provided in the homework activities so you can work hands-on.

Multicolumn List Comparisions with COUNTIFS

A multicolumn list comparison is a list comparison that uses two or more columns for the matching criteria. Typically, Excel users familiar with the VLOOKUP function will try to combine multiple columns into a single lookup column, often using concatenation, in order to perform the list comparison. However, this step of combining columns is not necessary, and in recurring use workbooks our goal is to eliminate steps. This online Excel class explores the COUNTIFS function. The Excel lesson walks through the function's syntax, illustrates how to use it to perform multicolumn list comparisons, and provides several practical examples so you can work hands-on.

Indentation

How do you create indented report labels? Do you tab over to a new column? Do you pad the report label with two spaces? Both of these common approaches impede our productivity. This online Excel class discusses this minor detail, and demonstrates how the way we indent can have a major impact on our efficiency. This Excel lesson provides several practical examples so you can work hands-on.

Perform Lookups with SUMIFS

What is your favorite lookup function? Excel has many lookup functions, including VLOOKUP, INDEX, and MATCH. However, when we consider using SUMIFS to perform lookups, we are able to avoid many limitations of traditional lookup functions. This online Excel course demonstrates how to apply our dear friend SUMIFS to the task of lookups.

Determine the Last Day of the Month with EOMONTH

Date driven workbooks are common for accountants. This online Excel course discusses one of the most important date functions...EOMONTH. This Excel lesson reviews the function's syntax, demonstrates how to integrate it into your workbooks, and provides a sample Excel file so you can practice hands-on.

Date Parts - MONTH and YEAR

Date driven workbooks are common for accountants, and thus, it is important to be familiar with key date functions. We can pull the date parts out of a date, and this online Excel class explores the MONTH, YEAR, and DAY functions. This Excel lesson reviews the syntax and walks through several practical examples. The sample Excel file is provided 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
  • Be able to trap errors with IFERROR function
  • Demonstrate multiple uses for the IF function
  • Enact multi-column comparisons with COUNTIFS
  • Be able to create indented report labels
Last updated/reviewed: August 26, 2023

Included In Certifications

This course is included in the following Certification Programs:

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
183 Reviews (512 ratings)

Reviews

4
Anonymous Author
I appreciate that Jeff provides interactive excel sheets so that you can do the example problems with him. There was a time or two that he would use a keyboard shortcut to do something (which I didn't know how to do) and then I was lost going forward, but overall, I think his teaching approach is great. He is fast because he is an expert with the information, but he gives you ample opportunity to practice.

5
Anonymous Author
An excellent introduction course into trapping errors and using combined functions along with using date functions. I will be able to use these combined functions in complicated bank reconciliations and date functions in prepaid worksheets and many others. Explained well with superb materials. Best excel teacher thus far.

4
Member's Profile
Really great content, that is taught efficiently without consuming excessive time. The only reason I gave less than 5 stars on this one is, I do not remember covering the match function in the presentation or worksheets (unless I missed it, then I retract this comment), but there was a question on the exam about it.

5
Anonymous Author
The information provided was great for beginners, but some of the methods seem to be outdated. Specifically as it relates to vlookups. I think details on the xlookup function would be a game changer for some! Also a further analysis of using index and match together.

5
Member's Profile
This course was a little more challenging. I was not prepared with knowing the Match function and there were some extra credit exercises that made you think beyond the course instructions. I felt challenged and enjoyed Jeff's teaching style.

5
Anonymous Author
The list comparison build it quickly to determine which item from one list appear nor another is useful. This is another go application the amazing excel lookup function. It is practical useful in daily task on bank reconciliation.

5
Member's Profile
I took this course, which is number 4 in the series, first but it did not really matter. I think I will take the whole course since it has been very helpful so far in letting me know just what can be done using the language of excel.

5
Anonymous Author
Good commentary about the ISERROR function - I didn't know about that function until this video. Also never knew that the VLOOKUP only returned the first matching value before this CPE course - another helpful detail to know in Excel.

4
Anonymous Author
Great course! Learned something new. This course would benefit someone brushing up on excel or someone new to it. I’m glad that this resource is available. Videos made it easy to learn required material.

5
Anonymous Author
Material was clearly presented and the examples were easy to follow. The speaker was very articulate and did well to ensure that the material was thoroughly reviewed before moving onto the next topic.

4
Anonymous Author
I'd really like to explore more on excel so this is really a great help for me. Just noticed that the audio playing took so long that made me nap waiting for the next picture/graphics to come out.

5
Member's Profile
Interesting tip on the use of SUMIFS as a lookup function. The comparison to VLOOKUP was insightful. EOMONTH is new for me and I have already thought of specific applications for this function.

5
Member's Profile
I liked the way multiple uses were demonstrated for each function. what surprised me was the intensity of the course. I learned a much that can be transferred to various everyday tasks.

4
Anonymous Author
Excellent overview of these functions with easy to follow examples. I was not familiar with several of the functions and will be able to incorporate them into some of my worksheets.

5
Anonymous Author
It always amazes me how much I learn every time I take an excel course. This course was basic but I still learned an efficiency I didn’t know. The extra practice was great too.

5
Anonymous Author
I enjoyed learning all the benefits of using SUMIFS formulas and plan to put them to use in my next spreadsheets. Jeff clearly explains concepts and shows great examples.

5
Anonymous Author
This course reviews key formulas and gives important Excel tips. Jeff has a good way of explaining the concepts. Practice is key to mastering some of these concepts.

5
Member's Profile
This course was great. It showed how to use some functions that I didn't consider very important and also some different uses for functions that I hadn't thought of.

5
Member's Profile
The exercises and speed he goes through them were spot on. Also I liked that there were a few related functions that were covered in depth vs too many to remember.

4
Anonymous Author
Content is presented in a coherent, clear manner. Presentation style builds upon itself beginning with the foundation and working toward the conclusion. Very goold.

5
Anonymous Author
Easy to follow and the excel examples illustrate the functions well. However the exam for this course did ask questions about formulas taught in a previous course.

4
Anonymous Author
This course provided useful tips in an easy to understand manner that I will be able to start implementing today to increase efficiency when building workbooks.

5
Member's Profile
Another excellent course by Jeff Lenning. So many helpful functions and tips. I just hope I can remember them all. I would have like to seen more examples.

5
Anonymous Author
The formulas presented in this course are very valuable and should be a part of every accountant's toolkit. I will definitely watch more videos like this.

5
Member's Profile
Good presentation, and useful for both functions I was unfamiliar with and functions I knew but could benefit from a deeper understanding of their power.

5
Anonymous Author
I like the short sessions. I like the examples/worksheets. This class has addressed several issues I have that can improve my spreadsheets. Great Class

5
Member's Profile
Mr. Lenning is an excellent instructor. He has a lot of valuable tricks and techniques to make the student's use of Excel more effective and efficient.

5
Member's Profile
Thanks for all the insight into these functions. The way you start with one function then wrap the second is so helpful. Great class and instructior.

5
Member's Profile
Great course to learn all the functions. Worksheets are extremely helpful to "ingrain" the formulas so they aren't forgotten when you next need them.

5
Anonymous Author
Entire series is fantastic. No matter how much I have used these functions, I always learn something new in these courses. Great use of one's time.

5
Anonymous Author
This course contained practical and useful content presented in a straightforward manner. Great workbook examples to practice your new excel skills!

4
Anonymous Author
Had a hard time understanding the error functions. Seemed backwards to how I think and very hard to conceptualize. More examples would have helped.

4
Anonymous Author
The speaker is great and provided a very clear example that is easy to follow and understand. This is very useful to anybody that deals with excel.

5
Anonymous Author
Learned some new skills that will make me more efficient. Also, learning formula alternatives will make following other's workbooks easier.

4
Member's Profile
Very helpful and useful tools, specifically the benefit of SUMIFS versus VLOOKUP. And, understanding what the date serial numbers were.

5
Anonymous Author
I found the course to be easy to understand and engaging. It covered a wide variety of topics that are all very useful excel functions.

5
Anonymous Author
I appreciated how the instructor walked through the lesson and showed the formula in real time. Very helpful and easy to understand.

4
Anonymous Author
If you're new to functions you might need to review this course a couple of times to understand and remember the different functions.

4
Anonymous Author
It would help to know what formulas are assumed known before taking this course. It just lists what formulas are being taught.

5
Anonymous Author
Very Clear instruction which made it easier to understand the course. The excises are fun to do and really help me to remember.

5
Member's Profile
Useful material for any Excel user -- from beginners to veterans. Well presented with good worksheet examples and exercises.

5
Member's Profile
Great course. Exactly as promised. Materials are spot on and very helpful. The pace was right on and exactly as expected.

5
Anonymous Author
Yet another very helpful course from Mr. Lenning. I love how he breaks down every formula into a simple to understand lesson.

4
Anonymous Author
Very informative but Mr. Lennig went a bit fast. Would like to take individual courses on the IF and IFerror functions.

5
Anonymous Author
This course is really helpful with my daily job. It will increase the efficiency when I compare different sets of data.

5
Anonymous Author
This was a great excel training that helps standardize workpapers and create more dynamic equations to reduce updates

5
Member's Profile
This course was helpful in considering the differences between Vlookups and Sumifs. I'll use this in my work daily.

5
Anonymous Author
The instructor was easy to listen to and follow. I appreciated the spreadsheets to use as tools during the course.

4
Member's Profile
quick and informative Surprised by the match formula question, since the course did not cover the Match formula

4
Member's Profile
I thought the instructor could spend less time on the non functional portions and more time on the "homework".

4
Anonymous Author
Pretty basic stuff, but well done course that would be good for those who don't have much experience with Excel

4
Member's Profile
I like the content, it showed a few new uses of some of the formulas, and new ways to think about some lookups.

5
Member's Profile
Clear. Need to remind shortcuts in demonstrations, such as which key is being pressed for locking down cells.

4
Member's Profile
I liked how the professor showed multiple examples of how to use the formulas including more complex uses.

5
Member's Profile
I really enjoy way Jeff demonstrates the formula syntax. Never been so comfortable using SUMIFS functiion

3
Member's Profile
Moved a little fast for grasping the details/terminology. Had to review more on my own to pass the exam.

5
Anonymous Author
Extremely polished presentation and the the detailed examples help drive home how each function works.

5
Member's Profile
Good review of a complicated formulas. Glad for the worksheets provided with the course to practice!

5
Anonymous Author
This was an excellent overview of fundamental excel functions. I love the multiple uses of examples.

5
Member's Profile
The format of lecture and demo was very effective. This provides practical examples for the student/

5
Anonymous Author
Very informative and practical. I can put the formulae into my everyday use. Thank you very much!

4
Anonymous Author
Simple and easy explanation/examples. Materials are appropriate for understanding and excercise.

5
Member's Profile
Even though I know how to use most of these functions, it opened my eyes to different approaches.

5
Member's Profile
This course had some very helpful tips for analyzing data sets and comparing multiple data sets.

5
Member's Profile
Excellent demonstration of functions. Really expanded ways in which i can increase efficiency.

3
Member's Profile
The topics seemed a little disjointed. The exam questions included items not covered: =MATCH

5
Anonymous Author
Well explained and good examples. I wish the PDS were included to use for future reference.

5
Anonymous Author
Clear instructions, easy to follow example workbooks with answer keys. Quick and to the point.

5
Member's Profile
Quite possibly the best vendor our firm has used. Great service and support, great courseware.

5
Anonymous Author
Which question on the final exam was wrong? Hard to learn if you can't see the test results!

4
Anonymous Author
This course presented useful information in a concise manner. The examples were very helpful.

5
Anonymous Author
easy to follow - will make an effort to use "sumifs" instead of "vlookup" beginning today.

5
Member's Profile
I am good with Excel - but still learned some things I didn't know. It was worth my time.

5
Member's Profile
The detailed explanation followed by an example helps to understand and learn the material.

5
Anonymous Author
As usual, these Excel University courses are excellent. Very good tips and well presented.

4
Anonymous Author
This course was a helpful overview of common excel functions that I use in my regular work.

4
Member's Profile
A nice quick tutorial on number of logic functions to use in common spreadsheet situations.

5
Member's Profile
Really easy to watch, listen and learn with the lesson setup and on-screen demonstrations!

5
Anonymous Author
Great course and very informative. The instructor uses great examples and easy to follow.

4
Anonymous Author
Another great course by this instructor. Good tips even for the experienced excel user.

5
Anonymous Author
Great explanations and examples of each function, ideal for anyone who works with lists

4
Member's Profile
Well presented, recommended.Well presented, recommended.Well presented, recommended.

4
Member's Profile
Good class to know that these functions are out there. I liked the indent the best.

5
Member's Profile
great course-I learn so many things in each of these exel training courses...love it

4
Anonymous Author
Easy to follow the examples presented. Useful functions for comparing information.

5
Member's Profile
Love Jeff Lenning's instruction method, best excel teacher I have ever experienced!

4
Anonymous Author
Great instructor. I have enjoyed all of the courses I have taken that he teaches.

5
Anonymous Author
This training was helpful and the instructor was good at presenting these skills.

Anonymous Author
Spent too much time on simple functions like EOMonth (the worst function ever).

5
Anonymous Author
First time using multicolumn list comparisons - this will really come in handy!

5
Member's Profile
Nice demonstration of functions to use for list comparisons and related topics

3
Anonymous Author
Overall not bad, a lot of little lessons versus one long extended discussion.

5
Member's Profile
A number of helpful ideas and I've been using Excel at a high level forever.

5
Anonymous Author
Very good review and summary of excel functions. Always a good refresher.

4
Anonymous Author
good course. learning lots of excel things I didn't realize were out there.

3
Member's Profile
Very basic for me. I was a comp sci professor and I taught 5 excel course.

5
Anonymous Author
Very good information that will be beneficial when completing my formulas.

5
Member's Profile
Fantastic new ideas. I thought I was an excel expert but apparently not!

5
Member's Profile
Love this professor! He is concise and easy to follow! Best on Illumeo!!!

5
Member's Profile
material to reivew more than once to understand the logic is interesting

5
Anonymous Author
Very helpful course and tricks. Instructor moves at an appropriate pace.

5
Member's Profile
Pretty basic information but a good review. I hate doing these reviews.

4
Member's Profile
Helpful introduction, but could be expanded to provide more use cases.

3
Member's Profile
A lot of info, which would probably take several times to understand.

4
Anonymous Author
Good overview of these functions and their strengths and weaknesses.

5
Anonymous Author
Your videos are very easy to follow and very informative! Thank you!

5
Member's Profile
A lot of very useful day to day excel formulas to improve efficiency

5
Member's Profile
Very helpful tips - had never heard of the iserror function before.

5
Anonymous Author
I like that there are training worksheets included in this course.

5
Anonymous Author
Great course if you don't know the functions listed in the synopsis

5
Member's Profile
Jeff Lenning is awesome! It is easy to learn from his instruction.

5
Member's Profile
Good review of some very useful but not famous tips and functions.

5
Anonymous Author
Great functions. Perfect presentation. I'm a clever person now :-)

3
Member's Profile
Test should have included actual problems similar to the examples.

5
Member's Profile
Very helpful. I like the side-by-side comparisons of functions.

5
Anonymous Author
This was a good course that I will use the information daily.

5
Anonymous Author
Great content i enjoyed learning the different excel function

4
Member's Profile
Nice tips and tricks. Great instructor. Very useful tools.

5
Member's Profile
Very concise presentation and very useful worksheets. thanks

5
Member's Profile
Полезный курс. Автор эффективен

4
Anonymous Author
I liked the quick examples that were easy to understand.

5
Member's Profile
i like the course we need more like this including counta

5
Anonymous Author
Course contains a lot of good information. Very helpful.

4
Anonymous Author
This was a good overview of several useful Excel tools.

5
Member's Profile
Great examples and ideas on how to use Excel functions

5
Member's Profile
Very thorough, the workbook goes along quite well.

5
Anonymous Author
great course, effective instructor, great learning

5
Member's Profile
Loved a nice comparison of vlookup with sum ifs.

4
Anonymous Author
good explanations of how to use advanced features

4
Anonymous Author
Great course. Makes you think. Very well taught.

4
Anonymous Author
I enjoyed the course. Great learning experience.

5
Anonymous Author
Again another excellent course by the author.

4
Anonymous Author
It is a good refreshment course for beginners

Anonymous Author
Little too beginner. Wanted more in depth.

5
Anonymous Author
Jeff - is a good resource for excel issues.

5
Member's Profile
Covered material which I can put to use now

5
Anonymous Author
Good to go back and review these formulas.

5
Member's Profile
Best lesson to learn and understand excel.

4
Member's Profile
I found the worksheets provided helpful.

5
Anonymous Author
Great review for very useful functions

5
Member's Profile
The course is so easy to understand.

5
Member's Profile
Very helpful and easy to understand.

5
Member's Profile
I love Excel, and all Excel courses.

5
Anonymous Author
Learned a lot on using IF functions

5
Anonymous Author
Information is presented very well.

5
Member's Profile
Nice! Excited for the next course.

5
Member's Profile
Excellent Course. Advance excel.

5
Member's Profile
I like to the class very much.

5
Member's Profile
Good overview of basic funtions

4
Anonymous Author
The course had some great tips.

5
Member's Profile
Love this course. Thank you.

4
Anonymous Author
interesting course material

5
Member's Profile
Good probing presentation

5
Member's Profile
Very professionally done!

5
Member's Profile
very helpful information.

5
Anonymous Author
Good class, very useful

5
Anonymous Author
Interactive questions.

3
Anonymous Author
It's a fair review....

4
Anonymous Author
Lots of information.

Member's Profile
Great review for me

4
Anonymous Author
It was a very good

5
Anonymous Author
Exselent material!

4
Anonymous Author
Excellent course.

5
Anonymous Author
Another good one!

4
Anonymous Author
Quite informative

5
Anonymous Author
Great explanation

5
Member's Profile
well informative

3
Anonymous Author
Nice refresher

5
Anonymous Author
Helpful hints

5
Member's Profile
Great tools.

5
Member's Profile
Good course.

5
Anonymous Author
great course

5
Anonymous Author
Great course

5
Member's Profile
Good Course

5
Member's Profile
interesting

4
Member's Profile
Nice course

Member's Profile
Good course

4
Anonymous Author
very clear

5
Member's Profile
Very good.

5
Member's Profile
Good tips

4
Member's Profile
Helpful.

Member's Profile
Good

5
Member's Profile
Good

Prerequisites
Course Complexity: Intermediate

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 .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
Lessons
  Trap Errors with IFERROR4:15
  The IF Function3:32
  List Comparisons3:30
  The ISERROR Function7:04
  Multicolumn List Comparisons with COUNTIFS6:46
  Indentation6:15
  Perform Lookups with SUMIFS8:02
  Determine the Last Day of the Month with EOMONTH7:50
  Date Parts - MONTH and YEAR2:40
CONTINUOUS PLAY
  Excel Training: Mastering Fundamental Functions - IFERROR, IF, List Comparisons and more49:56
SUPPORTING MATERIALS
  WorksheetsZIP FILE
  Mastering Fundamental Functions Glossary/IndexPDF
REVIEW & TEST
  Review Questionsquiz
 Final Examexam