Download the Course Brochure Link

This comprehensive course provides a deep dive into advanced data management and analysis techniques within the Excel environment. Participants will learn how to manipulate and organize data effectively, work with databases, utilize templates, create and revise charts, use advanced filters, protect worksheets, utilize auditing tools, import and export data, use advanced functions, and work with pivotal data and scenarios. The course is designed to equip participants with the skills necessary for efficient data handling and analysis in a professional setting.

What will you learn in this Microsoft Excel Intermediate and Advanced Level?

• Develop proficiency in sorting, filtering, and managing data for better organization and retrieval.
• Learn advanced database management methods for accurate and reliable data analysis.
• Master the use of templates for smoother and more organized worksheet management.
• Acquire skills to create and customize charts effectively for clear data representation.
• Learn to protect worksheets and workbooks for enhanced data security and integrity.
• Develop skills in managing complex data relationships and tracking using auditing tools and range names.
• Acquire the ability to seamlessly import and export data between different platforms and applications.
• Become proficient in using various advanced functions for complex data analysis and manipulation.
• Develop expertise in creating and managing pivot tables for comprehensive data analysis.
• Learn effective techniques for tracking and merging workbooks for improved collaboration and version control.
• Acquire skills in applying custom formats and recording macros for efficient and automated data formatting and manipulation.

Course Outline

Module 1: Data Management
• Sorting Data
• Sorting Data in Multiple Columns
• Finding Specific Data
• Replacing Data Values
• Modifying Cell Formats

Module 2: Working with Databases
• Creating and Removing Subtotals in Lists
• Sorting Data in Multiple Levels
• Custom Sorting
• Validating and Removing Data
• Customizing Error Messages

Module 3: Using Template
• Managing Worksheets
• Adjusting Workbook Templates
• Handling Worksheets (Hide, Unhide, Delete)
• Accessing Online Templates

Module 4: Creating Chart
• Inserting and Formatting Different Chart Types
• Customizing Chart Elements (Title, Background, Data Table)
• Managing Chart Types and Layouts
• Working with 3-D Charts and Sparklines

Module 5: Revising Charting Features
• Formatting Axis and Data Series
• Including Data from Different Worksheets
• Using Secondary Axis and Changing Data Series Types

Module 6: Working with Advanced Filters
• Formatting Axis and Data Series
• Including Data from Different Worksheets
• Using Secondary Axis and Changing Data Series Types

Module 7: Using Worksheet Protection
• Protecting and Unprotecting Worksheets and Workbook Windows
• Managing Allow-Editing Ranges and Passwords

Module 8: Using Auditing Tools and Range Names
• Applying and Removing Range Names
• Displaying Relationships between Cells (Dependent/Precedent Arrows)
• Showing Formulas

Module 9: Exporting and Importing Data
• Managing External Data Properties and Hyperlinks

Module 10: Using Advanced Function
• Utilizing Various Functions (VLOOKUP, HLOOKUP, IF, ISERROR, AND, OR, ROUND, COUNTIF, RANK)
• Exploring Financial and Text Functions

Module 11: Creating/Revising Pivot Tables
• Constructing and Customizing PivotTable Reports and PivotCharts
• Working with Data Grouping and Summary Functions

Module 12: Using Scenarios
• Creating, Editing, and Displaying Scenarios
• Generating Scenario Summary Reports
• Handling Data Tables and Variable Tables

Module 13: Tracking and Merging Workbook
• Managing Shared Workbooks and Tracked Changes
• Handling Conflicting Changes and History Worksheets

Module 14: Using Conditional and Custom Formats
• Applying and Customizing Conditional Formats
• Working with Data Bars and Custom Number Formats

Module 15: Recording Macros
• Recording, Running, and Deleting Macros
• Customizing Shortcut Keys and Quick Access Toolbar (QAT)