Automating Worksheet Functionality Using Excel Macros, Validation and Auditing tools
Training TypeLive Training
Automating Worksheet Functionality Course Introduction
About Automating Worksheet Functionality 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.
Automating Worksheet Functionality Course Objective
Update workbook properties
Create and edit a macro
Apply conditional formatting
Add data validation criteria to a workbook
Troubleshoot invalid data and formula errors
Watch and evaluate formulas
Who is the Automating Worksheet Functionality Course Target Audience?
Any Professional who would like to save time by learning more about automating repetitive tasks, data validation and auditing workbooks
What Basic Knowledge Required to Learn Automation Training Course?
Foundational Excel knowledge would be helpful for this course.
|15 Feb 2023||Wed ( 1 Day )||12:00 PM - 03:00 PM (Eastern Time)|
|16 Mar 2023||Thu ( 1 Day)||12:00 PM - 03:00 PM (Eastern Time)|
Remove Personal Information Using the Document Inspector
The Record Macro Dialog Box
Visual Basic for Applications
Copying Macros Between Workbooks
Macro Security Settings
The Conditional Formatting Rules Manager Dialog Box
The New Formatting Rule Dialog Box
The Data Validation Dialog Box