Use Copilot in Excel to Scan Utility Data for Gaps, Duplicates and Odd Values

What This Does

You ask Copilot in Excel to read a table of electricity bills and point to blank months, repeated rows and values that look out of line. It gives you a first-pass list to chase, so your own checking starts from a shortlist instead of a blank screen.

Before You Start

• Excel for Microsoft 365 is open with your activity data in a worksheet (Site, Meter, Month, kWh).
• You can see the Copilot icon in the lower-right corner of Excel. Microsoft's support page says Copilot may not appear if it is not included with your Microsoft 365 subscription or is switched off by your organization's settings. Copilot needs its own license, which you may not have. Check Microsoft's Copilot licensing page or ask your IT admin. If it is missing, skip this guide and use the COUNTIFS check in Step 4 on its own.
• The data is safe to use. Utility bills carry account numbers and street addresses. Use site codes and meter codes instead, and leave the account number and service address columns out of the sheet you work in. If the data is a client's unreleased emissions data, your engagement letter or NDA and your IT team's rules decide whether any AI feature may touch it.
• You know your own expected pattern: which meters should have a bill for which months.

Steps

1. Open Copilot

Select the Copilot icon in the lower-right corner of Excel. Microsoft's page says Copilot in Excel offers three modes: edit, plan and chat. It opens in edit mode by default, and edit mode changes your workbook directly. For a scan, choose chat mode, which the page says analyzes your data and gives insights without changing the workbook. Look for the mode control in the Copilot pane, since Microsoft may move it.

The Microsoft page does not list a table format or file location as a requirement for asking questions about your data. If Copilot says it cannot read your range, ask your IT admin or read the page named in the footer.

2. Ask for the three checks

Type a prompt that says what the data is and what each column means. Example:

This worksheet has monthly electricity bills for one site. Columns: Site (a code),
Meter, Month (first day of the month), kWh. Meter M2 was installed in February 2025,
so it has no January row. Please list: (1) any month with no row for a meter that
should have one, (2) any rows that repeat the same Meter, Month and kWh, and
(3) any kWh value that looks unusual compared with the same meter's other months.
For each item give the row numbers. Do not change any cell.

Telling Copilot about the February install keeps it from flagging a gap that is not one.

3. Read the flags against the bills

Copilot's page says it can show insights as charts, PivotTables, summaries, trends or outliers. Treat every flag as a lead. Open the source bill for each flagged row and compare Meter, billing period and kWh. A repeated row might be a true double entry, or two bills that happen to match. A blank month might be a missing bill, or a bill filed under the wrong meter. Never change a value without the source document in front of you.

4. Run your own check, because Copilot can miss things

Add a helper column E with a count of rows for each Meter and Month pair:

=COUNTIFS($B$2:$B$25,B2,$C$2:$C$25,C2)

Any result above 1 is a repeated pair. For gaps, list the 12 months down column G, put each meter code across row 1, and use the same COUNTIFS to count rows per meter and month. Any zero that you did not expect is a gap. Do not rely on a row count per meter alone. A meter can show the right number of rows while hiding one repeated month and one missing month at the same time.

Real Example

Scenario (all data invented): Site S01 has two meters. Meter M1 should have 12 monthly rows for 2025. Meter M2 was installed in February, so it should have 11. The sheet has 24 data rows, which looks right at a glance.

What you type: The prompt from Step 2, with the table in cells A1 to D25.

What you get: A reply in chat that lists, as an example of the kind of output to expect:

• M1, July 2025: no row.
• M1, March 2025: two rows with 40,100 kWh each.
• M2, October 2025: two rows with 12,300 kWh each.

In this invented sheet, M1 has 12 rows (11 distinct months plus the repeated March) and M2 has 12 rows (11 distinct months plus the repeated October). Both counts match what you expected for M1 and look one too high for M2, so a plain row count would have hidden the problem on M1. Your COUNTIFS helper shows a 2 against both repeated rows and a 0 for M1 in July.

You then pull the March, July and October bills for the site. If the July bill exists, you add its kWh from the document. If it does not, you request it from the data owner. If Copilot's reply differs from your COUNTIFS result, the COUNTIFS result and the documents win. Ask Copilot again with a narrower prompt.

Tips

• Ask for row numbers in every answer. A flag you cannot locate in the sheet is not usable.
• Keep a short log of what each flag turned out to be. It becomes part of your data quality notes.
• Run the scan again after you fix a few rows. A fix can create a new problem, such as a value typed into the wrong month.

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".