course

Excel Training: Tips for Improving Data Validation, Error Checking, Reporting and Other Performance Obstructions

(102)
4.607845
236 Enrolled
1 Hour (On-Demand)
1 CPE  

This course is a premium+ course it can be accessed either by individual purchase or through a premium+ subscription

Purchase this course | $42

Learn tips for optimizing many frequently occurring performance obstructions in Microsoft Excel. At the end of this course, you'll be able to articulate tactics for data validation, error checking and reporting. You learn the following:

Build Your Own Date with DATE

This online Excel course explores the DATE function. We walk through the function's syntax, illustrate how to use it through practical examples, and provide homework activities that allow you to work hands-on.

Concatenation Basics

This online Excel course digs into concatenation which is the joining of text strings. This Excel lesson reviews the CONCATENATE worksheet function and the concatenation operator. It provides hands-on exercises so you can work along.

Improve SUMIFS with CONCATENATE

In the previous chapters in this volume, we've only been able to get partial utility out of the SUMIFS function. This online Excel course demonstrates how to improve our SUMIFS formulas by using concatenation. This Excel lesson includes a sample Excel file so you can work hands-on.

Dynamic Headers

This online Excel course discusses the power of dynamic headers, and how they are a critical concept in recurring-use reporting workbooks. This Excel lesson includes a sample Excel file so you can work hands-on.

Horizontal Reports

Accountants often prepare reports that have a horizontal orientation, for example, when report values are placed into monthly columns. This online Excel course demonstrates how to build such reports and provides a sample Excel file so you can work along.

Mapping Tables

This online Excel course covers the concept of mapping tables and demonstrates how they are used to automate recurring-use reports. A common issue encountered by accountants is that the account names, such as Cash, are different than the report labels, such as Cash and Cash Equivalents. This lesson explores the mapping table, and illustrates how they solve this issue and provide Excel with the translation needed to automate the data flow.

Data Validation and Reporting

This online Excel course explores the utility of data validation when preparing and delivering digital reports. This Excel lesson demonstrates several ways to implement data validation into your reporting workbooks.

Improve Error Check

This online Excel course further improves the ErrorCk sheet originally discussed in Volume 1 Chapter 15. This Excel lesson demonstrates how to use Boolean values, comparison formulas, the logical AND function, and conditional formatting to build a reliable error check sheet.

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
  • Articulate tactics for Excel data validation and reporting
  • Improve error checking
  • Implement concatenation basics
  • Build your own Date with the Date function
Last updated/reviewed: August 20, 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
38 Reviews (102 ratings)

Reviews

4
Anonymous Author
The course is really helpful. There is a lot of excel functionalities presented that are really useful in preparing and reviewing the accuracy of the reports. Moreover, the tips provided are really good and will definitely make the preparation and review process more efficient.
4
Anonymous Author
I liked this class, but I don't actually work in tables, so while the ideas are still useful, the practicalities didn't always line up with my job. Also, I think the classes should be shorter, maybe 30 minutes. An hour to take a training is a bit of a luxury.
5
Member's Profile
These are some complicated concepts and Mr. Lenning went quickly through them, so it is important to work through the supporting worksheets to get a really good grasp on the material. Keeping that in mind, this was an excellent course.
5
Anonymous Author
Great information that can be applied immediately to anyone using spreadsheets regularly. The zip files with examples and answers are extremely helpful.
5
Member's Profile
Great course! Even for experience user, we always learn something new or an intelligent way to organize data with Jeff Lenning courses. Thank you Jeff!
4
Member's Profile
Jeff is a great teacher and always informative but it would be great to have the presentation as a continuous video rather than in blocks.
5
Member's Profile
This is quite simply the best Excel course I have taken on Proformative. Perfect detail, explanation and pace. HIGHLY RECOMMENDED.
5
Anonymous Author
This is a great course and a great speaker. There is so much to learn about Excel and this is one of many steps to get there.
3
Member's Profile
The "Concepts Used" file was not included in the Zip files containing the worksheets. Overall, pretty helpful.
3
Anonymous Author
It took me 2 hours to complete this course, so I feel like it should be a 2 hour course, not a one hour one.
5
Anonymous Author
I like that each topic is broken down into its own presentation with hands on worksheets to practice on.
4
Member's Profile
It has some tips that can be useful but sometimes the formulas can be replaced with simpler ones
5
Member's Profile
Separate discussion of related topics cooling together nicely. Very well summarised.
5
Member's Profile
Glad for the training on dynamic reporting and appreciate the homework to go along.
5
Member's Profile
Really well explained, and good exercises to help get to grips with the concepts.
4
Anonymous Author
Exceptional course...I enjoy your presentation skills. Keep up the good work.
5
Member's Profile
Great! I just completed all the courses by this instructor. Thank you sir!
5
Member's Profile
Best one hour excel course I have ever taken. Watch this and be awesome.
4
Anonymous Author
Another great course by Jeff chock full of useful tips. Great job!
5
Member's Profile
the information provided here will save me a LOT of time. thanks!
4
Member's Profile
A recap of slides would be helpful. Some formulas are detailed.
4
Member's Profile
Good refresher of some essential commands and useful tools.
5
Member's Profile
Good overview of Excel functions and some tips and tricks.
5
Anonymous Author
The information was very helpful and I will use it daily.
2
Anonymous Author
It is hard to follow to what tabs are being referenced.
4
Anonymous Author
Excellent course. Very well structured and presented.
5
Member's Profile
Once again Mr. Lenning provides clear understanding.
5
Anonymous Author
Very useful functions of excel. Made my life easier.
5
Member's Profile
Good way to get into areas I wasn't familiar with.
5
Anonymous Author
Thanks a lot. I found it helpful and relevant.
5
Anonymous Author
Great, thorough explanations and examples
5
Member's Profile
Excellent suggestions- Thank you!
4
Member's Profile
Good information.
5
Anonymous Author
New learnings
5
Anonymous Author
Very helpful.
5
Member's Profile
Great ideas
4
Anonymous Author
Useful tips
5
Anonymous Author
Very good.
show all reviews
Prerequisites
Course Complexity: Advanced

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 Questions and Answers(7 Questions)
There are no questions.         Answers to Frequently Asked Questions (FAQs)
User picture
Anonymous
(Lead Wireless Data Analyst) | Oct 25, 2019

RE: Additional classes to certification. I have five reports that are updated monthly. I do index match lookups on all five.
I want to create a template that will allow me to bring in the five reports each month, do the indexing automatically and put the results, also automatically, into one spreadsheet. It would have 25 columns of info. Which of your classes would cover that? Thank you.

Member's Profile
Jeff Lenning
View Instructor Profile | Oct 25, 2019

If each report is in a separate Excel workbook, then one option to quickly import data from multiple workbooks (or csv) is Power Query. I have free tutorials here:
https://www.excel-university.com/retrieve-values-from-many-workbooks/
https://www.excel-university.com/get-transform-an-alternative-to-copy-paste-append/
https://www.excel-university.com/pivottable-from-many-csv-files/
Hope they help!
Thanks
Jeff

User picture
Anonymous
(Lead Wireless Data Analyst) | Oct 25, 2019

Thank you very much!

User picture
Anonymous
(Lead Wireless Data Analyst) | Nov 14, 2019

Power query is great, but I am stuck trying to bring in multiple columns tied to one column:
I have five workbooks, one spreadsheet per workbook. Each sheet starts with column A as "EP_ID".
As example in column A: A1 is 101, A2 is 102, A3 is 103 etc.
I need to bring in three to eight columns from each the different sheets linking the EP_ID in each sheet.
Is this possible?
Thank you very much! This is an impressive function.

Member's Profile
Jeff Lenning
View Instructor Profile | Nov 14, 2019

Although I'm absolutely happy to answer questions about the course files, I don't have the capacity to provide Excel support/consulting on student workbooks.
Thanks
Jeff

Member's Profile
Leslie Harding-Ide
(Lead Wireless Data Analyst at Charter Communications) | Nov 15, 2019

Understood, I will try to rephrase. Can the rows of several sheets be linked using a key field?

Member's Profile
Leslie Harding-Ide
(Lead Wireless Data Analyst at Charter Communications) | Nov 18, 2019

Thank you for your time, but I found it. Import the queries into the new query and merge. Thanks again.

Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  1:05PREVIEW: Introduction to Excel Training: Tips for Improving data validation and more
Training
  Build Your Own Date with DATE5:17
  Concatenation Basics7:42
  Improve SUMIFS with CONCATENATE9:59
  Dynamic Headers6:44
  Horizontal Reports4:40
  Mapping Tables5:02
  Data Validation and Reporting7:19
  Improve Error Check9:57
  Concepts Used4:58
CONTINUOUS PLAY
  Excel Training: Tips for Improving Data Validation, Error Checking, Reporting and Other Performance Obstructions 1:01:42
SUPPORTING MATERIALS
  WorksheetsZIP FILE
  Tips for Improving data validation Glossary/IndexPDF
REVIEW & TEST
  Review Questionsquiz
 Final Examexam