Energy Audit in Excel: Steps, Formulas, and Limits

Formulas & functions · Updated November 22, 2026

Yes—you can use Excel for a practical energy audit by organizing utility bills, meter readings, building details, and equipment estimates, then calculating energy use, costs, and potential savings. Excel helps compare and prioritize findings; it does not replace a qualified auditor, a calibrated measurement, or an inspection of the building.

What an Energy Audit Spreadsheet Can Do

An energy audit looks for how a building uses energy and where consumption or cost might be reduced. A spreadsheet can bring scattered information into one place, reveal patterns across billing periods, and make assumptions visible. It is useful for a homeowner tracking electricity use, a facilities manager preparing a project list, or a small business reviewing utility costs.

There is no single official “energy audit Excel” workbook that fits every building. A home, office, workshop, and apartment building have different meters, fuels, schedules, and equipment. Treat a workbook as a record and screening tool: it can identify questions worth investigating, but estimates based on bills are not the same as measured savings from a retrofit.

Depending on the building, track electricity, natural gas, heating oil, propane, district energy, or other fuels separately. Keep each unit clear—such as kWh, therms, gallons, or cubic metres—and do not add unlike units as if they were interchangeable. If you need a combined energy total, convert to a common unit using an appropriate, documented conversion factor.

Set Up a Clear Workbook

Use separate sheets for source data, calculations, and recommendations. That separation makes it easier to trace an output back to a bill or assumption instead of overwriting original information. A practical workbook might include:

  • Overview: building name, address or site identifier, floor area, audit dates, and a short summary of key findings.
  • Utility bills: billing dates, meter readings if available, billed consumption, units, charges, and notes about estimated bills or rate changes.
  • Equipment and schedules: equipment type, rated power, quantity, operating hours, and the source of each figure.
  • Opportunities: proposed action, estimated energy reduction, expected cost, assumptions, priority, and next step.

Give columns descriptive headers, keep one measurement per cell, and use consistent dates and units. Record the bill period rather than just the date the bill arrived. If a utility bill includes a fixed service charge, taxes, demand charges, or multiple rate periods, note them separately where possible; dividing the entire bill by kWh gives an average cost, not necessarily the price of saving one additional kWh.

Collect and Check the Input Data

Start with a full year of bills when available, because heating and cooling needs vary by season. Two or more years can help distinguish a recurring pattern from an unusual period, such as a vacancy, renovation, equipment failure, or extreme weather. Enter the utility’s billed consumption as the main record. Meter readings are useful as a cross-check, but the dates and reading method must match the billing period.

Gather information that explains changes in use, including opening hours, occupancy, production levels, thermostat schedules, and major equipment additions. For a building energy intensity comparison, record the floor area and define what space it includes. Comparing a conditioned floor area with a total gross area, for example, can make otherwise similar sites appear different.

In Excel for Microsoft 365 on Windows, you can enter or paste records into a worksheet and format the range as a table with Home > Format as Table. Tables make headings easier to filter and help formulas extend as rows are added. Excel for Mac and Excel for the web also support tables, though ribbon placement and some dialogs can vary. Do not delete source bills after entering figures; keep copies or a clear file reference.

Calculate Consumption and Cost

Suppose a sheet uses columns A through G for billing month, opening meter reading, closing meter reading, calculated meter use, billed kWh, energy rate in dollars per kWh, and estimated energy charge. In row 2, with the opening reading in B2 at 12,500 kWh and closing reading in C2 at 15,100 kWh, enter =C2-B2 in D2. The result is 2,600 kWh. This difference is meaningful only if the readings use the same meter and units and cover the relevant period.

With billed kWh in E2 and the energy-only rate in F2, enter =E2*F2 in G2. If E2 is 2,600 and F2 is $0.18 per kWh, the estimated energy charge is $468. That will not necessarily equal the full bill: fixed charges, taxes, demand charges, credits, and tiered rates can change the total. Use the bill’s actual charge breakdown when available rather than treating an estimated unit rate as the tariff.

With billed monthly kWh in E2:E13, a year total can be calculated in E14 with =SUM(E2:E13). If those twelve illustrative values add to 32,400, the formula returns 32,400 kWh. Check that the selected rows cover twelve distinct, non-overlapping billing periods; a bill spanning an unusual number of days can make a simple monthly comparison misleading.

For energy intensity, suppose the annual total is in E14 and the building floor area in square feet is in B16. Enter =E14/B16 in B17. With 32,400 kWh and 12,000 square feet, the result is 2.7 kWh per square foot for the recorded year. This is a screening metric, not proof that a building is efficient or inefficient: climate, building use, occupancy, and the area definition all matter.

Estimate Equipment Use Carefully

When bills do not identify which equipment is responsible for consumption, an equipment inventory can help form a rough estimate. For a device with a known power rating, the basic estimate is power in kilowatts multiplied by operating hours. For example, a 0.5 kW fan running eight hours per day for 250 days would use an estimated 1,000 kWh per year (0.5 × 8 × 250). This assumes it runs at that power for every stated hour; actual cycling, variable-speed operation, standby use, and controls can change the result.

For equipment labeled in watts, divide watts by 1,000 to express the rating in kilowatts before multiplying by hours. Record whether the rating came from a nameplate, manufacturer documentation, a meter, or an assumption. A nameplate value is not always the device’s actual operating draw. For major motors, refrigeration, process equipment, or systems with variable loads, a qualified person may need to measure operation rather than rely on a simple runtime estimate.

Compare Energy-Saving Opportunities

Build an opportunity list that separates estimated energy reduction from estimated bill savings. For a simple illustration, assume a baseline of 32,400 kWh per year, a proposed reduction of 8%, an energy-only rate of $0.18 per kWh, and an installed cost of $3,000. Put those assumptions in cells B2:B5 in that order.

With baseline annual kWh in B2 and the reduction fraction in B3, enter =B2*B3 in B6 for estimated annual savings. The result is 2,592 kWh. With annual kWh savings in B6 and the assumed rate in B4, enter =B6*B4 in B7; the illustrative energy-cost saving is $466.56 per year. With project cost in B5 and annual cost savings in B7, enter =B5/B7 in B8; simple payback is about 6.43 years.

This simple example omits rate changes, maintenance, equipment life, financing, demand charges, incentives, taxes, and interactions between measures. It is a way to screen an idea, not a forecast or financial recommendation. Check actual tariffs, project scope, and contract terms with the utility, installer, lender, or an appropriately qualified professional before relying on an estimate.

For each proposed measure, document the source of its savings estimate and whether it depends on occupant behavior or operating schedules. Rank measures using practical considerations as well as payback: safety, comfort, reliability, maintenance needs, disruption, and whether a planned replacement is already due. A low-cost control adjustment may be worth checking before replacing serviceable equipment, but controls must be set appropriately for the building and its occupants.

Use Charts and Formatting to Find Patterns

A line chart of monthly kWh can make seasonal peaks or a sudden change easier to see. Select the billing-period labels and consumption values, then use Insert > Insert Line or Area Chart in Excel for Windows. A column chart can be useful for comparing annual use or fuel types, provided the units are shown and unlike units are not placed on a misleading common scale. On Mac or Excel for the web, chart options remain available from Insert, although the exact gallery layout may differ.

Conditional formatting can flag unusual values for review. For example, use Home > Conditional Formatting to highlight values above a chosen threshold, but make clear that the threshold is a review trigger rather than an automatic diagnosis. A high month could reflect cold weather, longer operating hours, a meter-reading issue, or a real fault. Add notes to explain what was checked and what remains unknown.

Common Spreadsheet Problems and Limits

  • Mixing units: Do not total kWh, therms, and gallons in one column. Keep separate fuel sections or convert with a stated factor.
  • Comparing unequal periods: Billing periods may have different lengths. Record start and end dates, and consider daily use as a supplemental comparison rather than assuming every bill represents one month.
  • Ignoring weather or occupancy: A colder winter or busier facility can raise use without an equipment fault. Note relevant conditions before drawing conclusions.
  • Using average cost as a marginal rate: The bill’s total divided by consumption may include charges that will not fall when energy use falls.
  • False precision: A formula can show many decimal places even when the input is an approximate runtime or estimated savings percentage. Format results sensibly and preserve the uncertainty in the notes.

Excel can also conceal errors if formulas are replaced with typed values or copied into the wrong rows. Keep a clean source-data sheet, inspect formulas when adding periods, and reconcile annual totals against utility statements. For complex facilities, interval meter data, multiple meters, demand tariffs, or regulated reporting, a spreadsheet may need to be supported by dedicated energy-management software or professional analysis.

Safety and When to Get an Auditor

A spreadsheet review does not make electrical or mechanical work safe to perform. Do not open electrical panels, handle exposed wiring, enter restricted equipment spaces, or alter combustion equipment as part of data collection unless qualified and authorized to do so. Use bills, accessible nameplates, operating schedules, and observations that can be gathered safely. Follow site procedures and applicable safety requirements.

Consider a qualified energy auditor when a project is large, savings estimates must support a funding decision, equipment interactions are complicated, or the cause of high consumption is unclear. An auditor can assess building systems and operating conditions using methods beyond a bill-only spreadsheet. Ask what the assessment covers, what measurements are included, what assumptions will be reported, and whether recommendations are suitable for the site’s needs.

Frequently Asked Questions

Is there a free energy audit Excel template?
Many organizations and utilities publish worksheets or templates, but there is no one template appropriate for every building. Check that a workbook records units, billing dates, assumptions, and source information, and adapt it to the meters and fuels at your site.

Can Excel calculate an energy audit automatically?
Excel can calculate totals, costs, intensity, and scenario estimates once accurate inputs and formulas are supplied. It cannot independently inspect equipment, verify a meter, or determine whether an assumed runtime or savings percentage is correct.

How many months of utility bills should I enter?
A year is a useful starting point because it captures a full seasonal cycle. Longer records can add context, especially if occupancy, equipment, rates, or weather changed; label unusual periods rather than treating them as directly comparable.

Can I compare different buildings with kWh per square foot?
You can calculate the metric, but comparisons are only meaningful when the buildings have similar uses, climate context, operating hours, and floor-area definitions. Treat it as a prompt for investigation, not a stand-alone rating.

Does an energy audit spreadsheet show exactly how much money a retrofit will save?
No. Spreadsheet savings are estimates based on stated assumptions. Actual results depend on equipment performance, installation, weather, occupancy, operating behavior, and utility rates, so measure and review performance after implementation where possible.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.