Master the organization, analysis, and sharing of diverse data types across personal and professional contexts, from budgeting to project scheduling. Excel Level 3 expands your capabilities with advanced data entry functions, worksheet automation, data analysis techniques, and collaborative features. This course uses Excel 2019 as the primary platform, but accommodates Excel 365 users as well.
Prerequisite: Excel Level 2 or equivalent knowledge and experience
Course Overview:
Your Excel foundation has equipped you with practical skills for efficient problem-solving and business applications. In this advanced course, you will explore sophisticated Excel features, including task automation, workbook error prevention, data collaboration with colleagues, data analysis capabilities, and integration of Excel data with other applications. Microsoft Excel extends far beyond basic functions, offering advanced tools for creating more sophisticated and accurate spreadsheets. Additionally, built-in features enable seamless collaboration and sharing of your work with team members and stakeholders.
Course Objectives:
In this course, you will develop expertise with advanced Microsoft Excel functionality. Specifically, you will learn to automate worksheet operations, conduct workbook audits, perform data analysis, manage multiple workbooks simultaneously, and import and export data across different formats and applications.
Target Student:
This course is designed for participants with practical Excel experience who want to develop expertise in macros, shared documents, data analysis methodologies, and worksheet auditing techniques.
Course Structure:
Lesson 1: Automating Worksheet Functionality
- Update and manage workbook properties and metadata
- Create, edit, and test macros for repetitive tasks
- Apply conditional formatting rules and styling
- Establish and enforce data validation criteria
Lesson 2: Auditing Worksheets
- Trace cell dependencies and precedent relationships
- Identify and correct invalid data and formula errors
- Monitor and evaluate formula behavior and calculations
- Organize data with outline structures
Lesson 3: Analyzing and Presenting Data
- Generate sparklines for quick visual data summaries
- Build scenario analysis models and comparisons
- Execute what-if analysis for decision making
- Conduct statistical analysis using the Analysis ToolPak
- Create interactive data visualizations with Power View
Lesson 4: Working with Multiple Workbooks
- Consolidate data from multiple workbooks and sources
- Establish and manage links between workbook cells
- Merge multiple workbooks into unified structures
Lesson 5: Exporting and Integrating Excel Data
- Export Excel data in multiple formats
- Import and process delimited text files
- Connect Excel data with web-based resources
- Construct and execute web queries for data retrieval
