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

Data Cleaning & Analysis Using Excel VBA

Learn how to clean consolidated Excel data, handle missing values, identify duplicate records, validate business information, calculate sales metrics, and prepare accurate datasets for automated reporting.

1. Introduction to Data Cleaning and Analysis

After consolidating records from multiple workbooks, the next step is to check whether the collected data is complete, consistent, and suitable for analysis.

Source workbooks may contain duplicate order IDs, blank product names, invalid quantities, inconsistent branch names, incorrectly formatted dates, or incorrect sales calculations. If these issues are not handled, reports and dashboards may display misleading information.

Data Cleaning identifies and handles data quality problems. Data Analysis uses the cleaned data to calculate totals, compare business performance, and identify useful patterns.

Learning objective: Build an Excel VBA workflow that validates sales records, identifies duplicates, handles incomplete data according to defined rules, and generates useful business metrics.

2. Data Cleaning and Analysis Workflow

Data quality checks should happen before calculating totals or creating reports. The following workflow shows the recommended sequence for the sales capstone project.

Consolidated Sales Data Input from the master worksheet Data Quality Checks Blanks, duplicates, formats, and invalid values Clean and Validate Apply business rules and document exceptions Calculate Business Metrics Total sales, branch totals, and product totals Verify Results Check record counts and calculation accuracy Analysis-Ready Dataset Ready for reports and dashboard automation

Diagram 1: Clean and validate the data before using it for business calculations and reporting.

3. Understanding Common Data Quality Problems

Consider the following sample sales dataset. It contains several problems that should be identified before calculating business performance.

Order ID Branch Product Quantity Unit Price Total Amount Issue
ORD1001 Pune Laptop 2 45000 90000 Valid
ORD1002 Mumbai Monitor 3 12000 36000 Valid
ORD1002 Mumbai Monitor 3 12000 36000 Duplicate
ORD1003 Nashik 5 1500 7500 Blank product
ORD1004 Pune Keyboard -2 1500 -3000 Invalid quantity
ORD1005 Mumbai Mouse 4 500 2500 Wrong total

These examples demonstrate why data quality matters. A duplicate record may overstate sales, a negative quantity may indicate an invalid entry or a return, and a wrong total can produce incorrect reports.

Business rule matters: Do not automatically delete every duplicate-looking record or every negative quantity. A duplicate order ID may require investigation, and negative quantities may represent legitimate returns. Decide how to handle these records before modifying the data.

4. Define Data Cleaning Rules

Before writing VBA, establish rules that define what a valid sales record looks like. The rules should be agreed upon by the business team and applied consistently.

Field Validation Rule Suggested Action
Order ID Must not be blank. Flag incomplete records for review.
Order Date Must be a valid Excel date. Flag invalid dates.
Branch Must match an approved branch name. Standardize known spelling variations.
Product Must not be blank. Flag incomplete records.
Quantity Must follow the agreed quantity rules. Flag invalid values; handle returns separately.
Unit Price Must be numeric and follow pricing rules. Flag invalid values for review.
Total Amount Should match the approved calculation rule. Recalculate or flag mismatches.

5. Cleaning Extra Spaces and Text Values

Text imported from different systems may contain leading spaces, trailing spaces, or inconsistent capitalization. For example, " Pune " and "Pune" may be treated as different values during grouping.

Example: Trim Text and Standardize Branch Names
VBA Code
Sub CleanBranchNames()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Master")

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

    For r = 2 To lastRow

        If Not IsError(ws.Cells(r, "C").Value) Then

            If Len(Trim$(CStr(ws.Cells(r, "C").Value))) > 0 Then

                Select Case LCase$(Trim$(CStr(ws.Cells(r, "C").Value)))

                    Case "pune"
                        ws.Cells(r, "C").Value = "Pune"

                    Case "mumbai"
                        ws.Cells(r, "C").Value = "Mumbai"

                    Case "nashik"
                        ws.Cells(r, "C").Value = "Nashik"

                End Select

            End If

        End If

    Next r

    MsgBox "Branch name cleaning completed.", vbInformation

End Sub
Explanation
  • Trim$() removes leading and trailing spaces.
  • LCase$() converts text to lowercase for comparison.
  • Select Case matches recognized branch names.
  • The original branch values are replaced only when a recognized name is found.

This example handles common spacing and capitalization differences. It does not automatically correct every spelling mistake. Unknown branch names should be reviewed rather than guessed.

6. Identifying Blank and Incomplete Records

Blank values can affect calculations and reports. For example, an order without an Order ID may be difficult to trace, and a record without a product name may be unsuitable for product-level analysis.

A safe initial approach is to flag incomplete rows instead of immediately deleting them. This allows the data owner to decide whether the missing value can be recovered.

Example: Flag Missing Required Fields
VBA Code
Sub FlagIncompleteRecords()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Master")

    ws.Range("H1").Value = "Data Quality Status"

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

    For r = 2 To lastRow

        If IsError(ws.Cells(r, "A").Value) Or _
           IsError(ws.Cells(r, "C").Value) Or _
           IsError(ws.Cells(r, "D").Value) Then

            ws.Cells(r, "H").Value = "Review Required"

        ElseIf Len(Trim$(CStr(ws.Cells(r, "A").Value))) = 0 Or _
               Len(Trim$(CStr(ws.Cells(r, "C").Value))) = 0 Or _
               Len(Trim$(CStr(ws.Cells(r, "D").Value))) = 0 Then

            ws.Cells(r, "H").Value = "Review Required"

        Else

            ws.Cells(r, "H").Value = "Complete"

        End If

    Next r

    MsgBox "Incomplete records have been flagged.", vbInformation

End Sub

In this example, column H records whether the Order ID, Branch, or Product field needs review. Adjust the columns to match your actual worksheet.

Good practice: Keep a record of flagged rows and the reason they were flagged. Missing information should be corrected from a trusted source instead of being filled with invented values.

7. Identifying Duplicate Records

Duplicate records can inflate sales totals and produce inaccurate business reports. However, the definition of a duplicate must be clear before any record is removed.

For this example, the business treats an identical Order ID as a potential duplicate. If an Order ID can legitimately contain multiple line items, a composite key such as Order ID plus Product or Line ID may be more appropriate.

Example: Flag Repeated Order IDs
VBA Code
Sub FlagDuplicateOrderIDs()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim orderID As String

    Set ws = ThisWorkbook.Worksheets("Master")

    ws.Range("I1").Value = "Duplicate Status"

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

    For r = 2 To lastRow

        If IsError(ws.Cells(r, "A").Value) Then

            ws.Cells(r, "I").Value = "Review Required"

        Else

            orderID = Trim$(CStr(ws.Cells(r, "A").Value))

            If Len(orderID) = 0 Then

                ws.Cells(r, "I").Value = "Missing Order ID"

            ElseIf Application.CountIf( _
                ws.Range("A2:A" & lastRow), orderID) > 1 Then

                ws.Cells(r, "I").Value = "Potential Duplicate"

            Else

                ws.Cells(r, "I").Value = "Unique"

            End If

        End If

    Next r

    MsgBox "Duplicate check completed.", vbInformation

End Sub
How It Works
  • CountIf counts how many times an Order ID appears.
  • IDs appearing more than once are flagged as potential duplicates.
  • Blank or error-valued IDs are identified separately.
  • The macro flags records for review; it does not delete them.
Important: Review potential duplicates before deleting anything. Two records with the same Order ID may represent legitimate order lines, updates, or returns depending on the business system.

8. Data Validation Decision Process

Validation checks whether a record meets the defined business rules. Records that fail a check should be flagged, corrected from a reliable source, or excluded from a calculation according to the approved policy.

Read a Sales Record Required Fields Valid? No Flag for Review Record the issue Yes Check Business Rules and values Calculate Approved Metrics Verify Totals and Record Counts Prepare analysis-ready data

Diagram 2: Validation separates records needing review from records that are ready for approved calculations.

9. Validating Quantity, Price, and Total Amount

Numeric values should be checked before they are used in financial calculations. In this example, the business rule requires quantity and unit price to be numeric and non-negative. If your organization handles returns or adjustments, define a separate rule for those transactions.

Example: Validate Numeric Fields
VBA Code
Sub ValidateSalesValues()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim quantityValue As Variant
    Dim priceValue As Variant

    Set ws = ThisWorkbook.Worksheets("Master")

    ws.Range("J1").Value = "Numeric Validation"

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

    For r = 2 To lastRow

        quantityValue = ws.Cells(r, "E").Value
        priceValue = ws.Cells(r, "F").Value

        If IsError(quantityValue) Or IsError(priceValue) Then

            ws.Cells(r, "J").Value = "Review Required"

        ElseIf Not IsNumeric(quantityValue) Or _
               Not IsNumeric(priceValue) Then

            ws.Cells(r, "J").Value = "Invalid Numeric Value"

        ElseIf CDbl(quantityValue) < 0 Or CDbl(priceValue) < 0 Then

            ws.Cells(r, "J").Value = "Negative Value - Review"

        Else

            ws.Cells(r, "J").Value = "Valid"

        End If

    Next r

    MsgBox "Numeric validation completed.", vbInformation

End Sub
Explanation
  • IsNumeric checks whether a value can be interpreted as numeric.
  • CDbl converts a validated numeric value to Double for comparison.
  • Negative values are flagged rather than automatically deleted.
  • The macro records the validation status in column J.

10. Recalculating and Checking Total Amount

In the sample sales dataset, the expected total is calculated as Quantity multiplied by Unit Price. If the stored Total Amount differs from that calculation, the row should be investigated.

Example: Check Calculated Totals
VBA Code
Sub CheckTotalAmounts()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    Dim quantityValue As Variant
    Dim priceValue As Variant
    Dim totalValue As Variant
    Dim expectedTotal As Double

    Set ws = ThisWorkbook.Worksheets("Master")

    ws.Range("K1").Value = "Total Check"

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

    For r = 2 To lastRow

        quantityValue = ws.Cells(r, "E").Value
        priceValue = ws.Cells(r, "F").Value
        totalValue = ws.Cells(r, "G").Value

        If IsError(quantityValue) Or _
           IsError(priceValue) Or _
           IsError(totalValue) Then

            ws.Cells(r, "K").Value = "Review Required"

        ElseIf Not IsNumeric(quantityValue) Or _
               Not IsNumeric(priceValue) Or _
               Not IsNumeric(totalValue) Then

            ws.Cells(r, "K").Value = "Invalid Numeric Value"

        Else

            expectedTotal = CDbl(quantityValue) * CDbl(priceValue)

            If Abs(CDbl(totalValue) - expectedTotal) > 0.01 Then

                ws.Cells(r, "K").Value = "Mismatch"

            Else

                ws.Cells(r, "K").Value = "Correct"

            End If

        End If

    Next r

    MsgBox "Total amount verification completed.", vbInformation

End Sub

The tolerance of 0.01 allows a small difference when comparing decimal amounts. Adjust it to match the precision and rounding rules used by the business.

Important: A mismatch does not always mean the source value is wrong. Discounts, taxes, shipping, returns, and rounding may affect the actual total. Confirm the approved calculation formula before changing any financial values.

11. Performing Basic Sales Analysis

Once records are cleaned and validated, the next step is to calculate useful business metrics. These metrics can help management understand total sales, branch performance, and product performance.

Common Sales Metrics
Metric Meaning Example Calculation
Total Sales Sum of approved transaction amounts. Sum of Total Amount column.
Total Orders Count of valid order records. Count records using the approved order definition.
Branch Sales Sales attributed to each branch. Sum amounts for Pune, Mumbai, or Nashik.
Product Sales Sales attributed to each product. Sum amounts for each product.
Average Order Value Average value per qualifying order. Total sales divided by qualifying orders.
Decide whether your analysis uses transaction rows, unique orders, net sales, or gross sales. These definitions can produce different results, especially when orders contain multiple lines or returns.

12. Example: Calculate Total Sales

The following macro calculates the sum of the Total Amount column. Run it only after deciding which records are eligible for inclusion.

VBA Code
Sub CalculateTotalSales()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim totalSales As Double

    Set ws = ThisWorkbook.Worksheets("Master")

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

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

    totalSales = Application.Sum(ws.Range("G2:G" & lastRow))

    ws.Range("M1").Value = "Sales Summary"
    ws.Range("M2").Value = "Total Sales"
    ws.Range("N2").Value = totalSales

    ws.Range("N2").NumberFormat = "#,##0.00"
    ws.Columns("M:N").AutoFit

    MsgBox "Total sales calculated successfully.", vbInformation

End Sub
How It Works
  • Application.Sum adds numeric values in the specified range.
  • The result is stored in cell N2.
  • Number formatting makes the result easier to read.
  • The example sums all numeric amounts in column G, so exclude or correct invalid records before using the result as an official report.

13. Example: Calculate Sales by Branch

Branch-level analysis helps compare sales performance across locations. The macro below creates a small summary for Pune, Mumbai, and Nashik.

VBA Code
Sub CalculateBranchSales()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim branchNames As Variant
    Dim i As Long
    Dim outputRow As Long

    Set ws = ThisWorkbook.Worksheets("Master")

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

    branchNames = Array("Pune", "Mumbai", "Nashik")

    ws.Range("P1").Value = "Branch"
    ws.Range("Q1").Value = "Total Sales"

    For i = LBound(branchNames) To UBound(branchNames)

        outputRow = i + 2

        ws.Cells(outputRow, "P").Value = branchNames(i)

        ws.Cells(outputRow, "Q").Value = _
            Application.SumIf( _
                ws.Range("C2:C" & lastRow), _
                branchNames(i), _
                ws.Range("G2:G" & lastRow))

    Next i

    ws.Range("Q2:Q4").NumberFormat = "#,##0.00"
    ws.Columns("P:Q").AutoFit

    MsgBox "Branch sales summary completed.", vbInformation

End Sub

Application.SumIf sums the Total Amount values where the Branch column matches the selected branch name.

For official reporting, use the approved dataset after validation and ensure that returns, cancelled orders, and duplicate records are handled according to the business rules.

14. From Clean Data to Business Insights

Cleaning and analysis prepare the data for the next project stages. A reliable dataset can be used to produce consistent PDF reports and dashboards.

Clean Data Validated records Consistent values Reviewed exceptions Sales Analysis Total sales Branch comparison Product metrics PDF Reports Printable summaries Shareable outputs Dashboard Charts and KPIs Performance overview

Diagram 3: Validated data becomes the foundation for analysis, PDF reporting, and dashboard automation.

15. Testing and Verifying the Results

A macro should not be considered complete merely because it runs without an error. The results must also be checked for accuracy.

  • Compare source and master record counts.
  • Check whether required fields are blank.
  • Review all flagged duplicate Order IDs.
  • Confirm that numeric values follow the agreed rules.
  • Compare calculated totals with independently verified values.
  • Confirm that legitimate returns or adjustments are handled correctly.
  • Keep a record of corrected, excluded, or unresolved rows.
Testing tip: Use a small sample dataset containing known duplicates, blanks, invalid numbers, and incorrect totals. Verify that the macro flags each problem correctly before processing a larger workbook.

16. Practical Exercises

  1. Remove leading and trailing spaces from branch and product names.
  2. Flag records that have missing Order IDs, branches, or product names.
  3. Identify repeated Order IDs without deleting the records automatically.
  4. Validate quantity and unit price according to an agreed business rule.
  5. Check whether Total Amount matches Quantity multiplied by Unit Price.
  6. Calculate total sales for the approved dataset.
  7. Create a branch-level sales summary.
  8. Compare the results before and after cleaning.
  9. Document how returns, cancelled orders, and incomplete records should be handled.

17. Important Points to Remember

  • Define data quality rules before changing source records.
  • Flag suspicious values for review rather than guessing corrections.
  • Do not assume every repeated Order ID is a duplicate transaction.
  • Handle missing values according to business requirements.
  • Validate financial calculations before preparing reports.
  • Keep a backup of the consolidated data before cleaning.
  • Verify totals and record counts after processing.
  • Use the validated dataset as input for automated PDF reports and dashboards.

Summary

Data Cleaning and Analysis is an essential stage of the Corporate Capstone Project. It improves the quality of consolidated sales records through text standardization, missing-value checks, duplicate identification, numeric validation, and calculation verification.

After applying the agreed business rules, analyze the approved data to calculate sales totals and branch performance. Validate the results before using the dataset in the next stage: Automated PDF Reports.