How to geocode customer addresses and show them on an Excel bubble chart?

If your data contains addresses (or just part of it like city or post code) you can try to show the data on the map. Although Excel Map does not support zoom in like Google Maps or any other Internet map do, it can give you an overview on where your addresses are (e.g. of the customers). But how to turn an address to a point on the map?

How to geocode addresses?

There are many websites that allow you to complete the so called “geocoding process”. What you actually need at the end is the Latitude and Longitude instead of your address. One of the tools that works fine on the Internet up to 180 addresses is the website: https://www.doogal.co.uk/BatchGeocoding.php

It allows you to copy & paste the address and get the GPS values from the text section. The example below is based on Euro 2016 stadiums in France.

When pasted into Excel depending on your local settings you might need to replace the dots with the comma.

You need also to prepare data in the order: first Longitude, then Latitude.

How to show locations on a bubble chart in Excel Map?

Once you have the data you need to put them somewhere into Excel Map. In the Data sheet you have two dedicated columns that allow you to paste Longitude and Latitude values: V and W.

You can paste your data below the administrative area table, add a label value in the T column and a numerical value in the H column, which is Bubble size value.

Once you turn on the the bubble chart on the map, you will see your addresses.

The column T is responsible for the tooltip and label – when you click the bubble or turn on bubble labels, you will see your point’s details.

Watch the video tutorial to learn how to use the Excel Map

← All tutorials