Excel - Automating Excel with Macros and Analysis Tools

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