Working with Map Data

Part of a Series of Tutorials on using Google Sheets

Last updated: October 12, 2017

What are Map Data?

Sometimes data are related to locations and it can be useful to visualize them on a map. In online infographic canvas tools and other map-making tools, there are a variety of map templates for the countries of the world, continents, US States, and even counties in a US state. To use these templates, you have to match up your data to the preset names. This means that you should check what the preset states/countries/continents names are, and make sure you have the corresponding data for that option. Otherwise, it won’t show up on the map.

Getting a Sample Map Dataset

The following example shows how to do this with a map of the US with state-based data from the US Census. From the US Census Bureau, we can find a dataset on how much money is spent on public education per state for the latest year of collected data. These data can be found at: http://www.census.gov/govs/school/

Download the first Excel file, the State-level tables, which is the first link under Downloadable Data.

Importing into Google Sheets

In Google Drive in your web browser, create a new Google Sheet file, going to New -> Google Sheets.

Once there, go to File ->Import… and then upload the file. (You can drag the file onto the browser window if you are using Google Chrome.) Google Sheets may ask whether you’d like to replace the spreadsheet or create a new one. If it does ask and you are starting with a blank spreadsheet, choose the option to replace the spreadsheet. If your spreadsheet already has data in it, choose to create a new one.

Reading the Data

Keep in mind that each data set, especially those found on the Web, has a different structure and different sources. This particular data set reports on each state’s education spending and presents the data in various ways. This is reported data, and likely took many hours to collect and analyze from local governments. There’s a lot to take in.

In your imported spreadsheet, the first worksheet is a Table of Contents that gives an overview, listing what sort of data you can find on the other numbered sheets. The first item listed is a summary of revenue and spending per state. This information is not particularly useful, since states with more students will likely spend more than states with fewer students. On the bottom of the list, the 20th worksheet, however, includes Per Pupil Current Spending (PPCS), so it will give us a better idea of how much states spend, taking into account the size of the state’s student population.

Go to this worksheet, by clicking on the worksheet tab number 20 in the bottom.

This worksheet provides Per Pupil Current Spending (PPCS) for each year from 2009 to 2014 and Percent Change from the previous year. Let’s focus on 2014, the most recent year in this dataset.
Reorganizing the Data for a Map

Notice the how the data are displayed in this sheet. What we want is a table with two columns, one with the state name and one with the corresponding value. Note how the current table is different from that. Currently, column A lists state names and column C shows the data for per pupil spending. Column B is empty. The states are grouped into sets of 5, with an empty row after every 5 states. In order to reorganize these data into the format we need for online infographic canvas tools, we’re going to copy just the data from columns A through C and rows from Alabama to Wyoming, paste it in a new sheet, delete the empty columns, and clean it up a bit.

Copy the cells that contain the state names and the PPCS data in Columns A to C, starting with Alabama, cell A11, going all the way down to Wyoming.

Create a new sheet by clicking on the plus button on the worksheet bar. It will automatically open to the new sheet. Double-click on the name of the new sheet on the bottom, and rename it “PPCS 2014”.

Paste the data into your new sheet. Delete the empty rows in between each set of 5 states. (To delete, right click on the row number and select Delete row from the pop-up menu.) There are 51 rows left. District of Columbia is included, but it’s not an available option on most infographic canvas tools’ US States map, so you may need to delete it (row 9). Your data should now have just the states in Column A and the PPCS, the per pupil current spending, in Column C. Delete Column B since there’s nothing there.
The extra dots following each state name need to be removed so that an infographic canvas tool can find the exact match for the state name on its map. First select Column A, then go to Edit->Find and Replace and enter “.” in the Find section, and nothing in the Replace section. Then click the button for Replace all. That will take out the dots.

Add a top row with header labels, “States” in cell A1, and “2014 PP Current Spending” in B1. Your data should now look like this:

Making the Map

Let’s first test out making a map in Google Sheets. Select the two columns of data, and go to Insert->Chart. Google Sheets displays the data as a bar chart by default. Click on the three dots in the top right corner and go to Edit Chart.

This next part may look different depending on Google Sheets updates, but for the most part, the settings are organized the same way. In the Data tab, under “Chart Type” and then under “Map,” select the option for “Geo Chart.” This will turn the bar graph into a world map. To change it into a US Map, go to the “Customize” tab, and under “Geo” -> “Region,” select “United States.” There are also options for setting the colors for minimum value, median value, and max value. We went with a gradient of light to dark green.

Currently, there are no options in Google Sheets for adding any titles or a legend to the map, so the final product looks like this:

Most infographic canvas tools do allow for chart titles and legends on the map, as you can see below.
Making the Map Using Other Tools

You can copy and paste the map made in Google Sheets to your infographic, or re-make it in an infographic canvas tool so that it can better match your design aesthetics. Go to your infographic or map-making tool of choice. Find the option for a map of the US with states. Go to the edit data option for the map. Go back to Google Sheets, copy the State and 2014 PPCS data and paste it into the data table in the infographic canvas tool. You may have the option to edit the colors for minimum value, median value, and maximum value.

Give the map a title based on the dataset. “US Public School Spending per Student 2014”. If you can, add a legend to give the viewer a sense of scale in color. Here’s an example from Venngage:

Reflecting on the Data

Now that you’ve completed this walkthrough, here are some key points to take away:

Education spending is highest in New York State. It would be interesting to see what the spending is in New York City compared to the rest of the state.
It’s hard to see which state spends the least.
Education spending in Alaska looks relatively high. But that may be due to a largely rural population or high cost of living.