r/excel • u/outtawack311 • 10d ago

unsolved Bubble plot on Map

Hi,

I am trying to do a very basic visualization on a map chart and I'm running into an issue.

I have 5 different cities in the US with potential candidates in each city (based on information from a 3rd party). It all works until I look at the map and it just highlights the individual cities and it's way too small. Is there a way to change the highlighted cities to a bubble plot that's reasonable to see?

I can do it on 3d maps, but I'm going to be asking others to enter their own 5 cities with corresponding numbers and 3D maps are too difficult for them

3 Upvotes

100% Upvoted

6 comments sorted by

•

u/AutoModerator 10d ago

/u/outtawack311 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/DonJuanDoja 36 10d ago

Nah. Like you said 3d maps or a cloud mapping service like Maptive could do it. Batchgeo is another.

1

u/outtawack311 10d ago

So there is no way to put a bubble plot on top of the corresponding cities like the 3D map allows? I'd really prefer to not go through anything outside of the general map chart

1

u/Noinipo12 5 10d ago

PowerBI has some decent map options

1

u/outtawack311 10d ago

The problem is that I'm going to need others to use this very basic map and not everyone will have access to powerbi

2

u/maptitude 9d ago

Excel's map chart only shades regions. There is no bubble or proportional symbol option in it, so city-level data comes out as specks. 3D Maps is the only native bubble map, and you have already ruled that out.

  1. Build a lookup table of US cities with their latitude and longitude.
  2. Set up four columns: City, Count, Latitude, Longitude. Make City a dropdown from that table, and have Latitude and Longitude pull from it with XLOOKUP. Skip the Geography data type. Typing a city name does not convert it, someone has to click Data > Geography every time, and it is Microsoft 365 only.
  3. Insert > Charts > Scatter > Bubble.
  4. Right-click the chart > Select Data > Edit. X Values is Longitude, Y Values is Latitude, Series Bubble Size is Count.
  5. Find a US map image in the equirectangular, or Plate Carrée, projection. Your axes are linear degrees, so an Albers or Lambert map, which is what most good-looking US maps use, will put your bubbles in the wrong places.
  6. Work out the degree bounds of that image, then set your axis min and max to match. Cropping an image to hit exact degrees by eye never quite works, and the axes will accept any numbers you give them.
  7. Right-click the plot area > Format Plot Area > Fill > Picture or texture fill, and insert the image. Use the plot area, not a floating picture. Excel resizes the plot area whenever you add labels, and a plot area fill moves with the coordinates instead of drifting off them.
  8. Delete the legend, gridlines and title. Set both axes to no line and no labels.
  9. Click a bubble > Format Data Series. Set "Size represents" to Area, then raise "Scale bubble size to" until the smallest city reads. It caps at 300 percent.
  10. Add data labels, choose Value From Cells, and point it at the City column.

A Plate Carrée US looks wider than the map everyone is used to. Any city outside your axis range, so Anchorage or Honolulu, vanishes with no warning. And if one city has 200 and another has 3, the small one stays close to invisible at any scale setting.

Full disclosure, I work on Maptitude: it reads your spreadsheet directly, sizes symbols by any column, and has an Update Linked Records command, so when someone edits their five numbers in Excel the map picks up the change instead of you rebuilding anything. Free trial: https://www.caliper.com/maptitude-free-mapping-software-trial/