Excel - Automating Excel with Macros and Analysis Tools

Duration

1 Days

This course is intended for

To ensure success, students should have completed Excel Essentials and Excel Functions Including Pivot Tables and Lookups or have the equivalent knowledge and experience.

Overview

Upon successful completion of this course, students will be able to enhance productivity and efficiency by streamlining workflow, collaborate with others, and audit and analyse data.

Description

This course is designed for students desiring to gain skills necessary to create macros, collaborate with others, audit and analyse data, incorporate multiple data sources, and import data.

Working with Multiple Worksheets and Workbooks

Working with Named Ranges
Link Cells Across Worksheets and Workbooks
Use 3D References to Calculate Across Worksheets
Consolidate Data

Use Formula Auditing and Error Checking

Reveal Formulas
Trace Cell Precedents and Dependents
Locate Errors in Formulas
Watch and Evaluate Formulas

Reviewing and Protecting Workbooks

Control Data Entry via Data Validation
Protect Workbook Access
Protect Worksheets and Cell Content
Add and Edit Comments
Prepare a Workbook for Distribution
Modify Excel's Default Settings

Using Macros to Automate Workbook Functionality

Create Macros via Recording
Run Macros via Buttons and Shortcuts
Assign Macros to the Quick Access Toolbar and Ribbon
Assign Macros to Objects
View Macro Code

Forecasting and Analysis Data

Use Conditional Formatting to Highlight, Sort and Filter Key Data
Advanced Conditional Formatting using Formulas
Create Sparklines to Visualise Data
Add Trendlines to Charts to Visualise and Forecast Trends
Use Data Tables and Scenarios to Project Potential Outcomes
Use Goal Seek to Calculate Outcomes
Forecast Data Trends Using Solver