1. Introduction to Microsoft Excel
• Overview of Excel Interface (Ribbon, Formula Bar, Worksheet Tabs)
• Types of Excel Workbooks and Worksheets
• Basic Terminology (Cells, Rows, Columns)
• Understanding Excel File Formats (.xlsx, .xls, .csv, .xlsm)
2. Creating and Managing Spreadsheets
• Creating New Workbooks and Worksheets
• Opening, Saving, and Closing Workbooks
• Saving Files in Different Formats
• Working with Multiple Worksheets and Workbooks
• Navigating in Large Workbooks (Freeze Panes, Split, Navigation Shortcuts)
3. Data Entry and Formatting
• Entering Text, Numbers, and Dates in Cells
• Autofill and Flash Fill
• Formatting Cells (Font, Size, Color, Borders, Background)
• Number Formats (Currency, Percentage, Date, Time)
• Custom Number Formats
• Adjusting Row Height and Column Width
4. Data Organization and Sorting
• Sorting Data by Columns (Ascending, Descending)
• Sorting by Multiple Columns
• Custom Sorting (Using Custom Lists)
• Using Filter to Display Specific Data
• AutoFilter and Advanced Filters
• Grouping Data
5. Basic Formulas and Functions
• Introduction to Formulas (Basic Arithmetic)
• Using SUM, AVERAGE, MIN, MAX, COUNT
• Relative, Absolute, and Mixed Cell References
• Using Parentheses for Formula Order
• Using AutoSum for Quick Calculations
6. Advanced Formulas and Functions
• Logical Functions: IF, AND, OR, NOT
• Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH
• Text Functions: CONCATENATE, LEFT, RIGHT, MID, TRIM
• Date Functions: DATE, TODAY, NETWORKDAYS
• Statistical Functions: COUNTIF, SUMIF, AVERAGEIF, FREQUENCY
• Mathematical Functions: ROUND, CEILING, FLOOR, POWER
7. Working with Tables
• Creating and Formatting Tables (Insert Table)
• Table Styles and Formatting
• Sorting and Filtering within Tables
• Structured References in Tables
• Table Data Validation
8. Data Analysis Tools
• Conditional Formatting (Color Scales, Icon Sets, Data Bars)
• Using Pivot Tables for Data Summarization
• Pivot Charts (Creating and Customizing)
• Data Validation (Input Restrictions, Drop-down Lists)
• Goal Seek and Solver for Optimization
• Using the Analysis Toolpak (Regression, Descriptive Statistics)
9. Charts and Graphs
• Types of Charts (Column, Line, Pie, Bar, Scatter, Area)
• Creating and Modifying Charts
• Chart Elements (Title, Legend, Axes, Data Labels)
• Customizing Chart Styles and Layouts
• Using Combo Charts for Mixed Data Types
10. Advanced Charting Techniques
• Adding Trendlines to Charts
• Creating Sparklines (Mini-Charts within Cells)
• Creating Waterfall Charts
• Creating Dynamic Charts with Formulas
• Using Secondary Axes for Complex Data
11. Data Consolidation and Pivoting
• Combining Data from Multiple Worksheets (Consolidate Tool)
• Creating and Modifying Pivot Tables
• Grouping Data in Pivot Tables (By Date, Text, Number)
• Slicing Pivot Table Data with Slicers
• Pivot Table Calculated Fields and Items
12. Text Manipulation and Data Cleaning
• Text-to-Columns (Split Data into Columns)
• Removing Duplicates
• Using Find and Replace for Data Editing
• Removing Extra Spaces (TRIM, CLEAN)
• Text Formatting and Case Functions
13. Using Excel for Financial and Accounting Tasks
• Creating Budgets, Expense Sheets, and Financial Models
• Loan Amortization Schedules
• Using Excel for Invoice and Tax Calculations
• Forecasting and Trend Analysis
• Financial Functions: NPV, IRR, PMT, FV, PV
14. Working with External Data
• Importing Data from Text Files (CSV, TXT)
• Importing Data from External Databases (SQL, Access)
• Linking Data from Other Workbooks
• Connecting Excel to Web Data (Live Stock Prices, Currency Rates)
15. Collaborating and Sharing Excel Files
• Protecting and Locking Worksheets (Password Protection)
• Sharing Workbooks and Tracking Changes
• Using Comments and Notes for Collaboration
• Merging Workbooks and Resolving Conflicts
• Using OneDrive and Google Sheets for Cloud Sharing
16. Excel Automation with Macros
• Recording and Running Macros
• Writing Simple VBA (Visual Basic for Applications) Code
• Assigning Macros to Buttons
• Automating Repetitive Tasks with Macros
17. Excel Security and Privacy
• Protecting Excel Files with Passwords
• Data Encryption and Privacy Settings
• Managing Permissions and User Access
• Disabling Macros from Unknown Sources
18. Advanced Data Analysis
• Scenario Manager for Multiple Data Scenarios
• Data Tables for Sensitivity Analysis
• Creating and Using Dynamic Dashboards
• Advanced Conditional Formatting for Data Analysis
• Statistical Analysis: Regression, Correlation, Descriptive Statistics