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 Final Assignments

Apply your Excel, Advanced Excel, PivotTable, dashboard, and VBA programming skills through practical business assignments. These projects will help you practise data preparation, analysis, reporting, automation, validation, and professional report creation.

1. Introduction to Final Assignments

Final assignments combine the topics covered in the Excel Practice module. Instead of practising each feature separately, you will use formulas, tables, PivotTables, charts, and VBA macros to solve practical business problems.

Imagine that you are working as a reporting analyst in a company. The company provides sales records and expects you to prepare accurate summaries, identify business trends, create a dashboard, and automate repetitive reporting tasks.

Complete the assignments in sequence. Start with data preparation, proceed to formulas and PivotTables, create the dashboard, and then automate suitable tasks using VBA.

Learning objective: Develop a complete Excel reporting solution that uses clean data, correct calculations, meaningful visualizations, and reliable automation.
Final Assignment Workflow
1. Prepare and Validate Data 2. Calculate with Excel Formulas 3. Analyze Using PivotTables 4. Build an Interactive Dashboard 5. Automate and Deliver the Report

Follow the workflow from source-data preparation to the final validated and automated report.

2. Assignment 1: Create a Sales Dataset

Create a new workbook named FinalSalesAssignments.xlsx. Add a worksheet named SalesData and enter the following sample records.

Order ID Date Region Product Quantity Sales (₹) Cost (₹)
ORD001 01-Oct-2026 West Laptop 2 1,00,000 70,000
ORD002 02-Oct-2026 South Mobile 3 60,000 42,000
ORD003 03-Oct-2026 North Tablet 4 48,000 32,000
ORD004 04-Oct-2026 West Laptop 1 50,000 35,000
ORD005 05-Oct-2026 North Mobile 2 40,000 28,000
Tasks to Complete
  1. Enter all records with the correct column headings.
  2. Format the Date column as a valid Excel date.
  3. Format Sales and Cost as currency values.
  4. Convert the data range into an Excel Table.
  5. Name the table tblSales.
  6. Check that Order ID values are unique and required fields are populated.
Important: Keep the column headings consistent. Formulas, PivotTables, and VBA procedures will depend on the structure of this dataset. This five-record dataset is for learning; use more records when testing larger reporting scenarios.

3. Assignment 2: Apply Excel Formulas

Add three calculated columns to the sales dataset: Profit, Profit Margin, and Average Sales per Unit.

If the original columns are A:G, use column H for Profit, column I for Profit Margin, and column J for Average Sales per Unit. Enter the following formulas in row 2 and fill them down.

Excel Formula — H2: Profit
=F2-G2
Excel Formula — I2: Profit Margin
=IFERROR(H2/F2,0)
Excel Formula — J2: Average Sales per Unit
=IFERROR(F2/E2,0)

Format Profit as currency and Profit Margin as a percentage. Average Sales per Unit should use a suitable currency format.

Summary Formula Tasks
No. Requirement Formula Expected Result
1 Total Sales =SUM(F2:F6) ₹2,98,000
2 Total Cost =SUM(G2:G6) ₹2,07,000
3 Total Profit =SUM(H2:H6) ₹91,000
4 Number of Orders =COUNTA(A2:A6) 5
5 Average Order Sales =AVERAGE(F2:F6) ₹59,600
6 West Region Sales =SUMIF(C2:C6,"West",F2:F6) ₹1,50,000
7 Mobile Sales =SUMIF(D2:D6,"Mobile",F2:F6) ₹1,00,000
8 Orders with Sales above ₹50,000 =COUNTIF(F2:F6,">50000") 2
Validation check: Total Profit should equal Total Sales minus Total Cost: ₹2,98,000 − ₹2,07,000 = ₹91,000. The overall profit margin is approximately 30.54%.

4. Assignment 3: Use Lookup and Logical Functions

Create a separate worksheet named ProductMaster. Enter the product details below and practise lookup and logical functions using the master data.

Product Category Target Sales (₹)
Laptop Computers 1,20,000
Mobile Electronics 1,10,000
Tablet Electronics 50,000
Practical Tasks
  1. Use XLOOKUP to retrieve the category for each product in the sales dataset.
  2. Use IF to display "High Value" when sales exceed ₹50,000; otherwise display "Standard".
  3. Use IFERROR to display a helpful message when a lookup value is missing.
  4. Use SUMIFS to calculate sales for a selected region and product.
  5. Use COUNTIFS to count orders that meet two conditions, such as a selected region and product.
Excel Formula — XLOOKUP
=XLOOKUP(D2,ProductMaster!A:A,ProductMaster!B:B,"Not Found")
Excel Formula — IF
=IF(F2>50000,"High Value","Standard")
Excel Formula — SUMIFS
=SUMIFS(F2:F6,C2:C6,"West",D2:D6,"Laptop")
Note: XLOOKUP is available in supported modern Excel versions. If your Excel version does not support it, practise the equivalent lookup using INDEX and MATCH.

5. Assignment 4: Data Cleaning and Validation

Reporting accuracy depends on data quality. Create a copy of the original dataset and intentionally introduce a few test issues, such as extra spaces, inconsistent text case, missing values, duplicate Order IDs, and invalid quantities.

Tasks to Complete
No. Task Expected Outcome
1 Remove unnecessary spaces from text. Consistent region and product names
2 Identify duplicate Order IDs. Duplicates reviewed and resolved
3 Find blank required fields. Missing values identified
4 Validate Quantity values. Only valid positive quantities accepted
5 Standardize region and product names. Consistent category values
6 Check date and currency formats. Correctly stored dates and numbers
Useful Cleaning Formulas
Excel Formula — Remove Extra Spaces
=TRIM(C2)
Excel Formula — Standardize Text Case
=PROPER(TRIM(C2))

Use Data Validation to restrict Quantity to whole numbers greater than zero. Test the rule with both valid and invalid inputs. Use Remove Duplicates only after checking whether duplicate records represent actual errors.

Important: Do not delete records automatically just because they look unusual. Check the source data first, preserve an original copy, and document the cleaning decisions.

6. Assignment 5: Create PivotTables

Use tblSales to create PivotTables that summarize business performance. Place the reports in a worksheet named PivotReports.

No. PivotTable Requirement Rows Values
1 Region-wise Sales Region Sum of Sales
2 Product-wise Profit Product Sum of Profit
3 Region and Product Analysis Region, Product Sum of Sales
4 Order Count by Region Region Count of Order ID
5 Date-wise Sales Date Sum of Sales
Procedure
  1. Select a cell inside tblSales.
  2. Choose Insert → PivotTable.
  3. Select the destination worksheet.
  4. Drag the required fields into Rows, Columns, and Values.
  5. Confirm that Sales and Profit use Sum, not Count.
  6. Apply suitable currency and date formats.
PivotTable Field Arrangement
Source Fields Region Product Sales Date Rows Region Columns Product Values Sum of Sales Filters Date

Field placement determines how the PivotTable groups, summarizes, and filters the source data.

Expected check: The grand total for the Region-wise Sales PivotTable should be ₹2,98,000. If it differs, verify the source range, filters, aggregation method, and duplicate or missing records.

7. Assignment 6: Create Charts and Visual Reports

Create a worksheet named Charts. Use your PivotTable results to create charts that answer clear business questions.

No. Chart Purpose Recommended Type
1 Region-wise Sales Compare sales across regions. Clustered Column
2 Product-wise Sales Compare product performance. Bar or Column
3 Sales and Profit Compare revenue and profitability. Combo Chart, if appropriate
4 Date-wise Sales Review changes over time. Line Chart
Chart Formatting Tasks
  • Add a descriptive title to every chart.
  • Display readable category labels and units.
  • Use consistent number formats.
  • Avoid unnecessary 3-D effects and decorative elements.
  • Check that chart values match the source PivotTable.
Best practice: Choose a chart according to the question being answered. A line chart is useful for trends over time, while a bar or column chart is suitable for comparing categories.

8. Assignment 7: Build an Interactive Dashboard

Create a worksheet named Dashboard. Arrange the key performance indicators at the top and charts below them. Use consistent fonts, spacing, and number formats.

Required KPI Cards
KPI Calculation Expected Result
Total Sales Sum of Sales ₹2,98,000
Total Cost Sum of Cost ₹2,07,000
Total Profit Total Sales − Total Cost ₹91,000
Total Orders Count of Order IDs 5
Average Order Sales Total Sales ÷ Number of Orders ₹59,600
Profit Margin Total Profit ÷ Total Sales Approximately 30.54%
Dashboard Requirements
  1. Add a clear dashboard title.
  2. Create the KPI cards using formula-driven values.
  3. Insert region-wise and product-wise sales charts.
  4. Add a date filter or timeline where appropriate.
  5. Add a Region slicer to compatible PivotTables and PivotCharts.
  6. Connect the slicer to the reports it should control.
  7. Test the figures after changing filters and refreshing the data.
Suggested Dashboard Layout
SALES PERFORMANCE DASHBOARD Total Sales ₹2,98,000 Total Profit ₹91,000 Total Orders 5 Profit Margin 30.54% Region-wise Sales North West South Product-wise Sales Laptop Mobile Tablet Region All North West Illustrative layout — connect charts and filters to real data

Use this layout as a planning guide. The actual chart values, filter controls, and KPI cards must be linked to your workbook data.

9. Assignment 8: Record and Write VBA Macros

Save a copy of your workbook as FinalSalesAssignments.xlsm. Enable the Developer tab and use the Visual Basic Editor to create a standard module.

Task A: Format the Sales Report

Write a macro that formats the header row, adjusts column widths, applies currency formatting to Sales and Cost, and adds a title.

VBA — FormatSalesReport
Option Explicit

Sub FormatSalesReport()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("SalesData")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No sales records are available.", vbExclamation
        Exit Sub
    End If

    With ws.Range("A1:G1")
        .Font.Bold = True
        .Font.Color = vbWhite
        .Interior.Color = RGB(13, 110, 253)
    End With

    ws.Columns("B").NumberFormat = "dd-mmm-yyyy"
    ws.Range("F2:G" & lastRow).NumberFormat = "₹#,##0.00"

    ws.Columns("A:G").AutoFit

    MsgBox "Sales report formatting completed.", vbInformation

End Sub

Run the macro and verify that the heading format, date display, currency values, and column widths are correct.

Task B: Add a Profit Column
VBA — CalculateProfit
Option Explicit

Sub CalculateProfit()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("SalesData")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No sales records are available.", vbExclamation
        Exit Sub
    End If

    ws.Range("H1").Value = "Profit"
    ws.Range("H2:H" & lastRow).Formula = "=F2-G2"

    ws.Range("H2:H" & lastRow).NumberFormat = "₹#,##0.00"
    ws.Columns("H").AutoFit

    MsgBox "Profit calculation completed.", vbInformation

End Sub
Task C: Export the Report as PDF
VBA — ExportSalesReportToPDF
Option Explicit

Sub ExportSalesReportToPDF()

    Dim ws As Worksheet
    Dim outputPath As String

    Set ws = ThisWorkbook.Worksheets("SalesData")

    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook before exporting the PDF.", vbExclamation
        Exit Sub
    End If

    outputPath = ThisWorkbook.Path & Application.PathSeparator & _
                 "SalesReport.pdf"

    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=outputPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    MsgBox "PDF report exported successfully.", vbInformation

End Sub
VBA safety: Save a backup before running macros. Test your code on a copy of the workbook and review file paths and worksheet names before allowing automation to modify or export data.

10. Assignment 9: Automate the Reporting Process

Combine your formatting, calculation, and report preparation procedures into a single master macro. The master procedure should execute each step in the correct order.

VBA — RunSalesReportingProcess
Option Explicit

Sub RunSalesReportingProcess()

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Call FormatSalesReport
    Call CalculateProfit

    ThisWorkbook.Worksheets("SalesData").Activate

    MsgBox "Sales reporting process completed.", vbInformation

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    MsgBox "The reporting process failed: " & Err.Description, _
           vbExclamation
    Resume CleanExit

End Sub

Place the procedures in a standard module. Run RunSalesReportingProcess and verify that the formatting and profit calculation procedures both execute.

Additional Automation Tasks
  • Add a button to run the master reporting macro.
  • Display a confirmation message before exporting a PDF.
  • Check that the source worksheet exists before processing.
  • Add suitable error handling for missing sheets or invalid data.
  • Document the steps required to run the automation.
Note: This master macro demonstrates a basic reporting sequence. PivotTable refresh, dashboard refresh, and PDF export can be added as separate steps after you test each operation independently.

11. Assignment 10: Test and Validate the Workbook

Before delivering the workbook, verify its calculations, PivotTables, charts, and VBA procedures. A report should not be considered complete merely because it looks correct.

No. Test Expected Result
1 Compare Total Sales with the source data. ₹2,98,000 for the supplied sample
2 Verify Total Profit. ₹91,000 for the supplied sample
3 Refresh the PivotTables. Reports reflect the current source range
4 Change the Region slicer selection. Connected reports respond correctly
5 Add a new test record. Table formulas and reports can include the record
6 Run the formatting macro twice. No unexpected changes or duplicate columns
7 Export the report as PDF. File opens and displays the intended report
8 Test missing or invalid inputs. Errors are handled or reported clearly
Validation rule: When you add new records, make sure the source is an Excel Table or that the PivotTable source range is updated. Refreshing a PivotTable alone does not always expand a manually selected source range.

12. Final Capstone Project: Sales Reporting System

Build a complete sales reporting system that combines the skills from all previous assignments. The workbook should support data entry, calculations, analysis, visualization, and report delivery.

Required Worksheets
Worksheet Purpose Required Content
SalesData Store the source records. Structured table and calculated columns
ProductMaster Maintain product reference data. Product, category, and target sales
PivotReports Summarize business performance. Region, product, and date analysis
Dashboard Present important metrics visually. KPI cards, charts, and filters
Instructions Explain how to use the workbook. Refresh, macro, and export instructions
Final Project Deliverables
  1. A clean, structured sales dataset.
  2. Formula-driven Profit and Profit Margin calculations.
  3. Lookup and conditional analysis.
  4. At least three useful PivotTable summaries.
  5. An interactive dashboard with connected filters.
  6. VBA macros for formatting and profit calculation.
  7. A PDF export of the final report.
  8. Testing notes and a short user guide.
Capstone objective: Create a reporting workbook that another user can understand, refresh, validate, and operate without manually repeating every reporting step.

13. Final Assignment Evaluation

Use the following evaluation checklist to review the completed project. Tick each item after testing the corresponding feature.

Criteria What to Check Marks
Data Preparation Correct structure, clean values, and validation 15
Excel Formulas Correct calculations and error handling 20
PivotTables Accurate grouping, summaries, and refresh 15
Dashboard Readable KPI cards, charts, and filters 20
VBA Automation Working macros and basic error handling 20
Documentation Clear instructions and validation evidence 10
Total 100
Evaluation guidance: Award marks based on demonstrated functionality and correctness. A visually attractive dashboard should not receive full marks if its calculations or filters produce incorrect results.

14. Important Points

  • Preserve the original source data before cleaning or modifying it.
  • Use clear column headings and structured Excel Tables.
  • Verify formulas with independently calculated totals.
  • Use the appropriate aggregation method in PivotTables.
  • Connect slicers only to compatible reports and test their behaviour.
  • Save macro-enabled workbooks with the .xlsm extension.
  • Test VBA macros on a copy of the workbook before routine use.
  • Handle missing worksheets, empty data, and invalid inputs safely.
  • Refresh PivotTables and verify that new records are included.
  • Keep the final workbook, exported PDF, and instructions organized.

15. Summary

In this module, you applied Excel formulas, lookup functions, logical functions, data-cleaning techniques, Data Validation, PivotTables, charts, and dashboard design to a practical sales reporting scenario.

You also practised writing VBA macros for report formatting, profit calculations, and PDF export. The automation assignment introduced procedure reuse, execution order, and basic error handling.

Complete the capstone project by validating every calculation, testing interactive controls, and documenting the workbook. These practices will help you build reliable Excel reporting solutions for real business requirements.

Previous: VBA Programming Exercises