Finding Patterns in Large Datasets

You have thousands or tens of thousands of data rows in Excel... feeling overwhelmed about where to start? Is sales trending up, are there seasonal patterns, any abnormal values... checking all this manually is endless.

Finding hidden patterns in data is the core of data analysis. You need to identify trends, discover seasonality, detect outliers, and find relationships between variables to gain meaningful insights.

With AI, you can easily perform these complex statistical analyses. Even without professional statistical knowledge, just say "show me the trend" or "find abnormal values" and AI creates the analysis code.

In this post, we'll learn how to find patterns in large datasets with AI. From time series analysis to seasonality discovery, outlier detection, and correlation analysis—we'll cover practical techniques you need at work.

1. Time Series Pattern Analysis

Trend Line Analysis

AI Prompt:

"Analyze increase/decrease trends in monthly sales data
and predict next month's sales."

AI Generated Formulas:

// Linear trend forecast
=FORECAST.LINEAR(A2, HistoricalSales!$B:$B, HistoricalSales!$A:$A)

// Or TREND function
=TREND(HistoricalSales!$B$2:$B$37, HistoricalSales!$A$2:$A$37, A2)

// Apply seasonality index
=C2*INDEX(SeasonalIndex!$B:$B, MATCH(MONTH(A2), SeasonalIndex!$A:$A, 0))

// Confidence interval (upper/lower bounds)
// Calculate standard error
=STEYX(HistoricalSales!$B$2:$B$37, HistoricalSales!$A$2:$A$37)

// 95% confidence interval upper bound
=D2+1.96*$StandardError

// 95% confidence interval lower bound
=D2-1.96*$StandardError

VBA for Trend Analysis:

Sub SalesTrendAnalysis()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim trend As String
    Dim avgGrowth As Double
    Dim forecast As Double

    Set ws = ThisWorkbook.Sheets("SalesData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    ' Calculate month-over-month growth rate
    ws.Cells(1, 3).Value = "MoM Change(%)"
    For i = 3 To lastRow
        ws.Cells(i, 3).Formula = "=(B" & i & "-B" & i - 1 & ")/B" & i - 1 & "*100"
    Next i

    ' Average growth rate
    avgGrowth = Application.WorksheetFunction.Average( _
        ws.Range("C3:C" & lastRow))

    ' Determine trend
    If avgGrowth > 5 Then
        trend = "Strong uptrend"
    ElseIf avgGrowth > 0 Then
        trend = "Moderate uptrend"
    ElseIf avgGrowth > -5 Then
        trend = "Moderate downtrend"
    Else
        trend = "Strong downtrend"
    End If

    ' Next month forecast (simple linear)
    forecast = ws.Cells(lastRow, 2).Value * (1 + avgGrowth / 100)

    ' Create results sheet
    Dim resultWs As Worksheet
    On Error Resume Next
    Set resultWs = Sheets("TrendAnalysisResults")
    If resultWs Is Nothing Then
        Set resultWs = Sheets.Add(After:=ws)
        resultWs.Name = "TrendAnalysisResults"
    Else
        resultWs.Cells.Clear
    End If
    On Error GoTo 0

    With resultWs
        .Cells(1, 1).Value = "Sales Trend Analysis Report"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        .Cells(3, 1).Value = "Analysis Period:"
        .Cells(3, 2).Value = ws.Cells(2, 1).Value & " ~ " & ws.Cells(lastRow, 1).Value

        .Cells(4, 1).Value = "Average Growth Rate:"
        .Cells(4, 2).Value = Format(avgGrowth, "0.00") & "%"

        .Cells(5, 1).Value = "Trend:"
        .Cells(5, 2).Value = trend

        .Cells(6, 1).Value = "Recent Sales:"
        .Cells(6, 2).Value = Format(ws.Cells(lastRow, 2).Value, "#,##0")

        .Cells(7, 1).Value = "Next Month Forecast:"
        .Cells(7, 2).Value = Format(forecast, "#,##0")

        .Columns("A:B").AutoFit
    End With

    Application.ScreenUpdating = True

    MsgBox "Trend analysis complete.", vbInformation
End Sub

2. Discovering Seasonality Patterns

AI Prompt:

"Find seasonal patterns in 3 years of monthly sales data.
Analyze which months have high and low sales."

AI Generated VBA:

Sub SeasonalityAnalysis()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim monthlyAvg(1 To 12) As Double
    Dim monthCount(1 To 12) As Integer
    Dim i As Long
    Dim month As Integer

    Set ws = ThisWorkbook.Sheets("SalesData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Calculate monthly averages
    For i = 2 To lastRow
        month = Month(ws.Cells(i, 1).Value)
        monthlyAvg(month) = monthlyAvg(month) + ws.Cells(i, 2).Value
        monthCount(month) = monthCount(month) + 1
    Next i

    ' Calculate averages
    For i = 1 To 12
        If monthCount(i) > 0 Then
            monthlyAvg(i) = monthlyAvg(i) / monthCount(i)
        End If
    Next i

    ' Overall average
    Dim totalAvg As Double
    totalAvg = Application.WorksheetFunction.Average( _
        ws.Range("B2:B" & lastRow))

    ' Results sheet
    Dim resultWs As Worksheet
    On Error Resume Next
    Set resultWs = Sheets("SeasonalityAnalysis")
    If resultWs Is Nothing Then
        Set resultWs = Sheets.Add(After:=ws)
        resultWs.Name = "SeasonalityAnalysis"
    Else
        resultWs.Cells.Clear
    End If
    On Error GoTo 0

    With resultWs
        .Cells(1, 1).Value = "Month"
        .Cells(1, 2).Value = "Average Sales"
        .Cells(1, 3).Value = "Index"
        .Cells(1, 4).Value = "Characteristics"

        For i = 1 To 12
            .Cells(i + 1, 1).Value = i & " Month"
            .Cells(i + 1, 2).Value = Format(monthlyAvg(i), "#,##0")

            ' Seasonal index (ratio to average)
            Dim seasonIndex As Double
            seasonIndex = monthlyAvg(i) / totalAvg

            .Cells(i + 1, 3).Value = Format(seasonIndex, "0.00")

            ' Categorize characteristics
            If seasonIndex >= 1.2 Then
                .Cells(i + 1, 4).Value = "Peak Season"
                .Rows(i + 1).Interior.Color = RGB(146, 208, 80)
            ElseIf seasonIndex >= 1.1 Then
                .Cells(i + 1, 4).Value = "Semi-Peak"
                .Rows(i + 1).Interior.Color = RGB(255, 242, 204)
            ElseIf seasonIndex <= 0.8 Then
                .Cells(i + 1, 4).Value = "Off Season"
                .Rows(i + 1).Interior.Color = RGB(255, 199, 206)
            Else
                .Cells(i + 1, 4).Value = "Normal"
            End If
        Next i

        .Columns("A:D").AutoFit
    End With

    MsgBox "Seasonality analysis complete.", vbInformation
End Sub

3. Outlier Detection

AI Prompt:

"Find abnormally high or low values in sales data.
Use statistical methods (Z-score or IQR)."

AI Generated Formulas:

// Z-score method
// Column C: Calculate Z-score
=ABS((B2-AVERAGE($B$2:$B$1000))/STDEV($B$2:$B$1000))

// Column D: Outlier determination (|Z| > 2)
=IF(C2>2, "Outlier", "Normal")

// IQR method
// Column E: Q1 (25th percentile)
=QUARTILE($B$2:$B$1000, 1)

// Column F: Q3 (75th percentile)
=QUARTILE($B$2:$B$1000, 3)

// Column G: IQR
=F2-E2

// Column H: Outlier determination
=IF(OR(B2<E2-1.5*G2, B2>F2+1.5*G2), "Outlier", "Normal")

4. Correlation Analysis

AI Prompt:

"Analyze the correlation between advertising spend and sales.
I want to see if sales increase when advertising spend increases."

AI Generated Formulas:

// Correlation coefficient (CORREL)
=CORREL(B2:B100, C2:C100)

// Coefficient of determination R² (RSQ)
=RSQ(B2:B100, C2:C100)

// Regression slope
=SLOPE(C2:C100, B2:B100)

// Regression intercept
=INTERCEPT(C2:C100, B2:B100)

// Predicted value
=Slope*B2+Intercept

5. Customer Segmentation (RFM Analysis)

AI Prompt:

"Segment customer data by RFM (Recency, Frequency, Monetary) criteria
to classify VIP, Regular, and At-Risk customers."

AI Generated VBA:

Sub RFMAnalysis()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim today As Date
    Dim recency As Long
    Dim frequency As Long
    Dim monetary As Double

    Set ws = ThisWorkbook.Sheets("CustomerTransactions")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    today = Date

    ' Customer aggregation sheet
    Dim resultWs As Worksheet
    On Error Resume Next
    Set resultWs = Sheets("RFMAnalysis")
    If resultWs Is Nothing Then
        Set resultWs = Sheets.Add(After:=ws)
        resultWs.Name = "RFMAnalysis"
    Else
        resultWs.Cells.Clear
    End If
    On Error GoTo 0

    ' Headers
    With resultWs
        .Cells(1, 1).Value = "Customer ID"
        .Cells(1, 2).Value = "Customer Name"
        .Cells(1, 3).Value = "Last Purchase"
        .Cells(1, 4).Value = "R(days)"
        .Cells(1, 5).Value = "F(times)"
        .Cells(1, 6).Value = "M(won)"
        .Cells(1, 7).Value = "R Score"
        .Cells(1, 8).Value = "F Score"
        .Cells(1, 9).Value = "M Score"
        .Cells(1, 10).Value = "RFM Score"
        .Cells(1, 11).Value = "Customer Grade"
    End With

    ' Calculate RFM scores and grade customers...
    ' (Implementation continues with scoring logic)

    MsgBox "RFM analysis complete.", vbInformation
End Sub

Conclusion

Finding data patterns with AI:

• ✅ Time series trends and seasonality analysis
• ✅ Statistical outlier detection
• ✅ Discovering correlations between variables
• ✅ Customer segmentation and association rule analysis

In the next post, we'll create financial forecasting models using these analyses.