Track
Data Science
Duration
30 hours
Skill Level
Advanced
Language
English
About this Course
Automate repetitive tasks using Excel macros and VBA. Create custom functions and streamline data processes.
Learning Mode: Learn at ALC or at Home
Detailed Course Curriculum
Hands-on module breakdown aligned with MKCL production standards and industry requirements.
- 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
Eligibility Criteria
• Basic knowledge of computers and keen desire to build skills in this field.
• Open to students, job seekers, and working professionals.
• Open to students, job seekers, and working professionals.
Official Certification
• Official MKCL KLiC Certificate upon successful completion of the course and evaluations.
Work-Centric Learning Approach
• 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!
• 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!
Ready to start Automation with Excel Macros and VBA Programming?
Join our upcoming batch at ZICA Kalyani center with certified instructors.
