Data analytics · Course by Ravindra Bagale
Excel Course
Microsoft Excel: Complete Study Guide: From Beginner to Job-Ready
A free online Excel course for beginners, from basics and formulas to PivotTables, dashboards and VBA. The lessons are in simple English with Marathi phrases, and Ravindra Bagale's online and offline Excel classes are taught in Marathi and Hindi.
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.
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
Frequently asked questions
Is this Excel course free?
Yes. All 20 chapters and 198 concept pages are free to read online, and you do not need an account.
Is the Excel course in Marathi?
The lessons on this website are written in simple English with some Marathi phrases in the trainer's voice. In his online and offline classes, Ravindra Bagale teaches in Marathi, with some Hindi.
What does the Excel course cover?
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.
Is this Excel course for beginners?
Yes. It goes from beginner to job-ready, starting with 1. Excel Basics.
Does it cover VLOOKUP and XLOOKUP?
Yes. 4. Lookup Functions covers the lookup functions, from VLOOKUP to XLOOKUP.
Are there Excel practice and interview questions?
Yes: 16. Practice Exercises with Answer Hints, 17. Interview Questions and Answers and 18. Interview Questions Asked in MNC Interviews. There is also a final project: 15. Final Project: Blinkit Maharashtra Monthly Report.
How do I join online or offline Excel classes?
Send an enquiry on the enquiry page, or call / WhatsApp 7690071001. Ravindra Bagale runs online and offline batches for Excel, Power BI and Data Analytics.
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.