Got MS Excel? Need a simple map? A little known tool in older versions of Microsoft’s Excel is the ability to create simple maps from tabular data. You can create simple chloropeth, chart and dot density maps using data organized by country, state or region.
Disclaimer: GIS Lounge is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to amazon.com. Amazon, the Amazon logo, AmazonSupply, and the AmazonSupply logo are trademarks of Amazon.com, Inc. or its affiliates.
The instructions below are for Microsoft Excel versions earlier than Office 2000. For later versions of Excel, MapPoint
was introduced by Microsoft to work with their Office software to create maps, see Create a Map with MapPoint and Excel for instructions.
If you don’t have MapPoint and are working with a more current version of Microsoft Excel, visit the Making Maps with Excel for information about free and demo addins that allow you to create a map within Excel from spreadsheet data.
How to Make a Map in Excel
Create or load in an MS Excel compatible file. Compatible files (i.e. files that MS Excel can load in and properly place within columns and rows) include dbase (*.dbf), comma delineated (these are text files with the column values separated by commas and the row values separated by returns), as well as importable files from other spreadsheet programs.
Tip: For a complete list of files that Excel can open check the descriptions in the “file types:” drop down in the open file GUI.
Once you have loaded in or created the data you want to map, you will need to select the columns containing the data you want to map. The easiest way is to simply select all the columns (you select the actual columns to work with later on). To select the rows click on the header cells and drag your mouse across the columns until all columns you want have been highlighted. Make sure you include the column containing the names of the countries, states or cities that you are interested in mapping.
[alert type=”general” dismiss=”yes”]
More Making Maps in Excel
• Making Maps with Excel
• Free Spreadsheet Geocoding
[/alert]
Creating the Map in Excel
Now you are ready to start mapping. With the data columns still selected go to Insert à Map… Your cursor will change to a crossbar. Go to the part of the worksheet where you want to display the map. With your mouse, create the mapping area by clicking the upper left-hand corner of the intended map and dragging towards the lower right-hand corner and then release the mouse. MS Excel will then try to match the boundary names with existing map templates. If it can’t match it or finds more than one match a GUI will pop-up listing choices to select from.
Once you’ve selected your map template MS Excel will then attempt to match the boundary names with the boundary names attached to the template. If it comes across an unmatched name it will prompt you with a GUI listing all the boundary names from the template that you can match the unknown name to. If the name isn’t available in the list you can choose to discard that name and its associated values.
Once all the data issues have been resolved Excel creates a map using the data name column and the next immediate column. The default map is a graduate symbol map.
More Excel Mapping Resources
For making maps in Excel with versions 2007 and later, try out MapCite’s free Excel Addin for creating maps within a spreadsheet.
The Closet Entrepreneur has step-by-step instructions for turning addresses into x,y coordinates for mapping in Google Maps and Earth. If do-it-yourself mapping isn’t your thing, then the site recommends using batchgeocode.com or Dabble DB to upload and map out addresses from Excel.
Is there anything available like this for WinXP and MacOS?
Does this only work with Vista and/or Office 2007?
Your surely would be interested by “IDD Intégration Donnée Dessin”
You can find it for evaluation at http://www.deixsys.com
Regards,
Denis Jaubert
Can anyone pls help me ?
I need to create a “cheapness map” with MS Excel.
How does one do dahat ?
Note – this feature existed in an old version of Excel, and is not present in Office 2003, or Office 2007.
If you want to plot excel data on a map, my company has a tool that gives you free read-only maps of your Excel data.
Microsoft Map, which is a feature of Excel 2000 that creates, displays and enables you to analyze information by geographical region, is not available with Microsoft Excel 2002 and later. To create maps that can be used with data in Microsoft Excel 2002 or later, you can use a mapping program, such as Microsoft MapPoint.
hello.
please don’t laugh: i’m working on MSexcel macros (visual basic scripting) to create simple thematic maps within an excel file.
the results seems promising: using the graphical editing functions available, you can create maps on a graph sheet, provided that you have X/Y coordinates of the objects of your database.
currently i can manage maps of road network, tracing road links as simple segments ad formatting them (colour, thickness) according to road attributes. also possible maps based od points/circles.
i can send the .xls file to whom is interested.
Luca
I would like to see the .xls file that you are using to create the maps.
Luca
I would like to see the .xls file that you are using to create the maps.
Samir, Rafael, Luca,
I was just looking for a similar solution and found a solution at http://www.mapcrossing.com.
Downloaded their free demo for one country and it allowed me to create easily a heat map with the required information. Gonna use this solution for the countries I need it for.
BeGraphic gives for free thematic maps, fully dynamic with your live data.
10 000 additional maps are given to users who ask for them using their business email.
These choropleth maps are the free part of an Excel addin focused in new data visualization.
That is awsome, since we all use excel so much, i created a mashup of AS3XLS library with ESRI’s FLEX API that let you map point data from excel spreadsheet using a web browser. here is the url
http://zachsgeospatial.blogspot.com/2010/06/mapping-point-data-from-excel.html
I like your approach, since you can do more than just points.
Thanks for all the suggestions I need to create a map showing point where my company has trained and I have the information in an Excel spreasheet with the XY coordinates I took from Google Earth.
I just came across this post after having gone through an exercise of comparing he main mapping Apis while developing a way of geocoding and mapping straight out of excel.
Take a look at how to do that and download example excel workbooks here.. Ramblings.mcpher.com.
The capabilities of modern Apis are much more useful Than these kind of excel maps.
Bruce
I only use excel to filter and sort the data and then I copy / paste the data to website, for example http://www.copypastemap.com or geobatch or use a simple script using google API
i found this site http://www.mapcite.com/excel-addin-free.aspx as the url implies they have a free plug in that installs directly into excel. it’s like having a graph in map form, full interactive including GEOCODING!!! theres a pro version if you need advaced functionality but the free version was just fine for me, highly recommend this!
Hi Luca,
I would like to see the .xls file that you are using to create the maps. could you kindly send it to me.