Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Excel Practice & Assignments

Strengthen your Microsoft Excel skills through practical formula exercises, PivotTable problems, dashboard development, VBA programming tasks, and final business reporting assignments. Apply your learning to realistic datasets and improve your analytical and problem-solving skills.

1. Introduction to Excel Practice

Learning Excel requires more than understanding formulas and features. You must also practise applying them to real business problems. The Excel Practice module provides structured exercises to help you develop accuracy, analytical thinking, and confidence while working with data.

In this module, you will work with Excel formulas, data summarization, PivotTables, dashboards, and VBA automation. Each topic builds on the previous one, allowing you to move from basic calculations to complete reporting solutions.

The exercises are designed around common business activities such as calculating employee salaries, analysing sales, comparing regional performance, preparing management dashboards, and automating repetitive reporting tasks.

Learning objective: Practise each concept using sample data, verify your results, understand your mistakes, and gradually combine multiple Excel features to solve larger business problems.
Excel Learning and Practice Journey
Excel Practice Learning Path 1. Excel Formulas Calculations and logic 2. PivotTables Summaries and analysis 3. Dashboards KPIs and visualization 4. VBA Programming Automate repetitive operations 5. Final Assignments

Recommended learning sequence for the practice module.

2. Learning Objectives

After completing the practice exercises, you should be able to apply Excel concepts to common data-processing and reporting tasks.

Skill Area Learning Objective Practical Application
Excel Formulas Perform calculations and apply logical conditions. Salary calculation and sales commission.
Data Analysis Summarize data and identify business trends. Sales and regional performance analysis.
PivotTables Group, filter, summarize, and compare records. Product-wise and month-wise sales reports.
Dashboards Present important metrics and trends visually. Management performance dashboard.
VBA Programming Automate repetitive Excel operations. Report generation and worksheet processing.
Final Assignments Combine multiple concepts in a practical solution. Automated business reporting project.

3. Practice Modules Overview

The practice section is divided into focused modules. Complete the relevant exercises in sequence and verify the output before moving to the next topic.

3.1 Excel Formula Exercises

Practise arithmetic formulas, conditional functions, text manipulation, date calculations, lookup functions, and summary calculations.

  • Calculate total sales and average sales.
  • Use IF and nested IF conditions to classify records.
  • Apply SUMIF, SUMIFS, COUNTIF, and COUNTIFS.
  • Extract and combine text using text functions.
  • Use lookup functions to retrieve employee or product details.
3.2 PivotTable Exercises

Learn how to summarize large datasets, group records, compare categories, apply filters, and calculate meaningful business totals.

  • Create product-wise and region-wise sales summaries.
  • Group dates by month or year.
  • Apply sorting, filtering, and value-field settings.
  • Use calculated fields where supported.
  • Refresh PivotTables after updating source data.
3.3 Dashboard Exercises

Convert summarized information into a clear visual dashboard using charts, KPI cards, and interactive filters.

  • Display total sales, total profit, and profit margin.
  • Create suitable column, bar, and line charts.
  • Use slicers to filter dashboard results.
  • Arrange charts and KPIs in a readable layout.
  • Verify that dashboard figures match the source data.
3.4 VBA Programming Exercises

Practise writing macros that reduce repetitive manual operations and improve the consistency of reporting tasks.

  • Write procedures and use variables.
  • Work with worksheets, ranges, and workbook objects.
  • Use loops and conditions to process records.
  • Handle common runtime errors.
  • Automate report formatting and PDF export.
3.5 Final Assignments

Apply multiple Excel features to a larger practical task. Organize the source data, calculate the required metrics, prepare a dashboard, and present the final results.

Practice approach: Understand the requirement first, prepare a suitable dataset, choose the correct Excel feature, execute the solution, and verify the result.

4. Preparing a Dataset for Practice

A consistent dataset makes it easier to practise formulas, PivotTables, charts, and dashboards. For this example, use a sales dataset containing a date, region, product, quantity, unit price, and cost per unit.

Sample Sales Dataset
Order ID Date Region Product Quantity Unit Price (₹) Cost per Unit (₹)
ORD001 01-Oct-2026 West Laptop 2 50,000 35,000
ORD002 02-Oct-2026 South Mobile 3 20,000 14,000
ORD003 03-Oct-2026 North Tablet 4 12,000 8,000
ORD004 04-Oct-2026 West Monitor 5 10,000 7,000
Recommended Calculated Columns
Column Formula for Row 2 Purpose
Total Sales =E2*F2 Calculates quantity multiplied by unit price.
Total Cost =E2*G2 Calculates quantity multiplied by cost per unit.
Profit =H2-I2 Calculates sales minus cost, assuming H and I contain the preceding calculations.
Note: In the calculated-column example, first add Total Sales in column H and Total Cost in column I. Then use Profit in column J with =H2-I2. Adjust column references if your worksheet uses a different layout.

5. Example: Solving an Excel Formula Exercise

Suppose an employee earns a basic salary of ₹30,000 and receives an allowance of ₹5,000. The employee's gross salary is the sum of the basic salary and allowance.

Input Data
Employee Basic Salary Allowance Gross Salary
Employee A ₹30,000 ₹5,000 ₹35,000
Excel Formula
Excel Formula
=B2+C2

If the basic salary is in cell B2 and the allowance is in cell C2, the formula adds both values and returns ₹35,000. You can fill the formula down to calculate the gross salary for other employees.

Conditional Example

Suppose employees with gross salaries of ₹35,000 or more should be classified as "Eligible" for a hypothetical salary-based exercise.

Excel Formula
=IF(D2>=35000,"Eligible","Not Eligible")

This formula checks whether D2 is greater than or equal to ₹35,000. It returns "Eligible" when the condition is true and "Not Eligible" otherwise.

Expected result: The employee's gross salary is ₹35,000, so the example formula returns "Eligible". This is a practice rule, not a real employment eligibility policy.

6. Recommended Practice Workflow

Follow a consistent process for every exercise. This helps you understand not only how to produce an answer but also how to verify whether the answer is correct.

Step-by-Step Exercise Workflow
Step 1: Understand the Requirement Step 2: Prepare and Check the Dataset Step 3: Apply the Excel Solution Step 4: Verify the Result Step 5: Record the Solution and Learn
  1. Understand: Identify the required output and business rule.
  2. Prepare: Check headers, data types, blank cells, and duplicate records.
  3. Implement: Use the appropriate formula, PivotTable, chart, or macro.
  4. Verify: Compare the result with a manual calculation or trusted total.
  5. Document: Record the formula or steps and explain why they work.

7. Self-Assessment Checklist

Use this checklist to review your readiness before moving to the next practice module.






Use this checklist for your own review. Your selections are not saved to the server by this page.

8. Practice Guidelines

Guideline Why It Matters
Use a separate practice copy Protects the original dataset from accidental changes.
Maintain consistent headers Helps formulas, PivotTables, and VBA identify the correct fields.
Check formulas carefully Prevents incorrect references and calculations.
Refresh PivotTables Ensures summaries reflect the latest source records.
Use readable charts Makes the business message easier to understand.
Test macros safely Reduces the risk of unwanted workbook changes.
Verify the final output Improves accuracy and confidence in the report.

9. Getting Started: Practice Tasks

Complete these introductory tasks before moving to the dedicated exercise pages.

  1. Create a sample employee dataset containing Employee ID, Name, Department, and Salary.
  2. Calculate total salary and average salary using Excel formulas.
  3. Use an IF formula to classify salaries based on a specified threshold.
  4. Create a sales dataset with Date, Region, Product, Quantity, and Sales.
  5. Summarize sales by region using a PivotTable.
  6. Create a chart based on the summarized results.
  7. Identify three KPIs that would be useful on a sales dashboard.
  8. Write down one repetitive Excel operation that could be automated with VBA.
Expected outcome: You should have a basic dataset, working formulas, a PivotTable summary, and a clear idea of how the results could be presented in a dashboard.

10. Important Points

  • Practise Excel concepts using realistic sample datasets.
  • Understand the requirement before selecting a formula or feature.
  • Check data quality before performing calculations.
  • Verify formulas and PivotTable totals against the source data.
  • Use suitable charts and readable labels for dashboard reporting.
  • Test VBA macros using copies of important workbooks.
  • Document your approach so that you can explain and reproduce the solution.

11. Summary

The Excel Practice module provides a structured path for strengthening formula skills, analysing data with PivotTables, developing dashboards, and automating repetitive tasks with VBA.

By preparing suitable datasets, solving practical exercises, and checking the results, you can build confidence in applying Excel to business problems. The later assignments bring these skills together in more complete reporting solutions.

Start with Excel Formula Exercises, continue to PivotTable and Dashboard Exercises, and then practise VBA Programming before attempting the Final Assignments.