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.
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.
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:
- It allows you to use short, elegant functions instead of lengthy, difficult-to-read formulas.
- You can modify your Lambda function in a single location (Name Manager) rather than updating each formula in a workbook.
- 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:
- It is only accessible in Excel 365 and does not function in previous Excel versions.
- Lambda functions are workbook-specific and not shareable between workbooks.
- 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:
- Plot over a million rows of data in 3D on Microsoft Bing maps from an Excel table or Data Model in Excel.
- Discover new information by viewing your data and witnessing time-stamped data alter over time to help you gain fresh insights.
- 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.
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.
- 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.
- 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.
- 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!
