Data Analytics

• Complete Excel 2024 course structure from beginner to expert level

Chapter 1: Introduction to Excel 2024

• What is Microsoft Excel and why it matters
• What's new in Excel 2024
• Installing and setting up Excel 2024
• Understanding the Excel interface (Ribbon, Quick Access Toolbar, Formula Bar, Sheet Tabs)
• Workbooks vs. Worksheets
• Navigating cells, rows, and columns
• Saving, opening, and closing files (.xlsx, .csv, .xlsm formats)

Chapter 2: Working with Data

• Entering text, numbers, and dates
• Editing and deleting cell content
• Copy, cut, paste, and Paste Special
• Autofill and Flash Fill
• Find & Replace
• Undo and Redo
• Data types and cell formats (General, Number, Currency, Date, Text)

Chapter 3: Formatting Cells & Worksheets

• Formatting numbers, dates, and text
• Font styles, sizes, colors, and alignment
• Cell borders and background colors
• Merge & Center
• Conditional Formatting basics
• Row height and column width
• Freeze panes and split views
• Headers and footers

Chapter 4: Introduction to Formulas

• How formulas work in Excel
• Arithmetic operators (+, -, *, /, ^)
• Cell references: Relative, Absolute ($), and Mixed
• Order of operations
• Common formula errors (#DIV/0!, #VALUE!, #REF!, #NAME?)
• Tracing and debugging errors

Chapter 5: Essential Functions

• SUM, AVERAGE, MIN, MAX, COUNT, COUNTA
• IF, AND, OR, NOT
• ROUND, ROUNDUP, ROUNDDOWN
• TEXT, UPPER, LOWER, PROPER, LEN, TRIM, CONCATENATE
• TODAY, NOW, DATE, YEAR, MONTH, DAY
• VLOOKUP and HLOOKUP

Chapter 6: Intermediate Functions

• XLOOKUP (Excel 2024 enhanced)
• INDEX & MATCH
• IFERROR and IFNA
• COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
• CHOOSE, SWITCH
• LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE
• DATEDIF, NETWORKDAYS, WORKDAY, EDATE

Chapter 7: Advanced Functions & Array Formulas

• Dynamic Arrays and Spill Ranges
• FILTER, SORT, SORTBY, UNIQUE
• SEQUENCE, RANDARRAY
• TEXTSPLIT, TEXTBEFORE, TEXTAFTER (Excel 2024)
• LAMBDA and custom functions
• LET function
• BYROW, BYCOL, MAP, REDUCE, SCAN
• Array formulas with Ctrl+Shift+Enter

Chapter 8: Managing Large Datasets

• Sorting data (single and multi-level)
• Filtering data with AutoFilter
• Advanced Filter
• Removing duplicates
• Text to Columns
• Data Validation (dropdown lists, restrictions, custom rules)
• Named Ranges

Chapter 9: Tables & Structured References

• Creating and formatting Excel Tables
• Table styles and options (Total Row, Banded Rows)
• Structured references in formulas
• Adding and removing columns/rows in tables
• Slicers for tables

Chapter 10: Importing & Exporting Data

• Importing data from CSV, TXT, and databases
• Getting data from the web
• Power Query introduction (Get & Transform)
• Cleaning and transforming data with Power Query
• Exporting to PDF, CSV, and other formats
• Connecting to external data sources

Chapter 11: Charts & Graphs

• Creating basic charts (Bar, Column, Line, Pie, Area)
• Chart elements (Title, Legend, Axes, Data Labels)
• Formatting and customizing charts
• Combo charts
• Sparklines (Line, Column, Win/Loss)
• Dynamic charts with named ranges

Chapter 12: Advanced Charts

• Waterfall and Funnel charts
• Histogram and Box & Whisker charts
• Sunburst and Treemap charts
• Map charts (geographic data)
• Creating interactive dashboards with charts
• Chart templates

Chapter 13: PivotTables

• What is a PivotTable and when to use it
• Creating your first PivotTable
• Rows, Columns, Values, and Filters
• Sorting and filtering in PivotTables
• Value field settings (Sum, Count, Average, %)
• Grouping data (dates, numbers, text)
• Calculated fields and calculated items

Chapter 14: Advanced PivotTables & PivotCharts

• Slicers and Timelines
• PivotCharts
• Multiple consolidated ranges
• Power Pivot introduction
• Data Model and relationships
• DAX basics (CALCULATE, SUMX, RELATED)

Chapter 15: What-If Analysis & Forecasting

• Goal Seek
• Scenario Manager
• Data Tables (one-variable and two-variable)
• Solver add-in
• Forecasting with FORECAST.ETS
• Trendlines in charts

Chapter 16: Macros & VBA Introduction

• What are Macros and why use them
• Recording a macro
• Running and editing macros
• Introduction to the VBA Editor (IDE)
• Writing basic VBA code
• Variables, loops, and conditionals in VBA
• Creating simple automation scripts
• Assigning macros to buttons

Chapter 17: Advanced VBA & Automation

• Working with Workbooks and Worksheets in VBA
• Manipulating cells and ranges via code
• UserForms and dialog boxes
• Error handling in VBA
• Creating custom functions (UDFs)
• Automating reports and emails

Chapter 18: Collaboration & Sharing

• Sharing workbooks on OneDrive and SharePoint
• Co-authoring in real time
• Comments and Notes
• Track Changes
• Protecting sheets and workbooks (passwords)
• Managing permissions and access

Chapter 19: Excel & Microsoft 365 Integration

• Excel with Teams, Outlook, and Word
• Power Automate with Excel
• Using Excel on mobile and web
• Copilot in Excel 2024 (AI-powered features)
• Excel Online limitations vs. Desktop

Chapter 20: Financial Modeling with Excel

• Building a basic financial model
• Financial functions: PMT, PV, FV, NPV, IRR, RATE
• Amortization schedules
• Budget vs. Actual analysis
• Sensitivity analysis

Chapter 21: Data Analysis & Statistics

• Descriptive statistics with the Analysis ToolPak
• Regression analysis
• Histogram and frequency distribution
• Correlation and Covariance
• Sampling and hypothesis testing basics

Chapter 22: Building Professional Dashboards

• Dashboard design principles
• KPI cards and dynamic metrics
• Combining charts, slicers, and tables
• Form controls (dropdowns, scrollbars, buttons)
• Making dashboards printable and presentation-ready