How to plot postcode data from an excel spreadsheet on a map of Melbourne

Question

How to plot postcode data from an excel spreadsheet on a map of the SEMPHN catchment in Melbourne. Post code numbers, suburb names and the count number. I would like to plot this per program as well as per postcode location and per 'cluster' area provider.

Answer 1

Hi 

To plot your postcode data on a map of the SEMPHN catchment in Melbourne, you can use tools such as Power BI, Excel 3D Maps, or a GIS platform such as QGIS. I would generally recommend Power BI as it provides interactive mapping and filtering capabilities with relatively simple setup. 

To support this type of analysis, your Excel data should include columns similar to: 

• Postcode 
• Suburb Name 
• Count (e.g., participant, referral, or service count) 
• Program 
• Provider Cluster Area 

Using this data, you can create maps that show: 

• Service/activity counts by postcode location 
• Distribution of individual programs across the SEMPHN catchment 
• Activity by provider cluster area 
• Heat maps highlighting areas with higher service volumes 

In Power BI, the postcode field can be used to geographically map the data, while slicers can be added to filter by Program and Provider Cluster. If you require the map to align specifically with the SEMPHN catchment boundary, postcode boundary data and the SEMPHN catchment boundary can be incorporated to provide more accurate geographic reporting. 

Below is a step-by-step approach. 

1/ Prepare the Excel Data 

Ensure your spreadsheet contains the following columns: 

• Program 
• Postcode 
• Suburb 
• Count 
• Provider Cluster 

Example: 

Important: Format the Postcode column as Text rather than Number to avoid geocoding issues. 

2/ Load the Data into Power BI 

1. Open Power BI Desktop. 
2. Select Get Data > Excel. 
3. Choose your workbook and load the data. 
4. Verify that: 
   • Postcode = Text 
   • Count = Whole Number 

3/ Create a Postcode Map 

1. Add a Map visual. 
2. Drag: 
   • Postcode > Location 
   • Count > Bubble Size 
   • Program > Legend (optional) 

Power BI will automatically geocode Australian postcodes and display them on the map. 

4/ Add Program Filters 

1. Insert a Slicer visual. 
2. Add the Program field. 

This allows users to filter the map by individual programs. 

5/ Add Provider Cluster Filters 

1. Insert another Slicer. 
2. Add the Provider Cluster field. 

Users can then view data for a specific cluster area. 

6/ Display Activity by Postcode 

Create additional visuals such as: 

• Table: 
  • Postcode 
  • Suburb 
  • Count 
• Bar Chart: 
  • Axis = Postcode 
  • Values = Count 

This provides a detailed breakdown alongside the map. 

7/ Map Data Within the SEMPHN Catchment 

For a more accurate catchment view: 

1. Obtain: 
   • SEMPHN catchment boundary file 
   • Victorian/Australian postcode boundary file 
2. Import these boundaries into Power BI using: 
   • Shape Map visual, or 
   • ArcGIS for Power BI 
3. Join the postcode boundaries to your Excel data using the Postcode field. 

This allows postcode areas to be coloured based on the Count values rather than displaying only point locations.  8/ Create Separate Views 

You may wish to create: 

Page 1 – Total Activity by Postcode 

• Map coloured by count. 

Page 2 – Program Activity 

• Filterable by Program. 

Page 3 – Provider Cluster Activity 

• Summary by Cluster. 

Page 4 – Heat Map 

• Identifies areas with the highest activity. 

For more information, please refer to the following link: 

• Map visualizations in Power BI 
• Tips and tricks for Power BI map visualizations 
• Create ArcGIS maps in Power BI 
• Get started with 3D Maps 
• Get and prep your data for 3D Maps 

I hope this information is useful. Additionally, as other users have raised similar concerns and may not be aware of where to share their feedback, I hope this response helps clarify the situation and suggests a potential next step. Highlighting this information can make it more visible to others in the community who may be facing the same issue, making it easier for them to find guidance and contribute their feedback as well. 

Thank you again for your time and for raising this important usability concern. If you have any further questions or need additional assistance, please don’t hesitate to reach out. 

I look forward to your thoughts on this. 

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Excel gives you two built-in mapping tools, and which one you pick depends on whether you care more about the bubbles-plus-shading look or the per-program/per-cluster filtering.

Prep it the same either way: get your data into a tidy table (Postcode formatted as Text, plus Suburb, Program, Cluster, Count), and turn it into a regular Excel Table with Ctrl+T. Both tools geocode via Bing, so it helps to add a column like Postcode & ", Australia" or a State column so it resolves Australian postcodes correctly.

Tool A : 3D Maps (Insert → 3D Map). This is the only Excel tool that does shaded regions and proportional bubbles and filtering together. Steps: select your table → Insert → 3D Map → Open 3D Maps. Set Location to your Postcode field (type = Postal Code, Country = Australia). Then add two layers: Layer 1 as a Region visualization with Count driving color (the shaded postcodes), and Layer 2 as a Bubble visualization with Count driving size (the bubbles on top). Use the Filters pane to add Program and Cluster as filters so you can show one program or one cluster at a time, or set Program as the Category to color everything by program at once. You share it by screenshot or by exporting a short video/"tour"; it lives in the Excel app.

Tool B : Filled Map chart + PivotTable + Slicers, simplest for the filtering. Build a PivotTable (Rows = Postcode/Suburb, Values = Sum of Count) and insert Slicers for Program and Cluster. Those slicers are your per-program/per-cluster toggles. Then Insert → Maps → Filled Map to shade postcodes by count. Note: a Filled Map chart can't read a PivotTable directly, so you drive it with a small helper table using SUMIFS that references the slicer-selected values, and point the map at that helper range. This tool gives you clean shading and real slicer filtering, but shading only, no bubbles.

Excel is great for a quick internal look, but it geocodes postcodes through Bing and accuracy for AU postcodes varies. It can't clip to the actual SEMPHN boundary or combine shading and bubbles cleanly. If this needs to be accurate, catchment-bounded, and shareable as a single file, 3D Maps gets you closest inside Excel, but Power BI will look considerably better.