- In this course, you will learn to:
- Identify the components and functionalities of Macros in Excel, including the creation of input forms and database tabs
- Organize the steps involved in designing and modifying UserForms for data entry and modification in Excel
- Define the purpose and types of Database Management Systems (DBMS), including examples like MySQL and Oracle
- Compare and contrast cloud-based and local storage models for hosting DBMS servers
- Summarize the fundamentals of Power Pivot, including data analysis techniques and DAX formulas
- Differentiate between Pivot Tables and Pivot Charts and their application in Power Pivot for data visualization
- Categorize the features and functions of Power Query for data transformation and cleaning in Excel
- Examine advanced data transformation techniques in Power Query, such as error handling and conditional logic
- Predict the impact of various data visualization techniques, including Pivot Tables, Pivot Charts, and Dashboards, in Power Query
- Reorganize VBA code for automating tasks in Excel, including error handling and debugging techniques
- Operate Excel features and functions using VBA code, such as cell referencing, formatting, and automating data entry
- Diagnose and troubleshoot errors in VBA code, including identifying and correcting syntax and logic errors
- Arrange the steps involved in creating interactive dashboards in Power BI, including data modeling, visualization design, and publishing

Automation with Excel Macros and VBA Programming
Automate repetitive tasks using Excel macros and VBA. Create custom functions and streamline data processes.
Introduction
What you'll learn ?
- At the end of this course, learners will be able to:
- Interpret the functionality of Macros in Excel and demonstrate proficiency in designing input forms and database tabs
- Illustrate the use of IF conditions and variables in Macros to enhance automation and data processing capabilities
- Estimate the effectiveness of Power Pivot in data analysis and modelling, including the creation of relationships and DAX formulas
- Defend the importance of Power Pivot for advanced data analysis and visualization, including cross-filtering and pivot tables
- Compare Power Query functions and techniques for data transformation and loading, including error handling and conditional logic
- Review Power Query data visualization and reporting techniques, such as dashboards and dynamic visualizations
- Rewrite VBA code to automate tasks and Excel features, including user interface customization and data analysis
- Construct VBA procedures and control structures to manage data and automate repetitive tasks effectively
- Estimate the impact of VBA functions and subroutines on Excel automation and error handling
- Assess the effectiveness of automating tasks and Excel features using VBA, including font, border, and alignment formatting
- Combine Power BI features for data transformation, modelling, and relationship building to create interactive dashboards and reports
- Solve real-world data analysis challenges using Power BI visualizations, filters, and drill-through capabilities to derive actionable insights
Certification
- KLiC courses are recognised by Yashwantrao Chavan Maharashtra Open University (YCMOU).
- MKCL provides certificate to the KLiC learner after his/her successful course completion.
- Yashwantrao Chavan Maharashtra Open University (YCMOU) provides mark sheet to successfully passed KLiC learners (Jurisdiction: Maharashtra).
Academic Approach
The academic approach of the courses focuses on the “work-centric” education i.e. begin with work (and not from a book!), derive knowledge from work and apply that knowledge to make the work more wholesome, useful and delightful. The ultimate objective is to empower the Learner to engage in socially useful and productive work. It aims at leading the learner to his/her rewarding career as an employee or entrepreneur as well as development of the community to which s/he belongs. Learning methodology:
- Step -1: Learners are given an overview of the course and its connection to life and work.
- Step -2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
- Step -3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
- Step -4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
- Step -5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
- Step -6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
- Step -7: Learners are engaged in appreciation of real-life case studies developed by the experts.
- Step -8: Learners are encouraged to proceed from appreciation to imitation of the experts.
- Step -9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
- Step-10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
- Step-11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
- Step-12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!
Syllabus
- Designing Excel input form for customer info
- Create a new tab for the database
- How to use the IF condition for a button
- Use of IF Function
- Introduction of Macros
- Uses of Macros
- Enhancing VBA code for saving data in next row
- Use the variable in the code
- Entering serial numbers using code
- Add message box
- User Form Intro
- Create a UserForm for a personal cash manager
- UserForm - make database sheet
- UserForm - add cash
- UserForm - add expense
- UserForm - add code
- Code for adding cash in database
- UserForm null value check code
- UserForm code for saving data to sheet
- Introduction of Forms
- Form modification using various tools
- UserForm - code for dates, saving
- UserForm - code for negatives, cash in hand
- Create User Form to modify entry
- Check modify entry UserForm
- Introduction to Macros and Its Use
- Creating Code in Macros for Final Output
- Save code for modify entry
- Code to delete specified entry
- What is a DBMS and its purpose
- Types of DBMS
- Examples of DBMS - MySQL, Oracle, etc.
- Servers host DB, where are servers located (Local / Cloud)
- Comparison of clouds with local storage models
- Introduction to Power Pivot
- Data Analysis and Power Pivot Fundamentals
- Creating Relationships in Power Pivot
- DAX Formulas and Functions
- Data Modeling Techniques
- Hierarchies
- Categories
- Create Parent-Child Relationships
- End-to-End Hierarchy Column
- Handling Empty Members
- Ragged Hierarchies
- Advanced Data Analysis
- Advanced DAX Functions
- Cross-Filtering
- Cross-Highlighting
- Data Visualizations with Power Pivot
- What are Pivot Tables
- Pivot Tables with Power Pivot Data
- What are Pivot Charts?
- Pivot Charts with Power Pivot Data
- Introduction to Slicers
- Introduction to Timelines
- Multiple KPI's
- Creating Scorecards
- Advanced Power Pivot Features
- Date Tables in Power Pivot
- Date Tables with Hierarchy
- Hierarchies and Drill-Through
- Perspectives in Power Pivot
- Calculations with DAX
- Few Examples in DAX
- Important Error Handling In DAX
- Integration and Publishing
- Power Query
- Introduction
- Cleaning Data
- Combining Data
- Appending Data,
- Combining and Merging Data
- Introduction to Power Query
- Introduction to Power Query
- Data Transformation
- Power Query Functions,
- Intro to Power Query Functions
- Text Functions in Power Query
- Case Sensitive Functions
- LEFT, RIGHT & MID Functions
- Text With Delimiter
- Date Functions
- Difference Between 2 Dates
- Calculate Age
- Extract Start & End of Month, Quarter & Year
- Basic Numeric Functions
- Basic Round Functions
- Percentages in Power Query
- Power Query Data Transformation Techniques
- Error Handling
- Conditional Logic
- Introducing Custom Column
- Using Custom Columns
- Introducing Conditional Columns
- Grouping of Data
- Data Visualization and Reporting with Power Query
- Data Loading into Power Query
- Connecting Power Query with Excel
- Introducing Dashboards
- Difference between Reports and Dashboards
- Building Reports and Dashboards
- Pivot Tables with Power Query Data
- Pivot Charts with Power Query Data
- Introducing Dynamic Dashboards
- Examples of Dynamic Dashboards
- Introduction to VBA and Excel Automation,
- Introduction To VBA
- Writing A Macro
- Recording A Macro
- Recording a Macro using Relative References
- Saving & Running A Macro
- User Interface
- Developer Tab
- Developer Environment
- Creating A Message Box
- Creating A Message Box with Other Arguments
- Creating An Input Box
- Working with VBA Code,
- Writing a VBA Code
- Editing a VBA Code
- Variables & it's Usage
- Data Types & it's Usage
- Constants in VBA
- Control Structures Using If Statement
- Control Structures Using If - Else Statement
- Control Structures Using If - Then - ElseIf Statement
- Control Structures Using If - ElseIf - And Statement
- Control Structures Using If - Else for Loop Statement
- Basic For Loop Example
- Advanced For Loop Example
- For Each Next Loop
- Do - While Loop
- Excel VBA Functions and Subroutines,
- Creating Procedures
- Calling Procedures
- How to use Functions
- How to use Subroutines
- Built - In Functions Excel
- Error Handling
- Debugging
- Automating Tasks and Excel Features,
- Introduction to Sheets
- Adding Sheets
- Adding Sheets with Names
- Copy Sheets
- Hiding or Unhiding Sheets
- Cell Referencing
- Automating Data Entry or User Forms
- Font Formatting
- Border Formatting
- Alignment Formatting
- Customizing Excel Menus and Ribbons
- Performing Data Analysis
- Pivot Tables in VBA
- Introduction to Power BI,
- What is Power BI
- Power BI Features
- Getting Started with Power BI
- What are Data Sources
- Connecting to Multiple Data Sources
- Data Transformation in Power BI Power Query
- Power Query Editor
- Data Modeling and Relationships,
- Data Modeling in Power BI
- What are Relationships
- Creating A Relationship
- What is DAX
- Using DAX
- Improvisations of DAX over EXCEL
- Examples of DAX Functions
- Visualizations and Reports,
- What are Visuals or Visualizations
- Building Your First Visualization
- Types of Viz (New)
- Tables & Matrices
- Card & Multi-Row Card Visuals
- Map Visuals
- Line Graphs
- Bar Charts
- Column Charts
- Pie or Doughnut Charts & TreeMaps
- Formatting Tips
- Top 5 Custom Visualizations
- Formatting Visuals
- Creating Interactive Dashboards,
- Designing A Dashboard
- Sample Dashboards
- Slicers and Filters in PBI
- Adding Interactivity with Slicers and Filters
- Using Drill - Through
- Publishing and sharing
- Introduction to Power BI Service
- Publishing Reports on Power BI Service
- Sharing Reports and Dashboards
- Data Security and Permissions
Evaluation Pattern
Evaluation Pattern of KLiC Courses consists of 4 Sections as per below table:
Section No. | Section Name | Total Marks | Minimum Passing Marks |
---|---|---|---|
1 | Learning Progression | 25 | 10 |
2 | Internal Assessment | 25 | 10 |
3 | Final Online Examination | 50 | 20 |
Total | 100 | 40 | |
4 | SUPWs (Socially Useful and Productive Work in form of Assignments) | 5 Assignments | 2 Assignments to be Completed & Uploaded |
YCMOU Mark Sheet
Printed Mark Sheet will be issued by YCMOU on successful completion of Section 1, Section 2 and Section 3 and will be delivered to the learner by MKCL.
YCMOU Mark Sheet will be available only for Maharashtra jurisdiction learners
MKCL's KLiC Certificate
The certificate will be provided to the learner who will satisfy the below criteria:
- Learners who have successfully completed above mentioned 3 Sections i.e. Section 1, Section 2 and Section 3
- Additionally, learner should have completed Section 4 (i.e. Section 4 will comprise of SUPWs i.e. Socially Useful and Productive Work in form of Assignments)
- Learner has to complete and upload minimum 2 out of 5 Assignments
Courses Fee Structure from 01 July, 2025 Onwards
KLiC 30 hour course fee applicable from 01 July, 2025 all over Maharashtra
KLiC Course Duration | MFO (Inclusive of GST) |
ALC Share (Service Charges to be collected by ALC) |
---|---|---|
30 hours | Rs. 300/- | Rs. 1,500/- |
* Above mentioned fee is applicable for all Modes of KLiC Courses offered at Authorised Learning Center (ALC) and at Satellite Center
* Total fee is including of Course fees, Examination fees and Certification fees
* MKCL reserves the right to modify the Fee anytime without any prior notice