• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bubble Charts on top of a map

H

Homsie

New Member
I'm currently trying to create a bubble chart for a dashboard based on volume numbers from several locations across the UK. Once the bubble is complete can I overlay the data points onto a map of the UK within excel without using third party software.


If it is possible to create this I would be grateful for direction.
 
Homsie


Firstly, Welcome to the Chandoo.org Forums


You can do what you want

You need a few things

- A Map which uses a projection that isn't too distorted over the area your mapping, but seeing as thge UK is relatively small (Geographically speaking), most maps are probably close enough.

- Insert the chart on your worksheet, scale to size

- Setup your chart and set Min and Max for the two axis to known Lats/Longs or N/E depening on your data and map

- Set the Chart and Plot area fill colors to None

- Move and Scale the Chart to fit the map

- Select both the Chart and the Map and Group them
 
First of all thank you very much for the responses.


dan_I - I saw this acticle the other week and they mentioned tableau software so wondered if it is possible to do the same job in excel. From Hui comments it looks like it can and seems pretty straight forward.


I agree the acticle on clearlyandsimply.com is very impressive - so I'll take some ideas from that and use Hui's advise in Excel and see where I end up. Many thanks
 
Hi Everyone!


im new to Chandoo and somewhat lacking in my Excel skills compared to you guys by the sounds of it! :) i am trying to do the same as Homsie - plot relative volumes via bubble graph onto a geographical area. Again i want to use Excel and not 3rd party software. Above you state the type of map required and i think this is where i am going wrong! what format or type of map do you use?


any help appreciated!
 
Coco Chance


Firstly, Welcome to the Chandoo.org Forums


You need to use any map that suits your needs

It can use either an orthogonal grid or a spherical projection (Lats and Longs)

If using a spherical projection use limited areas and the grid is approximately orthogonal


You need to ensure that you have reference points or grids on the map so you can align the chart grid lines to them
 
Great - thanks for your help Hui.


I have another question... so i have my bubble chart and my map (with lat and long ready to go), how do i import the map in a format that the chart will recognise it? at the minuite Im using picture and they dont seem to be speaking the same language! or else its my language!!
 
Coco


You have 2 options


1. Set the Background of the plot area of the chart as the map

This requires that you know the outer edge co-ordinates of the map and set the scale to those to get it to the correct scale


2. Insert a Picture of the Map onto a worksheet

Then set the Chart Area and Plot Area of the Chart to have No Color

Then place the chart in front of the Map


Excel accepts most picture types
 
Hi,


Be sure to check this one as well if you want to use a Google API for longitude and latitude coordinates.


I use this as well to find coordinates and use a bubble chart to plot those on my map as other users suggested above!


Bram
 
You must log in or register to reply here.
Back
Top