The Best Way to Make A Google Map Using Google Spreadsheet Data

If you’re like us, you keep a lot of data in Google Spreadsheets. With many of the features of Excel and the ability to easily collaborate, it’s no wonder this has become the go-to for many business and personal use cases. At BatchGeo, we’ve noticed spreadsheets are a popular place to store location data, such as customer locations.

That’s why we built a way to quickly turn spreadsheets into maps. Below we’ll show you the best way to convert your Google Spreadsheets data into beautiful, interactive maps. In fact, this works for any spreadsheet application you use, with nothing to install or download.

1. Identify Your Location Data

Before you can make a map from your data, you’ll need to figure out if you have any geographic terms in your spreadsheet. There are many different types of location data, but it works best if it’s structured. You’ll see what we mean in the examples below.

Your location data will then be converted into geographic coordinates through a process called geocoding. The result is an estimate of the actual coordinates needed to plot the place on a map. Depending on the type of location data, the point can be highly accurate.

Addresses

Addresses are a really useful type of location data because they are common, human-readable, and specific. In fact, there’s probably an address to describe the place you live!

  • Full address is a single field with an entire address, including city, state/province, and postal code.
  • Segmented address is multiple fields for each piece of the address.
  • Partial address may be missing a street number, a city, or other information. Most geocoders will make educated guesses for partial addresses.

If you have customer data, you may very likely have an address for them! Mapping customers can help you make important business decisions and analyze where you’ve been successful with your efforts. These maps can even help you plan specific customer visits, because an address gives you an exact location.

Regions

For many analyses, a general location is enough information to get an interesting map. If you’re looking at a global scale, having an exact address is not that different from knowing a location’s city, for example. The marker will appear to be in roughly the same place. Regional data is often easier to find than specific address-level data, giving you more opportunities to map your data.

Some examples of regions you can convert to geographic coordinates:

  • Countries
  • States or provinces
  • Counties, parishes, or other localities
  • Postal codes

Regions are larger than places described by an address, so the “point” provided is less specific. Typically, geocoders use centroids, such as the geographic mid-point within a city’s boundaries.

One advantage of regions is that you can often glean this from other data. For example, some services can tell you a city of the person behind an email address. You can also geolocate by IP address for visitors of a website, for example.

Coordinates

The last type of geographic data is the simplest and most specific. It is not particularly human-readable, however. Raw coordinates are referred to as latitude and longitude pairs, because there are two numbers that plot a point on a two dimensional plane representation of earth.

For example: 44.4604788, -110.8281375

You could use those two numbers to find Old Faithful, one of the Geysers of Yellowstone National Park.

Often coordinates are listed in “latitude, longitude” format. However, annoyingly, sometimes they’re written in the reverse! For this reason, it’s best to include these as separate, named, fields. In other words, if you have coordinate data, your spreadsheet should include a latitude column and a separate longitude column.

2. Copy and Paste From Your Spreadsheet

Now you’ve discovered the location data within your Excel, Numbers, Google Sheets, or other spreadsheet! You’re ready to convert your spreadsheet into a map—that’s why you’re here, right?

To create a basic map, all you need is your location data. That can be as simple as a list of addresses, like this:

  • Hotel Address
  • 3570 Las Vegas Boulevard South Las Vegas, NV
  • 2000 Las Vegas Boulevard South Las Vegas, NV
  • 3400 Las Vegas Boulevard South Las Vegas, NV
  • 3799 Las Vegas Boulevard South Las Vegas, NV
  • 3600 Las Vegas Boulevard South Las Vegas, NV

If that was a column in your spreadsheet, you could simply copy it and paste into the box at the top of our mapping tool. You’d end up with a map like this:

View A Few Las Vegas Hotels in a full screen map

While that’s pretty cool, you may find yourself wanting to know more details about each hotel. For example, what’s the name? And that data may very well be in your spreadsheet! Why not make it available? Let’s say your spreadsheet looks something like this:

Name Hotel Address Rooms
Caesar’s Palace 3570 Las Vegas Boulevard South Las Vegas, NV 3,348
Stratosphere 2000 Las Vegas Boulevard South Las Vegas, NV 2,427
The Mirage 3400 Las Vegas Boulevard South Las Vegas, NV 3,044
MGM Grand 3799 Las Vegas Boulevard South Las Vegas, NV 5,124
Bellagio 3600 Las Vegas Boulevard South Las Vegas, NV 3,950


Highlight, copy, and paste that into the map builder and you’ll end up with a similar map as before, but with more data available!

View A Few Vegas Hotels with Names/Rooms in a full screen map

Note that the color of the icon is based on the number of rooms. And when you click a marker, you can see the name, as well as the room data. If you had other columns in your spreadsheet, they’d show up here, too. Bringing your additional data into your map opens up all sorts of opportunities to explore the meaning behind your data.

For a larger example, see this map of the world’s largest hotels.

3. Tweak Fields and Settings

You can create great maps with our three step "copy, paste, click” process. Yet, there is additional power within your map waiting to be released. You can provide filtering and grouping options, show clusters of data, and aggregate fields within your dataset.

Rather than clicking that “Map Now” button after pasting in your data, click Validate & Set Options. You’ll see a handful of additional options:

Declaring a region helps the geocoder make intelligent decisions (if it’s an international map, choose that option). The location, city, state, and zip fields help you correct BatchGeo if we’ve made an incorrect assumption with your data. This is especially useful when you have multiple location fields.

Finally, the “group by” option lets you decide which of your fields you’ll show as the filtering option when the map is loaded. For my Las Vegas map, I chose “Rooms,” which is why the markers were different colors in the second map of the previous section.

Again, you have the choice to “Make Map” or uncover even more settings by clicking the “Advanced Options” button, which will show another menu like this:

Here you can change the colors and style of markers, alter the underlying map style, and more. While BatchGeo Pro options are shown above, even free versions have a lot of ways to tweak your map.

Try changing some options and see how your map looks. Go back to the edit screen (a link is sent to the email address you used to create the map) and try another option. Make your map fit your individual needs and help uncover the story within your data.

If you’re ready to create some fantastic maps, be sure to see our 5 Steps to Becoming a BatchGeo Power User article, with tips for re-ordering data and including images.

Make a Disaster Relief Community Resource Map

Natural disasters, such as hurricanes and fires, make a devastating impact on a community. Often, despite being surrounded by ruins, a silver lining comes forth in a community that supports each other. Locals and relief workers set up shelters, provide food, donate clothing, and more. Word of mouth becomes incredibly important for getting the word out about what is available where. A map can be an invaluable tool to amplify the community’s efforts.

You can quickly make a map of community resources, even if there isn’t currently a disaster. In this post, we walk you through an easy way to get these unstructured locations into something usable from any web browser, embedded on your website, or on mobile.

Gather and Store Locations

The list of resources is an important part of your community effort. This is the information you want to pass along to those in need. Make sure you store it in a way that makes it easy to keep updated, such as a spreadsheet. Excel or Google Sheets are both easy ways to allow others to contribute to data collection and curation. Plus, it helps you provide a little structure to the location data you’re collecting.

A basic community resource spreadsheet will include:

  • A name of the place, something that helps others identify it
  • The address, intersection, or other geographic description
  • A short overview of what resources are available at this location — this is optional, but helpful for those browsing available resources

Your spreadsheet can include those three fields as column headers. Then each subsequent row is a single resource. For example, consider this list:

Name Address Description
Redwood Empire Food Bank 3990 Brickway Blvd., Santa Rosa, CA 95403 Accepts donations and provides free emergency food boxes
The Salvation Army 93 Stony Circle, Santa Rosa, CA 95401 Clothing and other necessities
Kaiser Permanente 401 Bicentennial Way Santa Rosa, CA 95403 Emergency and inpatient services
Sutter Health 30 Mark West Springs Rd, Santa Rosa, CA 95403 Access medical services via Mark West Springs Rd off of Hwy 101


Let’s say you have a list like this in a spreadsheet. You can highlight everything, including the headers, then copy and paste it into this map making tool to create a map like this:

View Disaster Relief Community Resource Map in a full screen map

As you find additional resources, or need to update existing ones, update your spreadsheet as your “source of truth.” Then simply copy-paste into the BatchGeo map to create your community resource map.

Help People Filter By Category

You may have noticed that the example resources are a mix of food, clothing, and medical services. Once you get more than a handful of locations, it can feel like a hodgepodge of places. You can categorize the resources available to make it easier to make it easier for users to see just what they’re interested in.

Go back to your spreadsheet and add another column for “category.” Here’s how you might expand the previous sample resources:

Name Address Description Category
Redwood Empire Food Bank 3990 Brickway Blvd., Santa Rosa, CA 95403 Accepts donations and provides free emergency food boxes Donations
The Salvation Army 93 Stony Circle, Santa Rosa, CA 95401 Clothing and other necessities Donations
Kaiser Permanente 401 Bicentennial Way Santa Rosa, CA 95403 Emergency and inpatient services Medical
Sutter Health 30 Mark West Springs Rd, Santa Rosa, CA 95403 Access medical services via Mark West Springs Rd off of Hwy 101 Medical


When you use the data in this updated spreadsheet to create a map, you’ll get something different:

View Disaster Relief Map with Categories in a full screen map

First, note the different colors of the icons. The donation sites are now visually separate from the medical facilities. Further, at the bottom of the map, you can choose only to see one or the other. Now you’re helping someone find the exact resource they need.

Of course, with four locations and two categories, it’s only minimally helpful. This magnifies when there are dozens or hundreds of resources. You can also include multiple types of categories. Using the BatchGeo grouping feature, any data in your spreadsheet becomes a potential filter field.

Promote Your Community Resource

Your map of community resources is now a resource itself! Just as word of mouth is the best way to spread the ways a community is ready to help each other, use those same channels to let everyone know about your map.

Share on Social Media

Use Twitter, Facebook, and neighborhood message boards to get the word out! You can link directly to BatchGeo maps and know they’ll work on any device. Our maps are mobile-optimized while still looking great on larger screens, using the same link.

Embed in Other Websites

If you already maintain a community resource on the web, add maps within your existing site. No need to link off to a different site for the map, bring the map into your site.

When you create your map, you’ll receive an email with a special code to embed your map. If you have access to add HTML to your site, you’ll be able to use this to include the map seamlessly within your other content.

Most Popular Official Languages in the World

The ability to communicate with each other is what separates human beings from animals. There are between 5,000 and 7,000 languages spoken throughout the world. The fact that there are so many different languages and yet we are still able to communicate is an amazing thing, and here at BatchGeo, we thought it would be pretty amazing to compile all of the most popular official languages throughout the world into one map.

View Official Languages by Country in a full screen map

As you can see on the map above, the most popular languages are available for filtering using the BatchGeo grouping feature.

There are five languages that are official in 10 or more countries: English (64), French (32), Arabic (27), Spanish (23), and Portuguese (10).

In addition to being the most popular, English is the only language officially designated on all six populated continents. Most of the 64 countries in which English is an official language are former territories of the British Empire.

Due to France’s position throughout history, as well as a history of colonialism, French is spoken throughout the world. Its designation as an official language, however, is situated mostly in Europe and Africa. The exceptions: Canada, Haiti, and Vanuatu.

Arabic, while nearly as common as French, is even more focused in its region of popularity. The Middle East and Northern Africa are home to all 27 Arabic-speaking countries. Similarly, many of those countries have some of the highest percentages of Muslim practitioners.

Spanish doesn’t have the European adoption of French, with Spain as the only country on that continent to consider it an official language. Still, Spanish manages to land in the fourth spot due to its incredible popularity in Latin America. Its only other locations are two countries in Africa (Western Sahara, Equatorial Guinea) and as the only language spoken officially on the South Pacific territory of Easter Island.

Finally, Portuguese may be fifth in terms of popularity, but its 10 countries are spread far and wide. The best known are likely Portugal and Brazil. Four others are on mainland Africa and two are African island nations. The other two? Macau (along with Mandarin and Cantonese) and East Timor, near Indonesia.

Our Monolingual World

Half the countries speak only one language, at least as an official language. On the monolingual list, English leads the way again, but not by nearly as much. There are 24 countries with only English as an official language, only 38% of all languages that have adopted English. By comparison, Spanish is the only official language of 19 of the 23 countries (83%) that “hablan español.”

Danish and Armenian have an interesting distinction not shared with any other languages. Danish is the official language in Greenland, Faroe Islands, and Denmark. Armenian is official in Nagorno-Karabakh and Armenia. And in all five of these countries, there is only a single language. In other words, 100% of countries that speak Danish or Armenian are monolingual.

There are another 26 monolingual countries that speak a language that is only official in that country. Most of them will sound familiar because they’re named after the one country that speaks it. For example Hungarian, Bulgarian, Latvian, and Lithuanian are probably not surprises. Some interesting ones from this group include Manx Gaelic (only official language of the Isle of Mann) and Gàidhlig (Scotland)

Countries with the Most Official Languages

Despite the large numbers of monolingual countries, a few go the other way. Seven countries have 10 or more official languages. All of these countries are in Africa, which has a history of both tribal dialects and multiple colonial occupants. Together these factors lead to many official languages.

The Republic of Benin, perhaps best known as the birthplace of Voodoo, has a population of only around 11 million. Yet, there are 21 official languages: Aja-Gbe, Anii, Bariba, Biali, Boko, Dendi, Fon-Gbe, Foodo, French, Fula, Gen-Gbe, Lukpa, Mbelime, Nateni, Tammari, Waama, Waci-Gbe, Xwela-Gbe, Yobe, Yom, and Yoruba. While three of Benin’s official languages can be found in other countries (French, Fula, and Yoruba) the rest of Benin’s 17 official languages are found exclusively in the country.

The second country with 10 or more official languages is Senegal. This far west country in Africa has 15 official languages for a population of around 15.5 million people. While French, Fula, Soninke, and Wolof are all languages found elsewhere, 11 of the 15 official languages are unique to Senegal.

Zimbabwe is the third country with 10 or more official languages. There are 14 official languages are spoken in Zimbabwe, which has a population of about 16 million people. Seven of those 14 official languages are exclusive to Zimbabwe, with English, Ndebele, Tswana, Chichewa, Venda, and Xhosa being spoken in other countries but the rest can only be found in Zimbabwe. The southernmost country of Africa, South Africa, right below Zimbabwe, has 12 official languages. Of those 12, four are unique to the country.

Mali, like South Africa, also has 12 official languages, one of which is Arabic which is only spoken in the Northern half of countries within Africa aside from the islands of Comoros in the Southern part. Niger, bordering Mali to the east, has 11 official languages, five of which are exclusively found in Niger. Finally, Ghana has exactly 10 official languages. Aside from English, all of the languages in Ghana are unique to this country.

Whether you live in a country that has one official language or 21 official languages, the map above provides some insights into the countries around the world and the languages spoken there. No matter if you are most familiar with a language that is official in 10 or more countries, or you are more familiar with a language utterly unique to your own country, we’ll always have some form of language to communicate with.