Microsoft Excel (Basic, Intermediate, and Advanced)

• Basic computer knowledge
• Familiarity with Excel basics

Audience

• Data analysts

Microsoft Excel is a powerful spreadsheet program developed by Microsoft, widely used for organizing, analyzing, and visualizing data.

This instructor-led, live training (online or onsite) is aimed at beginner-level to advanced-level data analysts who wish to use Excel to perform a variety of tasks such as data entry, formula creation, data analysis, and advanced automation.

By the end of this training, participants will be able to:

• Navigate Excel’s interface and perform basic data entry, formatting, and calculations.
• Apply advanced formulas, functions, and conditional formatting for data analysis.
• Create and manage pivot tables and charts for data visualization.
• Use tools like Power Query, Power Pivot, and perform data analysis.
• Automate tasks using macros and VBA to streamline workflows.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction to Excel

• Overview of Excel and its interface
• Understanding rows, columns, and cells
• Navigation and basic shortcuts

Basic Data Entry and Editing

• Entering data in cells
• Selecting, copying, pasting, and formatting cells
• Basic text formatting (font, size, color, etc.)
• Understanding data types (text, numbers, dates)

Simple Calculations and Formulas

• Basic arithmetic operations (addition, subtraction, multiplication, division)
• Introduction to formulas (e.g., SUM, AVERAGE)
• AutoSum feature
• Absolute vs. relative cell references

Working with Worksheets and Workbooks

• Creating, saving, and opening workbooks
• Managing multiple worksheets (rename, delete, insert, move)
• Basic print settings (page layout, print area)

Basic Data Formatting

• Formatting cells (number, date, currency)
• Adjusting rows and columns (width, height, hide/unhide)
• Cell borders and shading

Introduction to Charts and Graphs

• Creating simple charts (bar, line, pie)
• Formatting and editing charts

Basic Data Sorting and Filtering

• Sorting data by text, numbers, or dates
• Simple data filters

Advanced Formulas and Functions

• Using logical functions (IF, AND, OR)
• Text functions (LEFT, RIGHT, MID, LEN, CONCATENATE)
• Lookup functions (VLOOKUP, HLOOKUP)
• Math & statistical functions (MIN, MAX, COUNT, COUNTA, AVERAGEIF)

Working with Tables and Ranges

• Creating and managing tables
• Sorting and filtering data in tables
• Structured references in tables

Conditional Formatting

• Applying rules for conditional formatting
• Customizing conditional formats (data bars, color scales, icon sets)

Data Validation

• Setting data entry rules (e.g., drop-down lists, number limits)
• Error messages for invalid data entries

Data Visualization with Charts and Graphs

• Advanced chart formatting and customization
• Creating combination charts (e.g., bar and line in one chart)
• Adding trendlines and secondary axes

Pivot Tables and Pivot Charts

• Creating pivot tables for data analysis
• Using pivot charts for visual representation
• Grouping and filtering in pivot tables
• Slicers and timelines for better data interaction

Data Protection

• Locking cells and worksheets
• Password-protecting workbooks

Basic Macros

• Introduction to recording simple macros
• Running and editing macros

Advanced Formulas and Functions

• Nested IF statements
• Advanced lookup functions (INDEX, MATCH, XLOOKUP)
• Array formulas and functions (SUMPRODUCT, TRANSPOSE)

Advanced Pivot Tables

• Calculated fields and items in pivot tables
• Creating and managing pivot table relationships
• Using slicers and timelines in depth

Advanced Data Analysis Tools

• Data consolidation
• What-If analysis (Goal Seek, Scenario Manager)
• Solver add-in for optimization problems

Power Query

• Introduction to Power Query for data import and transformation
• Connecting to external data sources (e.g., databases, web)
• Data cleaning and transformation in Power Query

Power Pivot

• Creating data models and relationships
• Calculated columns and measures using DAX (Data Analysis Expressions)
• Advanced pivot tables with Power Pivot

Advanced Charting Techniques

• Creating dynamic charts with formulas and data ranges
• Customizing charts with VBA

Automation with Macros and VBA

• Introduction to Visual Basic for Applications (VBA)
• Writing custom macros to automate repetitive tasks
• Creating user-defined functions (UDFs)
• Debugging and error handling in VBA

Collaboration and Sharing

• Sharing workbooks with others (co-authoring)
• Tracking changes and version control
• Using Excel with OneDrive and SharePoint for collaboration

Summary and Next Steps