Visual Crossing Blog

Getting started with Maps for Microsoft Excel

Maps for Microsoft Excel result image

The goal of this tutorial is to introduce you to formatting a Visual Crossing map and then doing some simple geographic analysis.

We will start with a dataset of store points and customer points. This dataset contains two values, ‘sales’ and ‘profit’. When asked to map this data set, Visual Crossing will automatically create an initial map for us that shows our customer and store points color-coded based on their data values.

After briefly exploring this map, we will then edit the formats and labels and examine some of analysis features of Visual Crossing Maps.

Tutorial video

We have created a video of the tutorial at https://www.youtube.com/watch?v=veg5QDCzmps&feature=youtu.be

Quick installation & sample data

To install the addin please visit the Microsoft Office Store at https://store.office.com/en-us/app.aspx?assetid=WA104380804.
To download the sample Excel Workbook, please download and unpack the zip found at http://www.visualcrossing.com/installations/office/ExcelTutorialWorkbook.zip.

1. Installing the Visual Crossing Add-in

You must have the Visual Crossing Add-in available. The Visual Crossing Add-in is available for trial and purchase on the Microsoft Office Store at https://store.office.com/en-us/app.aspx?assetid=WA104380804. 1.1 Open the above sample data workbook in Excel for Windows or Excel online. The spread sheet represents sales information for customers by store and US zip.

1.2 After opening the spreadsheet, insert the add-in to the document.

2. Range Selection

After the map add-in is inserted into the document the initial range selection appears. This allows you to select the data that will be used to create map. The first step to selecting the data is to select the cells that form the headers to the dataset you would like to map. In this tutorial the headers are in the first row ranging from “Region” to “Profit”.It does not matter if you select more columns than you need – on the next screen we will select the exact columns and how they will apply to the map. 2.1 Select the data by either dragging the cells or by typing the range into the text box.

2.2 After selecting the range, hit “Continue” to proceed to the next step.

3. Creating Layers

The next screen allows you to configure the fields you chose on the previous step. A map is a series of layers which represented particularly geographies. Geographies include boundaries such as a Counties, Countries, States , Postal Codes or other administrative boundaries. Geographies can also include point information such as longitude or latitude points, addresses etc.The Excel Dataset Manager sets up how each layer will be displayed starting with how the geography for each layer will be displayed.

3.1 On the left hand side, is the list of columns selected in the previous step. These can be dragged to the boxes on the right hand side to configure, the geography, color by and label fields. To start, we will configure the geography. Drag the field ‘Store Latitude’ from the left hand side to the Geography drop box on the right. The add-in analyzes the new field and attempts to identify the geography being configured. It does this based off the name of the column and the contents of the data.

The add-in identifies the field as a latitude point and recommends the additional field categories required. The warning icons indicate areas that need to be completed before there is a valid geography. 3.2 Drag in the Store Longitude field into the longitude box and the Store Name into the key box. The key box is used by the add-in to identify unique geographical items even if there are multiple rows for the same geographical item. For example, in this dataset there are multiple rows for every store because multiple customers shop at every store. The map works best if it understands what is the unique key about the dataset – which in this case is Store Name. As soon as the latitude and longitude fields are complete, the add-in analyzes the data again. In this case the data successfully validates and displayed a green check to indicate that the geographic information looks good.

At this point the map is ready for display however we would like to format the layer to including coloring representing the Sales and Profit values and also add some labels to the map when the user moves the mouse. 3.3 Drag the Sales and Profit fields to the ‘Color by’ drop zone and drag the ‘Store name’, ‘Sales’ and ‘Profit’ fields to the label drop zone. 3.4 Finally change the name of the layer to ‘Stores’ in the top text entry box.

Next we will add one additional layer for zip codes of the customer data. 3.5 Create a new layer by clicking on the ‘+’ button in the bottom left. Zip codes are different because they represent polygon areas of geography. For area geographies the most common way to configure them is to have one or more columns on the dataset be used to look up the geography boundary based on the name. So in the case of this dataset, the Zip code layer will look up the boundary for each zip code in the dataset. 3.6 Drag the zip code field from the left hand side to the Geography drop zone. The map will immediately update and identify the appropriate geographic data set based on the field name.

3.7 Complete the layer by adding Sales and Profit to the color by and Zip Code, sales and profit into the label drop zone. Finally change the name of the layer to ‘Zip Code’ We have now defined a multi-layer map and are ready to see the results. 3.8 Hit the ‘Create Map’ button. The map will load the geographic data, identify the location of the data and immediately zoom in to produce this the following map.

4. Using the map

We can now explore the features of the map within Excel. The map is constructed as a series of layers – one for stores and one for zips. In addition, there is another layer called the base map in the background. In this initial map this is an OpenStreetMap base map. Other base maps are available including Google Maps, Bing Maps as well as various others. You can use the mouse to pan and zoom around the area and see the stores and their geographic relationship to the customers that they serve. Specifically notice the popup labels that Visual Crossing has automatically created for you. They show you the cell value information for a given zip code or store as well as any other available details about that entity. Also note how Visual Crossing has color-coded the points based on their cell data. Initially, the color-coding will be by the first measure given in the data set, but can change this in the next step by using the legend.

The initial map in Excel has four buttons on the right hand side for accessing additional functionality. At the top the plus and minus buttons give the user the ability to zoom in and out of the map. The mouse can also be used for the same functionality. The third button is the pen icon. This icon opens the edit menu where the user can view the layers, make selections and perform searching. For more information, see the next section. Finally, in the lower right-hand corner of the map opens and closes the legend. You will see that Visual Crossing has automatically created a legend to correspond to the data being presented. You can customize this legend later if you desire. However, note that Visual Crossing has automatically defined a series of sequence ranges to match the measure data. Also note the cycle icons beside each measure name that allow you to cycle between different measures on the data set. Click those to switch between the profit and sales measures. Notice how the coloration of the points changes when you cycle between the value columns? This is Visual Crossing automatically updating the map to match the selected values.

5. Formatting layers

5.1 Click the pen icon on the right hand side. This opens the interactive functionality within the map and allows the user to format the map, search and perform advanced selections within the map.

When clicked, the button first brings up the layer list menu which displays the layers on the map. As you can see the map includes the layers we added from our Excel data plus the ‘OSM Layer’ which is the Open Street Map base map layer. 5.2 Click on the check mark icon next to the ‘Zip Code’ layer. This enables and disables the layer. By un-checking it, the layer will be disabled.

5.3 Click on the arrow next to the Store layer and select Format from the menu that pops up. (Note that you can also click directly on the layer name as a shortcut to bring up the Format window.)

At the top of the Format Editor window that pops up, you can edit the Layer’s Name, Formats and Sequences associated with the Layer and Text Labels. Now let’s change the icon for those store points. Near the top of the popup window, you will see Visualization options. If you click on the left-most thumbnail, you will get a popup showing various point shape options. Select the push-pin option and select a graduated sizing option by selecting the last option with pins of various sizes. This will show are best performing Stores with bigger pins.  

5.4 Close all windows by clicking the “x” icon. Note how the map instantly and automatically reflects your change.

6. Searching layers

Open the edit menu again and then click on the Search icon.

You can use this to search for particular items on the map. 6.1 Enter “Dayton”

The map will search through all the layers and addresses to find matches. In this case it has found a match in the Stores data and it has also found an approximate address match. 6.2 Click on the Dayton Mall in the stores list.

The map zooms to the location selected and also selects it ready for additional analysis if needed.

7. Selections

The last stage of this introductory tutorial is to examine selections. First let’s zoom out the map to see all the customers and re-enable our Zip Code layer by selecting the check box in the layer list.

The add-in supports a variety of selection approaches including selections by clicking on the items on the map and drawing radii, rectangles and polygons on the map to provide fine grain selection. 7.1 In the edit dialog, hit the selections icon:

The selection control provides access to multiple types of selections. The blue shapes provide various ways to make selections. 7.2 Hit the blue circle to set the selector in ‘Radius’ mode. Then make sure to select ‘Zip Code’ as your selection layer and finally Click on the map and drag out a Radius selection of 30 miles.

The map provides a summary label of the selection in addition to the details that are available in the selection window via the toolbar.

The selections are quite powerful and we encourage users to explore by creating multiple selections and comparing them side by side as well as using other tools such as the polygon/lasso or the line buffer tool.

Conclusions and Next Steps

Thank you for completing this tutorial. This tutorial has just touched on some of the geographic features of the Visual Crossing map add-in for Excel. Check out the support site at http://support.visualcrossing.com and our YouTube channel to see more tutorials Visual Crossing Maps for Excel is the only complete, easy-to-use mapping solution available for all Excel users. With interactive, powerful maps you can achieve new insight from your existing workbooks all from within the Excel interface that you already know. Quickly create presentation quality maps and analyze large datasets using our proven business intelligence-grade analytics and visualizations.
  • Easy to Use: Create presentation-quality maps of your favorite spreadsheets using our automated configuration tools to easily match your geographic columns to map objects. Create multi-layer maps, custom text labels, and colored map themes for your point or polygon data.
  • Visualizations and Analysis: Analyze your existing data as a map without time-consuming, expensive mapping tools. Create heatmap, hotspot, cluster and Voronoi visualizations with just a few clicks. Utilize our advance selection tool to analyze and compare multiple geographic areas at the same time.
  • Scalable: Visual Crossing provides high performance and the ability to show an entire spreadsheet-worth of data on a single map. Visualize hundreds of thousands of entities & full postal code polygon maps in seconds.
  • One-Touch Formatting and Visualizations - See your data in many interactive visualizations simply by selecting heatmaps, hotspots, pie charts, Voronoi partitions, point clusters or dozens of point styles.
  • Macro-based Label Editor - Create rich text labels with embedded values and attribute data. Popups and Embedded labels allow for easy analysis of any map entity.
  • Printing – Print your map directly from within Excel in the same way that you are accustomed to printing today.
  • Value-based Thematic Sequences - Automatically or Manually distribute your data to be optimally viewed and analyzed. Page by all of your values to quickly change the map view.
  • Integrated Search Function - Search for anything on your map simply by typing in the search bar. Find addresses, your own geographic data, points of interest, and more.
  • Multi-layer Maps - Layer multiple geographic columns onto a single map view. Layer your stores and customers on top of Postal Codes, States, Counties, Countries and more.
  • Advanced Selections - Select, analyze, aggregate and compare areas of your geographic data with polygon, distance radius, and line buffer tools.