Course Outline

1. Introduction to Data Science Concepts & Dashboarding

• Understanding Desktop Data & Web Data
• Introduction to Server Databases (SQL, MySQL, Oracle)
• Working with Excel Data, Google Sheets & MS Access
• Using Aggregation Functions for Data Analysis
• Dashboard Concepts in Excel
• Basics of Power BI Dashboard & Data Analysis
• Connecting Excel to Server Databases

2. Data Analysis Using PivotTables, Slicers & PivotCharts

• Chart Types, Features, Modification & Formatting
• PivotTables for Calculations & Business Reporting
• Forecasting Using PivotTables
• Advanced PivotTable Reporting Techniques

3. Data Shaping & Cleaning (Tally/Software Data)

• Data Delimitation
• Fixed Width Data Processing
• Case Conversion
• Data Formatting
• Basic Data Mining Techniques

4. Presenting Data with Formatting, Filters & References

• Advanced Conditional Formatting with Formulas
• Advanced Sorting & Filtering
• Managing Hidden/Filtered Data Effectively
• Relative, Mixed & Absolute Cell References

5. Essential Excel Functions & Formulas

• Logical Functions (IF, AND, OR)
• IFS, Nested IF, LOOKUP Functions
• SUMIFS, COUNTIFS, AVERAGEIFS
• Advanced Lookup Functions (VLOOKUP, HLOOKUP, XLOOKUP)
• INDEX-MATCH for Dynamic Search
• Ranking Functions (RANK, SMALL, LARGE)

6. Advanced Reporting & Printing Tools

• Subtotal for Category Reporting
• SUMPRODUCT for Complex Calculations
• Working with Excel Tables
• Custom Printing & Data Delimitation Techniques

7. Working with Date, Time & Timelines

• Date & Time Functions
• Duration & Interval Calculations
• Timeline Usage in Excel Reports

8. Protection & Formula Auditing

• Worksheet & Workbook Protection
• Formula Auditing Tools
• Managing Common Excel Errors (###, N/A, DIV/0!, VALUE!, REF!, NAME?, NULL!)

9. Financial Functions in Excel

• PMT
• PV
• FV
• Additional Financial Function Options

10. Sharing, Viewing & Advanced Excel Tools

• Viewing Multiple Worksheets on One Screen
• Ranking Techniques for Real-World Scenarios
• Data Consolidation
• Introduction to Macros
• Power Query Concepts: Trimming, Merging, Appending
• Excel Shortcuts + 100 Practice Files
• Tips, Tricks & Final Evaluation Test

Learning Outcomes:

• Manage, organize, and structure large Excel datasets
• Perform advanced data analysis for business decisions
• Build interactive dashboards & automate reporting
• Apply data protection & security techniques
• Work confidently with Excel’s advanced tools & functions