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

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.