If you want to map addresses from Excel there are a variety of tools available. These tools speed up the process, saving valuable time and effort.
Whether the addresses are from personal or professional lists, these tools allow users to generate a map. Once created, users can share their map with others.
The map is interactive allowing viewers to click on markers. Once selected the markers can expand providing additional information about the locations.
There is a provision for grouping columns together. This allows viewers to choose which sets of data to display.
Raw location-based data such as addresses can become interactive and accessible maps. This post will offer examples of tools that can assist you in making an icon map from a list of addresses.
How to prepare the location data
Locations displayed on a map use geographic coordinates. This means that each address on the spreadsheet will need geographic coordinates.
This is achievable by using a process called geocoding. Geocoding takes a text-based description of a location, such as an address or the name of a place, and returns geographical coordinates.
They are set out as a latitude-longitude pair that identifies a location on the Earth’s surface.
The text input can represent an individual address or multiple addresses. These can be displayed in a table.
The output supplies users with geographic coordinates. These can form the basis for spatial analysis or mapping.
Addresses can be input in a variety of formats, such as house numbers, street names, and postal codes.
It is also possible to include points of interest or locations from a gazetteer. Users may add geographical locations such as stores, lakes, and bridges by name.
Having obtained the needed geographical coordinates it is now possible to map addresses from Excel.
Tools to map addresses from Excel
Below are a few of the tools available to help map addresses from Excel, and guidance on how to use them. Some of the tools include instructions for using a specific geocoder.
Import the data from a CSV file.
The first line of the CSV file must contain field names. Fields must be comma-separated, and lowercase (“title”, not “Title”).
Text fields that contain a comma must be surrounded with double quotes “…”. Before importing the CSV file create all fields in the ‘Database > Edit fields’ section.
Also, if importing the Location field, first choose the language of the addresses. Find this in ’Database > Edit fields > Location > Language’.
|MapSVG field type||How to import from CSV|
|Select, Radio||Import as “value” numbers or text “labels”. Whatever works best. Example: if there is an “area” select field with the following options in MapSVG: North:1 South:2 East:3 West:4 These would be correct CSV values area 1 2 East West|
Incorrect CSV values area South-West 64
|Checkbox||Import as 1/0 or true/false.|
Correct CSV values my_checkbox_field 1 0 true false
Incorrect CSV values my_checkbox_field yes no
|Location||Import as “lat,lng” coordinates or as an “address” string. If importing an address it gets converted to coordinates. If importing coordinates they get converted to an address. And then both values (address & coordinates) are stored in the _Location_ field.|
Please note that Google allows only 50 requests per second to the Geocoding API (which converts the address to coordinates). If importing many locations it can take quite a long time. Be patient and don’t reload the page. Also, Geocoding API has a limit of 2500 requests per day.
Correct CSV values location 45.1233, 56.9812 Main St. 1, New York, USA London Paris
Incorrect CSV values (coordinates are outside of the correct range -90,-180 .. 90,180) location 99.1211,199.2323
|Regions||Import as a comma-separated list of region IDs.|
Correct CSV values (on example of /geo-calibrated/usa.svg file) regions US-TX, US-AL, US-NY US-OR
Incorrect CSV values regions Texas, Oregon US-TX US-AL US-NY
Log in to your Google account. Go to Google My Maps.
In the welcome pop-up, select ”Create a new map”.
Click the text “Untitled map” to edit the map title and description. It is best if the title relates to the data, such as “vacation” or “clients”.
In the menu select ”Import”.
Select the CSV from your desktop. This will be the first data layer to upload.
Tip: Users can also import an XLSX file or a Google Sheet. It’s possible to upload a table containing up to 2,000 rows (see supported data formats and limits here).
After uploading the data, users must select the column(s) with location information. This places the data correctly on the map (e.g. columns with latitude and longitude information).
For this example, select the Lat and Long columns, and hit ”Continue”. Hover the mouse over the question marks to see sample data from that column.
Tip: If there is no latitude and longitude information, it is possible to use addresses in the columns instead.
Now pick the column to use to title the markers. For this example, select the Date column and hit Finish.
Users should now see their data as a layer in the menu, and their points plotted on the map. To change the name of this layer in the menu, select the text of the layer name (the default will be the file name).
Turn addresses into coordinates
Note: Proceed to the next step if you already have a dataset with coordinates.
A quick and simple way to get coordinates for addresses is to use the LocalFocus Geocoder.
To begin, copy-paste the relevant cells with addresses in the text field. Excluding any column headers, users can do this with a whole column of data.
Click “Add to geocoder”. Now check the results.
If any addresses return as failures, check the language settings.
Does Excel or Google Drive use a comma or a point as a separator? Use the same settings for the geocoder.
Now the results are ready to transfer to the original table.
To do that, copy the results from the geocoder and paste the addresses next to the original data. Leave the first row empty.
In the first row, leave space for the column headers such as address, latitude, and longitude.
Start a map project
Now that you have a table with coordinates, you are ready to put the data on a map.
The next step is to add the data. An empty spreadsheet will appear.
Paste the new table containing the coordinates into the empty spreadsheet. The addresses will automatically appear on the map.
If no mapped points appear, check that the latitude and longitude columns are referenced correctly.
Click the “Create your first map” button. Name the map, click “Continue” and you’re ready to start.
To map addresses from Excel, upload the Excel file into the platform. This will generate an interactive, customizable Google Map within seconds.
Select the columns on the spreadsheet that contain location data. Most of these are automatically matched based on the information entries.
Or choose one of these alternative methods to create a map:
- Use the API to connect your data directly to the map for real-time updates.
- Connect your Google Spreadsheet data to the platform. Changes made on the Google Spreadsheet will automatically be reflected on the map.
- Start with a blank map and add locations manually one at a time.
With the initial map created,users can customize their map by using the tools provided. Maps can be shared, saved, or embedded in a website or blog post.
Users must first install QGIS, an open-source GIS, and MMQGIS. The MMQGIS plugin adds geocoding capabilities to QGIS.
Feeding it the list of addresses, will output a list of geographic coordinates.
Geocoding requires that the data is in CSV (comma-separated values). A CSV file is a spreadsheet without all the formatting and colors found in Excel.
On the spreadsheet, separate addresses into columns for “address”, “city”, “state” and “country”. Addresses in a single column should be separated into individual fields before geocoding.
Address: 630 Old Country Road
City: Garden City
State (or equivalent outside US): NY
If using your own data, you can convert the spreadsheet to a CSV file in Excel or any other spreadsheet program.
To map addresses from Excel in this way, open the spreadsheet and select “Save As”. Then choose “CSV (Comma delimited)” from the “Save as type” option.
With the data in the correct format, it’s now possible to geocode it in QGIS. In the menu bar go to MMQGIS → Geocode → Geocode CSV with Google / OpenStreetMap.
Click ”Browse” and select your CSV.
The geocoder needs to know which columns represent the individual address components. MMQGIS will do it’s best to detect the appropriate column names (and its best is often very good).
In some instances the column names may be obscure or written in a different language. Users can click on the dropdown menu and choose the correct column in their CSV.
There is also a choice of web services to perform the geocoding. Users can opt for Google or OpenStreetMap/Nominatim.
Ensure that the Output Shapefile path and the Not Found Output List path are valid. It is also important to have permission to write to that location.
To change the path, click on the “Browse” button, and choose the preferred location.
Geocoded results are stored as a Shapefile which is a collection of at least four files (.shp, .shx, .dbf, .prj).
The Shapefile is the de facto standard for storing GIS data. It is supported by the majority of mapping applications, including Mango.
Also stored is a second file containing a CSV of records that could not be successfully geocoded. These records usually have an incomplete address, spelling mistakes, or are in remote areas not covered by the geocoder.
Hit ”OK” to proceed with the geocoding.
When the geocoding is complete, address points will appear in QGIS as circle markers. If the base map is not turned on in QGIS, they will display as floating points against white.
This is normal and means that the geocoding is complete. The geocoded coordinates are now in a Shapefile in the Output Location.
Go to the folder containing the notfound.csv file to see which addresses weren’t located. In this case, an empty CSV is a good result.
Upload the Shapefile to Mango following these instructions:
1. Select the “Create New Map” button in the administration sidebar.
2. When the map is ready, click “Layers”, then click on the “Add Layer” button.
3. Now click on “Upload Dataset”
4. Here users can select the type of data they want to upload. In this example it is a Shapefile, so there is no need to change anything.
Click on ”Select files”.
5. Navigate to the Output Location and select the four files of the Shapefile generated by the geocoder. Upload them to Mango.
To select multiple files at once, press and hold Ctrl and click each file (⌘ + click for Mac).
6. Once the upload is complete, hit “Save” and Mango will process the data.
7. When the processing is complete, the Layer Settings panel will appear. Here, select a color for the points, and hit “Save”.
8. Admire the geocoded locations on your map.
ZeeMaps makes it easy to map addresses from Excel or other spreadsheet applications. Copy and paste a spreadsheet into a form or attach a spreadsheet from Google Drive.
To upload a spreadsheet, select “Additions” from the menu on the map. Then “Add (Upload) Multiple Markers”.
Choose the “Upload Spreadsheet” option. A dialog box will open, allowing users to browse and select the desired spreadsheet.
Alternatively use the “Copy-and-Paste Table” option or the “Cloud Drive Spreadsheet” option.
The spreadsheet should provide location information for each row. This includes a postal code, zip code, city, state, country.
Users can have as many columns as is necessary.
Columns such as the ‘Name’ column will be utilized as a “standard” column. Information provided in other columns will add fields to the markers.
Spreadsheets can also contain the street address, city, state, latitude, and longitude. The more information there is, the more accurate the placing of location pins will be.
The header row of a spreadsheet is very important. It contains the names of the columns and dictates which information to use.
Based on the column names, ZeeMaps can automatically determine how to use the data in each column. It makes use of the standard column references.
When uploading a spreadsheet remember to assign the standard column headers.
It’s not necessary to use all of the standard columns. But do ensure that there is enough data for ZeeMaps to locate the entries.
At the very least, the spreadsheet should include a name and postal code column, or a name and city column. Any non-standard columns will be used as additional information attached to each marker.
Users can utilize the information provided in custom columns to search the map.
If you liked this article on how to map addresses from Excel, you should also check out this article on how to pin multiple locations on Google Maps.