Automating Worksheet Functionality Using Excel Macros, Validation and Auditing tools

About the Course

In Excel, you can view, add, or even edit the properties of an existing workbook. These properties can include information such as who authored the workbook, date information, and more. This type information is referred to as metadata, and it can very useful when managing multiple workbooks. Over the course of this topic you will learn all about workbook properties and how to work with them.

Due to the nature of Excel and how it is used, you may often find yourself repeating the same task over and over again. To help streamline your workflow, it is possible to automate those tasks by creating a macro. Macros are created using code in Visual Basic for Applications; however, you don’t need to know programming in order to create a macro. Over the course of this topic, you will learn how to create a macro using the Macro Recorder.

While Excel workbooks and worksheets can contain all sorts of important and enlightening data, those important bits can sometimes be lost in a sea of information. To help bring attention to patterns or outliers in your data, Excel allows you to apply conditional formatting. Conditional formatting is often used to highlight interesting and relevant data, but it can go much further. During this topic you will learn how conditional formatting works in Excel, as well as how to apply it.

By restricting the type of data that can be entered into your workbook, you can prevent data errors and have greater control over your data. For example, some data may need to be whole numbers while some may need to be a decimal value. Over the course of this topic, you will learn about data validation and how it is used in Excel.

To assist you in creating and troubleshooting formulas in a worksheet, the Trace Cells feature will graphically display where data is coming from and where it is going. For example, it can apply a colored outline to a range in a formula, as well as to where the solution of the formula is displayed. During this topic, you will learn about the Trace Cells feature and its related components.

While you already know how to use tracer arrows to identify the components of a formula and how they interact, sometimes that isn’t enough to figure out a formula problem. Invalid data can often be the root cause of formula errors and it can greatly affect the functionality of your worksheets. Over the course of this topic, you will learn how to find and troubleshoot invalid data and formula errors.

When working with complex formulas, you can ensure that they are operating correctly using the Watch Window. Additionally, by being able to evaluate formulas while you create them, you can ensure that they are operating as intended. During this topic you will learn how to watch and evaluate formulas in Microsoft Excel.

Course Objective

Update workbook properties

Create and edit a macro

Apply conditional formatting

Add data validation criteria to a workbook

Trace cells

Troubleshoot invalid data and formula errors

Watch and evaluate formulas

Who is the Target Audience?

Any Professional who would like to save time by learning more about automating repetitive tasks, data validation and auditing workbooks

Basic Knowledge:

Foundational Excel knowledge would be helpful for this course.