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.
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.
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.
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
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 Casematches 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
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.
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
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
CountIfcounts 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.
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.
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
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
IsNumericchecks whether a value can be interpreted as numeric.CDblconverts 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
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.
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. |
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.
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.Sumadds 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.
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.
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.
16. Practical Exercises
- Remove leading and trailing spaces from branch and product names.
- Flag records that have missing Order IDs, branches, or product names.
- Identify repeated Order IDs without deleting the records automatically.
- Validate quantity and unit price according to an agreed business rule.
- Check whether Total Amount matches Quantity multiplied by Unit Price.
- Calculate total sales for the approved dataset.
- Create a branch-level sales summary.
- Compare the results before and after cleaning.
- 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.