Module 1: Excel Essentials & Smart Functions
- Introduction to Data Analytics: What is Data, Data Analytics and it’s types?
- Excel Refresher: Interface, ribbons, data types, cell formatting, Formula Bar
- Productivity Tools: Keyboard Shortcuts, Filter, Cell referencing, templates
- Conditional Formatting: Cell highlights, Data Bars, color scales, icon sets
- Useful Tools: Series & Flash Fill, Justify, Remove Duplicates, Text to Columns, Consolidate, Hyperlinks, Objects, Checkbox, Slicers & Timelines
- Data Validation: Drop-down lists, Date, Time, Whole Number, Text Length
Module 2: Advanced Formulas & Charts
- Important Formulas: Statistical, Lookup & Reference, Logical & Conditional, Text Manipulation, Date & Time, Maths & Trigonometric
- Charts: Column, Bar, Line, Area, Pie, Doughnut, Treemap, Sunburst, Histogram, Pareto Chart, Box & Whisker, Scatter, Bubble, Waterfall, Funnel, Stock, Radar and Combo Chart.
Module 3: Power Query & Power Pivot with Data Modeling
- Introduction to Power Query:
-
- Connecting to external Data Sources (Excel, CSV, Web)
- Data Cleaning: Remove Rows, Columns, Replace Values, Change Data Types, Split Column, Sort Column, Conditional Column, Column Profile & Quality
- Merge & Append Queries
- Advanced Editor
- Power Pivot Basics: Creating a Data Model, Relationship between tables
- Protection and Security: Lock cells, sheet/workbook protectio
Module 4: Pivot Tables, Dashboard & Data Analysis Tools
- Pivot Tables: PivotTables, Grouping, Sorting, Filtering, Value Field Settings
- Pivot Charts: Interactive Dashboard Design Concepts with Slicers and Timelines
- Quick Analysis Tool & Sparklines
- What-If Analysis: Scenario Manager, Goal Seek, Data Table
- Optimization: Solver, Forcast Sheet
