An energy audit template in Excel is a workbook for recording utility use, building details, audit findings, and estimated improvement costs and savings. In Microsoft Excel for Microsoft 365 on Windows, a practical version can use separate sheets for monthly bills, audit observations, and recommended measures, with formulas to total energy use and estimate simple payback.
What an energy audit template should cover
An audit workbook is a place to organize evidence and calculations; it does not make the audit accurate by itself. Its purpose is to make the inputs, assumptions, and results visible so someone else can follow how a recommendation was reached. The right level of detail depends on the building and the audit’s purpose. A quick review of a small office may need fewer records than an evaluation intended to support a major equipment investment.
At a minimum, capture the site and reporting period, the energy sources reviewed, utility consumption and costs, building area, operating schedule, observed conditions, proposed actions, and the assumptions behind any savings estimate. Keep measured facts separate from estimates: a meter reading is not the same thing as an auditor’s estimate of equipment runtime.
Useful workbook tabs include:
- Site and assumptions: address or site identifier, building type, floor area, reporting period, units, operating hours, and the person responsible for the records.
- Utility bills: one row per billing period and energy source, with dates, consumption, charges, and notes about unusual bills.
- Observations: equipment, location, condition, schedule, evidence, and any follow-up needed.
- Measures: recommended work, estimated cost, expected energy reduction, annual cost savings, and simple payback.
- Summary: key totals, important assumptions, and unresolved data gaps.
Set up the workbook and data fields
Start with a clear reporting boundary. Specify which building, meters, fuels, and dates are included. If a site has several buildings or meters, record their identifiers rather than combining everything in one unlabeled total. Separate electricity from natural gas, heating oil, district energy, or other fuels: their units are different, and adding kWh to therms does not produce a meaningful total.
On a Utility Bills sheet, make each row one bill period for one meter and energy source. A useful layout is: A = billing month, B = electricity use in kWh, C = electricity charges, D = peak demand if shown on the bill, and E = a note. Add separate columns or tables for other fuels, recording the units printed on the bill. Include the start and end dates when billing periods do not match calendar months.
Record the building floor area and its source, such as a facility record or measured estimate. State whether the area is gross, conditioned, or another defined measure; comparisons are only meaningful when the area definition is consistent. Add operating hours, occupancy patterns, and major changes during the period. A closure, extension of hours, renovation, or unusually cold season can affect energy use and should not be hidden in a general notes field.
Use Excel tables for data that will grow. Select the header and data range, then choose Insert > Table and confirm that the table has headers. Tables expand when new rows are added and make sorting and filtering easier. Use descriptive column names, one unit per column, and consistent date and number formats. In Excel for Mac and Excel for the web, table features are also available, though ribbon placement and labels can differ from Windows.
Enter utility data and check the reporting period
Enter values from bills or meter records rather than relying on memory. Keep copies of source documents in the project record, and note whether a value came from a utility bill, a submeter, or an estimate. Check that each period is represented once, that dates are in order, and that the billing periods cover the intended year. A missing bill can make an annual total look like a real reduction.
For an illustrative electricity table, suppose monthly kWh readings are in B2:B13, one row for each of 12 billing periods. Put the annual total in B14 using =SUM(B2:B13). If those rows add to 120,000, B14 returns 120,000 kWh. This is a sum of recorded consumption, not an adjustment for weather or changes in building use.
Charges can include more than energy consumed. A bill may include fixed fees, taxes, demand charges, or other line items. Keep the bill total and its components distinct where possible, and label what the cost column contains. A simple average energy cost is useful for screening, but it is not necessarily the rate that applies to the next unit saved.
With monthly electricity consumption in B2:B13 and corresponding bill costs in C2:C13, enter =IFERROR(SUM(C2:C13)/SUM(B2:B13),0) in a clearly labeled average-cost cell. If total charges are $16,800 and use is 120,000 kWh, the result is $0.14 per kWh. This blended figure is only an approximation when charges include fixed or demand-based amounts.
Calculate useful building metrics
A common screening metric is energy use intensity (EUI), calculated as energy use over a stated period divided by building area. It helps put buildings of different sizes on a comparable scale, but it does not by itself prove that one building is more efficient: climate, occupancy, hours, building type, and the energy sources included all matter. State the period and units beside the result, such as annual kWh per square foot.
Assume annual electricity use is in B14 and building area in B15, with the area recorded in square feet. Put EUI in B16 with =IFERROR(B14/B15,0). For 120,000 kWh and 10,000 square feet, the result is 12 kWh per square foot for the period covered. If the period is shorter than a year, label it accurately rather than calling it annual EUI.
For a useful comparison, keep the energy source and time period consistent. If the building’s area or use changed, record the change and consider comparing equivalent periods or using an appropriate normalization method. Do not convert different fuels into a single total unless the conversion basis and purpose are clearly stated. Some analyses use site energy, while others use source energy; those are not interchangeable labels.
Record findings and estimate recommended measures
On the observations sheet, give each finding a unique identifier and describe what was observed, where it was observed, and how the information was obtained. “Lights left on” is less useful than “West corridor lighting remained on during the recorded unoccupied period; observation made during the evening walkthrough.” Where relevant, record equipment nameplate details, controls, operating schedule, condition, and a photograph or document reference. Avoid entering sensitive personal information that is not needed for the audit.
On the Measures sheet, use one row per proposed action. Include a plain-language description, affected equipment or area, estimated installation cost, estimated energy savings, the method and source for the estimate, and any operational or maintenance considerations. Separate an action that has been completed from one that is only proposed. If multiple measures interact, flag that their individual savings may not add up to the combined savings.
For a simple illustrative estimate, assume baseline annual electricity use is in B2 and an estimated savings percentage is in C2, entered as 12%. Put estimated annual kWh savings in D2 with =B2*C2. With a 120,000 kWh baseline, the result is 14,400 kWh per year. The percentage is an assumption here, not a measured result; cite its basis and use a range when the estimate is uncertain.
If D2 contains estimated annual kWh savings and E2 contains an assumed energy price per kWh, enter =D2*E2 in F2 for estimated annual energy-cost savings. At 14,400 kWh and $0.14 per kWh, the result is $2,016 per year. This estimate does not capture fixed fees and may not reflect demand charges, changing tariffs, or other bill components. For measures affecting peak demand, model that effect separately when reliable tariff and demand data are available.
Understand payback and other cost limits
Simple payback is a screening calculation: upfront cost divided by estimated annual cost savings. It does not account for financing, maintenance, equipment life, changes in energy prices, incentives, or the timing of future cash flows. It should not be presented as a complete financial evaluation or as a guarantee of savings.
With a measure’s upfront cost in G2 and estimated annual cost savings in F2, enter =IFERROR(G2/F2,0) in H2 and label the result “simple payback, years.” If the project costs $9,000 and the estimated annual savings are $2,016, the result is about 4.46 years ($9,000 divided by $2,016). If annual savings are zero or missing, the formula returns zero, so add a visible note or status field to distinguish unavailable payback from a genuine zero-year result.
Project cost estimates should say what they include, such as equipment, installation, commissioning, or other identified work. Record whether the amount is a quotation, budget estimate, or placeholder. Utility bills and estimated savings may not account for every charge or operational effect. For decisions involving substantial spending, use project-specific engineering and financial review rather than treating the workbook’s arithmetic as a forecast.
Make the template easier to review
Give each input an owner or source, use consistent units, and distinguish assumptions from measured values with a dedicated “source or basis” column. Add a review date and version to the summary sheet. Protecting formula cells can reduce accidental edits, but retain an editable working copy and document how to update it. Do not hide assumptions in cell comments or unexplained formulas.
Use simple checks to catch common data problems:
- Confirm that the number of billing periods matches the stated coverage and investigate gaps or overlaps.
- Compare totals with the utility’s annual summary, if available, and explain any difference.
- Check that cost, consumption, area, and savings use the intended units and periods.
- Review unusually high or low values against source bills before drawing conclusions.
- Keep estimates visibly labeled and avoid adding savings from overlapping measures without checking their interaction.
Formatting can help people notice exceptions, but color should not carry meaning by itself; use a status label such as “verify” or “estimated” as well. Avoid excessive merged cells, which make filtering and sorting harder. If you need charts, a monthly line chart can show seasonal patterns, while a separate measure table is usually clearer for comparing project costs and paybacks.
Common mistakes and misconceptions
A frequent mistake is treating one year’s bill total as a definitive measure of efficiency. Consumption can change because of weather, occupancy, operating hours, production, or equipment changes. A before-and-after comparison needs comparable periods and context. Another mistake is assuming that a lower bill always means lower energy use; prices and tariff structures can change independently of consumption.
It is also easy to confuse energy and demand. kWh measures energy consumed over time, while a utility’s demand charge is generally tied to a measured peak rate of use under its tariff. Reducing kWh does not automatically reduce peak demand charges. Check the bill and tariff definitions before estimating that benefit.
Finally, a spreadsheet template is not a substitute for appropriate fieldwork, calibrated measurements, or professional judgment. It organizes information and exposes assumptions. The required depth depends on the decision being made, the complexity of the site, and the quality of available data. If records are incomplete, identify the gap and state how it limits the estimate rather than filling it with an unmarked guess.
Frequently Asked Questions
Can I download a free energy audit template in Excel?
A workbook can be created from the layouts described here, or you can look for a template from a utility, government energy program, or other source that explains its scope. Check that its units, reporting period, and calculation assumptions fit your building before relying on its outputs.
What information do I need before starting an energy audit spreadsheet?
Gather utility bills or meter data for a clearly defined period, building area, energy-source details, and basic operating information. For recommendations, collect equipment observations, operating schedules, cost estimates, and the source or method behind each savings estimate.
Should I use kWh or dollars to compare energy performance?
Use consumption units to track energy use and cost to examine spending. Costs can change because of rates or billing structures, so dollars alone do not show whether the building used less energy. Keep demand charges and other bill components identified separately when possible.
How do I calculate payback in Excel?
Divide the project’s upfront cost by estimated annual cost savings. For example, $9,000 divided by $2,016 per year is about 4.46 years. This simple payback estimate excludes factors such as maintenance, financing, and changing energy prices.
Does an Excel energy audit template certify that a building is efficient?
No. A workbook can summarize data and calculations, but it does not certify performance or replace an audit appropriate to the building and decision. Results depend on the scope, source data, measurement quality, and assumptions documented in the workbook.
Related
- Free Debt Payoff Calculator Excel: How to Build One
- DC Format in Excel: What It Means and How to Format Cells
- DBeaver Export to Excel: Steps, Options, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Redo in Excel: Shortcuts, Limits, and FixesTo redo an action in Excel for Microsoft 365 on Windows, press Ctrl+Y after undoing it with Ctrl+Z.…
- Excel Not Updating Links From Other Workbooks? FixesIf Excel is not updating links from other workbooks, first open the source workbook and use Data >…
- Microsoft Excel: How to Flip a Row to a Column QuicklyTo flip a row into a column in Microsoft Excel for Microsoft 365 on Windows, copy the row,…
- Hide a Column in Power BI: Steps and Important LimitsTo hide a column in Power BI Desktop, find it in the Data or Fields pane, right-click it,…
- Meal Planner Template Excel: Build a Useful Weekly PlanA meal planner template in Excel can be a simple weekly grid for meals, a place to track…
- Excel Custom Number Format for Negative Numbers in BracketsYes. In Excel for Microsoft 365 on Windows, use a custom number format with a semicolon separating positive…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.