Excel Map Function: Visualize Your Data in Interesting Ways

Microsoft Excel is a spreadsheet tool that enables users to format, organize, and analyze data. Microsoft recently added a new feature, “Excel Map Function,” allowing businesses to map and connect data fields from different sources. It is an excellent tool for tracking, analyzing, manipulating, and visualizing geospatial data without learning complex programming languages.

With the help of other in-built Excel functions, it decreases the possibility of errors, standardizes data, and increases data visualization. Map data visualization can assist you in identifying patterns and trends that you may not ordinarily notice. This article contains all you need to know about the Excel Map function. Keep reading to learn more!

Can You Create a Map in Excel?

People often ask, “Can you create a map in Excel?’ The answer is YES! Microsoft Excel is a versatile and powerful software that allows users to do many activities, including creating maps.

Creating a map on Microsoft Excel with the new Excel map function is pretty simple. The Excel map function lets you plot your data on global and local maps. Now that you know you can create a map in Excel, let’s discuss how to do so effectively.

7 Steps on How To Create a Data Map in Excel Using Excel Map Function

It is essential to know how to create a data map in Excel using the Excel map function. It helps to gain quality insights from data. There are two options to create maps on Excel: “Map charts” and “Geography maps.” Map charts are the easiest way to map data in Excel. Here are seven steps on how to create a map in Excel using map charts:

Step 1: Open a recent version of Excel software on your PC (Excel 2016 upward) to utilize the Excel map function.

Step 2: Choose the data to include on your map. You can choose all or a subset of the data in your spreadsheet.

Step 3: Format your data. This step is critical because it ensures your data is ready for mapping. You’ll need to do two things to format your data. First, ensure that you label each column in your data. You can do this by including a header row in your spreadsheet. Secondly, each row in your data must contain a piece of information. For instance, let’s say you have a column for “City” and another for “State.” In that case, each row should only contain the city and state for one location.

Step 4: Select the type of Excel map chart you want to generate: “Map charts” or “Geography Maps.” Choose “Map charts.”

Step 5: Highlight the data you want to map.

Step 6: Tap “Insert” and choose “Maps” in the charts group of Excel.

Step 7: Choose “Fillable Maps” from the drop-down window. Excel will display your data on a map.


Two guys analyzing data on paper with their PCs in front of them.

How To Create a Geography Map Using Excel Map Function

The Excel map function allows you to map data on “Geography Maps” instead of “Map Charts.” Geography data types enable you to add information such as population, GDP, fertility rate, and tax rate to your spreadsheet. From step 3 in the previous section, using the Excel map function, follow these steps to create a geography map in Excel:

Step 4: Choose Geography maps as the type of Excel map chart you want to generate

Step 5: Select a column of the list of countries for which you want additional data and highlight the cells.

Step 7: Navigate to the “Data” menu and select “Geography” from the Data Types tab.

Step 8: Select the type of data you would like to see. Then, Excel will display your data on a geography map.

How the Excel Map Function Works

The Excel Map function is a new dynamic array formulas feature on Excel. You can use the map function for “Mapping a custom Lambda” and “Creating a 3D map in Excel.” This feature is only available on Excel version 365, 2016, 2019, and newer Excel versions.

The Map function helps to map a custom Lambda function to each value in an array (a structure with a collection of values). On the other hand, the 3D map displays data as it changes over time to create a compelling, cinematic visual presentation. Let’s now discuss how the Excel Map function works in each way.

The Excel Map Function and the Custom Lambda Function

The Excel map function and custom lambda function work hand in hand. The Lambda function is a built-in function that helps convert long formulas into functions you can use in different worksheets. You often need to mix Excel functions for computation and data analysis. Thus, this function allows you to write complex formulae in Excel without a programming language.

Suppose you wish to handle each item in an array independently but as an array option that yields an array result. In that case, the Excel map function comes in. It returns the array formed by mapping each data/value in the array to a new value by applying lambda to create a new value. Let’s discuss how to create and name a Lambda function in Excel.

Step 1: Write a core and standard formula.

Step 2: Create and test an unnamed generic lambda formula version. Then you can copy the Lambda formula.

Step 3: Open the Name Manager by pressing Ctrl + F3 shortcut.

Step 4: Click “New” in the Name Manager dialogue.

Step 5: Give the function a name. Keep it short and descriptive.

Step 6: Leave the scope of the workbook set as default.

Step 7: Paste the copied formula in Step 2 into the “Refers to” box and ensure it begins with an equality sign.

Step 8: Click “OK” to save the new name you created.

That’s it! You can now create a new column and apply the lambda function to each value. The Excel mapping function will now map the range of results in the exact dimensions as the original array.

A close -up view of someone using the Excel  maps function on their PC.

Can You Export the Custom Lambda and Excel Map Function Result?

People often wonder if they can export the custom lambda and Excel map function results to another workbook. There is only one way to export lambda, and Excel map function results on an Excel sheet. To do this, simply copy and paste a blank sheet from the old workbook into the new one.

Since you saved the function in the Workbook scope, it goes with any worksheet you copy or transfer. However, using a mapping software tool, you can export all lambda functions and map location data from the source workbook.

3 Benefits and Limitations of the Lambda Function

While the Excel map function enables users to plot data easily on a map, the lambda function in Excel has its fair share of perks and drawbacks. Let’s quickly review the three benefits and limitations of the lambda function in Excel. The benefits include:

  1. It allows you to use short, elegant functions instead of lengthy, difficult-to-read formulas.
  2. You can modify your Lambda function in a single location (Name Manager) rather than updating each formula in a workbook.
  3. You can complete many complex tasks previously only possible using VBA (Visual Basic Application). This implies you don’t have to save workbooks as macro-enabled xlsm files.

Here are some of the lambda function limitations:

  1. It is only accessible in Excel 365 and does not function in previous Excel versions.
  2. Lambda functions are workbook-specific and not shareable between workbooks.
  3. You can only duplicate a sheet containing a lambda-defined function from one workbook to another.

The 3D Excel Map Function

You can use the 3D Excel map function to plot geographic and temporal data on a globe or custom map from your data. The 3D map tool is available in Excel 2016, 2019, and Excel 365. Users of Excel 2013 can also access 3D Maps as an add-in called Power Maps. It displays data as it changes over time, creating a compelling, cinematic visual tour and, most importantly, allowing you to share with others. You can use 3D maps to:

  1. Plot over a million rows of data in 3D on Microsoft Bing maps from an Excel table or Data Model in Excel.
  2. Discover new information by viewing your data and witnessing time-stamped data alter over time to help you gain fresh insights.
  3. Tell your story by capturing screenshots and creating cinematic, guided video tours to share with a broad audience.

Follow these steps to create a 3D map function in Excel.

Step 1: Open the workbook that contains the data you want to analyze in 3D.

Step 2: Click any cell in the data on the spreadsheet.

Step 3: Tap the Insert tab. Different groups will come up.

Step 4: Click “Tour groups” and choose “3D Excel map function.”

The Launch 3D Maps box will suggest some tours. These can serve as a starting point and are simply Excel suggestions based on your data.

Step 5: Click “New Tour” if you don’t want the suggested tour. Then, 3D Maps geocodes your data using Bing based on the geographical information in your dataset. A globe will appear next to the first screen of the “Layer Panel,” where you can view your data changes in 3D.

Two people analyzing data via Microsoft Excel on a PC.

Limitations of the Excel Map Function

The 3D Excel map and custom lambda functions are valuable features of Microsoft Excel. However, it is a primary data analysis tool that depends on a simple but effective spreadsheet format. As such, there are limitations to what you can do. Let’s review some of the limitations of the Excel map function.

  1. Latitude/longitude and street address mapping are unsupported when creating lambda functions. This is because map charts only display high-level geographic information. Instead, you can use a tool that allows latitude and longitude mapping.
  2. You need a stable internet connection to connect to the Bing Maps service to create new maps or add data to existing maps. You can only view existing maps without an internet connection.
  3. Map charts can sometimes map incorrectly if admin fields are not present when mapping.

Final Thoughts on Excel Map Function

Microsoft Excel is a widely used software that allows you to perform tasks effortlessly. When data sets include geographical and geospatial data, you must use multiple Excel map functions to analyze and interpret them. However, the processes may be time-consuming, so you need a map routing tool like Mapize to create a map directly from your data set.

Mapize mapping tool is for individuals, business owners, and data analysts who want to learn more about their location data. Map customization, sophisticated sharing possibilities, and complex mapping tools are all available to map your Excel data set. You can also create a variety of lambda functions and 3D map visualization like heat maps and color-coded maps on Mapize. Try us out and create your first custom map for free today!

About The Author

Start creating map

Drag and drop your file (xls, xlsx or csv) Use our sample data, by clicking here. Or
AddressCityStateZipcodeNamePhone NumberGroupURLEmail
12032 SE Sunnyside rdClackamasOR97015Safeway Clackamas(503) 698-1121Group Bhttps://local.safeway.com/safeway/or/clackamas/12032-se-sunnyside-rd.html[email protected]
4715 SE Cesar Estrada Chavez BlvdPortlandOR97202Trader Joe's Portland SE(503) 777-1601Group Chttps://locations.traderjoes.com/or/portland/143/[email protected]
3380 Lancaster Dr NESalemOR 97305Safeway Salem(503) 399-1502Group Bhttps://local.safeway.com/safeway/or/salem/3380-lancaster-dr-ne.html[email protected]
12220 SW Scholls Ferry RdTigardOR97223Whole Foods Market Greenway(971) 371-7000Group Ahttps://www.wholefoodsmarket.com/stores/greenway[email protected]
8145 SW Barbur BlvdPortlandOR97219Safeway Barbur(503) 452-6068Group Bhttps://local.safeway.com/safeway/or/portland/8145-sw-barbur-blvd.html[email protected]
1550 NW 9th St Ste 100CorvallisOR97330Trader Joe's Corvallis(541) 753-0048Group Chttps://locations.traderjoes.com/or/corvallis/154/[email protected]
1210 NW Couch StPortlandOR97209Whole Foods Market Pearl(503) 525-4343Group Ahttps://www.wholefoodsmarket.com/stores/pearl[email protected]
353 E Broadway EugeneOR97401Whole Foods Market Eugene(541) 434-8820Group Ahttps://www.wholefoodsmarket.com/stores/eugene[email protected]
3527 SE 122nd AvePortlandOR97236Safeway 122nd(503) 760-5448Group Bhttps://local.safeway.com/safeway/or/portland/3527-se-122nd-ave.html[email protected]
9940 NE Cornell RdHillsboro OR97124Whole Foods Market Tanasbourne(503) 645-9200Group Ahttps://www.wholefoodsmarket.com/stores/tanasbourne[email protected]
15391 Bangy RdLake OswegoOR97035Trader Joe's Oswego(503) 639-3238Group Chttps://locations.traderjoes.com/or/lake-oswego/142/[email protected]
2490 OR-99WMcMinnivilleOR97128Safeway McMinniville(503) 435-3120Group Bhttps://local.safeway.com/safeway/or/mcminnville/2490-n-hwy-99-w.html[email protected]
4121 NE Halsey StPortlandOR97232Trader Joe's Portland Hollywood(503) 284-4232Group Chttps://locations.traderjoes.com/or/portland/144/[email protected]
Drag and drop your file here
- OR -
Copy + paste your data include column files
AddressCityStateZipcodeNamePhone NumberGroupURLEmail
12032 SE Sunnyside rdClackamasOR97015Safeway Clackamas(503) 698-1121Group Bhttps://local.safeway.com/safeway/or/clackamas/12032-se-sunnyside-rd.html[email protected]
4715 SE Cesar Estrada Chavez BlvdPortlandOR97202Trader Joe's Portland SE(503) 777-1601Group Chttps://locations.traderjoes.com/or/portland/143/[email protected]
3380 Lancaster Dr NESalemOR 97305Safeway Salem(503) 399-1502Group Bhttps://local.safeway.com/safeway/or/salem/3380-lancaster-dr-ne.html[email protected]
12220 SW Scholls Ferry RdTigardOR97223Whole Foods Market Greenway(971) 371-7000Group Ahttps://www.wholefoodsmarket.com/stores/greenway[email protected]
8145 SW Barbur BlvdPortlandOR97219Safeway Barbur(503) 452-6068Group Bhttps://local.safeway.com/safeway/or/portland/8145-sw-barbur-blvd.html[email protected]
1550 NW 9th St Ste 100CorvallisOR97330Trader Joe's Corvallis(541) 753-0048Group Chttps://locations.traderjoes.com/or/corvallis/154/[email protected]
1210 NW Couch StPortlandOR97209Whole Foods Market Pearl(503) 525-4343Group Ahttps://www.wholefoodsmarket.com/stores/pearl[email protected]
353 E Broadway EugeneOR97401Whole Foods Market Eugene(541) 434-8820Group Ahttps://www.wholefoodsmarket.com/stores/eugene[email protected]
3527 SE 122nd AvePortlandOR97236Safeway 122nd(503) 760-5448Group Bhttps://local.safeway.com/safeway/or/portland/3527-se-122nd-ave.html[email protected]
9940 NE Cornell RdHillsboro OR97124Whole Foods Market Tanasbourne(503) 645-9200Group Ahttps://www.wholefoodsmarket.com/stores/tanasbourne[email protected]
15391 Bangy RdLake OswegoOR97035Trader Joe's Oswego(503) 639-3238Group Chttps://locations.traderjoes.com/or/lake-oswego/142/[email protected]
2490 OR-99WMcMinnivilleOR97128Safeway McMinniville(503) 435-3120Group Bhttps://local.safeway.com/safeway/or/mcminnville/2490-n-hwy-99-w.html[email protected]
4121 NE Halsey StPortlandOR97232Trader Joe's Portland Hollywood(503) 284-4232Group Chttps://locations.traderjoes.com/or/portland/144/[email protected]
Copy + paste your data
Configuration

Map Fields

+ Add Custom Field
Show More

    Oh no!

    On the free tier you can only map up to 150 locations at one time. Please add a plan if you wish to map more than 150 locations.

    Related Articles

    • A company notices a reduction in operating costs after optimizing its delivery routes.
      Plan the Most Efficient Route: A Guide to Smarter Navigation

      Efficient route planning is essential for businesses and individuals alike. It can save time and ensure you make the most of available resources. But how do you plan the most efficient route? Where should you start, and what factors do you consider? Also, which mapping software should you use for this type of planning? The […]

    • A person enters geographic coordinates to find a specific location on Google Maps.
      Plot Coordinates on Map: Find Places With Pinpoint Accuracy

      One of the first things you must know when using mapping software is to plot coordinates on map. It forms the basis for visualizing geospatial datasets, making it easier to analyze large volumes of information. If you’re unfamiliar with how to do this on any mapping software, this article has you covered. Below, you’ll find […]

    • A person makes a postal code map using Google My Maps on their Macbook Pro.
      Zip Code Maps: Get Deep Insights From Numeric Identifiers

      Generally, zip codes improve the efficiency of mail delivery. However, you can do a lot more with these government-issued numbers. You can create zip code maps, which have a lot of use cases. For example, businesses rely on these charts to make various important decisions.  If you’re new to cartography or familiar with various mapping […]

    • How To Create a Custom Map in Google Maps

      Google Maps has become synonymous with navigation—you can find almost any place on this planet. Not only do you get turn-by-turn directions, but it also contains extensive information about the location. Usually, people use this tool to get from one point to another. But what if you want to do something more with this mapping […]