Automate an Operations Report with VBA

Build a one-click Excel report with INDEX/MATCH, SUMPRODUCT, and VBA macros.

Introduction

30 Second Summary

Every month, utility operations teams pull together reports from raw maintenance data. If the analyst builds that report by hand, every new batch of data means re-typing numbers, re-adjusting chart ranges, and hoping nothing gets missed.

In this project, you will build a macro-enabled Excel workbook that analyzes utility work order data with advanced formulas and generates a formatted operations report with a single button click using VBA.

What You'll Build

You click a button on your spreadsheet and watch a professional report rebuild itself with updated KPI metrics, a cost breakdown by category, and a column chart. Everything is calculated dynamically from whatever data is currently in your workbook.

By the end of this project, you'll have:

  • INDEX/MATCH formulas that pull facility data VLOOKUP cannot reach and SUMPRODUCT calculations that answer multi-criteria questions in a single cell.
  • A one-click Generate Report button that rebuilds a formatted report with KPI metrics, a category breakdown table, and a column chart from scratch every time you press it.
  • A professional workflow that demonstrates the exact Excel skills listed in a real PG&E Operations Data Analyst job posting: complex formulas, graphing, and VBA automation.
  • Secret Mission: Build a parameterized macro that generates facility-specific filtered reports on demand from a dropdown selector.

Are there any prerequisites?

You should be comfortable with basic Excel formulas like SUM, IF, and VLOOKUP. No VBA or programming experience is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

This step is your moment to commit to the goal. Once you can articulate what you're building and what problem it solves, the hands-on work that follows will make much more sense.

Set Up Your Macro-Enabled Workbook

Excel hides its VBA automation tools by default. Before you can write formulas or macros, you need to make those tools visible and create a properly structured workbook.

In this step, you'll enable the Developer tab, create a macro-enabled workbook with three organized sheets, and populate it with the raw utility maintenance data you'll analyze throughout this project.

In this step, get ready to:
  • Enable the Developer tab on the Excel ribbon.
  • Create and save a macro-enabled workbook with three structured sheets.
  • Enter sample utility work order data and a facility lookup table.
Enable the Developer tab

The Developer tab gives you access to the VBA Editor, macro controls, and Form Control buttons. Excel keeps it hidden until you turn it on.

  • Press the Windows key to open your search bar.
  • Type Excel and press Enter to open it.
  • Click the File tab in the top left corner.
  • Click Options at the bottom of the left sidebar.
  • In the Excel Options window, click Customize Ribbon in the left panel.
  • In the right pane under Main Tabs, check the box next to Developer.
  • Click OK.

The Developer tab now appears on the ribbon between View and Help.

Don't see the Developer tab?

Make sure you checked the box in the right pane (Main Tabs), not the left pane (which shows available commands). Go back to File > Options > Customize Ribbon and confirm the Developer checkbox is ticked.

Help me enable the Developer tab in Excel 2019.

Create and save your macro-enabled workbook

Standard .xlsx files strip VBA macros when saved. You need the macro-enabled .xlsm format from the start so your automation code persists.

  • Click File > Save As.
  • Choose your Desktop as the save location.
  • In the Save as type dropdown, select Excel Macro-Enabled Workbook (*.xlsm).
  • Type UtilityOpsReport as the file name.
  • Click Save.

The title bar now shows UtilityOpsReport.xlsm, confirming the macro-enabled format is active.

Now you need three sheets. A new workbook starts with one sheet, so you will rename it and add two more.

  • Double-click the Sheet1 tab at the bottom of the screen.
  • Type DATA and press Enter.
  • Click the + icon to the right of the DATA tab to add a new sheet.
  • Double-click the new tab, type FACILITIES, and press Enter.
  • Click the + icon again to add a third sheet.
  • Double-click the new tab, type OUTPUT, and press Enter.

You should now see three sheet tabs at the bottom: DATA, FACILITIES, and OUTPUT.

Why three separate sheets?

Separating raw data, lookup tables, and output is a professional data management pattern. The DATA sheet holds your work orders, FACILITIES holds reference information for lookups, and OUTPUT is where your macro will generate a polished report.

Accidentally created extra sheets?

Right-click any unwanted sheet tab and select Delete. You should have exactly three tabs: DATA, FACILITIES, and OUTPUT.

Help me set up my workbook sheets correctly.

Enter the sample data

Your workbook needs two datasets: a small facility lookup table and a set of maintenance work orders. Start with the lookup table since it is only three rows.

  • Click the FACILITIES tab.
  • Enter the following lookup table starting in cell A1:
Region      Facility                Manager
East        Substation Alpha        J. Martinez
West        Distribution Center B   K. Chen
East        Transmission Tower C    R. Patel

What does this table do?

This is a lookup table that maps each facility to its region and manager. In Step 2, you will use INDEX/MATCH to pull region data from this table into the DATA sheet. The key detail: Region is in column A and Facility is in column B, which means VLOOKUP cannot look left to find the region.

  • Confirm your FACILITIES sheet has headers in row 1 and three data rows in rows 2-4.

Now switch to the larger dataset.

  • Click the DATA tab.
  • Enter the following headers in row 1, one per column from A to F: Order_ID, Date, Facility, Category, Cost, Status.
  • Enter the 12 work order rows below, starting in cell A2:
WO-001  3/5/2026   Substation Alpha       Preventive  1200  Completed
WO-002  3/12/2026  Distribution Center B  Corrective  3400  Completed
WO-003  3/18/2026  Transmission Tower C   Emergency   7500  Completed
WO-004  3/25/2026  Substation Alpha       Preventive   950  Completed
WO-005  4/3/2026   Distribution Center B  Preventive  1100  Completed
WO-006  4/10/2026  Transmission Tower C   Corrective  2800  In Progress
WO-007  4/17/2026  Substation Alpha       Emergency   6200  Completed
WO-008  4/22/2026  Distribution Center B  Preventive  1350  Cancelled
WO-009  5/2/2026   Substation Alpha       Corrective  2100  Completed
WO-010  5/8/2026   Transmission Tower C   Preventive  1400  Completed
WO-011  5/15/2026  Distribution Center B  Emergency   5800  In Progress
WO-012  5/21/2026  Substation Alpha       Preventive  1050  Completed

What does this data represent?

This simulates three months of utility maintenance work orders across three facilities. Each row has a unique order ID, a date, the facility where the work happened, the maintenance category (Preventive, Corrective, or Emergency), the cost, and the current status.

The mix of categories and statuses gives you realistic data to analyze with multi-criteria formulas in the next step.

  • Confirm row 13 contains WO-012 as the last entry. You should have 12 data rows below the header row.
  • Press Ctrl+S to save your workbook.

Speed up data entry with Tab

After typing a value, press Tab to move to the next column. When you reach column F, press Enter to jump to column A of the next row.

Dates showing as numbers?

If your dates display as numbers like 46085, select the date column, right-click, choose Format Cells, select Date under Category, and click OK.

Help me fix my date formatting in Excel.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file by pressing Ctrl+S. Your title bar should show UtilityOpsReport.xlsm.

ⓧ I'd like to double check the full data

Confirm your workbook matches this checklist:

  • The Developer tab is visible on the ribbon.
  • The title bar shows UtilityOpsReport.xlsm (macro-enabled format).
  • Three sheet tabs exist at the bottom: DATA, FACILITIES, OUTPUT.
  • FACILITIES has headers in row 1 (Region, Facility, Manager) and 3 data rows (rows 2-4).
  • DATA has headers in row 1 (Order_ID, Date, Facility, Category, Cost, Status) and 12 data rows (rows 2-13), ending with WO-012.
  • OUTPUT is empty.

Your workbook is structured and loaded with data. Next up, you'll use INDEX/MATCH and SUMPRODUCT to extract insights that basic formulas cannot reach.

Analyze Data with INDEX/MATCH and SUMPRODUCT

Your workbook is set up and your data is ready. Now you need to extract insights from it.

The DATA sheet has facility names in every row, but no region information. That data lives on the FACILITIES sheet. The problem is that VLOOKUP can only search a column and return values to its right. In the FACILITIES table, Region is in column A and Facility is in column B. You need to look right (in B) and return left (from A). INDEX/MATCH solves this.

You will also use SUMPRODUCT to calculate totals that satisfy multiple criteria simultaneously. These are the "complex formulas" that utility operations roles demand.

In this step, get ready to:
  • Use INDEX/MATCH to pull region data from the FACILITIES lookup table.
  • Use SUMPRODUCT to calculate multi-criteria totals without helper columns or pivot tables.
Look up facility regions with INDEX/MATCH

VLOOKUP searches the leftmost column of a range and returns a value from a column to the right. Your FACILITIES table has Facility in column B and Region in column A. Since Region is to the left of Facility, VLOOKUP cannot reach it.

INDEX/MATCH works differently. MATCH finds the position of a value in any column. INDEX returns a value at that position from any other column. Together, they look in any direction.

  • Switch to the DATA sheet in your workbook.
  • Click on cell G1 and type Region as the column header.
  • Click on cell G2 and enter this formula:
=INDEX(FACILITIES!A:A,MATCH(C2,FACILITIES!B:B,0))

What does this formula do?

MATCH(C2,FACILITIES!B:B,0) searches for the facility name (from cell C2) in column B of the FACILITIES sheet. The 0 means exact match. It returns the row position where that facility is found.

INDEX(FACILITIES!A:A, ...) then takes that position number and returns the value at that position from column A (the Region column). The result is the region for the facility in the current row.

Press Enter. Cell G2 should now display East because Substation Alpha is in the East region according to your FACILITIES table.

  • Copy the formula down through the rest of your data. Click cell G2, then drag the small square at the bottom-right corner of the cell (the fill handle) down to cell G13.

Every row now shows its region. You should see "East" for Substation Alpha and Transmission Tower C rows, and "West" for Distribution Center B rows.

Seeing #N/A in your cells?

This means MATCH could not find the facility name. Check that the facility names in your DATA sheet column C match the FACILITIES sheet column B exactly. Even an extra space at the end of a name causes a mismatch.

Help me fix my INDEX/MATCH #N/A errors.

Calculate multi-criteria totals with SUMPRODUCT

Now you need to answer a harder question: what is the total cost of work orders that are both Preventive and Completed? A regular SUM cannot filter by two conditions at once. SUMPRODUCT handles this by multiplying arrays of TRUE/FALSE values together.

  • Click on cell G5 and type the label Completed Preventive Cost: then press Tab to move to H5.
  • In cell H5, enter this SUMPRODUCT formula:
=SUMPRODUCT((D2:D13="Preventive")*(F2:F13="Completed")*(E2:E13))

How does SUMPRODUCT work here?

  • (D2:D13="Preventive") creates an array where matching rows become 1 and non-matching rows become 0.
  • (F2:F13="Completed") creates a second array where Completed rows become 1 and others become 0.
  • (E2:E13) is the cost values array.
  • Multiplying these together means only rows where BOTH conditions are true (1 × 1 × cost) contribute to the sum. Any row failing either condition becomes 0.

Press Enter. Cell H5 should display $4,350 (format it as currency if needed: select H5, press Ctrl+Shift+4).

Count emergency orders with SUMPRODUCT

SUMPRODUCT can also count rows matching a condition. By multiplying a boolean array by 1, each TRUE becomes 1 and the sum gives you a count.

  • Click on cell G6 and type the label Emergency Order Count: then press Tab to move to H6.
  • In cell H6, enter this formula:
=SUMPRODUCT((D2:D13="Emergency")*1)

Why multiply by 1?

The comparison (D2:D13="Emergency") produces TRUE/FALSE values. Multiplying by 1 converts them to 1/0. SUMPRODUCT then sums those numbers, giving you the count of Emergency orders. The result is 3.

Press Enter. Cell H6 should display 3. You now have multi-criteria analysis without needing helper columns or pivot tables.

  • Save your workbook by pressing Ctrl+S.

Before you check your results, predict: what region do you think cell G4 shows? What about G10?

  • Click on cell G2 to confirm it shows East.
  • Click on cell H5 to confirm it shows $4,350.
  • Click on cell H6 to confirm it shows 3.

Seeing 0 or a wrong number in H5?

Double-check that your formula text matches exactly. The criteria strings must be in double quotes: "Preventive" and "Completed". Also confirm your DATA sheet has the Category values in column D and Status values in column F with no extra spaces.

Help me troubleshoot my SUMPRODUCT formula.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your workbook with Ctrl+S.

ⓧ I'd like to double check my formulas

Here is what your DATA sheet should have in the new cells:

  • G1: Region (header text).
  • G2: =INDEX(FACILITIES!A:A,MATCH(C2,FACILITIES!B:B,0)) copied down through G13.
  • G5: Completed Preventive Cost: (label text).
  • H5: =SUMPRODUCT((D2:D13="Preventive")*(F2:F13="Completed")*(E2:E13)) displaying $4,350.
  • G6: Emergency Order Count: (label text).
  • H6: =SUMPRODUCT((D2:D13="Emergency")*1) displaying 3.

You have working formulas that pull data across sheets and calculate multi-criteria totals. Next up, you will build a formatted report by hand and discover exactly why manual reporting breaks when new data arrives.

Build a Manual Report and Watch It Break

Your formulas are pulling the right answers from raw data. Now you need to turn those answers into something a manager can actually read.

In this step, you'll build a polished report by hand, complete with headers, KPIs, and a chart. Then you'll simulate next month's data arriving and watch your beautiful report fall apart. This is the exact pain that VBA automation eliminates in the next step.

In this step, get ready to:
  • Build a formatted report on the OUTPUT sheet with headers, KPIs, and a chart.
  • Add new work order data to simulate next month's arrivals.
  • Observe how the manual report fails to update itself.
Create a formatted report on the OUTPUT sheet

A professional operations report needs more than raw numbers. It needs a clear header, key metrics at a glance, and a visual chart. You'll build all of this by hand first.

  • Switch to the OUTPUT sheet.
  • Click cell A1 and type UTILITY OPERATIONS REPORT.
  • With A1 selected, go to the Home tab and set the font size to 14.
  • Click Bold (or press Ctrl+B).
  • Click the Fill Color dropdown arrow and choose a dark blue color.
  • Click the Font Color dropdown arrow and choose white.

You should see a bold, white-on-blue banner in A1 reading "UTILITY OPERATIONS REPORT". That's your report header.

  • Click cell A2 and type Generated: August 12, 2026.
  • With A2 selected, click Italic (or press Ctrl+I).
  • Click cell A4 and type Total Work Orders:.
  • Click cell B4 and type 12.
  • Click cell A5 and type Total Cost:.
  • Click cell B5 and type this formula:
=SUM(DATA!E2:E13)
  • Press Enter to confirm the formula.
  • Right-click cell B5, select Format Cells, choose the Currency category, and click OK.

Cell B5 should now show a dollar amount. You have your report header, a date stamp, and two KPI lines.

Add a chart to the report

A visual chart helps leadership quickly spot cost trends. You'll create one from the cost data on your DATA sheet.

  • Switch to the DATA sheet.
  • Select the range D1:E13 (the Category and Cost columns including headers).
  • Click the Insert tab on the ribbon.
  • In the Charts group, click Insert Column or Bar Chart.
  • Select Clustered Column (the first option).

A column chart appears on the DATA sheet. Now move it to OUTPUT.

  • Right-click the chart border and select Move Chart.
  • Select Object in: and choose OUTPUT from the dropdown.
  • Click OK.

You should now see the OUTPUT sheet with your formatted header, KPI values, and a column chart showing maintenance costs. It looks like a proper report.

Chart not showing on OUTPUT?

If the chart stayed on the DATA sheet, click anywhere on the chart to select it (you'll see grab handles on the corners). Then right-click the chart border, not a bar inside the chart. The Move Chart option only appears in the border context menu.

Help me move my chart to the OUTPUT sheet.

  • Save your workbook by pressing Ctrl+S.
Simulate next month's data arriving

In a real utility company, new work orders arrive every month. You'll add four June 2026 orders to see what happens to your report.

  • Switch to the DATA sheet.
  • Click cell A14 and enter the following four new work orders in rows 14 through 17:

New work order data (rows 14-17)

  • Row 14: WO-013 | 6/4/2026 | Transmission Tower C | Preventive | 1300 | Completed
  • Row 15: WO-014 | 6/11/2026 | Substation Alpha | Corrective | 2900 | Completed
  • Row 16: WO-015 | 6/18/2026 | Distribution Center B | Emergency | 6100 | In Progress
  • Row 17: WO-016 | 6/24/2026 | Transmission Tower C | Preventive | 1150 | Completed
  • After entering all four rows, select cell G13 (the last cell with your INDEX/MATCH formula).
  • Copy it by pressing Ctrl+C.
  • Select cells G14:G17 and paste by pressing Ctrl+V.

You should now see 16 rows of data (rows 2-17) with Region values populated for all entries. Your DATA sheet has grown.

Watch the manual report break

You've added 4 new work orders. Before you switch back to the OUTPUT sheet, think about what you expect to see.

Prediction time

Do you think the report on OUTPUT automatically updated to include your 4 new work orders? Will the chart show 16 bars now instead of 12? Will the "Total Work Orders" number reflect 16?

  • Switch to the OUTPUT sheet.

The report is completely stale. Here's what went wrong:

  • Cell B4 still says 12. You hardcoded that number, so it will never change on its own. The real count is now 16.
  • Cell B5 still sums only DATA!E2:E13. The four new rows (E14:E17) are completely excluded from the total.
  • The chart still shows only the original 12 data points. New rows fall outside its fixed data range.
  • Cell A2 still reads Generated: August 12, 2026 regardless of when the report is actually viewed.

Why does this matter?

If you were doing this monthly for a utility company, you would need to manually update every number, expand every chart range, fix the date, and reapply formatting every single time new data arrives.

This is exactly what the PG&E Operations Data Analyst job means by "creates automated data routines and processes for reporting." The pain you just felt is the problem VBA solves.

You've felt the pain of manual reporting firsthand. Next up, you'll write a VBA macro that dynamically finds all data, recalculates everything, and rebuilds the report in one click.

Automate Everything with a VBA Macro

In the last step, you saw exactly why manual reports fail. New data arrived and your OUTPUT sheet immediately became stale. The numbers were wrong, the chart was incomplete, and the date was frozen.

VBA (Visual Basic for Applications) is Excel's built-in programming language. You will write a macro that rebuilds your entire report from scratch every time you click a button. One click replaces 15 minutes of manual formatting and recalculating.

In this step, get ready to:
  • Open the VBA Editor and write a macro that generates a full report dynamically.
  • Run the macro and verify it produces correct, up-to-date output.
  • Assign the macro to a Form Control button for one-click report generation.
Open the VBA Editor and create a module

The VBA Editor is a separate window inside Excel where you write and manage macro code. You need to create a module, which is a container that holds your macro procedures.

  • Press Alt+F11 to open the VBA Editor.
  • In the Project Explorer pane on the left, find VBAProject (UtilityOpsReport.xlsm).
  • Right-click it, then select Insert, then Module.

A blank code window labeled Module1 appears. This is where you will type the macro.

Don't see the Project Explorer?

If the left pane is missing, press Ctrl+R or go to View then Project Explorer in the VBA Editor menu bar.

help me find the Project Explorer in the VBA Editor

Write the GenerateReport macro

Your macro will do four things every time it runs: clear the old report, calculate KPIs from the current data, build a category breakdown table, and create a chart. You will type it in sections so you understand each part.

  • Click inside the blank Module1 code window and type the following code to start the macro, declare variables, clear old output, and write the report header:
Sub GenerateReport()
    Dim wsData As Worksheet
    Dim wsOutput As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject
    Dim completed As Long
    Dim categories As Variant
    Dim i As Integer
    
    Set wsData = ThisWorkbook.Sheets("DATA")
    Set wsOutput = ThisWorkbook.Sheets("OUTPUT")
    
    ' Clear existing output
    wsOutput.Cells.Clear
    For Each chartObj In wsOutput.ChartObjects
        chartObj.Delete
    Next chartObj
    
    ' Find last row of data dynamically
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    
    ' Report header with formatting
    With wsOutput.Range("A1")
        .Value = "UTILITY OPERATIONS REPORT"
        .Font.Size = 14
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
    End With
    wsOutput.Range("A1:D1").Interior.Color = RGB(0, 51, 102)
    wsOutput.Range("A2").Value = "Generated: " & Format(Date, "mmmm d, yyyy")
    wsOutput.Range("A2").Font.Italic = True

What does this code do?

  • Dim declares variables. wsData and wsOutput hold references to your two sheets so you can address them by name.
  • wsOutput.Cells.Clear wipes everything on OUTPUT. The For Each loop deletes any existing charts so the report always starts fresh.
  • lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row jumps to the bottom of column A and looks up to find the last row with data. This means the macro adapts to however many rows DATA has.
  • Format(Date, "mmmm d, yyyy") inserts today's actual date, so the report timestamp is always correct.
  • Directly below the last line you typed, add the KPI section that calculates total work orders, cost, and completion rate:
    ' KPI section
    wsOutput.Range("A4").Value = "KEY METRICS"
    wsOutput.Range("A4").Font.Bold = True
    
    wsOutput.Range("A5").Value = "Total Work Orders:"
    wsOutput.Range("B5").Value = lastRow - 1
    
    wsOutput.Range("A6").Value = "Total Maintenance Cost:"
    wsOutput.Range("B6").Value = Application.WorksheetFunction.Sum(wsData.Range("E2:E" & lastRow))
    wsOutput.Range("B6").NumberFormat = "$#,##0"
    
    completed = Application.WorksheetFunction.CountIf(wsData.Range("F2:F" & lastRow), "Completed")
    wsOutput.Range("A7").Value = "Completion Rate:"
    wsOutput.Range("B7").Value = completed / (lastRow - 1)
    wsOutput.Range("B7").NumberFormat = "0.0%"

What does this code do?

  • lastRow - 1 gives the total work orders (subtracting the header row). Because lastRow is dynamic, this number updates automatically when rows are added.
  • Application.WorksheetFunction.Sum calls Excel's built-in SUM function from VBA, summing the cost column up to the dynamic last row.
  • CountIf counts only rows where the Status column says "Completed". Dividing by total orders gives a completion percentage.
  • Continue typing below your KPI section. Add the category breakdown table that loops through each maintenance category:
    ' Cost by category table
    wsOutput.Range("A9").Value = "COST BY CATEGORY"
    wsOutput.Range("A9").Font.Bold = True
    
    wsOutput.Range("A10").Value = "Category"
    wsOutput.Range("B10").Value = "Total Cost"
    wsOutput.Range("C10").Value = "Count"
    wsOutput.Range("A10:C10").Font.Bold = True
    wsOutput.Range("A10:C10").Interior.Color = RGB(221, 235, 247)
    
    categories = Array("Preventive", "Corrective", "Emergency")
    
    For i = 0 To 2
        wsOutput.Cells(11 + i, 1).Value = categories(i)
        wsOutput.Cells(11 + i, 2).Value = Application.WorksheetFunction.SumIf( _
            wsData.Range("D2:D" & lastRow), categories(i), _
            wsData.Range("E2:E" & lastRow))
        wsOutput.Cells(11 + i, 2).NumberFormat = "$#,##0"
        wsOutput.Cells(11 + i, 3).Value = Application.WorksheetFunction.CountIf( _
            wsData.Range("D2:D" & lastRow), categories(i))
    Next i

What does this code do?

  • The categories array holds three strings: "Preventive", "Corrective", and "Emergency".
  • The For i = 0 To 2 loop runs once for each category. It uses SumIf to total costs matching that category, and CountIf to count how many orders match.
  • The underscore _ at the end of a line is VBA's line-continuation character. It lets you split long lines for readability.
  • Finish the macro by adding the chart creation, column auto-fit, and the closing End Sub line:
    ' Create column chart
    Set chartObj = wsOutput.ChartObjects.Add( _
        Left:=wsOutput.Range("A15").Left, _
        Top:=wsOutput.Range("A15").Top, _
        Width:=350, _
        Height:=220)
    
    With chartObj.Chart
        .SetSourceData Source:=wsOutput.Range("A10:B13")
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Maintenance Cost by Category"
        .HasLegend = False
    End With
    
    ' Auto-fit columns
    wsOutput.Columns("A:C").AutoFit
    
    MsgBox "Report generated successfully!", vbInformation
End Sub

What does this code do?

  • ChartObjects.Add creates a new chart on the OUTPUT sheet, positioned at cell A15 with a fixed width and height.
  • SetSourceData points the chart at the category table (A10:B13). xlColumnClustered makes it a vertical bar chart, ideal for comparing costs across categories.
  • End Sub marks the end of the macro. VBA will not run the procedure until this closing line exists.
Run the macro and verify the report

Your macro is complete. Before you run it, think about what should happen on the OUTPUT sheet. You have 16 rows of data now. What numbers do you expect the report to show?

  • Click anywhere inside your Sub GenerateReport() code in the VBA Editor.
  • Press F5 to run the macro.
  • Switch back to Excel by pressing Alt+F11 again (or click the Excel window in the taskbar).
  • Navigate to the OUTPUT sheet.

You should see a dark blue header reading "UTILITY OPERATIONS REPORT", today's date, a KEY METRICS section showing 16 total work orders, the correct total maintenance cost, a completion rate percentage, a COST BY CATEGORY table, and a column chart titled "Maintenance Cost by Category".

Why is this better than the manual report?

Every single value came from the current data. The macro found all 16 rows automatically using lastRow. If you add 50 more work orders next month and run the macro again, it adapts without you changing a single line of code.

Seeing a compile error or runtime error?

  • If you see "Compile error: Sub or Function not defined", check that you typed End Sub on its own line at the very bottom of your code.
  • If you see "Subscript out of range", confirm your sheets are named exactly DATA and OUTPUT (case-sensitive in VBA string comparisons).
  • If the chart looks wrong, make sure the category table data is in rows 10-13 (header row 10, three data rows 11-13).

Help me debug my GenerateReport VBA macro

Assign the macro to a Form Control button

Running a macro from the VBA Editor works, but the goal is one-click automation from the worksheet itself. A Form Control button lets anyone generate the report without ever opening the editor.

  • Switch to the OUTPUT sheet in Excel.
  • Click the Developer tab on the ribbon.
  • In the Controls group, click Insert.
  • Under Form Controls (the top row of icons), click the Button icon (the top-left rectangle).
  • Draw a small rectangle anywhere on the sheet by clicking and dragging.
  • The Assign Macro dialog appears automatically. Select GenerateReport from the list and click OK.
  • Right-click the button and choose Edit Text.
  • Delete the default text and type Generate Report.
  • Click anywhere outside the button to deselect it.
  • Click the Generate Report button.

The entire report regenerates. You should see the same formatted output with the dark blue header, today's date, all 16 work orders reflected in the KPIs, the category breakdown, and the chart. A message box confirms "Report generated successfully!" after each run.

Test it with changing data

Go to DATA and delete one of the rows you added in the previous step. Click the Generate Report button again. The Total Work Orders value drops by one, and the cost totals adjust. This proves your macro is truly dynamic.

Button not running the macro?

  • If clicking the button selects it instead of running it, right-click was still active. Click away from the button first, then left-click it normally.
  • If you see a macro security warning, go to File then Options then Trust Center then Trust Center Settings then Macro Settings. Select "Enable all macros" and click OK.

Help me get my Form Control button working in Excel

  • Save your workbook by pressing Ctrl+S.

✔️ Awesome, I've got everything!

Great. Confirm you saved UtilityOpsReport.xlsm and the button works on every click.

ⓧ I'd like to double check the full code

Your Module1 should contain these four sections in order. Scroll through your code in the VBA Editor (Alt+F11) and confirm each one is present:

  • Section 1: Sub GenerateReport() through the report header formatting (ending at the A2 italic date line).
  • Section 2: KPI section (KEY METRICS, Total Work Orders, Total Maintenance Cost, Completion Rate).
  • Section 3: COST BY CATEGORY table headers, the categories array, and the For i = 0 To 2 loop with SumIf and CountIf.
  • Section 4: Chart creation (ChartObjects.Add), column auto-fit, the MsgBox, and End Sub.

Your reporting workflow is now fully automated. One click produces a formatted, chart-included report from whatever data exists. Next up, you will take this further by building a facility-specific filtered report in the Secret Mission.

Secret mission

Facility-Filtered Reports on Demand

Leadership often needs reports for individual facilities, not the whole operation. Can you build a parameterized macro that generates targeted, facility-specific reports on demand without editing code?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about. Everything lives in a single file on your machine.

Resources you used:

  • UtilityOpsReport.xlsm (local file containing all sheets, formulas, and VBA macros).

Decide whether to keep your workbook or delete it entirely.

Keep everything running

No action needed. Choose this if you want to keep your workbook as a portfolio piece or continue building on it.

  • Your UtilityOpsReport.xlsm file stays wherever you saved it on your machine.
  • Consider adding the file to a GitHub repository or including it in job applications to demonstrate your Excel and VBA skills.
  • The Developer tab remains visible on your ribbon for future VBA work.

Pause - I'll come back to this later

Since everything lives in one local file, there is nothing to pause or shut down.

  • Close Excel. Your UtilityOpsReport.xlsm file is already saved and ready to reopen anytime.
  • When you reopen the file, click Enable Content in the security bar to reactivate macros.

Delete - I don't want to use this again

Remove the workbook file from your machine entirely.

  • Close Excel if it is still open.
  • Navigate to the folder where you saved UtilityOpsReport.xlsm in File Explorer.
  • Right-click the file and select Delete.
  • Empty your Recycle Bin if you want to free the disk space immediately.

What about the Developer tab?

The Developer tab is just a ribbon visibility setting. It does not consume resources or affect other workbooks. You can leave it enabled for future projects, or disable it by going to File > Options > Customize Ribbon and unchecking the Developer box.

Nice Work!

Nice Work!

Nice work! You just built a one-click automated reporting workbook that proves you can handle the exact Excel workflow described in real operations analyst job listings.

You've learned how to:

  • Used INDEX/MATCH and SUMPRODUCT to solve complex lookup and multi-criteria calculation problems that simpler formulas cannot handle.
  • Built a VBA macro from scratch that dynamically calculates KPIs, formats output, and generates charts from any amount of source data.
  • Assigned your macro to a Form Control button so an entire formatted report regenerates with one click, replacing 15 minutes of manual work.
  • Secret Mission: Built a parameterized macro that generates facility-specific filtered reports on demand using Data Validation and multi-criteria WorksheetFunctions.

Ready to quiz yourself?