How to Create a Dynamic Excel Project Tracker Dashboard with Claude AI (Step-by-Step Guide)

Meta Title: Create an Excel Project Tracker Dashboard with Claude AI

Meta Description: Learn how to build a professional Excel Project Tracker Dashboard using Claude AI and VBA with interactive charts, KPIs, slicers, and one-click automation.

Focus Keyword: Excel Project Tracker Dashboard

Introduction

Building a professional Excel Project Tracker Dashboard traditionally required advanced Excel skills, formulas, PivotTables, charts, and VBA programming. Thanks to Claude AI, the entire workflow has become much faster and easier.

Instead of writing hundreds of lines of VBA code manually, you simply upload your Excel workbook, describe your dashboard requirements, and let Claude AI generate production-ready VBA code. Within minutes you can create a professional dashboard with KPI cards, charts, slicers, and automation.

If you’re new to dashboard automation, don’t miss our guide on Excel Dashboard in 60 Seconds, where we demonstrate how quickly a professional dashboard can be built.

Why Use Claude AI?

Generate VBA automatically
Create dashboards in minutes
Reduce manual coding
Build professional reports
Create interactive KPI cards
Generate charts automatically
Easy customization

If you also build business reports, check out our Excel Sales Dashboard tutorial.

Requirements

Microsoft Excel 365
Developer Tab enabled
Claude AI
Project data
Dashboard Prompt
VBA Prompt

Step 1 – Upload Your Excel File

Upload your workbook to Claude AI. Claude understands the table structure and prepares VBA specifically for your workbook.

ProjectTracker_SampleData Download

Step 2 – Paste Your Dashboard Prompt

Describe your dashboard including KPIs, charts, slicers, summary cards, and project status.

If you frequently automate PowerPoint presentations, read our guide on Automatic Table of Contents.

ProjectTracker_VBA_Prompt Download

Step 3 – Add the VBA Prompt

Add a second prompt requesting clean VBA with error handling, dynamic charts, formatting, and automation.

You can also explore our PowerPoint VBA Tricks article.

Step 4 – Generate VBA Code

Press Enter. Claude AI generates complete VBA code. Copy the generated code.

Option Explicit

'=====================================================================
' MODULE-LEVEL CONSTANTS AND VARIABLES
'=====================================================================
Private Const DATA_SHEET   As String = "Data"
Private Const PIVOT_SHEET  As String = "Pivot"
Private Const DASH_SHEET   As String = "Dashboard"
Private Const TABLE_NAME   As String = "tblProjectTasks"
Private Const PCT_FORMAT   As String = "0%"
Private BuildStep As String

'=====================================================================
' PUBLIC ENTRY POINT
'=====================================================================
Public Sub BuildProjectTrackerDashboard()
    On Error GoTo ErrHandler

    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic

    BuildStep = "RemoveAllSlicerCaches"
    ShowProgress "Clearing old slicers..."
    RemoveAllSlicerCaches

    BuildStep = "PrepareDataSheet"
    ShowProgress "Validating and preparing Data sheet..."
    PrepareDataSheet

    BuildStep = "BuildPivotSheet"
    ShowProgress "Building Pivot sheet..."
    BuildPivotSheet

    BuildStep = "BuildDashboardSheet"
    ShowProgress "Building Dashboard sheet..."
    BuildDashboardSheet

    Application.StatusBar = False
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Dashboard Created Successfully", vbInformation, "Done"
    Exit Sub

ErrHandler:
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    MsgBox "Error in step: " & BuildStep & vbCrLf & _
           "Error " & Err.Number & ": " & Err.Description, vbCritical, "Build Failed"
End Sub

'=====================================================================
' PROGRESS / TIMING HELPERS
'=====================================================================
Private Sub ShowProgress(ByVal msg As String)
    Application.StatusBar = "Project Tracker Dashboard: " & msg
    DoEvents
    Wait 0.12
End Sub

Private Sub Wait(ByVal seconds As Double)
    Dim t As Double
    t = Timer
    Do While Timer - t < seconds
        DoEvents
    Loop
End Sub

'=====================================================================
' GENERIC HELPERS
'=====================================================================
Private Function LastUsedRow(ws As Worksheet, ByVal col As Long) As Long
    LastUsedRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Private Function LastUsedCol(ws As Worksheet, ByVal rowIdx As Long) As Long
    LastUsedCol = ws.Cells(rowIdx, ws.Columns.Count).End(xlToLeft).Column
End Function

Private Function HeaderColumn(ws As Worksheet, ByVal headerName As String) As Long
    Dim lastCol As Long, i As Long
    lastCol = LastUsedCol(ws, 1)
    HeaderColumn = 0
    For i = 1 To lastCol
        If Trim(LCase(CStr(ws.Cells(1, i).Value))) = Trim(LCase(headerName)) Then
            HeaderColumn = i
            Exit Function
        End If
    Next i
End Function

Private Sub RequireHeader(ws As Worksheet, ByVal headerName As String)
    If HeaderColumn(ws, headerName) = 0 Then
        Err.Raise vbObjectError + 1000, "RequireHeader", _
            "Required column header '" & headerName & "' was not found on sheet '" & ws.Name & "'."
    End If
End Sub

Private Sub DeleteSheetIfExists(ByVal sheetName As String)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = False
    End If
End Sub

Private Sub RemoveAllSlicerCaches()
    Dim i As Long
    On Error Resume Next
    For i = ThisWorkbook.SlicerCaches.Count To 1 Step -1
        ThisWorkbook.SlicerCaches(i).Delete
    Next i
    On Error GoTo 0
End Sub

Private Function PivotDataRowsCount(pt As PivotTable) As Long
    On Error Resume Next
    PivotDataRowsCount = pt.RowRange.Rows.Count - 1
    On Error GoTo 0
End Function

Private Sub FormatPivotValues(pt As PivotTable, ByVal fmt As String)
    On Error Resume Next
    pt.DataBodyRange.NumberFormat = fmt
    On Error GoTo 0
End Sub

Private Function SafeNumberFormat(ByVal rng As Range, ByVal fmt As String) As Boolean
    On Error GoTo Fail
    rng.NumberFormat = fmt
    SafeNumberFormat = True
    Exit Function
Fail:
    SafeNumberFormat = False
End Function

'=====================================================================
' STEP 1: PREPARE DATA SHEET
'=====================================================================
Private Sub PrepareDataSheet()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim tbl As ListObject
    Dim lo As ListObject
    Dim requiredHeaders As Variant
    Dim i As Long, r As Long

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(DATA_SHEET)
    On Error GoTo 0
    If ws Is Nothing Then
        Err.Raise vbObjectError + 1001, "PrepareDataSheet", _
            "A sheet named '" & DATA_SHEET & "' was not found in this workbook."
    End If

    requiredHeaders = Array("Task ID", "Start Date", "Due Date", "Project", _
                            "Owner", "Status", "Priority", "% Complete")
    For i = LBound(requiredHeaders) To UBound(requiredHeaders)
        RequireHeader ws, CStr(requiredHeaders(i))
    Next i

    Dim taskIdCol As Long, dueCol As Long, statusCol As Long, startCol As Long, pctCol As Long
    taskIdCol = HeaderColumn(ws, "Task ID")
    dueCol = HeaderColumn(ws, "Due Date")
    statusCol = HeaderColumn(ws, "Status")
    startCol = HeaderColumn(ws, "Start Date")
    pctCol = HeaderColumn(ws, "% Complete")

    lastRow = LastUsedRow(ws, taskIdCol)
    lastCol = LastUsedCol(ws, 1)

    ' add / reuse "Is Overdue" helper column
    Dim isOverdueCol As Long
    isOverdueCol = HeaderColumn(ws, "Is Overdue")
    If isOverdueCol = 0 Then
        isOverdueCol = lastCol + 1
        ws.Cells(1, isOverdueCol).Value = "Is Overdue"
        lastCol = isOverdueCol
    End If

    ws.Cells(2, isOverdueCol).Formula = _
        "=IF(AND(" & ws.Cells(2, dueCol).Address(False, False) & "<TODAY()," & _
        ws.Cells(2, statusCol).Address(False, False) & "<>""Completed""),""Overdue"",""On Track"")"
    If lastRow > 2 Then
        ws.Cells(2, isOverdueCol).Copy Destination:=ws.Range(ws.Cells(3, isOverdueCol), ws.Cells(lastRow, isOverdueCol))
    End If
    Application.CutCopyMode = False

    ' normalize % Complete: if stored as 0-100 rather than 0-1, convert to decimal
    Dim isWholeScale As Boolean, v As Variant
    isWholeScale = False
    For r = 2 To lastRow
        v = ws.Cells(r, pctCol).Value
        If IsNumeric(v) Then
            If v > 1 Then
                isWholeScale = True
                Exit For
            End If
        End If
    Next r
    If isWholeScale Then
        For r = 2 To lastRow
            v = ws.Cells(r, pctCol).Value
            If IsNumeric(v) Then ws.Cells(r, pctCol).Value = CDbl(v) / 100
        Next r
    End If

    ' remove any existing table wrapper so it can be rebuilt cleanly
    For Each lo In ws.ListObjects
        lo.Unlist
    Next lo

    Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)), , xlYes)
    tbl.Name = TABLE_NAME

    ws.Range(ws.Cells(2, startCol), ws.Cells(lastRow, startCol)).NumberFormat = "dd-mmm-yyyy"
    ws.Range(ws.Cells(2, dueCol), ws.Cells(lastRow, dueCol)).NumberFormat = "dd-mmm-yyyy"
    ws.Range(ws.Cells(2, pctCol), ws.Cells(lastRow, pctCol)).NumberFormat = PCT_FORMAT

    With ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol))
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(30, 58, 95)
    End With

    ws.Columns.AutoFit
End Sub

'=====================================================================
' STEP 2: BUILD PIVOT SHEET
'=====================================================================
Private Sub BuildPivotSheet()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache
    Dim tbl As ListObject

    DeleteSheetIfExists DASH_SHEET
    DeleteSheetIfExists PIVOT_SHEET

    Set wsData = ThisWorkbook.Worksheets(DATA_SHEET)
    Set tbl = wsData.ListObjects(TABLE_NAME)

    ThisWorkbook.Worksheets.Add(After:=wsData).Name = PIVOT_SHEET
    Set wsPivot = ThisWorkbook.Worksheets(PIVOT_SHEET)

    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tbl.Range)

    ShowProgress "Building pivot: Tasks by Status..."
    BuildCountPivot pc, wsPivot, "A1", "PT_TasksByStatus", "Status"

    ShowProgress "Building pivot: Tasks by Owner..."
    BuildCountPivot pc, wsPivot, "D1", "PT_TasksByOwner", "Owner"

    ShowProgress "Building pivot: Completion Trend..."
    BuildTrendPivot pc, wsPivot, "G1", "PT_CompletionTrend"

    ShowProgress "Building pivot: Priority Breakdown..."
    BuildCountPivot pc, wsPivot, "J1", "PT_PriorityBreakdown", "Priority"

    ShowProgress "Building pivot: Project Load..."
    BuildCountPivot pc, wsPivot, "M1", "PT_ProjectLoad", "Project"

    ShowProgress "Building pivot: KPI summary..."
    BuildKPIPivot pc, wsPivot, "P1", "PT_KPI"

    ShowProgress "Building pivot: Overdue Status..."
    BuildCountPivot pc, wsPivot, "S1", "PT_OverdueStatus", "Is Overdue"

    wsPivot.Columns.AutoFit
End Sub

Private Sub BuildCountPivot(pc As PivotCache, wsPivot As Worksheet, ByVal destCell As String, _
                            ByVal ptName As String, ByVal rowField As String)
    Dim pt As PivotTable
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range(destCell), TableName:=ptName)
    With pt
        .PivotFields(rowField).Orientation = xlRowField
        .RowAxisLayout xlTabularRow
        With .PivotFields("Task ID")
            .Orientation = xlDataField
            .Function = xlCount
            .Caption = "Count of Task ID"
        End With
        .ColumnGrand = False
        .RowGrand = False
    End With
    On Error Resume Next
    pt.PivotFields(rowField).Subtotals(1) = False
    On Error GoTo 0
End Sub

Private Sub BuildTrendPivot(pc As PivotCache, wsPivot As Worksheet, ByVal destCell As String, ByVal ptName As String)
    Dim pt As PivotTable
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range(destCell), TableName:=ptName)
    With pt
        .PivotFields("Start Date").Orientation = xlRowField
        .RowAxisLayout xlTabularRow
        With .PivotFields("% Complete")
            .Orientation = xlDataField
            .Function = xlAverage
            .Caption = "Average of % Complete"
            .NumberFormat = "0.0%"
        End With
        .ColumnGrand = False
        .RowGrand = False
    End With
    On Error Resume Next
    pt.PivotFields("Start Date").LabelRange.Group Start:=True, End:=True, _
        Periods:=Array(False, False, False, False, True, False, False)
    On Error GoTo 0
End Sub

Private Sub BuildKPIPivot(pc As PivotCache, wsPivot As Worksheet, ByVal destCell As String, ByVal ptName As String)
    Dim pt As PivotTable
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range(destCell), TableName:=ptName)
    With pt
        With .PivotFields("Task ID")
            .Orientation = xlDataField
            .Function = xlCount
            .Caption = "Count of Task ID"
        End With
        With .PivotFields("% Complete")
            .Orientation = xlDataField
            .Function = xlAverage
            .Caption = "Average of % Complete"
            .NumberFormat = "0.0%"
        End With
    End With
End Sub

'=====================================================================
' STEP 3: BUILD DASHBOARD SHEET
'=====================================================================
Private Sub BuildDashboardSheet()
    Dim wsDash As Worksheet

    ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1)).Name = DASH_SHEET
    Set wsDash = ThisWorkbook.Worksheets(DASH_SHEET)
    wsDash.Activate

    ActiveWindow.Zoom = 70
    ActiveWindow.DisplayHeadings = True

    ShowProgress "Applying dashboard canvas..."
    ApplyDashboardCanvas wsDash

    ShowProgress "Creating header banner..."
    CreateHeader wsDash

    ShowProgress "Creating KPI helper formulas..."
    CreateKPIHelperFormulas wsDash

    ShowProgress "Creating KPI cards..."
    CreateKPICards wsDash

    ShowProgress "Creating dashboard charts..."
    CreateDashboardCharts wsDash

    ShowProgress "Creating Project slicer..."
    CreateProjectSlicer wsDash

    ShowProgress "Creating Owner slicer..."
    CreateOwnerSlicer wsDash

    ShowProgress "Applying final formatting..."
    FinalDashboardFormatting wsDash

    wsDash.Activate
    ActiveWindow.Zoom = 70
    ActiveWindow.DisplayHeadings = True
End Sub

Private Sub ApplyDashboardCanvas(ws As Worksheet)
    Dim c As Long
    With ws.Cells
        .Interior.Color = RGB(245, 247, 250)
        .Font.Name = "Aptos"
        .Font.Size = 10
    End With

    For c = 1 To 30
        ws.Columns(c).ColumnWidth = 7.3
    Next c

    ws.Rows("1:3").RowHeight = 22
    ws.Rows("4:29").RowHeight = 18

    ActiveWindow.DisplayGridlines = False
End Sub

Private Sub CreateHeader(ws As Worksheet)
    Dim banner As Shape, icon As Shape, titleBox As Shape, dateBox As Shape
    Dim bannerLeft As Double, bannerTop As Double, bannerWidth As Double, bannerHeight As Double

    With ws.Range("A1:AD3")
        bannerLeft = .Left
        bannerTop = .Top
        bannerWidth = .Width
        bannerHeight = .Height
    End With

    Set banner = ws.Shapes.AddShape(msoShapeRectangle, bannerLeft, bannerTop, bannerWidth, bannerHeight)
    With banner.Fill
        .Visible = msoTrue
        .TwoColorGradient msoGradientHorizontal, 1
        .GradientStops(1).Color.RGB = RGB(30, 58, 95)
        .GradientStops(2).Color.RGB = RGB(40, 160, 150)
    End With
    banner.Line.Visible = msoFalse
    banner.Name = "DashHeaderBanner"

    Set icon = ws.Shapes.AddShape(msoShapeOval, bannerLeft + 12, bannerTop + (bannerHeight - 42) / 2, 42, 42)
    icon.Fill.ForeColor.RGB = RGB(255, 255, 255)
    icon.Line.Visible = msoFalse
    With icon.TextFrame2.TextRange
        .Text = "P"
        .Font.Size = 22
        .Font.Bold = msoTrue
        .Font.Fill.ForeColor.RGB = RGB(30, 58, 95)
    End With
    icon.TextFrame2.VerticalAnchor = msoAnchorMiddle
    icon.TextFrame2.HorizontalAnchor = msoAnchorCenter
    icon.Name = "DashHeaderIcon"

    Set titleBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, bannerLeft + 70, bannerTop, bannerWidth - 280, bannerHeight)
    With titleBox.TextFrame
        .Characters.Text = "PROJECT TRACKER DASHBOARD | FY 2026"
        .Characters.Font.Size = 44
        .Characters.Font.Bold = True
        .Characters.Font.Color = RGB(255, 255, 255)
        .Characters.Font.Name = "Aptos"
        .VerticalAlignment = xlVAlignCenter
        .HorizontalAlignment = xlHAlignLeft
    End With
    titleBox.TextFrame2.WordWrap = msoFalse
    titleBox.TextFrame2.AutoSize = msoAutoSizeNone
    titleBox.Fill.Visible = msoFalse
    titleBox.Line.Visible = msoFalse
    titleBox.Name = "DashHeaderTitle"

    Set dateBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, bannerLeft + bannerWidth - 200, bannerTop, 190, bannerHeight)
    With dateBox.TextFrame
        .Characters.Text = "Data as of: " & Format(Date, "dd-mmm-yyyy")
        .Characters.Font.Size = 18
        .Characters.Font.Bold = True
        .Characters.Font.Color = RGB(255, 255, 255)
        .Characters.Font.Name = "Aptos"
        .VerticalAlignment = xlVAlignCenter
        .HorizontalAlignment = xlHAlignRight
    End With
    dateBox.TextFrame2.WordWrap = msoTrue
    dateBox.Fill.Visible = msoFalse
    dateBox.Line.Visible = msoFalse
    dateBox.Name = "DashHeaderDate"
End Sub

Private Sub CreateKPIHelperFormulas(ws As Worksheet)
    Dim t As String
    t = TABLE_NAME

    With ws
        .Range("AF2").Formula = "=COUNTA(" & t & "[Task ID])"
        .Range("AF3").Formula = "=IF(AF2=0,0,COUNTIFS(" & t & "[Status],""Completed"")/AF2)"
        .Range("AF4").Formula = "=COUNTIFS(" & t & "[Is Overdue],""Overdue"")"
        .Range("AF5").Formula = "=COUNTIFS(" & t & "[Status],""In Progress"")"
        .Range("AF6").Formula = "=IFERROR(AVERAGEIFS(" & t & "[Due Date]," & t & "[Status],""Completed"")-AVERAGEIFS(" & t & "[Start Date]," & t & "[Status],""Completed""),0)"
        .Range("AF7").Formula = "=SUMPRODUCT((( " & t & "[Priority]=""High"")+(" & t & "[Priority]=""Critical""))*(" & t & "[Status]<>""Completed""))"

        .Range("AF2").NumberFormat = "#,##0"
        .Range("AF3").NumberFormat = "0.0%"
        .Range("AF4").NumberFormat = "#,##0"
        .Range("AF5").NumberFormat = "#,##0"
        .Range("AF6").NumberFormat = "0.0"
        .Range("AF7").NumberFormat = "#,##0"

        .Range("AG2").Formula = "=TEXT(AF2,""#,##0"")"
        .Range("AG3").Formula = "=TEXT(AF3,""0.0%"")"
        .Range("AG4").Formula = "=TEXT(AF4,""#,##0"")"
        .Range("AG5").Formula = "=TEXT(AF5,""#,##0"")"
        .Range("AG6").Formula = "=TEXT(AF6,""0.0"")"
        .Range("AG7").Formula = "=TEXT(AF7,""#,##0"")"
    End With
End Sub

Private Sub AddKPICard(ws As Worksheet, ByVal addr As String, ByVal title As String, _
                       ByVal valueCellAddr As String, ByVal iconGlyph As String, _
                       ByVal subtitle As String, ByVal accentRGB As Long)
    Dim rng As Range
    Dim cardShape As Shape, iconShape As Shape
    Dim titleBox As Shape, valueBox As Shape, subtitleBox As Shape
    Dim L As Double, T As Double, W As Double, H As Double

    Set rng = ws.Range(addr)
    L = rng.Left: T = rng.Top: W = rng.Width: H = rng.Height

    Set cardShape = ws.Shapes.AddShape(msoShapeRoundedRectangle, L, T, W, H)
    With cardShape
        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .Line.ForeColor.RGB = RGB(208, 220, 230)
        .Line.Weight = 0.75
        .Shadow.Visible = msoTrue
        .Name = "KPICard_" & Replace(Replace(title, " ", ""), "%", "Pct")
    End With

    Set iconShape = ws.Shapes.AddShape(msoShapeOval, L + 10, T + (H - 38) / 2, 38, 38)
    With iconShape.Fill
        .Visible = msoTrue
        .OneColorGradient msoGradientDiagonalUp, 1, 0.3
        .ForeColor.RGB = accentRGB
    End With
    iconShape.Line.Visible = msoFalse
    With iconShape.TextFrame2.TextRange
        .Text = iconGlyph
        .Font.Size = 13
        .Font.Bold = msoTrue
        .Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
    End With
    iconShape.TextFrame2.VerticalAnchor = msoAnchorMiddle
    iconShape.TextFrame2.HorizontalAnchor = msoAnchorCenter

    Set titleBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, L + 56, T + 4, W - 62, 20)
    With titleBox.TextFrame
        .Characters.Text = title
        .Characters.Font.Size = 11
        .Characters.Font.Bold = True
        .Characters.Font.Color = RGB(45, 55, 65)
        .Characters.Font.Name = "Aptos"
    End With
    titleBox.Fill.Visible = msoFalse
    titleBox.Line.Visible = msoFalse

    Set valueBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, L + 56, T + 22, W - 62, H - 44)
    valueBox.Fill.Visible = msoFalse
    valueBox.Line.Visible = msoFalse
    valueBox.TextFrame.Characters.Text = " "
    valueBox.TextFrame.Characters.Font.Size = 20
    valueBox.TextFrame.Characters.Font.Bold = True
    valueBox.TextFrame.Characters.Font.Color = RGB(20, 40, 70)
    valueBox.TextFrame.Characters.Font.Name = "Aptos"
    valueBox.Select
    Selection.Formula = "=" & ws.Name & "!" & valueCellAddr

    Set subtitleBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, L + 56, T + H - 18, W - 62, 16)
    With subtitleBox.TextFrame
        .Characters.Text = subtitle
        .Characters.Font.Size = 8
        .Characters.Font.Color = RGB(90, 95, 100)
        .Characters.Font.Name = "Aptos"
    End With
    subtitleBox.Fill.Visible = msoFalse
    subtitleBox.Line.Visible = msoFalse
End Sub

Private Sub CreateKPICards(ws As Worksheet)
    ShowProgress "KPI card: Total Tasks..."
    AddKPICard ws, "A5:E8", "Total Tasks", "AG2", "#", "All Tasks", RGB(30, 58, 95)

    ShowProgress "KPI card: Completed %..."
    AddKPICard ws, "F5:J8", "Completed %", "AG3", "%", "Tasks Done", RGB(40, 120, 140)

    ShowProgress "KPI card: Overdue Tasks..."
    AddKPICard ws, "K5:O8", "Overdue Tasks", "AG4", "!", "Past Due Date", RGB(190, 70, 50)

    ShowProgress "KPI card: In Progress..."
    AddKPICard ws, "P5:T8", "In Progress", "AG5", ">", "Active Now", RGB(40, 160, 150)

    ShowProgress "KPI card: High-Priority Open..."
    AddKPICard ws, "U5:Y8", "High-Priority Open", "AG7", "^", "Needs Attention", RGB(210, 140, 30)

    ShowProgress "KPI card: Avg Completion (days)..."
    AddKPICard ws, "Z5:AD8", "Avg Completion (days)", "AG6", "T", "Per Task", RGB(60, 90, 130)
End Sub

'=====================================================================
' CHARTS — now genuine PivotCharts sourced directly from PivotTables
' so they auto-refresh whenever a connected slicer filters the data
'=====================================================================
Private Function AddPivotChart(ws As Worksheet, ByVal destAddr As String, pt As PivotTable, _
                               ByVal chartType As XlChartType, ByVal chartTitle As String) As ChartObject
    Dim rng As Range, co As ChartObject
    Set rng = ws.Range(destAddr)
    Set co = ws.ChartObjects.Add(rng.Left, rng.Top, rng.Width, rng.Height)
    With co.Chart
        .SetSourceData Source:=pt.TableRange1
        .ChartType = chartType
        .HasTitle = True
        .ChartTitle.Text = chartTitle
    End With
    Set AddPivotChart = co
End Function

Private Function AddPivotChartFixedCm(ws As Worksheet, ByVal destAddr As String, pt As PivotTable, _
                                      ByVal chartType As XlChartType, ByVal chartTitle As String, _
                                      ByVal heightCm As Double, ByVal shrinkFactor As Double) As ChartObject
    Dim rng As Range, co As ChartObject, h As Double
    Set rng = ws.Range(destAddr)
    h = Application.CentimetersToPoints(heightCm) * shrinkFactor
    Set co = ws.ChartObjects.Add(rng.Left, rng.Top, rng.Width, h)
    With co.Chart
        .SetSourceData Source:=pt.TableRange1
        .ChartType = chartType
        .HasTitle = True
        .ChartTitle.Text = chartTitle
    End With
    Set AddPivotChartFixedCm = co
End Function

Private Sub FormatChart(co As ChartObject, ByVal chartKind As String)
    Dim cht As Chart
    Set cht = co.Chart

    co.Border.LineStyle = 1
    co.Border.Color = RGB(208, 220, 230)
    co.Border.Weight = 0.7

    ' hide the default pivot field buttons for a clean dashboard look
    On Error Resume Next
    With cht.PivotLayout
        .ShowAxisFieldButtons = False
        .ShowLegendFieldButtons = False
        .ShowReportFilterFieldButtons = False
        .ShowValueFieldButtons = False
    End With
    On Error GoTo 0

    With cht.ChartTitle.Font
        .Color = RGB(20, 40, 70)
        .Bold = True
        .Size = 12
        .Name = "Aptos"
    End With

    On Error Resume Next
    With cht.SeriesCollection(1)
        .HasDataLabels = True
        .DataLabels.Font.Size = 8
        .DataLabels.Font.Bold = True
        .DataLabels.Font.Color = RGB(55, 55, 55)
    End With
    On Error GoTo 0

    If LCase(chartKind) = "trend" Then
        On Error Resume Next
        cht.SeriesCollection(1).DataLabels.NumberFormat = "0%"
        On Error GoTo 0
    End If

    Select Case LCase(chartKind)
        Case "owner"
            cht.HasLegend = True
            cht.Legend.Position = xlLegendPositionRight
        Case "project"
            cht.HasLegend = False
        Case "status"
            cht.HasLegend = True
            cht.Legend.Position = xlLegendPositionBottom
            On Error Resume Next
            cht.Legend.Left = 229.614
            cht.Legend.Width = 206.285
            On Error GoTo 0
        Case Else
            cht.HasLegend = True
            cht.Legend.Position = xlLegendPositionBottom
    End Select
End Sub

Private Sub CreateDashboardCharts(ws As Worksheet)
    Dim wsPivot As Worksheet
    Dim co As ChartObject
    Set wsPivot = ThisWorkbook.Worksheets(PIVOT_SHEET)

    ShowProgress "Chart: Tasks by Status..."
    Set co = AddPivotChart(ws, "A10:L18", wsPivot.PivotTables("PT_TasksByStatus"), xlDoughnut, "Tasks by Status")
    FormatChart co, "status"

    ShowProgress "Chart: Tasks by Owner..."
    Set co = AddPivotChart(ws, "M10:V18", wsPivot.PivotTables("PT_TasksByOwner"), xlBarClustered, "Tasks by Owner")
    FormatChart co, "owner"

    ShowProgress "Chart: Completion Trend..."
    Set co = AddPivotChart(ws, "A19:L28", wsPivot.PivotTables("PT_CompletionTrend"), xlLineMarkers, "Completion Trend")
    FormatChart co, "trend"

    ShowProgress "Chart: Priority Breakdown..."
    Set co = AddPivotChart(ws, "M19:V28", wsPivot.PivotTables("PT_PriorityBreakdown"), xlColumnClustered, "Priority Breakdown")
    FormatChart co, "priority"

    ShowProgress "Chart: Project Load..."
    Set co = AddPivotChartFixedCm(ws, "W19:AD28", wsPivot.PivotTables("PT_ProjectLoad"), xlColumnClustered, "Project Load", 7.18, 0.8986)
    FormatChart co, "project"
End Sub

'=====================================================================
' SLICERS
'=====================================================================
Private Sub AddPivotToSlicer(sc As SlicerCache, pt As PivotTable)
    Dim already As Boolean
    Dim spt As PivotTable
    already = False
    On Error Resume Next
    For Each spt In sc.PivotTables
        If spt.Name = pt.Name Then already = True
    Next spt
    On Error GoTo 0
    If Not already Then
        On Error Resume Next
        sc.PivotTables.AddPivotTable pt
        On Error GoTo 0
    End If
End Sub

Private Sub CreateProjectSlicer(ws As Worksheet)
    AddSlicerForField ws, "Project", "W10:AD14", "Project"
End Sub

Private Sub CreateOwnerSlicer(ws As Worksheet)
    AddSlicerForField ws, "Owner", "W15:AD18", "Owner"
End Sub

Private Sub AddSlicerForField(ws As Worksheet, ByVal fieldName As String, ByVal destAddr As String, ByVal captionText As String)
    Dim wsPivot As Worksheet
    Dim pt As PivotTable, pt2 As PivotTable
    Dim sc As SlicerCache
    Dim sl As Slicer
    Dim rng As Range
    Dim ptNames As Variant, i As Long
    Dim hasField As Boolean

    Set wsPivot = ThisWorkbook.Worksheets(PIVOT_SHEET)
    ptNames = Array("PT_TasksByStatus", "PT_TasksByOwner", "PT_CompletionTrend", _
                     "PT_PriorityBreakdown", "PT_ProjectLoad", "PT_KPI", "PT_OverdueStatus")

    Set pt = Nothing
    For i = LBound(ptNames) To UBound(ptNames)
        Set pt2 = Nothing
        On Error Resume Next
        Set pt2 = wsPivot.PivotTables(CStr(ptNames(i)))
        hasField = False
        If Not pt2 Is Nothing Then hasField = Not pt2.PivotFields(fieldName) Is Nothing
        On Error GoTo 0
        If hasField Then
            Set pt = pt2
            Exit For
        End If
    Next i

    If pt Is Nothing Then
        Err.Raise vbObjectError + 1002, "AddSlicerForField", _
            "Could not find field '" & fieldName & "' on any pivot table to build a slicer."
    End If

    Set sc = ThisWorkbook.SlicerCaches.Add2(pt, fieldName)
    Set rng = ws.Range(destAddr)
    Set sl = sc.Slicers.Add(ws, , sc.Name, captionText, rng.Top, rng.Left, rng.Width, rng.Height)
    sl.Style = "SlicerStyleLight6"
    sl.Caption = captionText
    sl.NumberOfColumns = 6

    For i = LBound(ptNames) To UBound(ptNames)
        Set pt2 = Nothing
        On Error Resume Next
        Set pt2 = wsPivot.PivotTables(CStr(ptNames(i)))
        On Error GoTo 0
        If Not pt2 Is Nothing Then AddPivotToSlicer sc, pt2
    Next i
End Sub

Private Sub FinalDashboardFormatting(ws As Worksheet)
    With ws.Range("A1:AD29")
        .Font.Name = "Aptos"
        .VerticalAlignment = xlCenter
    End With

    ws.Range("AF:AG").EntireColumn.Hidden = True
    ActiveWindow.DisplayGridlines = False

    With ws.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
    End With
End Sub

Step 5 – Open VBA Editor

Go to Developer → Visual Basic.

Step 6 – Insert Module

Insert a new module and paste the VBA code.

For HR automation, see our Salary Slip Generator using Excel VBA.

Step 7 – Create a Button

Insert a rounded rectangle shape and rename it “Generate Dashboard”.

Step 8 – Assign the Macro

Right-click the button, choose Assign Macro, select your macro, and click OK.

Step 9 – Generate Your Dashboard

Click the button to automatically create:

KPI Cards
Project Summary
Charts
Slicers
Department Reports
Status Analysis
Completion Trends

Real-World Applications

Inventory Management

Build warehouse dashboards using our Inventory Management Dashboard.

Expense Tracking

Create finance dashboards using our Excel Expense Tracker.

School Infrastructure

Monitor inspections using Project INSPECT School Infrastructure Dashboard.

Benefits

Save hours every week
Reduce coding errors
Create professional dashboards
Refresh reports with one click
Easy maintenance

Frequently Asked Questions

Can beginners use this?

Yes. Claude AI generates the VBA code for you.

Is it dynamic?

Yes. Refresh your data and regenerate the dashboard anytime.

Can I customize it?

Absolutely. Change colors, charts, KPIs, and layouts whenever needed.

Final Thoughts

Claude AI dramatically reduces the time needed to build professional Excel dashboards. Whether you manage projects, sales, HR, inventory, or finance, combining AI with Excel VBA allows you to automate repetitive work and build dashboards that update in just one click.

How to Create a Dynamic Excel Project Tracker Dashboard with Claude AI (Step-by-Step Guide-2026)