how to make a map using Excel and MapChart
You can now use an Excel spreadsheet to color some of the largest maps faster.

This is a quick tutorial on taking advantage of a MapChart-specific Excel workbook to create your maps easier and faster.

Time and again, I have received emails from users that ask for a faster way to fill-up some of the largest (in subdivisions count) maps on MapChart. There are also many users that have their data in Excel spreadsheets and need a quick way to process them and fill a map on a MapChart page.

Understanding that clicking and coloring each subdivision on a large map (USA Counties, EU NUTS maps, etc.) can become a tedious task, I went ahead and developed an Excel Workbook that lets you export a MapChart configuration file and speed up the process by an order of magnitude.

You can find the MapChart configuration Excel tool:

  1. On Google Drive (Don’t open it with Google Sheets. Save it on your PC in order to use.): https://drive.google.com/open?id=1P6VBvP0Y0Q34YC7ZZ6itWsOmcThZiuWS
  2. On MapChart’s server: https://mapchart.net/tutorial/MapChart%20Configuration%20Export%20Tool.xlsm

Here is how to use it:

The MapChart Configuration Export Tool Excel workbook includes a number of spreadsheets, corresponding to MapChart pages on the website (check the end of this post for updates on this file, as more maps will be available in time).

  • Open the Excel workbook (enable macros when prompted; the export script will not run without macros enabled):
Excel tool to make a map with MapChart
The MapChart configuration export tool in Excel 2016.
  • Select the sheet for the map you want to make. It contains all the available subdivisions of the map. For this tutorial, let’s make a USA Counties map:
set color in excel to make a map on MapChart
The USA Counties worksheet in the Excel tool.
  • The ‘COLOR’ column is the only one you need to edit. Change the Fill Color (background) of the cells to set what color it will be on the map:
set color in excel to make a map on MapChart
Here I randomly colored the COLOR column (column D) for some of the USA Counties.
  • When ready, hit the ‘Export Config File’ button on the right to export your configuration to a text (*.txt) file. The file will be created in the same folder as the Excel workbook.
exported text file from Excel to use on MapChart website
The Excel tool exports a .txt (text file) with the configuration of the map.
  • Copy the contents of the config file:
map configuration text file
Select and copy all the contents of the .txt file.
  • Navigate to the corresponding page on MapChart (e.g. the USA Counties map page) and click on the blue ‘Save/Upload map configuration’ button:
mapchart config upload process button
Select the blue ‘Save – Upload Map Configuration’ button to start the import process.
  • In the dialog that appears, paste the contents of the .txt file into the area at the bottom and hit ‘Submit’:
map config upload process
Submit your map’s configuration file.

Your map will now be colored with the settings you used in the Excel tool. You can now continue with editing the map, changing its background/borders color, filling in the legend, and so on.

Here is the final result from the example above:

usa counties random colored map from Excel
The map settings have been imported. You can now edit your map as usual and download it for free.

You can also check a video showing the steps above here:

Some final notes regarding this feature:

  • You can safely sort/filter the subdivision name columns, if you need to, as long as the headings and the order of the columns remain intact.
  • There is also a Reset button in each spreadsheet that lets you set all subdivisions to the default gray color.
  • Of course, you can save/copy the Excel file as you need. You can easily now keep different Excel files for maps you created, edit them, export new configurations, and upload them on the website.

Please note that from now on, a warning will appear when there are more than 50 different colors (legend entries) on a map:

warning for too many map legend entries
The warning shown if you have more than 50 color groups on the map.

If you try to preview a map with so many color groups, your browser may stall or crash unexpectedly. To mitigate that, you can now select if you need the whole legend, just the title, or no legend at all on your map:

The new options for the legend of a map.

Since having so many legend entries on a map is rarely useful, I would recommend just showing the title on the final map.

I would love to hear your feedback on the new Excel tool. Really hope that it will prove useful to many of you that have struggled when working with some of the largest maps on MapChart.

Please stay tuned on a couple more tutorials regarding the new function; they will be linked here when posted.

Appendix:

Links to the Excel (.xlsm) MapChart Configuration Export tool:

  1. On Google Drive (Don’t open it with Google Sheets. Save it on your PC in order to use.): https://drive.google.com/open?id=1P6VBvP0Y0Q34YC7ZZ6itWsOmcThZiuWS
  2. On MapChart’s server: https://mapchart.net/tutorial/MapChart%20Configuration%20Export%20Tool.xlsm

The currently available map pages in the MapChart Configuration Export tool are:

  • USA Counties
  • USA Congressional Districts
  • European Union NUTS2
  • European Union NUTS3