Building a Real-Time Excel Dashboard with Power Query and VBA: Automate Data Refresh, Exception Alerts, and Conditional Formatting for Operational Monitoring

Introduction

Picture this: it's 9:15 AM and your operations manager just walked in asking why a supplier's on-time delivery rate dropped to 61% — a number that apparently went south sometime yesterday afternoon. Nobody noticed because the monitoring dashboard is a static spreadsheet that someone updates manually on Fridays. By the time the alert is visible, the problem is already a crisis.

That's exactly the kind of operational gap this lesson is designed to close. We're going to build a dashboard that doesn't wait to be updated — it refreshes itself, evaluates its own data for exceptions, fires alerts when thresholds are breached, and applies conditional formatting dynamically so that the right rows demand immediate attention. The result is a living operational monitor, not a historical artifact.

By the end of this lesson, you'll have a complete working system: Power Query pulls and transforms live data from a source file or database, VBA schedules automatic refreshes on a timer, a custom exception engine scans every row after each refresh, color-coded conditional formatting highlights problems instantly, and Outlook email alerts notify stakeholders without anyone lifting a finger.

What you'll learn:

• How to structure a Power Query pipeline specifically for operational monitoring use cases
• How to use Application.OnTime to create a self-scheduling refresh loop in VBA
• How to write an exception detection engine that evaluates KPIs against configurable thresholds
• How to apply and update conditional formatting programmatically with VBA — including rules that respond to business logic, not just cell values
• How to trigger email alerts from VBA when exceptions are detected, and how to avoid alert fatigue with suppression logic

---

Prerequisites

You should be comfortable with Power Query basics (importing data, applying transformations, loading to a worksheet) and have working knowledge of VBA fundamentals — variables, loops, and working with ranges. If you need a refresher on the VBA side, Getting Started with VBA Macros in Excel covers the essentials. You should also understand how Excel Tables (ListObjects) work, since our architecture depends heavily on them — Understanding Excel Tables (ListObjects): Structure, Formulas, and VBA Integration for Dynamic Data Management is the right reference if you need it.

---

Designing the Dashboard Architecture

Before writing a single line of VBA, you need a clear architecture. Dashboards that become unmaintainable usually skip this step, and six months later they're a spaghetti of named ranges, hard-coded cell references, and mystery macros nobody remembers writing.

Here's the layout we'll build:

Sheet: RawData — This is where Power Query loads its output. Never touch this sheet manually. It should be named clearly and ideally hidden from casual users. Our Power Query connection loads a transformed table here called tbl_OperationsRaw.

Sheet: Config — A simple two-column table (tbl_Config) holding threshold parameters like minimum acceptable on-time delivery percentage, maximum allowable defect rate, and so on. Having thresholds here means you can adjust monitoring sensitivity without touching any code.

Sheet: Dashboard — The visible face of the system. It pulls from RawData via formulas or a second Power Query step, displays KPI summaries at the top, and shows the detailed supplier/region/product grid below. This is also where conditional formatting lives.

Sheet: AlertLog — A timestamped log of every exception the system has found. This gives you an audit trail and is the basis for alert suppression — we won't email the same alert ten times in a row.

This separation matters. Power Query owns data ingestion. VBA owns the orchestration logic. Excel formulas and conditional formatting own the visual presentation. Each layer does one job.

Key insight: The biggest mistake in dashboard design is letting VBA manipulate data that Power Query should own. Keep Power Query responsible for all transformation and loading — VBA's job is to trigger the refresh and react to the result, not to re-clean data.

---

Building the Power Query Data Pipeline

Open the Power Query Editor (Data tab → Get Data → Launch Power Query Editor, or click an existing query). For this lesson, our source is a CSV file that a logistics system exports every 15 minutes to a shared network folder, but the same approach works for SQL Server, SharePoint lists, or REST APIs.

Creating the Base Query

Start a new query from the CSV source. After the initial import, you'll apply these transformation steps in order:

1. Promote headers — First row as column names
2. Set data types — Explicitly type every column. Don't let Power Query guess. Set SupplierID as Text, DeliveryDate as Date, OnTimeRate as Decimal Number, DefectRate as Decimal Number, OrderVolume as Whole Number
3. Filter out placeholder rows — If your export includes summary rows or blank spacers at the bottom, filter them out here with a row filter
4. Add a DataAsOf column — This is a computed column using DateTime.LocalNow() that stamps when Power Query last loaded this data. It appears on the dashboard and is crucial for users to know whether they're looking at fresh data

The M code for the DataAsOf column addition looks like this:

= Table.AddColumn(#"Changed Type", "DataAsOf", each DateTime.LocalNow(), type datetime)

4. Rename and reorder columns to match the exact schema your Dashboard sheet expects. Consistency here prevents formula breakage when the source file format changes slightly.

Load this query to the RawData sheet: In the Home tab of Power Query Editor, click "Close & Load To," choose "Table," select the RawData worksheet, and uncheck "Add this data to the Data Model" unless you're using Power Pivot.

Name the resulting table tbl_OperationsRaw by clicking inside it and changing the Table Name field in the Table Design tab.

Tip: Set your Power Query connection to load only to the worksheet, not the Data Model, unless you genuinely need DAX measures. Loading to the Data Model when you don't need it slows refresh and complicates the VBA refresh call you'll make later.

Creating the Config Table

On the Config sheet, build a simple table with these rows:

ParameterName	Value
MinOnTimeRate	0.85
MaxDefectRate	0.03
RefreshIntervalMinutes	15
AlertCooldownMinutes	60
AlertEmailRecipient	ops-alerts@yourcompany.com

Name this table tbl_Config. You'll read from it in VBA using a lookup function instead of hard-coding values anywhere.

---

Writing the VBA Refresh Engine

Now open the VBA editor (Alt+F11) and insert a standard module. Call it mod_RefreshEngine.

Reading Config Values

First, a reusable helper function that reads from tbl_Config:

Function GetConfigValue(paramName As String) As String
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim dataRange As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.Worksheets("Config")
    Set tbl = ws.ListObjects("tbl_Config")
    Set dataRange = tbl.DataBodyRange
    
    For Each cell In dataRange.Columns(1).Cells
        If cell.Value = paramName Then
            GetConfigValue = cell.Offset(0, 1).Value
            Exit Function
        End If
    Next cell
    
    ' Return empty string if not found — caller handles the error
    GetConfigValue = ""
End Function

This pattern — reading configuration from a worksheet table rather than from constants in code — is something you'll use in every serious VBA project. It means non-developers can adjust thresholds without opening the editor.

The Self-Scheduling Refresh Loop

Here's where Application.OnTime earns its keep. The idea is simple: after each refresh, the code schedules itself to run again in N minutes. It's not a true background thread, but it works reliably for intervals of 5 minutes or more.

' Module-level variable to track the next scheduled run
Private nextRefreshTime As Date

Sub StartRefreshLoop()
    ' Cancel any existing scheduled run before starting a new one
    Call StopRefreshLoop
    
    ' Run the first refresh immediately
    Call RefreshAndEvaluate
    
    ' Schedule the next one
    Dim intervalMinutes As Long
    intervalMinutes = CLng(GetConfigValue("RefreshIntervalMinutes"))
    If intervalMinutes < 1 Then intervalMinutes = 15 ' Safety floor
    
    nextRefreshTime = Now + TimeValue("00:" & Format(intervalMinutes, "00") & ":00")
    Application.OnTime nextRefreshTime, "RefreshAndEvaluate"
    
    ' Update status on Dashboard
    ThisWorkbook.Worksheets("Dashboard").Range("StatusCell").Value = _
        "Auto-refresh active. Next run: " & Format(nextRefreshTime, "hh:mm AM/PM")
End Sub

Sub StopRefreshLoop()
    On Error Resume Next
    Application.OnTime nextRefreshTime, "RefreshAndEvaluate", , False
    On Error GoTo 0
    
    ThisWorkbook.Worksheets("Dashboard").Range("StatusCell").Value = "Auto-refresh paused."
End Sub

Sub RefreshAndEvaluate()
    ' This is the main orchestration routine called on each cycle
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Step 1: Refresh the Power Query connection
    Call RefreshPowerQuery
    
    ' Step 2: Apply conditional formatting based on fresh data
    Call ApplyConditionalFormatting
    
    ' Step 3: Run exception detection and alerts
    Call RunExceptionEngine
    
    ' Step 4: Re-enable calculation and screen updates
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    ' Step 5: Schedule the next run
    Call ScheduleNextRun
End Sub

Private Sub ScheduleNextRun()
    Dim intervalMinutes As Long
    intervalMinutes = CLng(GetConfigValue("RefreshIntervalMinutes"))
    If intervalMinutes < 1 Then intervalMinutes = 15
    
    nextRefreshTime = Now + TimeValue("00:" & Format(intervalMinutes, "00") & ":00")
    Application.OnTime nextRefreshTime, "RefreshAndEvaluate"
    
    ThisWorkbook.Worksheets("Dashboard").Range("StatusCell").Value = _
        "Last refresh: " & Format(Now, "hh:mm AM/PM") & _
        " | Next: " & Format(nextRefreshTime, "hh:mm AM/PM")
End Sub

Warning: Application.OnTime is process-relative, not workbook-relative. If the user closes and reopens the workbook, the scheduled task is gone. You must call StartRefreshLoop again — wire it to the Workbook_Open event in ThisWorkbook to handle this automatically.

Refreshing Power Query from VBA

Private Sub RefreshPowerQuery()
    Dim conn As WorkbookConnection
    Dim ws As Worksheet
    
    ' Find our specific connection by name
    For Each conn In ThisWorkbook.Connections
        If InStr(conn.Name, "tbl_OperationsRaw") > 0 Or _
           InStr(conn.Name, "OperationsRaw") > 0 Then
            conn.Refresh
            Exit For
        End If
    Next conn
    
    ' Wait for the refresh to complete — important for large data sets
    Dim tbl As ListObject
    Set ws = ThisWorkbook.Worksheets("RawData")
    Set tbl = ws.ListObjects("tbl_OperationsRaw")
    
    ' Poll until the table is no longer in a refreshing state
    Dim timeout As Date
    timeout = Now + TimeValue("00:02:00") ' 2-minute timeout
    
    Do While tbl.QueryTable.Refreshing
        DoEvents
        If Now > timeout Then
            MsgBox "Power Query refresh timed out. Check data source connectivity.", vbExclamation
            Exit Do
        End If
    Loop
End Sub

The polling loop is important. Without it, your exception engine runs against stale data because the Power Query refresh is asynchronous. The DoEvents call keeps Excel responsive while you wait.

For a deeper look at how scheduled refresh fits into a complete automation pipeline, see Building a Self-Updating Excel Report with Power Query, VBA, and Scheduled Refresh: End-to-End Automation for Live Data Pipelines.

---

Building the Exception Detection Engine

This is the intellectual core of the dashboard. After each refresh, the engine walks through every row of tbl_OperationsRaw, compares each KPI against the thresholds from tbl_Config, and logs any violation to tbl_AlertLog.

Private Sub RunExceptionEngine()
    Dim rawWs As Worksheet
    Dim logWs As Worksheet
    Dim rawTbl As ListObject
    Dim logTbl As ListObject
    Dim dataRange As Range
    Dim row As Range
    
    Dim minOnTime As Double
    Dim maxDefect As Double
    Dim supplierID As String
    Dim onTimeRate As Double
    Dim defectRate As Double
    Dim alertMessage As String
    Dim exceptionFound As Boolean
    
    Set rawWs = ThisWorkbook.Worksheets("RawData")
    Set logWs = ThisWorkbook.Worksheets("AlertLog")
    Set rawTbl = rawWs.ListObjects("tbl_OperationsRaw")
    Set logTbl = logWs.ListObjects("tbl_AlertLog")
    
    ' Read thresholds
    minOnTime = CDbl(GetConfigValue("MinOnTimeRate"))
    maxDefect = CDbl(GetConfigValue("MaxDefectRate"))
    
    ' Get column indices by name — more robust than hard-coded numbers
    Dim colSupplier As Long
    Dim colOnTime As Long
    Dim colDefect As Long
    Dim colVolume As Long
    
    colSupplier = rawTbl.ListColumns("SupplierID").Index
    colOnTime   = rawTbl.ListColumns("OnTimeRate").Index
    colDefect   = rawTbl.ListColumns("DefectRate").Index
    colVolume   = rawTbl.ListColumns("OrderVolume").Index
    
    ' Loop through each data row
    For Each row In rawTbl.DataBodyRange.Rows
        supplierID = row.Cells(1, colSupplier).Value
        onTimeRate = row.Cells(1, colOnTime).Value
        defectRate = row.Cells(1, colDefect).Value
        alertMessage = ""
        exceptionFound = False
        
        ' Check on-time delivery threshold
        If onTimeRate < minOnTime Then
            alertMessage = alertMessage & "OnTime=" & Format(onTimeRate, "0.0%") & _
                           " (min " & Format(minOnTime, "0.0%") & ") "
            exceptionFound = True
        End If
        
        ' Check defect rate threshold
        If defectRate > maxDefect Then
            alertMessage = alertMessage & "DefectRate=" & Format(defectRate, "0.00%") & _
                           " (max " & Format(maxDefect, "0.00%") & ")"
            exceptionFound = True
        End If
        
        ' Log the exception if found and not already logged recently
        If exceptionFound Then
            If Not AlertAlreadyLogged(logTbl, supplierID, alertMessage) Then
                Call LogAlert(logTbl, supplierID, alertMessage)
                Call SendAlertEmail(supplierID, alertMessage)
            End If
        End If
    Next row
End Sub

Alert Suppression: Preventing Notification Fatigue

The AlertAlreadyLogged function is what keeps this system from spamming your team. It checks whether the same supplier has generated the same class of alert within the cooldown window:

Private Function AlertAlreadyLogged(logTbl As ListObject, _
                                     supplierID As String, _
                                     alertMsg As String) As Boolean
    Dim cooldownMinutes As Long
    Dim cooldownWindow As Date
    Dim row As Range
    Dim loggedTime As Date
    Dim loggedSupplier As String
    
    cooldownMinutes = CLng(GetConfigValue("AlertCooldownMinutes"))
    cooldownWindow = Now - (cooldownMinutes / 1440) ' Convert minutes to fractional day
    
    AlertAlreadyLogged = False
    
    If logTbl.DataBodyRange Is Nothing Then Exit Function
    
    For Each row In logTbl.DataBodyRange.Rows
        loggedSupplier = row.Cells(1, logTbl.ListColumns("SupplierID").Index).Value
        loggedTime = row.Cells(1, logTbl.ListColumns("AlertTime").Index).Value
        
        If loggedSupplier = supplierID And loggedTime > cooldownWindow Then
            AlertAlreadyLogged = True
            Exit Function
        End If
    Next row
End Function

Private Sub LogAlert(logTbl As ListObject, supplierID As String, alertMsg As String)
    Dim newRow As ListRow
    Set newRow = logTbl.ListRows.Add
    
    newRow.Range.Cells(1, logTbl.ListColumns("AlertTime").Index).Value = Now
    newRow.Range.Cells(1, logTbl.ListColumns("SupplierID").Index).Value = supplierID
    newRow.Range.Cells(1, logTbl.ListColumns("AlertMessage").Index).Value = alertMsg
    newRow.Range.Cells(1, logTbl.ListColumns("AlertSent").Index).Value = "Yes"
End Sub

Note: The cooldown logic uses a per-supplier approach — the same supplier won't trigger duplicate alerts within the window, but a different supplier breaching the same threshold will still generate its own alert. This is usually the right behavior for operational monitoring. If you need per-metric suppression instead, extend the AlertAlreadyLogged check to compare alert message content as well.

---

Sending Email Alerts with Outlook VBA

This function sends an email when an unlogged exception is found. It uses late binding to Outlook so the code works even if Outlook's version changes:

Private Sub SendAlertEmail(supplierID As String, alertDetail As String)
    Dim recipient As String
    recipient = GetConfigValue("AlertEmailRecipient")
    
    If recipient = "" Then Exit Sub ' No email configured — skip silently
    
    Dim outlookApp As Object
    Dim mailItem As Object
    
    On Error GoTo EmailError
    
    Set outlookApp = CreateObject("Outlook.Application")
    Set mailItem = outlookApp.CreateItem(0) ' 0 = olMailItem
    
    With mailItem
        .To = recipient
        .Subject = "OPS ALERT: Supplier " & supplierID & " — Threshold Breach Detected"
        .Body = "An operational exception has been detected:" & vbCrLf & vbCrLf & _
                "Supplier ID: " & supplierID & vbCrLf & _
                "Exception Detail: " & alertDetail & vbCrLf & vbCrLf & _
                "Detected at: " & Format(Now, "yyyy-mm-dd hh:mm AM/PM") & vbCrLf & vbCrLf & _
                "Please review the Operations Dashboard for full context." & vbCrLf & _
                "(This alert will not repeat for " & GetConfigValue("AlertCooldownMinutes") & " minutes.)"
        .Send
    End With
    
    Exit Sub
    
EmailError:
    ' Log the failure but don't crash the refresh cycle
    Debug.Print "Email send failed for " & supplierID & ": " & Err.Description
End Sub

For more sophisticated email automation including attachments and HTML formatting, see Automating Email and File Operations with VBA.

---

Dynamic Conditional Formatting with VBA

Static conditional formatting rules are fine for simple cases, but in a real-time dashboard you often need formatting logic that responds to business rules — not just "is this cell value greater than X." For example: highlight a row only when both the on-time rate is low and the order volume is above a certain size (small-volume suppliers breaching a threshold are annoying; high-volume suppliers doing it are a crisis).

We'll apply conditional formatting programmatically so that it's always aligned with the current data range, regardless of how many rows Power Query returned.

Sub ApplyConditionalFormatting()
    Dim dashWs As Worksheet
    Dim rawWs As Worksheet
    Dim rawTbl As ListObject
    Dim applyRange As Range
    Dim fc As FormatCondition
    
    Set dashWs = ThisWorkbook.Worksheets("Dashboard")
    Set rawWs = ThisWorkbook.Worksheets("RawData")
    Set rawTbl = rawWs.ListObjects("tbl_OperationsRaw")
    
    ' We apply CF to the dashboard detail range, which mirrors RawData
    ' Adjust this named range to match your layout
    Dim detailStart As Range
    Set detailStart = dashWs.Range("DetailTableStart") ' Named range for first data cell
    
    Dim lastRow As Long
    lastRow = rawTbl.DataBodyRange.Rows.Count
    
    ' Full row range for the detail section (columns A through H in this example)
    Set applyRange = detailStart.Resize(lastRow, 8)
    
    ' Clear existing conditional formatting before reapplying
    applyRange.FormatConditions.Delete
    
    ' --- Rule 1: Critical alert — low on-time AND high volume ---
    ' Formula-based rule anchored to the OnTimeRate column (column D) and Volume (column G)
    ' Adjust column letters to match your actual layout
    Dim criticalRule As FormatCondition
    Set criticalRule = applyRange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=AND($D" & detailStart.Row & "<" & GetConfigValue("MinOnTimeRate") & _
                  ",$G" & detailStart.Row & ">500)")
    
    With criticalRule
        .Interior.Color = RGB(255, 80, 80)       ' Red fill
        .Font.Color = RGB(255, 255, 255)           ' White text
        .Font.Bold = True
        .StopIfTrue = True                          ' Don't apply lower-priority rules
    End With
    
    ' --- Rule 2: Warning — on-time rate low but manageable volume ---
    Dim warningRule As FormatCondition
    Set warningRule = applyRange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=$D" & detailStart.Row & "<" & GetConfigValue("MinOnTimeRate"))
    
    With warningRule
        .Interior.Color = RGB(255, 200, 60)        ' Amber fill
        .Font.Color = RGB(80, 60, 0)               ' Dark text for readability
        .Font.Bold = False
        .StopIfTrue = True
    End With
    
    ' --- Rule 3: Defect rate exceeded ---
    Dim defectRule As FormatCondition
    Set defectRule = applyRange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=$E" & detailStart.Row & ">" & GetConfigValue("MaxDefectRate"))
    
    With defectRule
        .Interior.Color = RGB(255, 140, 0)         ' Orange fill
        .Font.Bold = True
        .StopIfTrue = False                         ' Allow stacking with other rules
    End With
    
    ' --- Rule 4: Good performance — everything in green ---
    Dim goodRule As FormatCondition
    Set goodRule = applyRange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=AND($D" & detailStart.Row & ">=" & GetConfigValue("MinOnTimeRate") & _
                  ",$E" & detailStart.Row & "<=" & GetConfigValue("MaxDefectRate") & ")")
    
    With goodRule
        .Interior.Color = RGB(180, 240, 180)       ' Light green
        .StopIfTrue = False
    End With
End Sub

Warning: The formula references in FormatConditions.Add must use the row number of the first data row in your range, and they must be anchored with $ on the column but not the row — exactly the same way you'd write a CF formula manually in the dialog box. If you get this wrong, the rule either applies to only one row or not at all. Test by opening the Conditional Formatting Rules Manager after running the macro and inspecting what was actually written.

The key advantage of this approach over manually configured CF rules is that the range automatically adjusts to however many rows Power Query returned. If today's export has 140 rows and tomorrow's has 163, your formatting covers all of them.

For an even deeper treatment of chart and visual formatting automation in VBA, Automating Excel Chart Formatting with VBA: Dynamically Style, Label, and Export Charts Based on Data Conditions covers analogous patterns for chart objects.

---

Wiring Everything Together: The Workbook Events

In the ThisWorkbook module, add these event handlers to start and stop the refresh loop at the right moments:

Private Sub Workbook_Open()
    ' Start the refresh loop when the workbook opens
    Call StartRefreshLoop
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Clean up the scheduled task before closing
    Call StopRefreshLoop
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' Pause auto-refresh during save to prevent conflicts
    Call StopRefreshLoop
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    ' Resume after save completes
    If Success Then Call StartRefreshLoop
End Sub

The BeforeSave/AfterSave pairing is subtle but important. If Application.OnTime fires while Excel is mid-save, you can corrupt the workbook or cause a crash. Pausing and resuming around saves prevents this.

You'll also want a simple dashboard button wired to StartRefreshLoop (for manual restart) and another to StopRefreshLoop (for when someone needs to do a manual edit without the dashboard refreshing underneath them). Use form controls, not ActiveX buttons — they're more stable across Excel versions.

This event-driven pattern connects directly to the concepts in Building a Custom VBA Event-Driven Framework: Respond to Workbook, Worksheet, and Application Events for Real-Time Automation.

---

Adding KPI Summary Cards

The detail grid is essential, but operations managers want the headline numbers at a glance. We'll add three KPI summary cells at the top of the Dashboard sheet, updated programmatically after each refresh.

Add this to your RefreshAndEvaluate procedure (after RunExceptionEngine):

Private Sub UpdateKPISummary()
    Dim dashWs As Worksheet
    Dim rawTbl As ListObject
    Dim rawWs As Worksheet
    
    Set dashWs = ThisWorkbook.Worksheets("Dashboard")
    Set rawWs = ThisWorkbook.Worksheets("RawData")
    Set rawTbl = rawWs.ListObjects("tbl_OperationsRaw")
    
    Dim onTimeCol As Range
    Dim defectCol As Range
    Dim totalRows As Long
    Dim criticalCount As Long
    
    Set onTimeCol = rawTbl.ListColumns("OnTimeRate").DataBodyRange
    Set defectCol = rawTbl.ListColumns("DefectRate").DataBodyRange
    totalRows = rawTbl.DataBodyRange.Rows.Count
    
    Dim minOnTime As Double
    Dim maxDefect As Double
    minOnTime = CDbl(GetConfigValue("MinOnTimeRate"))
    maxDefect = CDbl(GetConfigValue("MaxDefectRate"))
    
    ' Count suppliers in exception state
    Dim cell As Range
    For Each cell In onTimeCol
        If cell.Value < minOnTime Then criticalCount = criticalCount + 1
    Next cell
    
    ' Fleet-wide average on-time rate
    Dim avgOnTime As Double
    avgOnTime = Application.WorksheetFunction.Average(onTimeCol)
    
    ' Fleet-wide average defect rate
    Dim avgDefect As Double
    avgDefect = Application.WorksheetFunction.Average(defectCol)
    
    ' Write to named KPI cells on Dashboard
    dashWs.Range("KPI_AvgOnTime").Value = avgOnTime
    dashWs.Range("KPI_AvgDefect").Value = avgDefect
    dashWs.Range("KPI_ExceptionCount").Value = criticalCount
    dashWs.Range("KPI_TotalSuppliers").Value = totalRows
    
    ' Color the exception count cell based on severity
    With dashWs.Range("KPI_ExceptionCount")
        Select Case criticalCount
            Case 0
                .Interior.Color = RGB(180, 240, 180) ' Green — all clear
            Case 1 To 3
                .Interior.Color = RGB(255, 200, 60)  ' Amber — watch closely
            Case Else
                .Interior.Color = RGB(255, 80, 80)   ' Red — take action now
        End Select
    End With
End Sub

Tip: Use named ranges (like KPI_AvgOnTime) for every KPI cell rather than hard-coded cell addresses. This way, if someone rearranges the dashboard layout, you update one named range definition rather than hunting through code for Range("C4") references. Mastering Excel's Name Manager is worth reading if you haven't systematized this yet.

---

Hands-On Exercise

Build the complete system described in this lesson from scratch using the following scenario:

Scenario: You're monitoring a warehouse receiving operation. Every 30 minutes, a file called WarehouseReceiving.csv is exported to C:\OpsData\. It contains columns: WarehouseID (text), ReceivingAccuracy (decimal, target ≥ 0.95), AvgDockToStockHours (decimal, target ≤ 4.0), UnitsReceived (whole number), and ExportTimestamp (datetime).

Your tasks:

1. Create a Power Query that loads and transforms WarehouseReceiving.csv, types all columns correctly, adds a DataAsOf column, and loads to a RawData sheet as tbl_WarehouseRaw
2. Build a Config sheet with tbl_Config containing: MinReceivingAccuracy = 0.95, MaxDockToStockHours = 4.0, RefreshIntervalMinutes = 30, AlertCooldownMinutes = 90, AlertEmailRecipient = your email address
3. Implement StartRefreshLoop, StopRefreshLoop, and RefreshAndEvaluate in a module named mod_RefreshEngine
4. Write an exception engine that flags warehouses where ReceivingAccuracy is below target OR AvgDockToStockHours is above target, and logs to tbl_AlertLog
5. Apply conditional formatting to the dashboard detail range: red for accuracy failure AND high volume (units > 1000), amber for accuracy failure at lower volume, orange for dock time failure
6. Wire Workbook_Open and Workbook_BeforeClose events to start and stop the refresh loop

Stretch goal: Add a second Power Query query that reads only the AlertLog sheet and produces a pivot-ready summary of exception frequency by warehouse, loaded to a separate ExceptionSummary sheet.

---

Common Mistakes & Troubleshooting

The refresh fires but the data doesn't update This almost always means your VBA is calling Refresh on a connection object that isn't the right one, or the connection name has changed since you wrote the code. Print all connection names to the Immediate Window with:

Dim c As WorkbookConnection
For Each c In ThisWorkbook.Connections
    Debug.Print c.Name
Next c

Find the exact name and update your RefreshPowerQuery procedure.

Conditional formatting accumulates rules on each refresh You forgot the applyRange.FormatConditions.Delete line before adding new rules. Each refresh cycle adds another full set of CF rules on top of the existing ones. After 10 refreshes you have 40 rules and Excel is crawling. Always delete before reapplying.

Application.OnTime stops firing after a while Usually caused by an unhandled error in RefreshAndEvaluate that terminates the procedure before ScheduleNextRun executes. Wrap the body of RefreshAndEvaluate in a proper On Error GoTo handler that ensures ScheduleNextRun is always called. See Error Handling and Debugging VBA Code Like a Pro for robust patterns.

Email alerts fire on every single refresh The AlertAlreadyLogged function is finding no match because the AlertLog table is empty or the column names don't match. Check that tbl_AlertLog has exactly the columns AlertTime, SupplierID, AlertMessage, and AlertSent. Column name mismatches cause the loop to never find a prior entry.

Power Query refresh is slow and blocking the loop If your source dataset is large, the 2-minute timeout in RefreshPowerQuery may be too short. More importantly, consider whether you need to pull the full dataset every 15 minutes. Optimize the Power Query itself — push row filtering to the source (use query folding for SQL sources), select only the columns you need, and avoid steps that prevent folding like Table.AddColumn with DateTime.LocalNow() if possible. For broad Excel performance guidance, Excel Performance Optimization: Fix Slow Workbooks and Scale Your Analysis covers this thoroughly.

Conditional formatting formula doesn't apply to all rows The formula reference in FormatConditions.Add must point to the first data row, not a fixed reference. Check that detailStart.Row is returning the correct row number, and verify that your range in applyRange actually covers all rows from the first to the last data row. Open the Conditional Formatting Rules Manager manually after running ApplyConditionalFormatting and read the formula it actually wrote — that usually reveals the bug immediately.

---

Summary & Next Steps

You've built a complete operational monitoring system in Excel: Power Query handles data ingestion and transformation on a clean, defined schedule; VBA orchestrates the refresh cycle using Application.OnTime; an exception engine evaluates every data row against configurable thresholds after each refresh; conditional formatting is applied programmatically so it always covers the current data footprint; and email alerts go out automatically with suppression logic to prevent notification fatigue.

The architecture here — Config table, event-driven orchestration, exception logging, alert suppression — is a pattern you can apply to almost any operational monitoring problem. The suppliers-and-delivery scenario is illustrative, but the same skeleton works for inventory levels, call center SLAs, manufacturing line output rates, or financial position limits.

Where to go from here:

• If your data volume is outgrowing what a worksheet can handle comfortably, explore Master Power Pivot and Excel Data Model for Million-Row Analysis — you can extend this dashboard to pull from a Data Model instead of a flat table
• If you want to connect this directly to a SQL Server or Oracle database instead of a CSV file, Connecting Excel to External Databases with VBA: SQL Queries, ADO, and Database Automation shows you how to bypass Power Query entirely for tighter control
• If you want to package this dashboard so that operations managers can deploy it themselves without your help, Building Excel Add-Ins with VBA: Package and Deploy Custom Tools Across Your Organization covers the deployment path

The gap between a static Friday spreadsheet and a live operational monitor is exactly the gap this system closes. Build it once, tune the thresholds, and let it run.