Certificate Program on Basics of Data Analytics

Course Objective To introduce learners to the fundamental concepts, tools, and techniques of data analytics, enabling them to collect, clean, analyze, visualize, and interpret data to support decisionmaking.

Course Instructor: Aman Jha

₹3999.00

To enroll in this course, please contact the Admin

Contact Admin

Course Overview

Key Learning Outcomes
By the end of the course, students should be able to:
 Understand the role of data in business and research.
 Work with different types of data (structured and unstructured).
 Use tools like Excel, SQL, and Python (or R) for basic data analysis.
 Perform data cleaning and preprocessing.
 Generate descriptive statistics and visualizations.
 Interpret basic analytical results to inform decisions.
Who is it for?
 Beginners in data analysis
 Business professionals looking to use data
 Students or career-switchers exploring data roles

Duration 30 hours

Schedule of Classes

Course Curriculum

8 Subjects

Excel Efficiency Engine: Interface s Data Handling

Advanced Navigation C Shortcuts: o Essential keyboard shortcuts for navigation, selection, formatting, and data manipulation (Ct

Advanced Navigation C Shortcuts: Excel Interface Customization

Advanced Navigation C Shortcuts: Customizing the Quick Access Toolbar and Ribbon.

Advanced Navigation C Shortcuts: Excel Options for Performance

Data Entry C Cleaning Tricks: o Flash Fill (Ctrl + E): For intelligent, pattern-based splitting and combining of data without fo

Data Entry C Cleaning Tricks: Custom Lists for rapid entry of frequent items (e.g., departments, names).

Structuring Data: Converting a range into a formal Excel Table (Ctrl + T) for automatic expansion, filtering, and structured ref

Structuring Data: Named Ranges C Dynamic Named Ranges

Structuring Data: Best Practices for Large Data Handling

Data Integrity s Validation

Data Validation for Error-Proofing: o Advanced Data Validation Rules

Data Validation for Error-Proofing: Creating static and dynamic drop-down lists using named ranges.

Data Validation for Error-Proofing: Dependent (Cascading) Drop-Downs

Data Validation for Error-Proofing: Setting restrictions for dates, numbers, and text length.

Data Validation for Error-Proofing: Creating custom input error and prompt messages.

Data Validation for Error-Proofing: Error Alerts C User Input Controls

Advanced Sorting C Filtering: Multi-level sorting (e.g., by Region, then by Sales).

Advanced Sorting C Filtering: Filtering by color, icon, or specific text/number criteria.

Data Consolidation C Subtotals: Using Consolidate to combine data from multiple sheets.

Data Consolidation C Subtotals: Using Subtotal to quickly create hierarchical, collapsible summary reports.

Module 3: Logical s Text Functions for Data Manipulation

Logical Functions for Decision Making: IF, AND, OR for building conditional outputs.

Logical Functions for Decision Making: IFS, SWITCH Functions

Logical Functions for Decision Making: Nested IFs and the cleaner IFS function for multi-level conditions.

Logical Functions for Decision Making: Using IFERROR to handle errors gracefully with Error Handling Techniques

Essential Text Functions for Cleaning C Preparation: Advanced Text Cleaning C Transformation

Essential Text Functions for Cleaning C Preparation: TRIM, CLEAN to remove extra spaces and non-printable characters.

Essential Text Functions for Cleaning C Preparation: UPPER, LOWER, PROPER for standardizing text.

Essential Text Functions for Cleaning C Preparation: LEFT, RIGHT, MID, FIND to extract and locate substrings.

Essential Text Functions for Cleaning C Preparation: TEXTJOIN, Substitute C Replace, CONCAT for merging text with delimiters.

Essential Text Functions for Cleaning C Preparation: Real-world data preparation scenarios

Advanced Lookup Formulas: Powerful Analysis s Dynamic Reporting

Mastering VLOOKUP: Limitations of VLOOKUP C HLOOKUP

Mastering VLOOKUP: Exact vs. Approximate match. Understanding its limitations.

The Superior INDEX-MATCH Method: o INDEX Function: Retrieving a value from a specific position

The Superior INDEX-MATCH Method: MATCH Function: Finding the position of a lookup value.

The Superior INDEX-MATCH Method: INDEX–MATCH (Two-Way C Dynamic Lookups)

Modern Lookups with XLOOKUP: MATCH with Approximate Logic

Modern Lookups with XLOOKUP: LOOKUP Optimization for Large Datasets

Modern Lookups with XLOOKUP: Simplified syntax, default exact matching, and built-in if-not-found argument. The ultimate VLOOKUP

PivotTables for Instant Business Intelligence

Building Effective PivotTables: o Transforming raw data into a structured report from Excel Tables.

Building Effective PivotTables: Configuring Fields: Values, Rows, Columns, and Filters.

Calculations C Grouping: Calculated Fields C Calculated Items

Calculations C Grouping: Applying different summary calculations (Sum, Count, Average).

Calculations C Grouping: Grouping dates (months, quarters, years) and numeric data into bins.

Calculations C Grouping: Using Value Field Settings to show % of Total, Running Totals, and Rank.

Calculations C Grouping: Custom Sorting C Filterin

Design C Presentation: Applying and customizing PivotTable Styles for professional reports.

Design C Presentation: PivotTable Performance Optimization

Visualizing Data with PivotCharts s Slicers

Creating Dynamic PivotCharts: o Best practices in chart selection for different data types.

Creating Dynamic PivotCharts: Slicers C Timelines (Multiple Pivot Linking)

Creating Dynamic PivotCharts: Formatting charts for clarity and impact.

Creating Interactive Dashboards: Inserting Slicers and Timelines for intuitive filtering.

Creating Interactive Dashboards: KPI-based Dashboards

Creating Interactive Dashboards: Dynamic Titles C Dashboard Controls

Creating Interactive Dashboards: Connecting Slicers to multiple PivotTables and PivotCharts to create a cohesive, interactive da

Creating Interactive Dashboards: Dashboard Design Best Practices

Power Query: The Ultimate Data Wrangling Advanced Tool, Automation

Introduction to Power Query: o Connecting to data from Excel ranges, CSV files, and the web.

Introduction to Power Query: Understanding the Power Query Editor interface and "Applied Steps.

Key Transformation Techniques: o Advanced Power Query Transformations

Key Transformation Techniques: Changing data types, renaming columns, removing duplicates/errors.

Key Transformation Techniques: Unpivoting Data: Converting cross-tab tables into analysis-friendly flat lists.

Key Transformation Techniques: Merging Queries: Performing VLOOKUP-like operations that are more robust and refreshable.

Key Transformation Techniques: Key Transformation Techniques:

Key Transformation Techniques: Appending Queries: Stacking multiple tables or sheets together.

Key Transformation Techniques: Merge vs Append (Advanced Scenarios)

Key Transformation Techniques: Handling Errors C Null Values

Loading C Refreshing: Building a dynamic data pipeline that updates with one click: Building Automated Data Refresh Pipelines

Introduction to Automation with Macros: Building a dynamic data pipeline

Macro Concepts: Macro Concepts C Use Cases

Macro Concepts: Understanding what macros can automate (formatting, filtering, report generation).

The Macro Recorder: Recording Advanced Macros

The Macro Recorder: Recording a simple macro to automate a repetitive task.

The Macro Recorder: updates with one click. o Building Automated Data Refresh Pipelines Module 8: Introduction to Automation wi

The Macro Recorder: Assigning a macro to a Buttons C Shapes for one-click execution.

The Macro Recorder: Macro Security C Best Practices

Course Instructor

Aman Jha

9 Courses • 77 Students