About Lesson
-
Introduction to Excel
- Excel Interface Overview
- Navigating the Ribbon
- Understanding Worksheets and Workbooks
- Creating and Saving Workbooks
- Data Entry and Editing
- Customizing the Quick Access Toolbar
- Excel File Formats
-
Excel Formula
- Basic Arithmetic Formulas (SUM, SUBTRACT, MULTIPLY, DIVIDE)
- AutoSum Function, Cell References (Relative, Absolute, Mixed)
- Common Statistical Functions (AVERAGE, MEDIAN, MODE)
- Text Formulas (CONCATENATE, LEFT, RIGHT)
- Date Formulas (TODAY, NOW, DATE)
- Logical Formulas (IF, AND, OR)
-
Formula Based Formatting
- Conditional Formatting Rules
- Highlighting Cells Based on Values
- Data Bars, Color Scales, and Icon Sets
- Custom Conditional Formatting
- Managing Conditional Formatting Rules
- Using Formulas in Conditional Formatting
- Applying Conditional Formatting to Pivot Tables
-
Conditional Statements & Logical Operators
- IF Statements, Nested IF Statements, AND, OR Operators, NOT Operator
- Combining Multiple Conditions
- Using IF with Text, Numbers, and Dates
- Conditional Formatting with Logical Operators
-
Text Functions
- CONCATENATE (or CONCAT), LEFT, RIGHT, MID, LEN, and TRIM
- UPPER, LOWER, PROPER
- SUBSTITUTE and REPLACE
- FIND and SEARCH
- TEXT Function for Formatting Numbers and Dates
-
Date and Time Functions
- TODAY and NOW
- DATE and TIME
- DAY, MONTH, YEAR, HOUR, MINUTE, SECOND
- DATEDIF, NETWORKDAYS, EDATE, and EOMONTH
-
Excel Tables
- Creating and Formatting Excel Tables
- Table Styles and Options
- Sorting and Filtering Tables
- Adding and Removing Table Rows/Columns
- Structured References
- Table Calculations and Totals
- Converting Table to Range
-
Basic and Advanced Table Operations
- Creating Dynamic Tables
- Using Table Formulas
- Advanced Filtering Techniques
- Using Slicers with Tables
- Advanced Sorting Techniques
- Working with Subtotals
- Merging and Splitting Tables
-
Data Pivoting with Different Examples
- Creating Pivot tables, PivotTable Fields and Areas
- Grouping Data in PivotTables
- Pivotable Calculations (Sum, Count, Average)
- Creating Pivot Charts
- Filtering Pivot tables with Slicers and Timelines
- Advanced Pivotable Techniques (Calculated Fields, Custom Calculations)
-
Cell Reference Functions
- Understanding Cell References
- Using Relative References
- Using Absolute References
- Mixed References
- INDIRECT Function
- OFFSET Function
- Linking Cells Between Sheets and Workbooks
-
LookUp and VLookUp
- Introduction to LookUp Functions
- VLOOKUP Basics
- HLOOKUP Basics
- Using VLOOKUP with Exact and Approximate Match
- Combining VLOOKUP with Other Functions
- Common VLOOKUP Errors and Fixes
- Alternatives to VLOOKUP (INDEX and MATCH)
-
Excel Data Visualization
- Creating Basic Charts (Column, Line, Pie)
- Customizing Chart Elements (Titles, Legends, Labels)
- Using Sparklines for Miniature Charts
- Creating Combo Charts
- Using Conditional Formatting for Data Visualization
- Creating Heat Maps
- Using Data Bars and Color Scales
-
Introduction to Power Query
- What is a Power Query?
- Power Query Interface Overview
- Loading Data into Power Query
- Basic Data Transformation (Filter, Sort, Remove Columns)
- Combining Data from Multiple Sources
- Data Profiling in Power Query
- Saving and Loading Data to Excel
-
Power Query Editor
- Navigating the Power Query Editor
- Applying Basic Transformations (Replace Values, Remove Duplicates)
- Advanced Transformations (Pivot/Unpivot, Group By)
- Creating Custom Columns
- Merging Queries
- Appending Queries
- Managing Query Steps
-
Basic Charts in Excel
- Creating Column Charts
- Creating Line Charts
- Creating Pie Charts
- Creating Bar Charts
- Creating Area Charts
- Creating Scatter Plots
- Customizing Chart Types and Styles
-
Formatting Charts
- Creating Column Charts
- Creating Line Charts
- Creating Pie Charts
- Creating Bar Charts
- Creating Area Charts
- Creating Scatter Plots
- Customizing Chart Types and Style
-
Creating Reports in Excel with Dataset
- Data Preparation and Cleaning
- Using PivotTables for Reports
- Combining Multiple Data Sources
- Adding Visual Elements (Charts, Sparklines)
- Creating Interactive Dashboards
- Automating Report Updates
- Distributing and Sharing Reports
