Excelling with Microsoft Excel (Level 3)

Course Description

Participants will learn to use data validation, create macros, and apply tools like scenarios and goal seek for “what-if” analysis. The course also covers auditing worksheets, working with external data, and inserting hyperlinks. Participants will learn to manage multiple workbooks, create forms, and get an introduction to Visual Basic for automating tasks. By the end, participants will be proficient in advanced Excel techniques to enhance productivity and data management.

Course Content:

• Creating Macros and Using Visual Basic
• Reviewing Cell Names & Applying Range Names
• Data Validation
• Using Flash Fill
• Tracing Cells and Error Checking
• Sparklines
• What-If Analysis: Scenarios, Goal Seek, and Solver
• Consolidating Data
• Exporting Data
• Importing Data: CSV, Web Query

Applications and Hands-On:

• Create and record macros to automate repetitive tasks in Excel
• Explore Visual Basic for Applications (VBA) to enable advanced automation and functionality
• Assign and use named ranges to simplify formulas and enhance worksheet navigation
• Use data validation to control data entry, ensuring consistency and accuracy across worksheets
• Leverage Excel’s Flash Fill feature to automate workflows based on patterns in adjacent data
• Audit formulas and visualizing cell dependencies and errors
• Trace Precedents, Trace Dependents, and use Error Checking to troubleshoot issues
• Create and use Sparklines for compact visual representation of data trends
• Perform What-If analysis with Scenarios, Goal Seek, and Solver to solve complex problems
• Consolidate data from multiple sources to analyze large volumes of data from different places
• Export data from Excel to various file formats to share and distribute your work outside
• Import data into Excel, including the use of web queries, to connect with external data sources

Resources Provided:

• Individual computer access
• Microsoft Excel software