Design your email template with placeholders in double curly braces like {{FirstName}}:

Create a comma-separated list of data with headers in the first row:

Click the button to generate personalized emails for each record:

Output:

Create two sheets:

• Data: Contains your records (headers in row 1)
• Template: Contains your email template with placeholders

Insert a new module and paste this code:

Sub PerformMailMerge()
    Dim wsData As Worksheet, wsTemplate As Worksheet
    Dim rngData As Range, lastRow As Long, i As Integer
    
    ' Set worksheet references
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsTemplate = ThisWorkbook.Sheets("Template")
    
    ' Find last row of data
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' Set data range (headers in row 1)
    Set rngData = wsData.Range("A2:D" & lastRow)
    
    ' Clear previous output
    wsTemplate.Range("Output").ClearContents
    
    ' Loop through records
    For i = 1 To rngData.Rows.Count
        Dim mergedText As String
        mergedText = wsTemplate.Range("TemplateText").Value
        
        ' Replace placeholders with actual values
        mergedText = Replace(mergedText, "{{Name}}", rngData.Cells(i, 1).Value)
        mergedText = Replace(mergedText, "{{Product}}", rngData.Cells(i, 2).Value)
        mergedText = Replace(mergedText, "{{Amount}}", Format(rngData.Cells(i, 3).Value, "$#,##0.00"))
        mergedText = Replace(mergedText, "{{Date}}", Format(rngData.Cells(i, 4).Value, "mmmm d, yyyy"))
        
        ' Output merged text
        wsTemplate.Range("Output").Offset(i - 1, 0).Value = mergedText
    Next i
    
    MsgBox "Mail merge completed for " & rngData.Rows.Count & " records.", vbInformation
End Sub

Press F5 to execute the code. The merged emails will appear in your Template sheet.

• lookup_value: Value to search for
• table_array: Range containing the data
• col_index_num: Column number to return (starting from 1)
• range_lookup: FALSE for exact match, TRUE for approximate match

To find the department for employee ID 102:

This will return "Sales"

To find the salary for "Bob Johnson":

This will return "$7,800"

MATCH finds the position, INDEX returns the value at that position.

To find the department for employee ID 102:

Returns "Sales"

To find EmployeeID for "Bob Johnson":

Returns 103

Go to File > Options > Customize Ribbon > Check "Developer" > OK

On the Developer tab, click "Record Macro". Perform some actions (like formatting cells), then stop recording.

Press Alt+F11 to open the VBA editor. You'll see the generated code in Module1.

Insert this code to automatically format a sales report:

Sub FormatSalesReport()
    ' Turn off screen updating for better performance
    Application.ScreenUpdating = False
    
    With ActiveSheet
        ' Autofit all columns
        .Columns.AutoFit
        
        ' Format headers
        With .Range("A1:G1")
            .Font.Bold = True
            .Interior.Color = RGB(200, 230, 255)  ' Light blue
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
        
        ' Format currency columns
        .Range("F2:F100").NumberFormat = "$#,##0.00"
        
        ' Add alternating row colors
        With .Range("A2:G100")
            .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
            .FormatConditions(1).Interior.Color = RGB(240, 240, 240)
        End With
    End With
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "Report formatted successfully!", vbInformation
End Sub

On the Developer tab, insert a button and assign this macro to it for one-click formatting.

Common function numbers: 1=AVERAGE, 2=COUNT, 9=SUM, 11=VAR

For East region total:

Returns $2,500

For West region average:

Returns $1,900

Ensure your data has:

• No blank rows or columns
• Clear headers in the first row
• Consistent data in each column

Select your data > Insert > PivotTable > Choose location

Drag fields to these areas:

• Rows: Categories to group by (e.g., Region)
• Columns: Secondary categories (e.g., Quarter)
• Values: Numbers to calculate (e.g., Sum of Sales)
• Filters: Optional filters

Sub CreatePivotAutomatically()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache, pt As PivotTable
    Dim rngData As Range, lastRow As Long
    
    ' Set references
    Set wsData = ThisWorkbook.Sheets("SalesData")
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotAnalysis"
    
    ' Find last row dynamically
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    Set rngData = wsData.Range("A1:G" & lastRow)
    
    ' Create pivot cache and table
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range("B3"), TableName:="SalesPivot")
    
    ' Configure fields
    With pt
        ' Add row and column fields
        .AddFields RowFields:="Region", ColumnFields:="Quarter"
        
        ' Add data field
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
        
        ' Format numbers
        .DataBodyRange.NumberFormat = "$#,##0"
        
        ' Apply style
        .TableStyle2 = "PivotStyleMedium9"
    End With
    
    MsgBox "Pivot table created successfully!", vbInformation
End Sub