Blog

A quick guide to getting started with Map Charts in Excel

A recent update to Microsoft Office 365 subscribers added 3D models and boosted the overall performance of Excel 2016. If you're like me and work in complex workbooks with multiple sheets, you noticed that everything from crunching formulae with large ranges to simple copy-and-paste functions is quicker after the update.

But while experimenting with the new 3D model options, I came across a forgotten feature in Excel that automatically creates stunning, dynamic maps out of your spreadsheets: Map Charts.

Originally offered as a "Bing Maps" add-in for Excel 2013, the Map Charts feature in Excel 2016 reads columns with geographic locations in your spreadsheet and automatically turning them into color-coded maps. For example, take this (totally scientific) table of statistics for total barbeque restaurants per capita in Dallas, Texas based on ZIP codes:

 

With one click of the Insert Map Chart button, Excel reads the ZIP codes and creates a map based on the most current online data. Excel transposes the per capita numbers from the spreadsheet onto the map and inserts it as a color-coded breakdown of the table:


Excel Map Charts can represent variations of two to three colors per map. In my BBQ example, the number of restaurants are values with each portrayed using a gradient spectrum of red. The color for each ZIP code is dictated by where along the spectrum its value falls with respect to the others. Higher concentrations of BBQ restaurants equal a darker color, fewer numbers result in a "weak sauce" lighter red.

You can allow Excel to format the colors automatically per values and by categories (denoting a specific group of data) or customize your colors using the feature's rich design tools. For anyone crunching numbers based on geographic data in Excel spreadsheets, the Map Charts feature is your secret weapon for data visualization. To impress your audience with an Excel Map Chart on your next report, click Insert > Charts > Maps from the menu ribbon in Excel 2016:

Modern businesses rely on data to act with knowledge and confidence. Amaxra consultants have years of experience developing intelligent data visualization solutions for SMBs to enterprise companies; allowing them to better understand the numbers that show progress towards their business goals.

Want to learn how Amaxra can design a business intelligence and data visualization solution tailored for your needs?

Contact me at Rosalyn.arntzen@amaxra.com or call 425 749 7471 if you have any questions or comments on this blog.

Comments

Subscribe to Our Blog

Get the latest news from Amaxra delivered to your inbox

Tags

{
  "moduleName": "tag_blogtaglist_2",
  "moduleDescriptor": {
    "templatePath": null,
    "parameters": ",collection=\"tagList\",template=\"\"",
    "apiEndpoint": "/api/v3/tag_blogtaglist_2",
    "objectType": -1,
    "objectId": -1,
    "adminUrl": ""
  },
  "items": [
    {
      "id": 359043,
      "name": "Azure Active Directory",
      "count": 5
    },
    {
      "id": 345675,
      "name": "Business",
      "count": 11
    },
    {
      "id": 345676,
      "name": "Business Analytics",
      "count": 9
    },
    {
      "id": 348247,
      "name": "Business Intelligence",
      "count": 12
    },
    {
      "id": 345678,
      "name": "Business Solutions",
      "count": 45
    },
    {
      "id": 345680,
      "name": "Business Tools",
      "count": 39
    },
    {
      "id": 348246,
      "name": "ClickDimensions",
      "count": 7
    },
    {
      "id": 345684,
      "name": "Cloud Solutions",
      "count": 58
    },
    {
      "id": 345685,
      "name": "Collaboration",
      "count": 25
    },
    {
      "id": 345686,
      "name": "Communication",
      "count": 12
    },
    {
      "id": 345690,
      "name": "CRM",
      "count": 23
    },
    {
      "id": 345692,
      "name": "Customer Service",
      "count": 4
    },
    {
      "id": 345696,
      "name": "Digital Transformation",
      "count": 23
    },
    {
      "id": 345697,
      "name": "Dynamic Workforce",
      "count": 22
    },
    {
      "id": 345698,
      "name": "Dynamics CRM",
      "count": 24
    },
    {
      "id": 345699,
      "name": "Email Marketing",
      "count": 6
    },
    {
      "id": 345702,
      "name": "Excel",
      "count": 4
    },
    {
      "id": 345705,
      "name": "Giving Back",
      "count": 2
    },
    {
      "id": 345706,
      "name": "Going Green",
      "count": 3
    },
    {
      "id": 345709,
      "name": "Lead nurturing",
      "count": 8
    },
    {
      "id": 345711,
      "name": "Management Tools",
      "count": 12
    },
    {
      "id": 345713,
      "name": "Marketing Automation",
      "count": 9
    },
    {
      "id": 345714,
      "name": "Metadata",
      "count": 1
    },
    {
      "id": 363808,
      "name": "Microsoft 365",
      "count": 14
    },
    {
      "id": 345716,
      "name": "Microsoft Dynamics",
      "count": 24
    },
    {
      "id": 345717,
      "name": "Microsoft Office",
      "count": 35
    },
    {
      "id": 345718,
      "name": "Microsoft Partner",
      "count": 2
    },
    {
      "id": 363789,
      "name": "Microsoft Teams",
      "count": 6
    },
    {
      "id": 345721,
      "name": "Office 365",
      "count": 75
    },
    {
      "id": 358337,
      "name": "Office Planner",
      "count": 1
    },
    {
      "id": 345722,
      "name": "OneDrive",
      "count": 7
    },
    {
      "id": 345725,
      "name": "Planning",
      "count": 9
    },
    {
      "id": 345727,
      "name": "Power Bi",
      "count": 4
    },
    {
      "id": 345728,
      "name": "PowerPoint",
      "count": 2
    },
    {
      "id": 345730,
      "name": "Presentation Design",
      "count": 2
    },
    {
      "id": 345732,
      "name": "Productivity",
      "count": 46
    },
    {
      "id": 348227,
      "name": "Security",
      "count": 42
    },
    {
      "id": 348245,
      "name": "SharePoint",
      "count": 18
    },
    {
      "id": 348226,
      "name": "Sustainability",
      "count": 3
    },
    {
      "id": 358338,
      "name": "Windows 10",
      "count": 14
    },
    {
      "id": 348228,
      "name": "Work for Amaxra",
      "count": 20
    }
  ]
}