Mastering Microsoft Excel: From Basics to Advanced Analysis

Chapter 1: Introduction to Excel & Basic Navigation

Ribbon, Quick Access Toolbar, Formula Bar, Status Bar. Workbooks and worksheets.

Introduction to the Excel interface: (30 min)

Basic Navigation: (30 min) span>

Data Entry and Editing:

Basic Formatting:

Saving and Opening Workbooks:

Chapter 2: Working with Data & Basic Formulas

Number, currency, date, time formats and custom formats; creating Excel tables; adding/deleting rows & columns.

Data Types and Formatting:

Creating and Editing Tables:

Basic Formulas (SUM, AVERAGE, COUNT, MIN, MAX) & References:

Basic Error Handling:

Advanced Formatting

Conditional formatting; cell styles, table styles.

Advanced Formatting:

Sorting & Worksheet Management

Sorting & filtering, freezing panes, splitting windows, working with multiple worksheets, data validation.

Sorting and Filtering Data:

Worksheet Management:

Text Functions

LEFT, RIGHT, MID, CONCATENATE, TRIM, UPPER, LOWER, PROPER.

Text Functions: (1 Hour)

Date & Time Functions

TODAY, NOW, DATE, YEAR, MONTH, DAY, WEEKDAY; Text to Columns; Find & Replace.

Date and Time Functions:

Text to Columns and Find & Replace:

Logical Functions

IF, AND, OR, NOT, nested IF statements.

Logical Functions:

Lookup Functions

VLOOKUP, HLOOKUP, INDEX, MATCH, IFERROR.

Lookup Functions:

Conditional Sums & Counts

SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS.

Conditional Sums and Counts:

Named Ranges & Array Formulas

Named Ranges and a short intro to array formulas.

Named Ranges:

Array formulas intro:

Pivot Tables & Analysis

Subtotals, Tables & PivotTables, Slicers & Timelines, Recommended Pivot Tables.

Subtotals: (30 min)

Tables and PivotTables:

Slicers and Timelines:

Recommended Pivot tables:

Chart Types & Elements

Column, Bar, Line, Pie, Scatter charts; adding chart elements (titles, labels, legends); sparklines.

Chart Types:

Chart Customization:

Advanced Charting

Combination Charts, Dynamic Charts, Creating simple Dashboards.

Combination Charts: (1 hour)

Dynamic Charts:

Creating Simple Dashboards:

Import & Export

Import text files, CSV, web data; export data.

Importing Data:

Exporting Data:

Project & Review

Practical case studies and a final project to apply learning.

Case Studies/Projects: (1-2 Hours)