Use Copilot in Excel to Build an Indicator Summary Table and Chart by Site and Quarter

What This Does

You have clean indicator data by site and quarter and want a summary table and a chart for a client update or a report draft. Copilot builds a PivotTable and a chart from your sheet, and you check the totals before anything leaves your hands. The data should already be checked. If you are still checking raw bills, use the activity data scan guide first.

Before You Start

• Excel for Microsoft 365 is open with one clean table: Site, Quarter, Energy_kWh, Water_m3, Waste_t.
• Copilot is available to you. Microsoft's support page says it might not be included with your Microsoft 365 subscription or may be limited by your organization's settings, and that it needs a Copilot license. Check Microsoft's Copilot licensing page or ask your IT admin. There is no AI step you cannot do by hand with a PivotTable, so a missing license only costs you time.
• The figures were calculated in your own spreadsheet or carbon accounting tool. Copilot summarizes numbers you give it and should not produce new ones.
• If the data is a client's unreleased figures, your engagement letter and your IT rules come first. Use site codes rather than names or addresses.

Steps

1. Open Copilot

Select the Copilot icon in the lower-right corner of Excel. Microsoft's page says Copilot works with Excel's own tools such as tables, charts, PivotTables and formulas, and that the results stay editable. Edit mode is the default and it changes the workbook, so work on a copy of the sheet if the original is shared.

2. Ask for the PivotTable and chart

Example prompt:

Using the table on this sheet, create a PivotTable with Site in rows and Quarter in
columns showing the sum of Energy_kWh. Add a column chart of the same figures. Then
make a second PivotTable for Waste_t by Site. Put both on a new sheet and label the
units in the headings.

The page lists PivotTables, charts and summaries as things Copilot creates with links back to the source data. If a result is not what you wanted, say what to change in plain words, such as "Put Quarter on the horizontal axis".

3. Recompute two totals yourself

Pick two numbers at random from the summary and check them with SUMIFS on the source table. For example:

=SUMIFS($C$2:$C$13,$B$2:$B$13,"Q1")
=SUMIFS($E$2:$E$13,$A$2:$A$13,"S01")

The first gives the Q1 energy total across all sites. The second gives the annual waste for site S01. Both must match the PivotTable exactly. If either does not, look for rows that Copilot left out, text stored as numbers, or a typo in a site code.

4. Check labels and units

Read the chart title, axis labels and headings. Confirm the unit is on every figure and that "Q1" means the period you mean. Rename anything that could be misread by someone who has not seen the sheet.

Real Example

Scenario (all data invented): Three sites report each quarter for one year. Energy in kWh, water in cubic meters and waste in tonnes.

Site S01 energy by quarter: 120,000, 115,000, 128,000 and 118,000 kWh. Site S02: 80,000, 76,000, 84,000 and 79,000. Site S03: 45,000, 43,000, 47,000 and 44,000. S01 waste by quarter: 18, 17, 19 and 16 tonnes.

What you type: The prompt in Step 2.

What you get: A PivotTable with three site rows and four quarter columns, a column chart, and a waste PivotTable with a row per site.

Your check: Q1 energy should be 120,000 + 80,000 + 45,000 = 245,000 kWh. S01 waste should be 18 + 17 + 19 + 16 = 70 tonnes. If your SUMIFS formulas return those values and the PivotTable shows the same, the summary is safe to move into the draft. A grand total of energy across all sites and quarters should come to 979,000 kWh.

Tips

• Paste the final table and chart into your report as values. Note the sheet and date they came from.
• Ask Copilot for the table first and the chart second. A wrong table makes a wrong chart.
• Do not ask Copilot to compare sites or explain why one is higher. You know the operating context, so write that part yourself.

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area. Microsoft's current page: support.microsoft.com, "Get started with Copilot in Excel".