Acrosys Technologies

Advanced Excel Course

Duration: 3 Months / 12 Weeks
Level: Beginner to Advanced
Mode: Practical Training
Tools: Microsoft Excel, Pivot Tables, Power Query, Power Pivot, Advanced Formulas, Dashboards, Data Analysis and Automation

Course Level Beginner to Advanced

Training Type Practical & Project Based

Career Focus MIS, Data Analysis & Reporting

Month 1: Excel Fundamentals & Advanced Formulas

Introduction to Microsoft Excel
Understanding workbooks and worksheets
Excel interface and ribbon
Rows, columns and cells
Entering and editing data
Cell references
Relative and absolute references
Creating and saving workbooks
Worksheet management
Copy, cut and paste operations
Formatting cells
Number, currency and percentage formats
Professional spreadsheet formatting
Page setup and print settings
Freeze panes and worksheet navigation

Practical: Create a professional employee database and format it for printing.

Understanding Excel formulas
Basic mathematical operators
SUM function
AVERAGE function
MIN and MAX functions
COUNT and COUNTA
ROUND, ROUNDUP and ROUNDDOWN
Percentage calculations
Basic financial calculations
Relative references
Absolute references
Mixed cell references
Formula auditing basics
Common formula errors
Error handling fundamentals

Practical: Build a monthly sales calculation sheet using formulas and functions.

IF function
Nested IF statements
IFS function
AND and OR functions
NOT function
IFERROR
VLOOKUP
HLOOKUP
XLOOKUP
INDEX function
MATCH function
INDEX and MATCH combination
Lookup error handling
Conditional calculations
Dynamic lookup techniques

Practical: Create an employee salary and performance lookup system.

LEFT, RIGHT and MID functions
LEN function
TRIM function
UPPER, LOWER and PROPER
CONCAT and TEXTJOIN
FIND and SEARCH
SUBSTITUTE and REPLACE
DATE and TODAY functions
DAY, MONTH and YEAR
DATEDIF fundamentals
Working with working days
IF with dates
Conditional formatting
Highlighting duplicate values
Custom conditional formatting rules

Practical: Build an employee attendance and employee age analysis report.

Month 2: Data Analysis, Pivot Tables & Power Query

Excel Tables
Creating structured tables
Sorting data
Multi-level sorting
Filtering data
Advanced filtering
Data validation
Drop-down lists
Custom validation rules
Removing duplicate records
Find and Replace
Data cleaning techniques
Handling blank cells
Text to Columns
Flash Fill

Practical: Clean and prepare a large customer dataset for analysis.

SUMIF
SUMIFS
COUNTIF
COUNTIFS
AVERAGEIF
AVERAGEIFS
MAXIFS and MINIFS
SUBTOTAL
AGGREGATE
Ranking functions
RANK and RANK.EQ
Large and Small functions
Percentage analysis
Advanced business calculations
Combining multiple functions

Practical: Create a department-wise sales and employee performance analysis.

Introduction to Pivot Tables
Creating Pivot Tables
Rows, columns and values
Pivot Table filters
Grouping data
Grouping dates
Calculated fields
Sorting Pivot Table data
Filtering Pivot Tables
Slicers
Timeline filters
Pivot Charts
Formatting Pivot Charts
Interactive reporting
Refreshing Pivot data

Project: Build a monthly sales analysis dashboard using Pivot Tables and Pivot Charts.

Introduction to Power Query
Importing Excel data
Importing CSV files
Importing folder data
Connecting to external data
Removing unnecessary columns
Changing data types
Replacing values
Removing duplicate records
Splitting columns
Merging columns
Appending queries
Merging queries
Transforming datasets
Refreshing Power Query reports

Mini-project: Clean and combine multiple monthly sales files using Power Query.

Month 3: Dashboards, Power Pivot, MIS & Automation

Advanced Power Query transformations
Query parameters
Conditional columns
Custom columns
Advanced filtering
Data type management
Handling missing data
Combining multiple files
Folder-based data import
Reusable queries
Query dependencies
Data refresh techniques
Automated monthly data processing
Error handling in Power Query
Building repeatable data workflows

Practical: Build an automated monthly MIS data preparation workflow.

Introduction to Power Pivot
Creating data models
Importing multiple tables
Creating table relationships
Primary and lookup tables
Data model concepts
Introduction to DAX
Calculated columns
Measures
SUM and CALCULATE
Basic DAX calculations
Time-based analysis
Using Power Pivot with Pivot Tables
Large dataset analysis
Building reusable analytical models

Project: Create a multi-table business data model using Power Pivot.

Introduction to Excel dashboards
Dashboard planning
Choosing appropriate KPIs
Creating KPI cards
Charts and visualizations
Column charts
Bar charts
Line charts
Pie and doughnut charts
Combo charts
Slicers and interactive filters
Dashboard layout and design
MIS reporting concepts
Management reporting
Professional dashboard presentation

Project: Build a complete Sales MIS Dashboard with KPIs, charts and interactive filters.

Students will complete a real-world Advanced Excel project covering:

Data collection and preparation
Data cleaning
Advanced formulas
Lookup functions
Conditional calculations
Pivot Tables
Pivot Charts
Power Query
Power Pivot
Data modelling
MIS reporting
Dashboard creation
KPI development
Report automation
Final presentation
Management-level reporting

Final Project: Create a complete automated MIS and business dashboard using Excel.

Suggested Practical Projects

Sales MIS Dashboard
Employee Attendance Tracker
Expense Management Tracker
Inventory Management Report
Monthly Sales Dashboard
Customer Data Analysis
Financial Reporting Dashboard
Loan / EMI Tracking Report
Automated MIS Report
Management Dashboard

Assessment Structure

Assessment	Weightage
Weekly Excel practical assignments	15%
Advanced formula exercises	10%
Pivot Table and data analysis project	15%
Power Query project	15%
MIS Dashboard project	20%
Final Advanced Excel project	20%
Attendance and presentation	5%

Course Outcomes

After completing this course, students will be able to:

Create professional Excel spreadsheets.
Use advanced Excel formulas effectively.
Perform data cleaning and transformation.
Analyse large datasets.
Create Pivot Tables and Pivot Charts.
Build interactive Excel dashboards.
Create professional MIS reports.
Use Power Query for data transformation.
Use Power Pivot for advanced data analysis.
Understand basic DAX and data modelling.
Automate repetitive reporting tasks.
Prepare management-level reports.
Work with real-world business datasets.
Build practical Excel projects for professional use.

Tools & Technologies

Microsoft Excel
Advanced Excel Formulas
Pivot Tables
Pivot Charts
Power Query
Power Pivot
DAX Fundamentals
Excel Dashboards
MIS Reporting
Data Analysis
Data Visualization
Data Modelling

Career Opportunities

MIS Executive
MIS Analyst
Data Analyst
Reporting Executive
Business Analyst
Operations Executive
Finance Executive
Excel Specialist
Reporting Analyst
Business Operations Analyst

Excel Best Practices

Maintain clean and structured datasets.
Use meaningful worksheet and column names.
Avoid unnecessary duplicate data.
Use appropriate formulas for calculations.
Apply consistent formatting.
Use data validation wherever required.
Protect important formulas and worksheets.
Create clear and readable dashboards.
Validate data before preparing reports.
Maintain proper version control for important workbooks.
Use Power Query for repeatable data preparation.
Keep source data separate from reports and dashboards.

Final Course Project

Students will develop a complete business reporting solution using Advanced Excel. The project will include data preparation, formula-based calculations, Pivot Tables, Power Query, Power Pivot, KPI analysis and an interactive management dashboard.

Final Deliverable: Complete Advanced Excel MIS Dashboard with automated reporting and presentation.