Auditable spreadsheets for ESG data
Build emission and metric workbooks with separate inputs, formulas, unit conversions and checks, so another analyst or an auditor can follow every number. You will build one, then try to break it.
- Core
- Beginner
- about 25 hours
Written by the GreenHired editorial teamSources last checked 20 November 2026How we research roadmaps
Every number in an emissions workbook makes the same trip: inputs, then factors with their sources, then calculations, then checks, then a summary. Build in that order, and write the checks before you trust any total, because a plausible total is how an error slips through. Each tab has one job. A reviewer should be able to start at the summary and walk back to a source document without asking you anything. The same workbook then serves Scope 1 and 2 calculations.
How a plausible total hides a unit slip
Site B’s supplier portal shows electricity in MWh, but your input column is in kWh. Every month is 42,000 kWh except March, which the portal reports as 45 MWh, and someone types 45. All of these figures are invented, and so is the 0.2 kg CO2e per kWh factor, which is not a real grid factor (real ones come from emission factors and datasets).
The correct year is 11 x 42,000 = 462,000 kWh, plus 45,000 for March, which makes 507,000 kWh. At 0.2 that is 101,400 kg, or 101.4 tonnes. With the slip the year is 462,000 + 45 = 462,045 kWh, or 92,409 kg (92.4 tonnes). The total is about 9 tonnes too low, 8.9% of the true figure (8,991 / 101,400), and it still looks reasonable.
Two checks catch it. A range test flags any month below a tenth of the site’s typical month, and a units column with data validation stops “45” arriving with no unit at all. Then log the fix: “March, Site B: 45 (MWh read as kWh) corrected to 45,000 kWh; found by range check”. The correction and its reason stay on the change log.
Habits that break the audit trail
- Links to someone’s personal drive break when that person leaves. Keep source files in a shared, named folder and link there.
- Last year’s data overwritten by this year’s means you can no longer explain a change. Add a year column and append new rows.
- A total you trust because it looks right needs an independent tie: an invoice total or the row count of the source file.
Then test the workbook the way a reviewer would, before its figures reach a dashboard. Pick one number on the summary and trace it to a source in two minutes. If you cannot, add the missing note.
What you will learn
- Use lookup, conditional-sum and logic functions to turn raw activity data into totals by site, fuel or month.
- Structure a workbook with separate input, calculation, check and output tabs, and no numbers typed inside formulas.
- Summarise data with pivot tables and refresh imports with Power Query instead of copy and paste.
- Convert between energy, volume and mass units without silent errors, and label every unit.
- Add checks that tie totals back to source data, and keep a change log and source notes.
The parts of this topic
XLOOKUP, SUMIFS and IFabout 5 hours
Three functions that cover most ESG calculations: find a factor, add up by condition, and branch on a rule.
XLOOKUP finds a value in one column and returns the matching value from another, such as the emission factor for a fuel name. SUMIFS adds numbers that meet one or more conditions, such as litres of diesel at one site in one quarter. IF chooses between two results, for example flagging a missing meter reading. Example: =SUMIFS(litres, fuel, "diesel", site, B2). If a fuel name is spelled slightly differently in two tabs, XLOOKUP returns #N/A and SUMIFS leaves that row out of the total, so check for errors and blanks.
- XLOOKUP function Microsoft · Guide
- SUMIFS function Microsoft · Guide
- IF function Microsoft · Guide
Pivot tables for summariesabout 3 hours
Summarise thousands of rows by site, month or fuel without writing formulas, and refresh when the data changes.
A PivotTable groups rows and totals them: drag 'site' to rows, 'month' to columns and 'kWh' to values. It answers questions such as 'which site used the most electricity in Q2?' in seconds. Pivoting a year of utility invoices, for instance, can show one site with twice the usual consumption, which may be a duplicate invoice, so a pivot is a good first check on a new data file. Base it on a table that grows with the data: a pivot built on a fixed range misses new rows.
- Create a PivotTable to analyze worksheet data Microsoft · Guide
Structured tables and named rangesabout 3 hours
Turn ranges into tables and name the cells that hold assumptions, so formulas read clearly and keep working when rows are added.
An Excel table gives a block of data a name, filters, and formulas that refer to column names instead of cell addresses. A named range does the same for a single cell, such as GWP_CH4 or Reporting_Year. Put every assumption in a named input cell with its source next to it, and never hard-code a number in a formula (for example, 0.233 for a grid factor). A formula like =Activity[@kWh]*Factor_Grid is far easier for a reviewer than =C2*Sheet3!$F$9.
- Overview of Excel tables Microsoft · Guide
- Define and use names in formulas Microsoft · Guide
Power Query importsabout 4 hours
Load and clean files from sites or suppliers with a repeatable set of steps instead of copying and pasting each month.
Power Query lets you connect to files, folders or databases, then clean them with recorded steps: remove blank rows, change types, split columns, append a folder of monthly files. The steps run again when you refresh. Twelve site CSV files with different header spellings, for instance, become one clean table. Cleaning by hand in the source file leaves no trace, and the next person cannot see what you changed. With Power Query the steps are visible, so the process is repeatable and easy to audit.
- About Power Query in Excel Microsoft · Guide
Units and conversionsabout 3 hours
Move correctly between kWh, MWh, GJ, litres, cubic metres and tonnes, and label every number with its unit.
ESG data arrive in mixed units: electricity in kWh or MWh, gas in cubic metres or kWh, fuel in litres or gallons, waste in kilograms or tonnes. Excel's CONVERT function handles many conversions, but fuel conversions between volume, mass and energy need a factor from a source such as the UK conversion factors file. Converting litres of diesel to kg CO2e uses a factor per litre, not per kWh. Mixed units in one column, or a MWh figure read as kWh, create a thousand-fold error.
- CONVERT function Microsoft · Guide
Check and reconciliation tabsabout 4 hours
Add formulas that prove totals tie to source data and flag impossible values before anyone reads the results.
A check tab holds simple tests: does the sum by site equal the sum by fuel? Does the number of rows imported equal the number in the source file? Is any monthly value negative, blank or ten times the average? Show each test as OK or ERROR, with one overall flag at the top. Put the overall status on the summary page, where a failure cannot be missed, because a check nobody reads is useless. Data validation can stop bad entries, such as a unit that is not on the list.
- Apply data validation to cells Microsoft · Guide
Change log and source notesabout 3 hours
Record what changed, why, by whom and where every input came from, so last year's figure can be explained next year.
A change log is a tab with date, person, what changed and why. Source notes sit next to each input: file name, owner, date received and any estimate used. Together they make a workbook auditable: someone can trace a number back to an invoice or a meter. If you overwrite a number with no trace, the next reviewer cannot tell a correction from an error. Example: 'June electricity for Site B estimated from May because the bill was late; replaced on 12 July'.
- Improve your financial modelling practice ICAEW · Guide Pointers to spreadsheet good practice and the 20 principles.
Practice task
Build a small workbook for an imaginary three-site company: one input tab of monthly electricity and fuel data, one factors tab with a source for every factor, one calculation tab using lookups, a pivot summary, and a checks tab that proves the totals tie back. Add a change log and send it to a friend to try to break.
Skills you build
XLOOKUP and SUMIFSPivot tablesStructured tables and named rangesPower Query importsUnit conversionCheck and reconciliation tabsChange logs
Resources for this topic
Every source here is free. Each link opens the provider's own site in a new tab.
- Excel help and learning Microsoft · Guide The official help centre: functions, tables, charts and Power Query.
- Financial Modelling Code ICAEW · Standard A set of principles for robust modelling in spreadsheets, written for finance but equally useful for ESG data.
- ICAEW Excel Community ICAEW · Community
- UK government conversion factors for company reporting UK Government (DESNZ) · Dataset A real factor file to practise on: trace each factor, its unit and its year.
Live ESG analyst jobs
Roles whose title matches this career path.
Spot a problem with this topic?
Tell us what is wrong, missing or unclear here. Your note is attached to this topic.
Notes are sent from this page with JavaScript on. Without it, write to us on the contact page.