Data analytics · Course by Ravindra Bagale
Excel
Microsoft Excel: Complete Study Guide: From Beginner to Job-Ready
Excel basics and references, data entry tools, formulas and functions, lookups (VLOOKUP to XLOOKUP), data cleaning A–Z, Tables, PivotTables, charts, dynamic arrays, Power Query, what-if analysis, Macros and VBA, dashboards, a final project and interview questions.
20 chapters198 concepts
Course outline
Click a chapter to see its concepts. Each concept has its own page.
How to Read This Book – start here
Chapters 1–10
3. Formulas and Functions 17 concepts
- 3.1 Formula Basics and Operators
- 3.2 SUM, SUMIF and SUMIFS
- 3.3 COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS
- 3.4 AVERAGE, AVERAGEIF(S), MINIFS and MAXIFS
- 3.5 ROUND Family and RANK
- 3.6 Text Functions: LEFT, RIGHT, MID, LEN, FIND and SEARCH
- 3.7 SUBSTITUTE, REPLACE, TRIM, CLEAN and Case Functions
- 3.8 Joining Text: &, CONCAT, TEXTJOIN and TEXT
- 3.9 TEXTBEFORE, TEXTAFTER and TEXTSPLIT
- 3.10 Date Functions: TODAY, NOW, DATE, DAY/MONTH/YEAR, EOMONTH, EDATE
- 3.11 Working Days, DATEDIF and WEEKDAY
- 3.12 Time Calculations
- 3.13 IF, Nested IF and IFS
- 3.14 AND, OR, NOT and SWITCH
- 3.15 IFERROR and IFNA
- 3.16 SUMPRODUCT
- 3.17 Error Types and How to Fix Them
4. Lookup Functions 10 concepts
- 4.1 VLOOKUP with Exact Match
- 4.2 VLOOKUP Approximate Match and the Column-Index Trap
- 4.3 HLOOKUP
- 4.4 INDEX and MATCH
- 4.5 Two-way Lookup (Row and Column)
- 4.6 XLOOKUP Basics and if_not_found
- 4.7 XLOOKUP Match Modes, Search Modes and Multiple Returns
- 4.8 XMATCH
- 4.9 Approximate Lookup: Delivery Fee Slabs
- 4.10 Common Lookup Errors and Fixes
5. Data Cleaning A–Z 18 concepts
- 5.1 Duplicates
- 5.2 Blank Cells
- 5.3 Extra Spaces and Non-printable Characters
- 5.4 Inconsistent Case
- 5.5 Inconsistent City Spellings
- 5.6 Numbers Stored as Text
- 5.7 Text Dates and Mixed Date Formats
- 5.8 Splitting Columns
- 5.9 Merging Columns
- 5.10 Phone Numbers
- 5.11 E-mail Addresses
- 5.12 Pincodes and Codes with Leading Zeros
- 5.13 Extracting Parts of Text
- 5.14 Removing Unwanted Characters
- 5.15 Outliers
- 5.16 Error Values in Data
- 5.17 Unpivot: Wide to Long (Concept)
- 5.18 End-to-End: Cleaning a Messy Blinkit Export
7. PivotTables and PivotCharts 11 concepts
- 7.1 Creating a PivotTable
- 7.2 Field Areas: Rows, Columns, Values and Filters
- 7.3 Summarize Values By
- 7.4 Show Values As
- 7.5 Grouping Dates and Numbers
- 7.6 Calculated Fields and Calculated Items
- 7.7 Sorting, Filtering and Top 10
- 7.8 Slicers and Timelines
- 7.9 Report Connections, Refresh and Data Source
- 7.10 GETPIVOTDATA
- 7.11 PivotCharts
8. Charts 16 concepts
- 8.1 Creating and Formatting a Chart
- 8.2 Column and Bar Charts
- 8.3 Line and Area Charts
- 8.4 Pie and Doughnut Charts
- 8.5 Combo Chart with a Secondary Axis
- 8.6 Scatter Chart
- 8.7 Histogram and Box & Whisker
- 8.8 Waterfall and Funnel
- 8.9 Treemap and Sunburst
- 8.10 Map Chart
- 8.11 Sparklines
- 8.12 Choosing the Right Chart
- 8.13 Dynamic Chart from a Table
- 8.14 Drop-down Driven Dynamic Chart (INDEX / XLOOKUP)
- 8.15 Named Ranges with OFFSET (Rolling Charts)
- 8.16 Checkbox to Show/Hide a Series
10. Power Query in Excel 9 concepts
Chapters 11–20
12. Macros and VBA 16 concepts
- 12.1 Developer Tab, Macro Security and .xlsm
- 12.2 Recording a Macro: Absolute vs Relative
- 12.3 Running Macros: Button, Shortcut and the Macros Dialog
- 12.4 The VBA Editor (VBE)
- 12.5 Sub Procedures
- 12.6 Variables and Data Types
- 12.7 Range, Cells and Worksheets
- 12.8 MsgBox and InputBox
- 12.9 If and Select Case
- 12.10 Loops: For, For Each and Do
- 12.11 Finding the Last Row (and Column)
- 12.12 Practical Macros on Our Data
- 12.13 User-Defined Functions: a Delivery Fee Function
- 12.14 Error Handling with On Error
- 12.15 Debugging: F8, Breakpoints, Immediate and Locals Windows
- 12.16 The Personal Macro Workbook
13. Excel Dashboards 8 concepts
- 13.1 Planning: Audience, Questions and KPIs
- 13.2 Structuring the Workbook: Data, Calc, Dashboard
- 13.3 PivotTables that Feed the Dashboard
- 13.4 KPI Cards
- 13.5 Charts for the Dashboard
- 13.6 Slicers and a Timeline for All Pivots
- 13.7 Layout and Design Principles
- 13.8 Refresh, Finishing Touches and a Checklist
14. Protection, Sharing and Printing 11 concepts
- 14.1 Locked Cells and Protect Sheet
- 14.2 Hiding Formulas and Allow Edit Ranges
- 14.3 Protect Workbook Structure vs Encrypt with Password
- 14.4 Print Area and Page Breaks
- 14.5 Page Setup: Orientation, Scaling and Margins
- 14.6 Headers and Footers
- 14.7 Print Titles (Repeat Header Rows)
- 14.8 Exporting to PDF
- 14.9 Sharing and Co-authoring
- 14.10 Comments vs Notes
- 14.11 Version History
16. Practice Exercises with Answer Hints 15 concepts
- 16.1 Modules 1–2: Basics and Data Entry
- 16.2 Module 3: Formulas and Functions
- 16.3 Module 4: Lookups
- 16.4 Module 5: Data Cleaning
- 16.5 Module 6: Tables, Sorting and Filtering
- 16.6 Module 7: PivotTables
- 16.7 Module 8: Charts
- 16.8 Module 9: Dynamic Arrays
- 16.9 Module 10: Power Query
- 16.10 Module 11: What-If Analysis
- 16.11 Module 12: Macros and VBA
- 16.12 Module 13: Dashboards
- 16.13 Module 14: Protection and Printing
- 16.14 Mixed Scenario Exercises
- 16.15 Challenge Exercises
- 20. Glossary
Appendices
Join an online / offline batch — Enquire now
Ravindra Bagale runs online and offline batches for AWS Cloud, DevOps, Power BI, Excel, Data Analytics, Data Science and Cyber Security.