LumaQuote › Lighting Audit Spreadsheet

Spreadsheet structure + software that replaces it

Lighting Audit Spreadsheet
Every Column. Live Calculations.

The complete column structure for a commercial lighting audit spreadsheet, plus why LumaQuote replaces the spreadsheet entirely by calculating kWh/yr live as you enter fixtures and building the proposal automatically.

Try LumaQuote Free See All Spreadsheet Columns ↓

No credit card required  ·  Browser-based  ·  Calculates live: no formulas to build

📊 Lighting Audit Spreadsheet

F2 =(B2*C2*D2*E2)/1000

vs. LumaQuote: calculates live ✓

A · B · C · D · E · F · G · H · I

Zone / Area Fixture Type Watts (W) Qty h/d × d/yr kWh/yr =(W×Q×H×D)/1000 $/yr =kWh×rate LED Proposed Savings/yr

Warehouse · Metal Halide 400W · 458 · 6 · 12 × 260 · 8,566 · $1,114 · LED High Bay 200W · $617/yr

Warehouse · T8 4ft 2-Lamp · 72 · 24 · 10 × 260 · 4,493 · $584 · LED Tube 4ft · $388/yr

Office · T8 2ft 2-Lamp · 36 · 42 · 10 × 260 · 3,931 · $511 · LED Panel 2×2 · $298/yr

Totals · 72 · 16,990 · $2,209 · $1,303/yr

The core calculation

The kWh formula, and why it matters.

Every lighting audit spreadsheet is built around one formula. Get it right and every savings number in the proposal is defensible. Get it wrong and the whole proposal falls apart under client scrutiny.

kWh/yr Formula

( W × Qty × h/day × d/yr ) ÷ 1000

Excel: =(B2*C2*D2*E2)/1000

The division by 1,000 converts watt-hours to kilowatt-hours. Most errors happen in three places: using the rated wattage instead of the measured system wattage (especially on HID fixtures), guessing operating hours instead of confirming them, and forgetting to account for weekends or seasonal shutdowns in the days/year value.

In LumaQuote, this formula runs automatically per fixture as you type. You don't build it: you just read the result and verify it looks right before moving to the next zone.

Example: Metal Halide 400W, 6 fixtures

System wattage (measured) 458 W

Quantity × 6

Operating hours/day × 12 h/d

Operating days/year × 260 d/yr

Convert to kWh ÷ 1,000

= 8,566 kWh/yr = $1,114/yr at $0.13/kWh

Note: 458W vs. rated 400W = 14% higher energy use. Real measurements matter.

Column-by-column breakdown

Every column your lighting audit spreadsheet needs.

Organized by function. Columns A to E capture the data. Columns F to G calculate energy use. Columns H to L capture the retrofit scope and connect to the proposal.

Data Capture Enter on-site

Column · Field · Notes

A Area / Zone Main Warehouse, Office, Parking. Groups the fixture inventory by location.

B Fixture Type T8 4ft 2-Lamp, Metal Halide 400W, HPS 250W. Use a standardized list, not freeform text.

C Lamp / Ballast F32T8 / Electronic. Needed for rebate applications and wattage verification.

D Measured Watts (W) Always read the nameplate or use a meter. HID fixtures often draw more than rated.

E Quantity Count per zone. One row per unique fixture type + wattage + schedule combination.

F Hours/Day × Days/Year Confirm with facility manager. Most common errors: guessing 8h, forgetting weekends.

Calculated Formula columns: auto in LumaQuote

Column · Field · Formula / Notes

G kWh/yr =(D×E×F_h×F_d)/1000: The core energy model. Verify on a known fixture before trusting the whole sheet.

H $/yr (cost) =G×utility_rate: Use actual billed rate, not provincial average. Store rate in a named cell.

Retrofit Scope LED specification and savings

Column · Field · Notes

I Proposed LED Type LED High Bay 200W, LED Tube 4ft 15W. Matched to the existing fixture type.

J LED Watts Actual LED system wattage. Don't use labeled watts: use the fixture spec sheet value.

K LED kWh/yr =(J×E×F_h×F_d)/1000: Same formula as existing, with LED watts.

L Annual Savings ($/yr) =(G-K)×rate: The number that justifies the investment. Also used in payback calculation.

M Unit Cost (LED) Material cost per fixture. Used in payback and cash flow calculations.

N Rebate per Fixture Per-fixture rebate amount from the utility program. Confirm with the utility or distributor.

Status & Notes Scope tracking and field notes

Column · Field · Notes

O Scope Status In Scope / Leave As-Is / Excluded / Emergency Only. Every fixture needs a status.

P Condition Good / Fair / Poor. Documents existing state. Protects you if a client disputes a pre-existing issue.

Q Location Note Bay 3 Row A, West exterior wall. Allows the installation team to find the fixture without a re-walk.

R Notes Access issues, client preferences, mounting constraints, or reason for leaving as-is.

Using the spreadsheet on-site

How to use a lighting audit spreadsheet during the walk.

01

Set up zone tabs before arrival

Create a tab per zone or use a single sheet with the area column. Naming zones in advance means you're not wasting time typing area names during the walk. Main Warehouse, Office Block A, Loading Dock, Exterior, done before you arrive.

02

Freeze the column headers

In Excel or Google Sheets: View → Freeze top row. This keeps fixture type, watts, quantity, and hours visible as you scroll down on large sites. A minor detail that saves significant frustration on a 200-fixture audit.

03

Enter watts from the nameplate, not the dropdown

Even with a preset list of fixture types, always read the nameplate and enter the actual measured wattage. An HPS 250W fixture that's been retrofitted before, or has a failing ballast, may draw 295W. That difference changes the savings calculation significantly.

04

Verify the kWh formula on row 2

Before entering more than 5 rows, verify the formula is correct on the first data row. Calculate manually: watts × qty × h/d × d/yr ÷ 1000. If the spreadsheet matches your manual calculation, the formula is right. This one check prevents cascading errors across 50 rows.

05

Check the zone total before moving on

Sum the kWh/yr column per zone and sanity-check it. A warehouse with 40 Metal Halide fixtures at 12h/day should show roughly 70,000 to 100,000 kWh/yr. A number far outside that range means a data entry error. Catch it on-site, not back at the office.

06

Never delete excluded fixtures

Mark them as Excluded or Leave As-Is in column O. Deleting them removes them from the record entirely. They need to appear in the final proposal with a clear note, so the client understands the total fixture count and why some weren't included in the retrofit scope.

Spreadsheet limitations

What spreadsheets miss once you need proposal-ready output.

Manual formula verification

You build the kWh formula and hope it's right. In a large spreadsheet, a single broken cell reference silently produces wrong numbers in every row below it. LumaQuote calculates per-fixture: each row is independent, no chain of cell references to break.

No connection to the proposal

The audit spreadsheet captures data. The proposal is a separate Word document. You re-enter fixture quantities, savings totals, and rebate amounts manually, creating a second opportunity for errors and inconsistency.

No payback or cash flow calculation

Payback = net investment ÷ annual savings. That's a formula most contractors build in a separate tab, and one that breaks if the fixture costs or rebate amounts change. In LumaQuote, payback and 10-year cash flow update automatically when any input changes.

Revision creates cascading errors

Client changes scope on three fixtures. You update the spreadsheet, then need to manually check whether the totals, payback, rebate table, and proposal Word doc are all consistent. In LumaQuote: change the fixture, every number downstream updates automatically.

No controls analysis

A spreadsheet can't model occupancy sensing or daylight harvesting savings per area. Controls are often a meaningful upsell, but without purpose-built software, showing LED-only vs LED+Controls side by side requires building a second model from scratch.

From spreadsheet to LumaQuote

Build kWh formula manually

Calculates live as you type

Re-enter data into Word

Proposal built from audit automatically

Build payback table separately

Payback + 10-yr cash flow auto-generated

Manual rebate calculation

Per-fixture rebate tracked and totaled

No controls model

LED-only vs LED+Controls side by side

Revision requires checking everything

Change any input → all numbers update

Export: copy-paste into Word

One-click PDF + Word export

Try LumaQuote Free →

Full comparison

Lighting audit spreadsheet vs. LumaQuote.

Capability · Excel / Google Sheets · LumaQuote

Fixture data capture · Manual entry · ✓ Guided entry with dropdown

kWh/yr calculation · Build the formula yourself · ✓ Automatic, per fixture

Utility rate · Enter manually per project · ✓ Auto-filled by region

Savings per fixture · Additional formula tab · ✓ Live as you enter LED

Rebate calculation · Manual, error-prone · ✓ Per-fixture, tracked

Payback period · Separate formula · ✓ Auto-calculated

10-year cash flow · Build a table manually · ✓ Auto-generated

Controls analysis · ✗ Not possible · ✓ Separate layer

PDF export · ✗ Not included · ✓ One click

Word (.docx) proposal · ✗ Build manually · ✓ One click

Revision consistency · Check every formula · ✓ Auto-updates throughout

Starting price · Free / your time · Free plan, then $99/mo

Common questions

Lighting audit spreadsheet FAQ.

What columns should a lighting audit spreadsheet have?

A complete lighting audit spreadsheet should include: area/zone, fixture type, lamp type, ballast type, measured wattage, quantity, operating hours per day, days per year, kWh/yr (calculated), annual cost (calculated), proposed LED type, LED wattage, LED kWh/yr, annual savings, unit cost, rebate per fixture, scope status (in scope/leave as-is/excluded), condition, location note, and notes. LumaQuote captures all of these with automatic calculation of the energy columns.

What is the kWh formula for a lighting audit spreadsheet?

The kWh/yr formula is: (Watts × Quantity × Hours per day × Days per year) ÷ 1000. In Excel: =(B2*C2*D2*E2)/1000 where B2 is watts, C2 is quantity, D2 is hours/day, and E2 is days/year. Always verify this formula on a known fixture before relying on it across a large audit. LumaQuote runs this calculation automatically per fixture as you type: no formula to build or verify.

How do I set up a lighting audit spreadsheet in Excel?

Set up your columns: A=Zone, B=Fixture Type, C=Lamp/Ballast, D=Watts, E=Quantity, F=Hours/day, G=Days/yr, H=kWh/yr (formula: =(D*E*F*G)/1000), I=$/yr (formula: =H*rate, where rate is stored in a fixed cell). Freeze row 1. Create a separate tab for LED specifications and savings calculations. Maintain a totals row per zone. Verify the kWh formula manually on the first data row before entering more data. For anything beyond a simple single-area audit, consider LumaQuote, which handles all of this automatically including the proposal export.

What are the limitations of a lighting audit spreadsheet?

A lighting audit spreadsheet has four key limitations: (1) You build and verify the kWh formula yourself: errors are silent and can cascade across hundreds of rows; (2) The spreadsheet doesn't connect to the proposal: you re-enter data manually, creating a second opportunity for inconsistency; (3) Rebate calculations, payback, and cash flow require additional formulas or separate tabs; (4) Any revision requires checking every downstream calculation for consistency. LumaQuote handles all of this automatically.

Is a lighting audit spreadsheet enough for a professional proposal?

A spreadsheet captures the fixture data, but a professional proposal requires much more: per-fixture savings, rebate-adjusted payback, 10-year cash flow, controls analysis, CO₂ reduction, a full fixture schedule, and formatted export. Building all of this from a spreadsheet takes 4 to 6 hours of office work per project. LumaQuote builds the proposal automatically from the audit data and exports it in one click as PDF or Word.

⚡ LumaQuote

Replace the spreadsheet.
Keep the accuracy.

LumaQuote captures every column in this spreadsheet, and calculates kWh/yr automatically, connects directly to the proposal, and exports in one click.

Start Free Audit Template → Audit Checklist →

Free plan: 3 proposals, no credit card · Pro: $99/mo or $899/yr

Already a customer? Log In →