Showing relative heat vulnerability across Victoria, using Excel only

The short answer

Yes, put the areas into indices, but build one per indicator. Rank every SA2 against the rest of Victoria on each indicator separately. You never add indicators together, so every result still names what drives it.

A state percentile of 0.85 means an SA2 has a higher value than 85% of Victorian SA2s. That makes a share of children, a star rating and an ACS index directly comparable, and it is easy to explain.

My recommendation:

• use the state ranks as the foundation for everything
• publish the driver table and the LGA roll-up as the statewide views
• use the area profile for drill-down and briefing
• use the matrix when a program asks a specific targeting question

Before you scale up, fix the 3 data issues in the next section. One of them changes which SA2s sit in 2 of your 3 pilot LGAs.

Fix these before you scale up

These issues are in tab 1.3 All Victoria. The worked Excel file already fixes them, and its Fixes log tab lists every change.

1. Wrong LGA for SA2s that cross a boundary

For an SA2 that crosses an LGA boundary, the lookup has picked up an LGA that holds only a small part of it. For example, Moe – Newborough is 99.9% in Latrobe but sits under Baw Baw. Fitzroy North is 93% in Yarra but sits under Merri-bek.

This affects the pilot:

• Latrobe shows 3 SA2s but should have 6: Churchill, Moe – Newborough and Yallourn North – Glengarry are missing (your Future Heat tab already lists Churchill and Moe – Newborough under Latrobe)
• Merri-bek includes Fitzroy North, which belongs to Yarra
• Swan Hill is missing Swan Hill Surrounds
• star ratings, and later future heat, are joined by LGA, so these SA2s also get the wrong LGA's values

To fix it, use the LGA with the largest RATIO_FROM_TO for each SA2. In tab 4.6, this Excel 365 formula returns the main LGA for the SA2 code in A2:

=INDEX(LGA_NAME_2025, MATCH(1, (SA2_CODE_2021=A2) * (RATIO_FROM_TO=MAXIFS(RATIO_FROM_TO, SA2_CODE_2021, A2)), 0))

2. LGA values presented as SA2 values

Average star rating is the same for every SA2 in an LGA, so it cannot show differences between SA2s within an LGA. The same goes for future heat, which currently covers Swan Hill, Merri-bek and Latrobe. Label these as LGA values in every chart, and analyse them at LGA level (see the LGA roll-up below).

Until full future heat data arrives, the ACS heat exposure index is your only statewide SA2-level heat measure. It combines mean summer temperature, pre-1980 buildings, housing style and vegetation cover.

3. Non-residential SA2s

Remove the 10 SA2s with no residents or all-zero indices: . Their zeros push other SA2s up the rankings and drag averages down.

Smaller points

• The ACS indices (0 to 1) are national percentile ranks. A value of 0.8 means higher than 80% of Australian SA2s, not Victorian ones. Use state ranks when comparing within Victoria.
• Cool place access, social connectedness and health service access run the other way: a high value is protective. The views below flip them so a high percentile always means more vulnerable.
• The low household income column ($1,249 or less) covers 60% of households in the median SA2, so it barely separates areas. I used the VCOSS poverty rate for money and resources instead.
• Some columns are fractions (0.07) and others are percentages (7.0), and 'Age 4 or less' duplicates '% Age <4'. Pick one format and drop the duplicates.
• Tab 1. Summary labels heat as days above 30°C, but tab 1.3 holds days above 35°C. Confirm which measure you want to carry forward.

0Rank the areas, not the indicators

This is the step that answers your question about indices. Each SA2 gets a percentile for each indicator, calculated across all 514 residential SA2s. Nothing is weighted or added together.

Percentile (high = more vulnerable)
=PERCENTRANK.INC(D$2:D$515, D2)

Exact version (no rounding)
=COUNTIF(D$2:D$515, "<"&D2) / (COUNT(D$2:D$515) - 1)

Percentile for a protective indicator
=1 - PERCENTRANK.INC(D$2:D$515, D2)

Quintile (1 to 5)
=MIN(5, 1 + INT(E2 * 5))

Top 20% flag
=IF(E2 >= 0.8, "Top 20%", "")

Robinvale and Hadfield both rank high on heat exposure and poverty. Beyond that, their drivers differ. Robinvale also stands out on low English proficiency, renting and a low star rating (an LGA value for Swan Hill). Hadfield stands out on young children and people needing help with core activities. A single composite score would hide that difference. The ranks keep it.

One limit: a percentile shows position, not size. Always show the raw value next to the rank, so readers can see that a top-20% share might still be small in absolute terms.

1Driver table: every SA2 and its drivers

One row per SA2, one column per indicator, each cell coloured by state quintile. A dot marks the top 20% of Victoria. Sort by how many indicators are in the top 20%, or by any single indicator. Filter by region.

What it shows:

2LGA roll-up: connect people to future heat

Future heat is at LGA level, so bring the SA2 data up to LGA rather than spreading heat down to SA2. There are two ways to roll up, and they answer different questions:

• share of people living in top-20% SA2s: where are the concentrations of vulnerability?
• LGA average, weighted by population: how does the LGA compare overall?

3Two-way matrix: exposure against people

Choose one exposure measure and one people measure. Each SA2 falls into a third of Victoria on each. The top-right cell lists areas high on both, which is a targeting list that still names both drivers. Match the pair to a policy lever, for example:

• heat exposure against renting: minimum rental standards
• heat exposure against people 65 and over: heatwave welfare checks
• heat exposure against social housing: social housing retrofits

4Area profile: any SA2 against the state

This replaces building charts 3 SA2s at a time. Choose any SA2 and a comparison. Every indicator shows on the same 0 to 1 state scale, so the chart reads the same for every area.

Which view answers which question

Maps without GIS

Excel's filled map chart matches place names through Bing. It handles countries, states and some postcodes, but not SA2s, and Australian LGA names match unreliably. I have not tested it on your version of Excel, so test it with a handful of LGAs before you plan around it.

2 options work without GIS:

• group by region: the first 3 digits of an SA2 code are its SA4 code (=LEFT(A2, 3)), which gives 17 regions such as Ballarat, Shepparton and Melbourne – West (the worked file includes this column)
• use 3D Maps (Insert › 3D Map) with a latitude and longitude for each SA2, from ABS centroid data, to plot each area as a coloured dot (this is exploratory and hard to share as a static output)

For the report, a ranked table or chart grouped by region usually reads better than a map with 514 small areas.

Cautions when you interpret the views

• Ranks are relative. Every view shows how an area compares with the rest of Victoria, not whether its absolute level is safe.
• The top-20% cut-off is a choice. The worked file holds it in one cell (Lists tab, cell I2), so you can test 0.75 or 0.9.
• Some indicators overlap: renting and social housing, and low English proficiency and new arrivals. Pick the indicators that fit each policy question rather than always using all 18.
• Census-based shares for very small populations are unstable, because ABS randomly adjusts small cells. Show population next to every SA2.
• Star rating and future heat are LGA values. Never present them as differences between SA2s within an LGA.

What is in the worked Excel file