👉 After creating all pivot charts, arrange them neatly in a "Dashboard" sheet with attractive colors and formatting.

📋 Separate Worksheets

1. Female Clients - Data Summary (New Functions)

(If Excel 365 / 2021 is available)

excel

=UNIQUE(FILTER(ApplicantData!A:Z, ApplicantData!Gender="Female"))

(You can use SORT with it for better arrangement.)

If Excel doesn't support UNIQUE / FILTER, mention:

"Due to software limitations, UNIQUE and FILTER functions are unavailable. Theoretically, these functions would dynamically extract and sort female client data without manual filtering."

2. Male Clients - Data Summary (List Functions)

Use database functions like:

Sum of Income (Male Clients):

excel

=DSUM(ApplicantData!A:Z, "Income", G1:H2)

Average Loan Amount (Male Clients):

excel

=DAVERAGE(ApplicantData!A:Z, "Loan Amount", G1:H2)

(Where G1:H2 has the condition Gender = Male)

3. Flag Columns Data Summary (DCOUNT Variants)

Example for FLAG_DOCUMENT_2:

Count of FLAG_DOCUMENT_2 = 1:

excel

=DCOUNT(ApplicantData!A:Z, "FLAG_DOCUMENT_2", J1:K2)

(Where J1:K2 is criteria FLAG_DOCUMENT_2 = 1)

Do similar for FLAG_DOCUMENT_3, etc.

🛠 VBA CODES

Macro for Conditional Formatting & Freezing Panes

vba

Sub FormatAndFreeze()
    ' Apply conditional formatting
    With ActiveSheet.UsedRange
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=10000"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Color = -16752384
            .TintAndShade = 0
        End With
    End With

    ' Freeze top row
    ActiveWindow.FreezePanes = False
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
End Sub

VBA to Convert Selected Data into PDF

vba

Sub ExportSelectionToPDF()
    Dim rng As Range
    Set rng = Application.InputBox("Select the range to export", Type:=8)
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\SelectedData.pdf", Quality:=xlQualityStandard
End Sub

VBA to Check Spelling Mistakes

vba

Sub CheckSpelling()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If Not Application.CheckSpelling(cell.Value) Then
            cell.Interior.Color = RGB(255, 0, 0) ' Highlight misspelled word
        End If
    Next cell
End Sub