Accordion Content

Advanced Excel

introduction to Advanced Excel | Excel sheet Creation, edit, save, Folder Creation | Project 1 [Introduction to Excel

Conditional Formatting & Data Validation

Cell reference, Conditional formatting| Data validation| Important functions in Excel, Remove duplicate values, Hyperlink| Filter, Unique, Sort, SortBY, Sequence, Randarray Functions|

Logical & Statistical functions

Logical functions | Sum & Count if functions| Graphics, Print area setup , Camera tool project| Statistical functions, mathematical functions , Date related functions,  Financial Functions| Dsum, subtotal functions, Text functions, Insert object| Data extraction| Averageif& Averageifs.

Advanced Pivot Table

Classic pivot table and chart| Slicers, timelines and calculated fields| Building hierarchies in pivot tables| Advanced Pivot table data analysis

Advanced Formula for Data Analysis

Custom view, Header and footer, Text to column function| Goal seek, Solver, Scenario manager| Lookup| Project| Match and index function, Consolidating data

Advanced Formula for Data Analysis

Sparkline chart, People graph, Data forecast, Import text file| Mail merge, Consolidating data| Form data, Auditing tool, Web data, Speak cell | Macro, Password protection

Data Preparation and Cleaning

Importing data from various sources. | Handling missing values and duplicates. | Using Text-to-Columns and Flash Fill. | Data validation and error checking for quality assurance.|

Date and Time Analysis

Working with DATE, TIME, NETWORKDAYS, and EOMONTH. | Automating rolling date ranges for dynamic reports. | Calculating time differences in hours, minutes, and seconds. | Custom formatting for date and time (e.g., “MMM-YYYY”). | Dynamic date ranges and custom date logic with WEEKNUM, WORKDAY.INTL

Power Query (Get & Transform)

Importing and cleaning data from various sources| Merging and appending queries| Advanced filtering and transforming data

Introduction to Dashboard Design

Understanding the importance of dashboards | Planning a dashboard project| Data visualization best practices| Advanced Charts

Introduction to Power BI

Introduction to Power BI. | Power BI installation. | Main components of Power BI.

Data Import and Transformation

Data import and transformation. | Columns transformation. | Conditional formatting. | Data grouping. | Tooltip report page. | KPI visualization. | Map visualizations. | Card visual.

Power Query intro-User Interface

Power Query intro-User Interface| Data Consolidation in Power BI | Case Study -Payroll Data |

Advanced Visualizations and Data Consolidation

Advanced visualizations. | Data Modeling Intro-Start Schema| Case Study Sales Analysis| Snowflake Schema|

DAX Functions

Understanding DAX functions. | Adding new tables. | Logical functions. | Date and time functions.| Dax Syntax| Adding New Column| Adding New Measure| Adding New Table| Renaming Column &Measure| Arithmetic Operators

Statistical and Text Functions

Statistical functions (SUM, SUMX, MIN, MINX, AVERAGEX). | Text functions (LEN and others). | Hierarchy reports| Filter Functions|

Designing Interactive Dashboards

Designing interactive dashboards. | Matrix visualization. | Case Study: Sales Analytics Report (Project). | Case Study: HR Analytics Report (Project). | Publishing and sharing reports| Area Chart |Line  & Stacked Column Chart| Bookmark & Selection Pane| Data Grouping| Sorting|

KPI Visualization| Multi – Row Card| Formatting Visuals| Field settings| Tooltip Report Page| Table Visualization |Matrix Visualization|

Project

Top 10 Tricks in Power BI| Project 1, Project 2, Project 3

Module 1: Building Forms

Building a form. | Adding a title to the form. | Adding multiple-choice questions. | Adding paragraph questions. | Adding short-answer questions.

Module 2: Customization

Adding a title and description. | Adding collaborators. | Customizing the theme. | Reviewing responses.

Module 3: Advanced Features

Exploring advanced features in Google Forms.

Module 4: Projects

Feedback Form for a Workshop (Project 1):
Create a Google Form to collect feedback from participants after attending a workshop or class.

Module 1: Introduction to Google Sheets

Introduction to Google Sheets. | Practical use of the IF formula in Google Sheets. | Removing duplicates in Google Sheets.

Module 2: Data Collection and Import

Data collection and importing techniques. | Split function. | Image function. | Searchable drop-down list.

Module 3: Advanced Functions and Features

Creating advanced filters using multiple conditions. | Mini calendar and date picker. | Picture lookup. | MAX & MIN functions in Google Sheets.

Module 4: Practical Applications

Creating checklists in Google Sheets. | Stock management with Google Sheets. | Creating attendance trackers in Google Sheets. | Using pivot tables in Google Sheets.

Module 5: Collaboration

Collaborating on spreadsheets with multiple users. | Importing and sharing sheets with others.

Module 6: Add-ons and Assistance

Exploring add-ons for Google Sheets. | Using ChatGPT to create complex formulas. | Utilizing the Help function effectively.

Module 1: Advanced Excel Techniques for Dashboards

Purpose and benefits of dashboards in business. | Key principles of effective dashboard design. | Structuring raw data for analysis. | Using tables and named ranges. | Data validation and error checking.

Module 2: Dashboard Projects

Project 1: Payroll Management Dashboard
Importing employee payroll data. | Handling salary components (basic, allowances, deductions). | Net pay computation. | Overtime and bonus calculations. | Tax and statutory compliance computations.

Project 2: Financial Analytics Dashboard
Importing financial statements and transaction data. | Profit and loss computations. | Cash flow analysis. | Revenue and expense visualizations. | Break-even analysis charts. | Scenario analysis using what-if parameters.

Project 3: HR Management Dashboard
Importing employee data (demographics, performance metrics). | Employee turnover and retention rates. | Performance scoring and appraisal summaries. | Training needs assessment. | Workforce analytics visuals. | Interactive filters by department, role, or location. | Employee satisfaction and engagement indicators.

Project 4: Inventory Management Dashboard
Importing inventory levels, sales orders, and purchase orders. | Stock levels and reorder points. | Inventory aging analysis. | Supplier performance metrics. | Real-time stock monitoring visuals. | Alerts for low stock and overstock situations. | Sales vs. inventory turnover graphs.

Project 5: Project Management Dashboard
Importing project plans, task lists, and timelines. | Task progress tracking. | Resource allocation and workload analysis. | Budget vs. actual expenditure. | Milestone tracking visuals. | Risk assessment indicators.