You can show a pin for each of your locations, such as customer sites, offices, or event venues, by laying a scatter chart over a map image. The chart plots each location's longitude and latitude as a dot. Because the chart's axes are set to match the map's edges, every dot lands on the right spot, and the same data produces the same map every time you generate.
This works in PowerPoint and Google Slides templates with standard chart features. You need three things: a map image, the coordinates of that map's edges, and your locations as latitude and longitude.
Step 1: Choose a base map
Your base map must be in one of two projections. The projection decides how latitude turns into a position on the image, which decides what you put on the chart's vertical axis.
| Projection | Looks like | Vertical axis uses | Best when |
|---|---|---|---|
| Web Mercator | Google Maps, OpenStreetMap, and most online maps | Mercator Y, calculated from latitude with one formula | You want the familiar look and can add a calculated column to your data |
| Plain latitude/longitude (equirectangular) | A flat grid; areas far from the equator look wider and shorter than you're used to | Latitude, as is | Your coordinates come straight from a CRM or database and you can't add a calculated column |
Do not use a map in any other projection, such as the curved US maps that show Alaska and Hawaii as insets. Pins on those maps do not line up with a scatter chart.
What "the map's edges" means
The edges are four numbers that describe exactly what area the image covers:
- West: the longitude of the image's left edge
- East: the longitude of the image's right edge
- South: the latitude of the image's bottom edge
- North: the latitude of the image's top edge
For example, a map of the lower 48 US states might have edges of West -126, East -65, South 23, North 50. Longitudes west of Greenwich and latitudes south of the equator are negative.
The edges must describe the image exactly as it is placed on your slide. If you crop the image, its edges change. Get the edges after any cropping, or crop to edges you have already recorded.
Where to get a base map
Any of these sources works. Pick based on how exact you need the edges to be and how comfortable you are with the tool. Check each provider's attribution requirements: most require a credit line on the slide.
| Source | Cost | Projection | How you get the edges | Edge accuracy |
|---|---|---|---|---|
| Mapbox Static Images API | Free tier (50,000 images per month), then paid; requires a free account (pricing) | Web Mercator | You request the image by its edges (a bounding box) | Exact when the image proportions match the area |
| Google Maps screenshot | Free | Web Mercator | Right-click the map at the top-left and bottom-right corners of the area you capture (see below) | Approximate |
| OpenStreetMap image export | Free | Web Mercator | Share → Image, then Set custom dimensions to choose the area; read the corners the same way as Google Maps | Approximate |
| QGIS with Natural Earth data | Free | Plain latitude/longitude (set the project's coordinate system to EPSG:4326) |
You type the edges when you export the image (Exporting the map view) | Exact |
| Wikimedia Commons location maps | Free | Varies by map; use only maps described as equirectangular | Listed on each map's description page (for example, the USA location map) | Exact |
Reading the edges from a Google Maps or OpenStreetMap screenshot
- In Google Maps, turn off Globe view (Layers → More) so the map stays flat when you zoom out. Turn off any labels or layers you don't want on the slide.
- Frame the area you want and take your screenshot.
- Without moving the map, right-click exactly where the screenshot's top-left corner was. Google Maps shows the latitude and longitude at the top of the menu (Google's instructions). The first number is latitude (North), the second is longitude (West).
- Right-click exactly where the bottom-right corner was. That gives South and East.
Edges read this way are approximate, so check them with two known locations before you rely on the map (see Check the alignment). Google allows its maps in business presentations as long as the Google attribution stays visible on the slide (Google Maps attribution guidelines). OpenStreetMap requires the credit "© OpenStreetMap contributors" (OpenStreetMap copyright).
Step 2: Set up the scatter chart
The chart and the map are aligned when all three of these are true:
- The axis bounds equal the map's edges. Horizontal axis minimum = West, maximum = East. Vertical axis minimum and maximum = South and North (as Mercator Y for a Web Mercator map, or as latitude for a plain latitude/longitude map).
- The plot area covers the map exactly. The plot area is the region inside the chart's axes where dots are drawn. Its corners must sit exactly on the map image's corners.
- Nothing else takes up space. No visible axes, axis labels, gridlines, legend, title, or chart background.
If one of these is off, every pin shifts by the same amount, so the map can look plausible while every location is slightly wrong.
Calculating Mercator Y for the vertical axis bounds
For a Web Mercator map, convert the South and North edges with this spreadsheet formula, replacing latitude with the edge's latitude:
=DEGREES(LN(TAN(PI()/4 + RADIANS(latitude)/2)))
For the example edges above, South 23 becomes 23.6438 and North 50 becomes 57.9079. Use the same formula for your locations' data in Step 3.
PowerPoint
- In the PowerPoint desktop app, select Insert → Chart → X Y (Scatter) and choose the plain Scatter chart.
- In the chart's spreadsheet, put Longitude in column A and Latitude (or Mercator Y) in column B, with a header row. Enter a few example locations so you can see the dots while you format.
- Make the data range long enough for your largest list of locations. Select Chart Design → Select Data, select the series, and extend X values and Y values to cover enough rows, for example
=Sheet1!$A$2:$A$501and=Sheet1!$B$2:$B$501for up to 500 locations. Empty rows at the end are ignored. - Right-click the horizontal axis, select Format Axis, and set Bounds → Minimum to West and Maximum to East. Do the same on the vertical axis with South and North. Then select each axis and press Delete. The bounds you set still apply after the axis is hidden.
- Delete the gridlines, legend, and chart title.
- Format the dots as pins: select the series, open Format Data Series → Marker, and pick a shape, size, and color.
- Add the map. The most reliable way is to make the map the plot area's background, so the two cannot drift apart: right-click the plot area, select Format Plot Area → Fill → Picture or texture fill, and insert your map image. Then set Format Chart Area → Fill to No fill and Border to No line.
- Drag the plot area's handles out to the chart's edges, then resize the chart so its proportions match the map image's proportions. If they differ, the map stretches, but pins still land in the right place.
Google Slides
- In Google Sheets, put Longitude in column A and Latitude (or Mercator Y) in column B, with a header row and a few example locations. Insert a chart and set Chart type to Scatter chart. Set the Data range to cover your largest list, for example
A1:B501. - In Customize → Horizontal axis, set Min to West and Max to East. In Customize → Vertical axis, set Min and Max to South and North. Turn off axis labels on both axes.
- In Customize → Chart style, set Background color to None, set Chart border color to None, and check Maximize. Remove the gridlines, legend, and chart title.
- Copy the chart into your Slides template and keep it linked to the spreadsheet.
- Insert your map image on the slide and send it behind the chart (Arrange → Order → Send to back).
- Line up the map with the plot area. Google Sheets always leaves some space around the plot area, so you can't line up the map with the chart's outer edges. Instead, temporarily add two locations to your spreadsheet: one at West and North (top-left corner), one at East and South (bottom-right corner). Resize and move the map image until its top-left and bottom-right corners sit exactly under those two dots. Then delete the two temporary rows and refresh the chart.
Check the alignment
Before you use the map, add two or three locations you know well to your example data, ideally far apart (for example, your headquarters and a city near the opposite edge of the map). Each pin should land exactly on its city. If every pin is off in the same direction, one of your edges is wrong. If pins near one side of the map are right but pins near the other side are off, the plot area doesn't cover the map exactly.
Step 3: Populate the data
Create Dynamic Content that returns one row per location, with a header row and these two columns, in this order:
| Column | Web Mercator map | Plain latitude/longitude map |
|---|---|---|
| 1st | Longitude | Longitude |
| 2nd | Mercator Y (from latitude, using the formula above) | Latitude |
Then connect it to the chart using either method in Connecting Charts to Dynamic Content in Templates: tag the chart with a Chart-type Dynamic Content, or tag the top-left cell of the chart's spreadsheet with a Table-type Dynamic Content.
Make the chart's data range from Step 2 at least as long as the number of locations your Dynamic Content can return. If it's shorter, what happens depends on how you tagged the chart. If you tag the chart's spreadsheet, the extra locations are left off the chart without an error, in both PowerPoint and Google Slides. If you tag a Google Slides chart with Chart Dynamic Content, Matik shows an error. A PowerPoint chart tagged with Chart Dynamic Content resizes to fit your data automatically.
Getting latitude and longitude
Look up coordinates once, store them alongside each address in your system, and update them only when an address changes. That keeps every generated map identical and fast.
-
Salesforce: Salesforce can add latitude and longitude to Account billing and shipping addresses automatically, using its geocoding data integration rules (Salesforce: Automatically Get Geocodes for Addresses). Once a Salesforce admin activates the rule, query
BillingLatitudeandBillingLongitude(or the Shipping equivalents) in your Salesforce Dynamic Content. These fields don't appear on the Account page, but they are available to queries. - Gainsight and HubSpot: Neither stores latitude and longitude for companies by default. Add coordinates using one of the sources below, and store them in custom fields so Matik can read them.
Other sources for looking up coordinates:
| Source | Cost | Good for | Notes |
|---|---|---|---|
| US Census Geocoder | Free | US street addresses | Upload a file of up to 10,000 addresses |
| Geocodio | 2,500 free lookups per day, then paid | US and Canada addresses, and some other countries | Upload a spreadsheet; you can keep the results permanently |
| Excel Geography data type | Included with Microsoft 365 | Cities, states, countries, and postal codes | Not for street addresses. Check each match: cities that share a name can resolve to the wrong one. |
The standard Google Maps and Mapbox geocoding services limit how long you can store their results, so they don't suit a look-up-once list.
Limitations
- Pins are all one size and color per series. You can't size or color a pin by a value such as revenue or usage.
- Locations outside the map's edges are left off. For example, sites in Alaska or Hawaii don't appear on a map of the lower 48 states. Check your data if a pin seems to be missing.
- Nearby locations overlap. Sites in the same city, or in neighboring towns on a country-wide map, show as one pin.
- Labels are best kept off. City names next to pins quickly overlap on a busy map.
- Accuracy depends on your coordinates. The chart places pins exactly where the coordinates say. A city-level coordinate places the pin at the city's center, not the street address.
Comments
0 comments
Please sign in to leave a comment.