Comprehensive Excel

Master Microsoft Excel from the ground up, building from core spreadsheet skills to advanced data analysis, automation, and collaboration. This hands-on course covers formulas, pivot tables, Power Query, Power Pivot, and VBA, so you can organize, analyze, and visualize data with confidence.

What You'll Learn

By the end of this course, you will be able to:

• Navigate Excel's interface and perform data entry, formatting, and calculations
• Apply advanced formulas and functions including lookups, logical expressions, and array formulas
• Create and manage pivot tables and charts for data visualization and analysis
• Use Power Query to connect to external data sources and transform data for analysis
• Build data models and calculated measures using Power Pivot and DAX
• Automate repetitive tasks and create custom functions using macros and VBA
• Protect, share, and collaborate on workbooks using OneDrive and SharePoint

Requirements

• Basic computer literacy
• Familiarity with Excel fundamentals such as navigation and basic data entry

Audience

• Data Analysts and Reporting Professionals
• Business Users looking to move beyond basic Excel skills
• Anyone who works regularly with data and wants to analyze and present it more effectively

Topics

Introduction to Excel

• Overview of Excel's Interface and Core Features
• Understanding Rows, Columns, Cells, and Data Types
• Navigation Techniques and Essential Keyboard Shortcuts

Data Entry and Formatting

• Entering, Selecting, Copying, and Pasting Data
• Formatting Cells Including Text, Numbers, Dates, and Currency
• Adjusting Rows and Columns, Applying Borders and Shading

Formulas and Calculations

• Basic Arithmetic and Introduction to Formulas
• Using SUM, AVERAGE, and AutoSum
• Understanding Absolute vs. Relative Cell References

Working with Worksheets and Workbooks

• Creating, Saving, and Managing Workbooks
• Organizing Multiple Worksheets
• Configuring Page Layout and Print Settings

Charts and Data Visualization

• Creating and Formatting Basic Charts Including Bar, Line, and Pie
• Advanced Chart Customization and Combination Charts
• Adding Trendlines, Secondary Axes, and Dynamic Chart Ranges

Sorting, Filtering, and Tables

• Sorting Data by Text, Numbers, and Dates
• Applying Filters and Managing Structured Table Ranges
• Using Structured References Within Tables

Conditional Formatting and Data Validation

• Applying and Customizing Conditional Formatting Rules
• Setting Data Entry Rules, Drop-Down Lists, and Error Messages

Advanced Formulas and Functions

• Logical Functions Including IF, AND, and OR
• Text Functions Including LEFT, RIGHT, MID, and CONCATENATE
• Lookup Functions Including VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP
• Statistical Functions Including MIN, MAX, COUNT, and AVERAGEIF
• Nested IF Statements, Array Formulas, and SUMPRODUCT

Pivot Tables and Pivot Charts

• Creating and Configuring Pivot Tables for Data Analysis
• Using Pivot Charts for Visual Representation
• Grouping, Filtering, and Using Slicers and Timelines
• Calculated Fields, Items, and Pivot Table Relationships

Advanced Data Analysis Tools

• Consolidating Data Across Multiple Sources
• What-If Analysis with Goal Seek and Scenario Manager
• Solving Optimization Problems with the Solver Add-In

Power Query

• Importing and Connecting to External Data Sources
• Cleaning and Transforming Data in Power Query
• Automating Data Preparation Workflows

Power Pivot and DAX

• Building Data Models and Defining Relationships
• Creating Calculated Columns and Measures with DAX
• Extending Pivot Tables with Power Pivot

Automation with Macros and VBA

• Recording and Running Simple Macros
• Writing Custom Macros to Automate Repetitive Tasks
• Creating User-Defined Functions
• Debugging and Handling Errors in VBA
• Customizing Charts with VBA

Data Protection

• Locking Cells and Protecting Worksheets
• Password-Protecting Workbooks

Collaboration and Sharing

• Co-Authoring and Sharing Workbooks with Others
• Tracking Changes and Managing Versions
• Integrating Excel with OneDrive and SharePoint