Currently Empty: ₹0.00
Curriculum
- 18 Sections
- 73 Lessons
- 10 Weeks
Expand all sectionsCollapse all sections
- Advanced Data Organization & Management5
- 1.1Excel Tables: Converting data to tables, structured references, table styles, slicers for interactive filtering.
- 1.2Named Ranges: Defining, managing, and using named ranges in formulas for readability and efficiency.
- 1.3Advanced Sorting & Filtering: Multi-level sorting, custom lists, advanced filters with complex criteria.
- 1.4Data Validation: Creating dropdown lists, restricting data entry, input messages, and error alerts.
- 1.5Conditional Formatting: Advanced rules, icon sets, data bars, color scales for visual insights.
- Essential Advanced Formulas & Functions1
- Lookup & Reference Functions7
- 3.1XLOOKUP (Primary Focus): Versatile lookup for exact, approximate, and multiple matches, wildcards, and search modes.
- 3.2INDEX & MATCH: Understanding its power for complex lookups (as an alternative/complement to XLOOKUP).
- 3.3VLOOKUP & HLOOKUP: Review of limitations and appropriate use cases.
- 3.4Text Functions: CONCAT, TEXTJOIN, LEFT, RIGHT, MID, FIND, SEARCH, LEN, TRIM, CLEAN, SUBSTITUTE, REPLACE for text manipulation.
- 3.5Date & Time Functions: DATEDIF, EOMONTH, WORKDAY, NETWORKDAYS, YEARFRAC for time-based calculations.
- 3.6Statistical & Aggregation Functions: SUMIFS, COUNTIFS, AVERAGEIFS (multiple criteria).
- 3.7Error Handling Functions: IFERROR, ISNA for robust formula design.
- Data Analysis & Visualization for Impact0
- PivotTables3
- What-If Analysis5
- 6.1Goal Seek: Finding input values to achieve a target output.
- 6.2Scenario Manager: Comparing different sets of input values and their impact on outcomes.
- 6.3Data Tables: Analyzing the impact of one or two variables on formulas.
- 6.4Solver: Introduction to optimization problems, solving for maximums, minimums, and specific values with constraints.
- 6.5Data Consolidation: Combining data from multiple worksheets or workbooks.
- Advanced Charting & Data Visualization9
- 7.1Chart Types: Choosing the right chart for your data (e.g., Waterfall, Combo, Sparklines, Scatter Plots for correlation).
- 7.2Customizing Charts: Advanced formatting, dynamic chart titles, adding trendlines, error bars.
- 7.3Dashboard Design Principles: Best practices for creating clear, concise, and impactful dashboards.
- 7.4Interactive Dashboards: Combining charts, slicers, and formulas to create dynamic reports.
- 7.5Introduction to Power Query (Get & Transform Data)
- 7.6What is Power Query? Overview of its capabilities for data import, cleaning, and transformation.
- 7.7Connecting to Various Data Sources: Files (CSV, Excel), Folders, Databases, Web.
- 7.8Basic Transformations in Power Query Editor: Unpivoting, Merging Queries, Appending Queries.
- 7.9Automating Data Refresh: Refreshing data with a single click.
- Automation, Collaboration & Advanced Techniques6
- 8.1Introduction to Macros (VBA Basics)
- 8.2Understanding Macros: What are macros and when to use them.
- 8.3Recording Macros: Automating repetitive tasks (e.g., formatting, data manipulation).
- 8.4Editing Recorded Macros: Basic understanding of VBA code for simple modifications.
- 8.5Assigning Macros: To buttons, shapes, or keyboard shortcuts.
- 8.6Security Considerations for Macros.
- Collaborative Features & File Management4
- Data Integrity & Auditing4
- Department-Specific Applications & Case Studies0
- HR Department Applications4
- 12.1Employee Data Analysis: Attrition rate calculation, salary distribution analysis, tenure analysis using DATEDIF, PivotTables.
- 12.2Performance Tracking: Using conditional formatting for performance ratings, creating interactive dashboards for HR metrics (e.g., headcount, recruitment efficiency).
- 12.3Leave Management: Calculating leave balances, tracking absenteeism using NETWORKDAYS.
- 12.4Payroll Reconciliation: Using VLOOKUP/XLOOKUP and SUMIFS for comparing payroll data.
- Marketing Department Applications4
- 13.1Campaign Performance Tracking: Analyzing ad spend vs. conversion rates, A/B testing analysis using statistical functions.
- 13.2Customer Segmentation: Using filters, conditional formatting, and COUNTIFS/SUMIFS to identify customer groups.
- 13.3Sales Funnel Analysis: Creating dashboards to visualize lead conversion stages.
- 13.4Market Research Data Analysis: Summarizing survey responses with PivotTables, identifying trends with charts.
- Finance Department Applications5
- 14.1Financial Modeling: Building basic income statements, balance sheets, and cash flow forecasts using financial functions (e.g., FV, PV, PMT, NPV, IRR).
- 14.2Budgeting & Forecasting: Using What-If Analysis (Scenario Manager, Goal Seek, Data Tables) for budget variations.
- 14.3Variance Analysis: Comparing actual vs. budget figures using formulas and conditional formatting.
- 14.4Investment Analysis: Calculating ROI, payback period, and discounted cash flows.
- 14.5Reconciliation: Bank statements, inter-company accounts using advanced lookups and conditional formatting.
- Plant/Operations Department Applications4
- 15.1Production Planning: Using Solver for optimizing production schedules and resource allocation.
- 15.2Inventory Management: Tracking stock levels, calculating reorder points, analyzing lead times.
- 15.3Quality Control: Creating control charts, tracking defects using conditional formatting and statistical functions.
- 15.4Maintenance Scheduling: Using date functions to plan preventative maintenance.
- Supply Chain Management (SCM) Department Applications4
- 16.1Logistics & Route Optimization: Basic route planning scenarios with Solver.
- 16.2Supplier Performance Analysis: Tracking delivery times, quality, and costs using conditional formatting and Pivot Tables.
- 16.3Demand Forecasting: Simple forecasting models using trend functions or historical data analysis.
- 16.4Warehouse Management: Inventory tracking and space utilization.
- Admin Department Applications4
- 17.1Resource Scheduling: Managing meeting rooms, equipment, or shared resources.
- 17.2Event Planning & Budgeting: Tracking expenses, guest lists, and timelines.
- 17.3Reporting & MIS: Generating automated reports for management, consolidating data from various sources.
- 17.4Data Cleaning & Standardization: Using Power Query and text functions for routine data hygiene.
- Research Department Applications4
- 18.1Data Cleaning & Preprocessing: Extensive use of Power Query for preparing raw data for statistical analysis.
- 18.2Statistical Analysis: Using functions like CORREL, STDEV, SKEW, KURT, and the Data Analysis ToolPak (Regression, ANOVA, Descriptive Statistics).
- 18.3Survey Data Analysis: Summarizing large datasets from surveys using PivotTables and advanced filtering.
- 18.4Graphing Scientific Data: Creating specialized charts for data presentation.
Survey Data Analysis: Summarizing large datasets from surveys using PivotTables and advanced filtering.
Prev