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
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
