Excel Learning Plan
1. Excel Basics (Fundamentals)
- Understanding Excel interface (Ribbon, Sheets, Cells, Rows, Columns)
- Formatting cells (number formats, conditional formatting, themes, styles)
- Data entry and management (sorting, filtering, data validation, drop-down lists)
- Freezing panes, hiding/unhiding rows/columns
2. Essential Excel Formulas & Functions
- Basic Functions: SUM(), AVERAGE(), MIN(), MAX(), COUNT(), COUNTA()
- Logical Functions: IF(), IFS(), AND(), OR(), NOT()
- Lookup & Reference: VLOOKUP(), HLOOKUP(), INDEX(), MATCH(), XLOOKUP()
- Text Functions: LEFT(), RIGHT(), MID(), LEN(), TRIM(), CONCATENATE(), TEXTJOIN(),
SUBSTITUTE(), FIND(), SEARCH()
- Date & Time Functions: TODAY(), NOW(), YEAR(), MONTH(), DAY(), EOMONTH(),
NETWORKDAYS()
- Error Handling: IFERROR(), IFNA(), ISERROR()
- Array Functions: TRANSPOSE(), SEQUENCE(), FILTER(), SORT(), UNIQUE()
3. Data Cleaning & Preparation
- Removing duplicates
- Splitting text using TEXT TO COLUMNS and LEFT(), RIGHT(), MID()
- Using TRIM(), CLEAN() to remove spaces and unwanted characters
- Filling missing values
- Removing blank cells and errors
4. Data Visualization (Charts & Graphs)
- Creating different charts: Column, Bar, Pie, Line, Scatter, Combo charts
- Conditional formatting with color scales and data bars
- Using Sparklines for mini-charts inside cells
- Creating interactive charts using slicers and drop-downs
5. PivotTables & PivotCharts
- Creating and customizing PivotTables
- Adding calculated fields in PivotTables
- Using Slicers and Timelines for interactive reports
- PivotCharts for dynamic data visualization
6. Data Analysis with Excel Tools
- What-If Analysis: Goal Seek, Data Tables, Scenario Manager
- Solver: Optimizing values based on conditions
- Data Analysis ToolPak: Regression, Descriptive Statistics, Correlation
7. Power Query (Get & Transform Data)
- Importing and cleaning data from multiple sources
- Merging and appending datasets
- Unpivoting and transforming data
- Automating data preparation tasks
8. Power Pivot & DAX (Advanced Data Modeling)
- Creating relationships between tables
- Writing DAX functions (SUMX(), CALCULATE(), RELATED(), ALL())
- Using Measures and Calculated Columns
9. Automation with Macros & VBA (Optional but Powerful)
- Recording macros to automate repetitive tasks
- Writing basic VBA scripts for custom functions and automation
10. Advanced Techniques & Best Practices
- Structuring large datasets efficiently
- Using Named Ranges for better formula management
- Creating dashboards for reports
- Understanding best practices for performance optimization
