How to Analyse NEM12 Data in a Spreadsheet

Pivot Tables, E1 and B1 Registers, and Time-of-Day Bands in Excel or Google Sheets

Last Updated: 2026-07-29

Key Takeaways

• To analyse smart meter data in a spreadsheet, start with a flattened NEM12 CSV. Every reading needs its own date, time, and Meter Register.
• Long Format gives one row per interval and suits a Pivot Table. Wide Format gives one row per day and suits a single-day chart.
• B1 is always Solar Export. E1 is a mains-grid import register. Never sum an import register and an export register.
• One NEM12 pivot table can group Half-Hourly Data by day, week, month, or time of day.
• SUMIFS, MINIFS, MAXIFS, and AVERAGEIFS take the same arguments in Excel and in Google Sheets.
• Time-of-day bands, Baseload, and Self-Consumption come from the same file. A Spreadsheet needs no add-in and no code for any of them.

Long Format vs Wide Format for Analysis

Your DNSP or your Electricity Retailer supplies the file in the NEM12 CSV Format, which AEMO defines. A raw file mixes several row types, so a spreadsheet cannot read it directly.

A representative extract from the raw file — one 200-record (metadata) and one 300-record (interval data) for each register, plus the end-of-file 900 marker. The same column position carries different information depending on the row type.

Column 1 holds the NMI in a 200-row but the date in a 300-row. Column 2 holds the NMI configuration code (B1E1) in a 200-row but the first 30-minute energy reading in a 300-row. A spreadsheet has no way to resolve this ambiguity without a custom parser.

Analysis starts after you flatten that file into one consistent table. Read our guide on how to open NEM12 smart meter data in a spreadsheet first. Our NEM12 CSV Spreadsheet Converter writes both table shapes.

Try our NEM12 CSV Spreadsheet Converter Tool

• Wide Format: one row per day per Meter Register, with 48 half-hour columns across the row.
• Long Format: one row per 30-minute interval, with the NMI, Meter Register, date, and time on every row.

Why Long Format Suits a Pivot Table

A Pivot Table groups rows by repeated key columns. Long Format repeats the date, the time, and the Meter Register on every row.

The same day (15 Jan 2025) in long format — one row per 30-minute interval, NMI and register repeated on every row. Overnight grid import (E1, 00:30–03:00) and midday solar export (B1, 10:30–13:00) are shown.

The full 366-day file contains 35,136 rows (366 days × 2 registers × 48 intervals). Filter Register = E1 for grid consumption, Register = B1 for solar export, then group by Date, Time, or month to build any summary you need.

When Wide Format Is Still Useful

Wide Format is harder to pivot. It suits a chart of one day, and some solar design tools accept it directly.

Four days from the same file in wide format — one row per day per register, with time-of-day column headers. Columns shown: overnight (00:30–02:00), midday (11:30–13:00) and end-of-day (23:30–24:00). 40 of the 48 time columns are hidden.

Summer day 15 Jan 2025, winter day 15 Jul 2024. B1 solar export is zero at night regardless of season and non-zero at midday in summer. At midday on the summer day, E1 grid import falls to zero — the solar system covers all consumption and exports the surplus.

Separate the E1 and B1 Registers First

B1 is always Solar Export. E1, E2, and E3 are always mains-grid import registers. Never sum an import register and an export register, because that adds energy in to energy out.

The metadata rows in this NEM12 file identify the meter, the registers present, and the period covered.

Source: 200-records in the NEM12 file. NMI 6200000000.

Filter Before You Aggregate

In a Long Format table, filter the Meter Register column before you total anything. Every recipe below assumes that you have done this.

• Grid import: filter Register = "E1", then sum the kWh column.
• Solar export: filter Register = "B1", then sum the kWh column.
• Net import: subtract the B1 total from the E1 total. Never add the two totals.

Try our NEM12 Smart Meter Data Analyzer Tool

Build a NEM12 Pivot Table in Excel or Google Sheets

A Pivot Table turns a flattened NEM12 CSV into a seasonal picture. The chart below is a pivot output from a real 366-day file, grouped by month.

The Same Pivot Table Steps in Excel and Google Sheets

In Excel, select the Long Format range, then choose Insert > PivotTable. In Google Sheets, choose Insert > Pivot table. Then set four fields.

1. Rows: drag Date into the rows area for the time axis.
2. Columns: drag Register into the columns area to split E1 from B1.
3. Values: drag kWh into the values area. Set Sum for totals, or Average for a Load Profile.
4. Group: right-click a date, then group by month, quarter, or year.

Summarise Daily, Weekly, or Monthly Averages

The same Pivot Table answers daily, weekly, and monthly questions. Change the grouping on the date field, and set the value field to Average.

• Daily: group by date, and compare one day against another.
• Weekly: add a helper column with =WEEKNUM(A2), then group on that column.
• Monthly: group the date field by month to show seasonal change.

Monthly energy totals from all 366 days — the result of summing the 48 half-hourly readings per day and grouping by month. Bars are scaled to the highest single-month total across both registers.

NMI 6200000000, 2023-02-19 to 2024-02-19. Net import = E1 − B1. Solar export peaks in the summer months; grid import peaks in winter when solar generation is lowest.

Calculate Minimum, Maximum, and Average Values

Peaks and floors matter as much as totals. Excel 2019 and later, and Google Sheets, both provide MINIFS, MAXIFS, and AVERAGEIFS. All three take the same arguments as SUMIFS.

• Maximum demand: =MAXIFS(E:E, B:B, "E1") returns the largest half-hourly import in the file.
• Average interval: =AVERAGEIFS(E:E, B:B, "E1") returns the mean import per 30-minute interval.
• Minimum interval: =MINIFS(E:E, B:B, "E1") returns the smallest import in the file.

Find the Baseload with MINIFS

Baseload is the continuous background draw of a home with the active appliances off. The overnight minimum is a good estimate, because most appliances are off then.

Calculate Solar Self-Consumption

Self-Consumption is the share of your generated solar that you use on site instead of exporting it. The E1 and B1 registers give you an estimate.

1. Total the export: sum the B1 kWh column for the period.
2. Estimate the on-site use: compare daytime E1 import against your overnight Baseload rate.
3. Divide and scale: Self-Consumption = (solar used on site ÷ total solar generated) × 100.

A low ratio shows that most of your generation leaves the property as export. Flexible loads can absorb that export, and our buyers guide on comparing split-system air-conditioners is one place to start. Further reading sits in our how-to guides.

Group Half-Hourly Data into Time-of-Day Bands

Reading all 48 half-hour slots one by one is rarely useful. Group the intervals into the tariff bands that your Electricity Retailer charges on. A common Australian Time-of-Use (TOU) Tariff uses bands like these.

• Morning peak: about 6:00am to 9:00am on weekdays.
• Shoulder: about 9:00am to 5:00pm.
• Evening peak: about 5:00pm to 8:00pm, usually the most expensive band.
• Off-peak: about 8:00pm to 6:00am overnight.

Bucket Intervals with SUMIFS

One SUMIFS totals one Meter Register inside one time window. To find the total evening-peak grid import, build the formula in this order.

1. Set the sum range: use the kWh column, for example E:E.
2. Filter by register: add the criteria pair B:B, "E1".
3. Open the band: add D:D, ">=17:00" for the 5:00pm start.
4. Close the band: add D:D, "<20:00" for the 8:00pm end.

The finished formula is =SUMIFS(E:E, B:B, "E1", D:D, ">=17:00", D:D, "<20:00"). It returns your evening-peak import in kWh. The table below shows the averaged profile behind those bands.

Average energy per 30-minute slot across all 366 days — the result of grouping the long-format data by time interval and averaging the kWh column.

Averaged across 366 days. B1 solar export rises from zero at dawn, peaks around midday, and returns to zero at dusk. E1 grid import shows morning and evening demand peaks with a midday dip where solar generation reduces grid draw.

Plot Smart Meter Data in a Spreadsheet

A chart often shows a pattern that the numbers alone hide. Select the Pivot Table output, then insert a chart from the Insert menu.

• Column Chart: good for monthly or seasonal totals per Meter Register.
• Line Chart: good for a trend across many days.
• Stepped Area Chart: good for a Load Profile, because each reading holds flat across its interval.

Our guide on how to visualize NEM12 data with charts compares these chart types. To read the same file in a browser instead, use our NEM12 Smart Meter Data Analyzer or one of our other online tools.

Try our NEM12 Smart Meter Data Analyzer Tool

Frequently Asked Questions

• How do I analyze smart meter data in a spreadsheet? Flatten the NEM12 CSV into Long Format, filter by Meter Register, then build a Pivot Table on date, time, and kWh.
• Should I use Long Format or Wide Format for a Pivot Table? Use Long Format, because it repeats the date, the time, and the Meter Register on every row.
• What is a NEM12 pivot table? It is a Pivot Table built on a flattened NEM12 CSV, with dates in rows, Meter Registers in columns, and kWh in values.
• Why must I separate the E1 and B1 registers? B1 is always Solar Export and E1 is a mains-grid import register, so a sum of the two adds energy in to energy out.
• Which E register holds my main household consumption? Where a file carries more than one E register, E1 is almost always the Main/General register. Treat a file that appears otherwise as suspect.
• Can a Controlled Load appear on the B1 register? No. A Controlled Load appears on a secondary import register, typically E2, and never on B1.
• How do I summarize daily, weekly or monthly averages for smart meter data in a spreadsheet? Group the date field of the Pivot Table by day, week, or month, then set the value field to Average.
• How do I calculate minimum, maximum and average values for smart meter data in a spreadsheet? Use MINIFS, MAXIFS, and AVERAGEIFS on the kWh column with a Meter Register criterion such as "E1".
• How do I plot smart meter data in a spreadsheet? Select the Pivot Table output, then insert a Column Chart, a Line Chart, or a Stepped Area Chart.
• Do the formulas differ between Excel and Google Sheets? No. SUMIFS, MINIFS, and AVERAGEIFS take the same arguments in Excel, in Google Sheets, and in LibreOffice Calc.
• How do I total peak-period consumption? Use one SUMIFS that filters the kWh column by Register = "E1" and by a time window that matches the peak band of your tariff.
• How do I estimate Baseload from Half-Hourly Data? Take the minimum E1 reading in a quiet overnight window of about 2:00am to 5:00am with MINIFS.
• How many rows does a year of Interval Data produce? About 17,500 rows per Meter Register for Half-Hourly Data, or about 35,000 rows across E1 and B1 together.
• Can I calculate Self-Consumption in a spreadsheet? Yes. Compare the B1 export total against the drop in E1 import during daylight hours.

Try our NEM12 Smart Meter Data Analyzer Tool

Glossary

200 Record

In the NEM12 Format, a Metadata row that declares the NMI, NMI Suffix, Meter Number, Unit of Measure, and interval length for the data rows beneath it.

300 Record

In the NEM12 Format, an interval data row carrying one day of readings for a single Meter Register plus a Quality Flag.

AEMO

Australian Energy Market Operator — the body that administers the National Electricity Market and defines and maintains the NEM12 specification.

B1

In the NEM12 Format, the Meter Register code for Solar Export — electricity generated on site and fed back to the grid, typically measured in kWh.

Baseload

The continuous background electricity draw. In a Residential Home, when active appliances are off, estimated from the overnight minimum.

Check Digit

The 11th digit of a full NMI — calculated from the first ten digits to detect transcription errors; some DNSP portals require it. Also called a 'Checksum'.

Column Chart

A chart that represents each value as a vertical bar, well suited to comparing discrete totals such as average daily use across months or seasons.

Controlled Load

A separately metered circuit controlled by a signal from the DNSP. These circuits typically contain a single specific appliance or type of appliance - eg. an Electric Hot Water Service (and historically Air-Conditioners or Electric Slab Heating). They are typically billed at a discounted rate and often run only during off-peak hours. They appear on a separate Meter Register on an Electricity Bill. See also: Dedicated Circuit.

CSV

Comma-Separated Values — a plain text file in which each row is a record and fields are separated by commas.

Dedicated Circuit

The physical wiring and breaker serving a Controlled Load device, isolated so the DNSP can independently control supply. See also: Controlled Load.

DNSP

Distribution Network Service Provider - the company that owns and operates the physical poles and wires of the local electricity network, and usually provides the smart meter data portal. Not the same as your Electricity Retailer. See also: Electricity Retailer.

E1

In the NEM12 Format, the Meter Register code (or NMI Suffix) for the sole/main/primary mains grid import channel — electricity drawn from the network. Where there is No (Solar) Generation on site, this is the Total Consumption. Typically measured in kWh.

E2

In the NEM12 Format, the Meter Register code (or NMI Suffix) for a secondary mains grid import channel — typically controlled-load or off-peak consumption such as dedicated hot water. Present only where that tariff exists.

Electricity Retailer

The company that sells electricity to the consumer, issues the bill, and manages the account. Not the same as your DNSP (Distribution Network Service Provider) which owns and operates the network. See also: DNSP.

Half-Hourly Data

Interval data recorded every 30 minutes — 48 readings per register per day.

Interval Data

Energy readings recorded at fixed time steps — typically every 30 minutes (but sometimes 5 or 15 minutes on newer meters) rather than as a single billing total. This allows charging different Tariffs at different times of day or days of the week. See also: Time-of-Use (TOU) Tariff.

kW

KiloWatt — the standard unit of (Electrical) Power: 1,000 Watts of Power used continuously for one hour is equivalent to 1000 Watt-Hours or 1 KiloWatt-Hour of Energy. Power is not an Amount of Energy but a Rate of Energy Transfer. See also: kWh.

kWh

KiloWatt-Hour — the standard unit of (Electrical) Energy: 1,000 Watts of Power used continuously for one hour is equivalent to 1000 Watt-Hours or 1 KiloWatt-Hour. NEM12 CSV records Electricity Use in kWh. See also: kW.

Line Chart

A chart that joins successive values with straight segments to trace a trend over time; it implies continuous change between the plotted points.

Load Profile

The pattern of a site's electricity use over time, derived from its interval data. The granularity or resolution of the Profile is the Interval Length (eg. 30 Minutes, Hours), and is typically based on averages for a longer period (eg. Months, Seasons).

Long Format

A table shape with one row per interval — keys such as date, time, and register repeated on every row, making the data easy to filter, sort, and pivot. See also: Pivot Table.

Meter Register

See NMI Suffix.

NEM

The Australian National Electricity Market — the interconnected electricity grid covering Queensland, New South Wales, Victoria, South Australia, Tasmania and the ACT. Western Australia and the Northern Territory operate separate grids.

NEM12

The AEMO file format (version 12) for exchanging Interval Data from Smart Meters across the Australian National Electricity Market (NEM). NEM12 Format records the Amount of Energy per Time Interval in kWh. And also other types of data, using specific types of labelled rows or records (100, 200, 300, 400, 900) to record different types of data. This makes it a 'Semi-Structured' 'Non-Tabular' Format. See also: AEMO, NEM, kWh, Semi-Structured, Non-Tabular.

NEM12 CSV

A NEM12 File stored as comma-separated values — the form most DNSP and retailer portals distribute. See also: NEM12, CSV.

NMI

National Metering Identifier — the unique 10-digit identifier for an electricity connection point (11 with the Check Digit), permanent to the property. Note that the electricity connection point identified by the NMI, typically has multiple NMI Suffixes, and can have multiple Physical Meters. See also: Check Digit, NMI Suffix.

NMI Suffix

In the NEM12 Format, the short two-character code (E1, E2, E3, B1, B2, Q1, K1) identifying a specific Meter Register for recording electricity flows on the mains electricity connection identified by that NMI — also called the Meter Register or Register Code. See also: NMI, NEM12.

Non-Tabular

Data that does not fit a single consistent grid — in NEM12's case because of its semi-structure, hierarchy and mixed-record layout.

Off-Peak

The lowest-priced time period in a Time-of-Use (TOU) Tariff — typically overnight and sometimes weekends. In current usage refers to the Time electricity is imported on a Meter Register and at Other Times, the import rate on that Same Meter Register might be different. It can also currently refer to Dedicated Circuits / Controlled Loads that are only permitted to draw power at set Off-Peak times. And historically, before Smart Meters, only the latter could receive an Off-Peak Rate. See also: Time-of-Use (TOU) Tariff, Controlled Load, Dedicated Circuit.

Peak

The highest-priced time period in a Time-of-Use (TOU) Tariff — typically weekday daytime and early evening. See also: Time-of-Use (TOU) Tariff.

Pivot Table

A spreadsheet feature that groups a long-format table by chosen keys and aggregates a numeric column (sum, average, minimum).

Self-Consumption

The share of the electricity generated by your own solar panels that your household uses on site, rather than exporting it to the grid.

Semi-Structured

A data format that follows strict syntactic rules (e.g. comma separation, defined record codes) but does not enforce a consistent column schema across all rows. NEM12 is a Semi-Structured Format. See also: NEM12.

Shoulder

A mid-priced period in some (but not all) TOU Tariffs, between Peak and Off-Peak. See also: Time-of-Use (TOU) Tariff.

Smart Meter Data

Electricity import and export readings recorded by a digital interval meter at regular intervals. In Australia, typically supplied in NEM12 CSV Format with 30 Minute Intervals. See also: NEM12 CSV.

Stepped Area Chart

An area chart that holds each value flat across its interval and steps to the next, honestly representing constant-within-interval meter readings rather than smoothing between them.

Time-of-Use (TOU) Tariff

A tariff with different prices at different times of day or days of the week (Peak, Shoulder, Off-Peak).

Wide Format

A table shape with one row per day per Meter Register and one column per Time Interval — good for charting a Load Profile. See also: Load Profile.

Try our NEM12 Smart Meter Data Analyzer Tool