Creating Maps in Excel
Workshop Data
The data used in this workshop are the locations and magnitudes of earthquakes across the globe beginning in 2014 through the present. The data are compiled by the United States Geological Survey (USGS) and can be obtained from http://earthquake.usgs.gov/earthquakes/search/. Note: the data have been modified from the original source to include the country and some demographic parameters for this example. If you obtain data from the USGS in the future, it will not have these fields. Earthquakes occurring more than 35 km from the coast of any country have also been excluded.
Download and open the spreadsheet titled “Earthquakes_2014-present_raw.xlsx” Examine the column headings and try to determine what data are contained in the sheet.
What are some examples of analysis you could do with the data using your current Excel skills?
What kind of stories can we tell with the data if we are able to map it?
Considerations
When you're creating a map, it's easy to get carried away and try to add a very large amount of data to the map. It's important to keep in mind that plotting too many individual features on a map can lead to viewer confusion and frustration, and doesn't provide a clear picture of your data. In addition to creating a map that's difficult to interpret, trying to add a large number of rows to a map negatively impacts the performance of ArcGIS Maps for Office. If your data contain a very large number of features, you can try adding it to the map in subsets; for example, if you have 100,000 features, create two separate layers containing 50,000 points each.
For this reason, ArcGIS Maps for Office restricts the number of features you can add to a map without impacting performance. Data import limits per layer are as follows:
Areas (polygons)—15,000
Lines—15,000
Features (points)—50,000
Excel currently allows for a maximum of 5 maps in an Excel workbook.
Signing In
1. To enable mapping functionality, click the ArcGIS Maps tab on the ribbon to see the following menu:
2. Click Sign In and log in with your username and password. The other options should now be available to you.
Adding a Map to Excel
To use Maps for Office, your data need to contain some sort of location information. The app is very flexible in the format it will accept, such as street addresses, latitude and longitude values (coordinates), or place names such as a United States city, United States county, state, world city, country, ZIP Code, or census tract. When you create a map, your data are automatically scanned for location information, so naming the columns of your location data appropriately helps the app. Note that the columns containing location data can be named anything you desire and manually selected when you add the data to a map.
What columns in the sheet contained location information?
1. Click on the Add Map button to open a new blank map. The Add Data window will also open.
Data can be mapped using a cell range, or better yet, from a table in your spreadsheet. First let’s select a cell range from our data.
2. Click on the Cell range button and then click Select range. Either drag to select the first 30 rows or enter =$A$1:$AC$30 in the box and click OK. Click OK in the window.
The app will scan the cell range and try to find our location data.
What location type is selected for us by default?
3. Click the text Confirm location columns. Notice that our coordinate columns (longitude and latitude) are automatically selected based on their name.
4. Scroll down and click OK to close the window.
The app analyzes your data and suggests the best ways to represent it on a map, offering a selection of styles for you to choose from. The options are different for categorical and numerical data. Our spreadsheet contains a mix of numerical and categorical data, so a number of options are displayed. To begin, we will map each earthquake with varying symbol sizes related to the earthquake magnitude.
What do you need to select as your Style by Column?
The map zooms to the location of our first 30 earthquakes, located in Afghanistan, with the Layer Style panel open on the left side of the map. This is the panel where you can customize the symbol shape, color, size, transparency, and many other options.
6. Click OK to accept the default layer style and close the panel.
5. Change the Style by Column to mag. Click Counts and Amounts (Size) (if not already selected) and then click Add Data. In the Confirm window, click Add Data again.
Navigating around the map
8. Click and hold on the map and move the mouse to pan around.
9. Use the mouse wheel to zoom in and out on the map.
The Map Contents shows up on the left of the map by default. This is where all data layers in the map will appear, and you can turn layers on and off, select layers, rename layers, and re-order the layers.
7. Click the text $A$1:$AC$30 by mag and rename it to First 30 earthquakes.
10. Use the + and - buttons in the lower right corner to zoom in and out.
By default, the map will be floating -- that is, it will be in its own window. You can maximize the window or resize it. You can also dock the map into the spreadsheet as you would a chart.
11. Click the arrow in the upper left corner to dock the map. Scroll around in the spreadsheet and you will see that the position is fixed. You can drag the map around the spreadsheet and resize it.
12. Click the arrow in the upper left corner to float the map again.
Change the Basemap
The basemap if the bottom-most layer in the map. All of the data layers we add are drawn on top of the basemap. You can choose from a variety of basemap styles - imagery, topographic, streets, artistic - to suit the theme and purpose of your map.
13. Click the Select basemap button () in the upper right corner of the map. The basemap gallery opens, showing thumbnails of available basemaps.
14. Try a few different basemaps and observe how the tone of the map changes with the basemap.
15. Click the X in the upper right corner to close the map and click OK to confirm.
Importing Data from a Table
It is highly recommended that you convert data to a table before adding it to a map. A table in Excel is an item created by the user and is not synonymous with spreadsheet! Tables add additional functionality, such as easier sorting, filtering, and calculating values. Data stored as a table will make it easier to add data to a map. Let’s create a table for our data and add it to a new map.
1. To create a table, click the Insert tab on the ribbon and click Table.
2. Choose the range covering all the earthquake data (=$A$1:$AC$5643) and click OK.
3. Select the table (if needed) by selecting row 1 and then click the Table Tools: Design tab on the ribbon.
4. In the Table Name, enter Earthquakes.
5. Add a new map to the spreadsheet. Click to add a Table to the map. The Earthquakes table is selected by default. Click OK.
6. In the Style by Column dropdown, select < None >.
7. Select Location (Single Symbol).
What will the earthquake data look like in our map?
8. Click Add data and confirm adding it to the map.
Do the data display as you imagined they would?
The app has combined our individual points into clusters. When more than a few hundred points display, this is the default. Let’s see all of our data instead!
9. Click the OK button to close the Layer style panel. Click Cluster Points on the ArcGIS Maps ribbon. The Cluster points pane will open.
10. Click the On/Off selector beside Clustering to turn off clustering for the layer. Click OK.
11. Pan and zoom around the map to get a sense of the data.
What can you learn from this map that wasn’t apparent from the spreadsheet?
Configuring the Pop Up
Clicking on one of the points on the map will open a pop up window listing all of the data from that row in the spreadsheet. You can customize the information contained in the pop up and adjust the column names.
1. Click on a few data points and inspect the pop up.
2. In the Map Contents panel, click the More button (. . .) and click Pop-ups.
3. In the Header dropdown, select the NAME_LONG field.
4. Click to deselect every field except date, mag, depth, POP_EST, GSP_MD_EST, and INCOME_GRP.
5. Click in the Alias column next to each selected field and enter the following aliases:
Date
Magnitude
Est. Population
Median GDP
Income Group
6. Click OK to accept the changes. Click on a point to view the new pop ups.
Changing the Layer Style
You have seen two ways of representing our data so far. The styles available to you depend on the geometry of the data -- whether it can be represented by a point, a line, or a polygon. The styling options are also influenced by the kind of data associated with features in the layer. For example, a point feature may only have location information such as geographic coordinates, categorical information such as retail location type, or numerical information such as sales details. Not every styling type can be used for every kind of data.
Style by location (using a single symbol)
This is how we are currently displaying the earthquakes. Drawing your data using a single symbol gives you a sense of how features are distributed—whether they're clustered or dispersed—and may reveal hidden patterns. For example, our earthquakes are concentrated along the margins of tectonic plates. We can customize the symbol.
1. Click on the symbol under Earthquakes in the Map Contents panel. The Symbol Selector window opens.
2. Experiment with the shape, fill color, outline color, and size of the symbol.
3. Click the dropdown arrow next to the word Shapes to see all of the categories available.
4. Click OK when you are finished.
Style by counts and amounts (using colors)
You can distinguish features based on a color gradient using numerical values in your spreadsheet. There are different kinds of color gradients that you can use; for example, a simple light-to-dark color scheme is good for showing low-to-high data values such as profit or revenue. Color sequences like this can be applied to poi
nts, lines, or polygons. Let’s again visualize the earthquake magnitudes using a color gradient.
1. Click the Layer Style button on the map ribbon.
2. Click on Choose another style.
3. Click the dropdown under Choose an attribute to show and select mag. Notice that the style options have changed.
4. In the Counts and Amounts (Color) box, click Select and then Options.
Notice the options available to you.
Under Divided by, you can select another field to normalize your data. For instance, if these were population data for each country, you could divide by the country’s area to get the population density.
The Theme determines the style of color scale used to color the symbols.
The Symbols button allows you to choose the shape, color scale, size, and outline color for your symbol.
You can manually adjust the ranges of values represented by the colors by adjusting the sliders.
There is a checkbox for classifying the data into groups. We will examine these options in detail later.
You can draw features with no value, if they exist in the data.
You can rotate symbols, if desired.
A slider for Transparency allows you to set how opaque your symbols are.
A slider for Visible Range will limit the levels for your data, if desired.
12. Experiment with the options and set the symbol and color as you like.
Classification
You can organize your data into groups, called classes, and use the same symbol for each class of data. You can have up to 10 classes in the map, though it generally gets more difficult to distinguish classes as the number of classes increases. Typically, the goal is to get data that are the most similar into the same class, and you have options for how you can create the classes. Changing the classes and classification method will result in very different looking maps, with the objective being to get your classes to be both appropriate for your data and easily understood by the viewer.
Equal interval. The range of all of your data values is divided into equal-sized subranges. You specify the number of intervals and the data are divided automatically.
Natural breaks. “Natural breaks classes are based on natural groupings inherent in the data. Class breaks that best group similar values and that maximize the differences between classes are identified. The features are divided into classes whose boundaries are set where there are relatively big differences in the data values. Natural breaks classification is best used for mapping data values that are not evenly distributed, but instead tend to cluster into groups as natural breaks places clustered values in the same class.”
Quantile. Each quantile contains an even number of data. This is best for linearly distributed data.
Standard deviation. This shows you how much the data vary from the mean, emphasizing data which are further from the mean.
Manual. You can define your own class groupings.
Do you have any ideas on which may be most appropriate for our earthquake magnitude data?
1. Click the Classify Data checkbox.
2. Increase and decrease the number of classes and examine the result.
3. Set the classification type to Quantile. Examine the data slider as you increase and decrease the number of classes.
What can you infer about the data distribution? (Look at the histogram underlying the slider or refer to this data histogram below).
Is Quantile a useful classification method for our data?
4. Set the classification type to Equal interval. Adjust the number of classes and note the result on the map.
Which method seems most appropriate for distinguishing differences in the earthquake magnitude?
5. Set the classification back to Natural Breaks with 4 classes. Click OK to exit.
Style by type (using unique symbols)
We can represent our data with different symbol sizes based on a range of values, as you have seen before. Use what you have learned about classification to experiment with this style!
1. Click the Layer Style button on the ribbon, click Choose Another Style, and choose Counts and Amounts (Size).
2. Change the symbol shape and color, classification scheme and number of classes to find what you think is the best representation of the data. Click OK when finished.
Style by Heat Map
Heat maps display the relative density of points on the map as smoothly varying sets of colors ranging from cool (low density of points) to hot (many points). This type of map works best with dense data. You can vary the color bar as well as the area of influence to make broader or finer areas around your points.
1. Click the Layer Styles button and select Heat Map and enter the Options.
2. Adjust the Transparency and Area of Influence sliders and observe the results in the map.
Selecting Data and Searching by Location
Maps for Office gives you powerful tools to find data in your spreadsheet based on location. Using the Find Nearby tool, you can select data and filter your spreadsheet to show other data within a specified distance or driving time of an individual or set of data points.
1. In the Search box in along the right side of the map, type Muisne and hit enter. This was the location of a catastrophic earthquake of magnitude 7.8 in Ecuador earlier this year.
Click on some of the earthquake points around Muisne and look at the Magnitude value in the pop up to find this particular earthquake. The one we are interested in is southeast of town about 30 km.
3. Click Find Nearby on the ribbon or in the pop up. The source layer (Earthquakes) should show up by default.
4. Click and drag a small box around the 7.8 magnitude earthquake. Click the Next button.
5. Search using a ring with a radius of 50 miles.
How many earthquakes occurred within 50 miles of this spot in our data?
How about within 100 miles of this spot? (Note: you may need to go Back and Clear the selection before analyzing again).
6. Click on the the Filter Spreadsheet button and then examine your spreadsheet.
7. To undo the filtering, click the Clear filter button.
Representing Data Using Polygons
So far we have used points to add our data to a specific coordinate. The Maps app can interpret columns in the spreadsheet which relate the row to other locations, such as cities and countries. It also interprets areas, such as a country, state, census tract, and zip code. We can symbolize our data not only as points, but we can do a quick analysis and map how many earthquakes occurred in each country.
1. Minimize the map, if needed. In the spreadsheet, create a column named Count and populate the entire column with a 1 in each row.
2. In the map, click the Add From Excel button . Click OK to add the Earthquakes table data.
3. In Location Type, select More. In the next window, select Country and click Confirm.
4. In the Country dropdown, select the NAME_LONG attribute.
5. Select Count as the Style by Column.
6. Select the Counts and Amounts (Size) as the style and click Next.
7. Select the option to Aggregate areas.
8. Leave the summarize method as Sum and click Next. Add the data to the map.
9. Set the minimum and maximum symbol sizes and symbol colors as you prefer. Classify the data, if desired.
10. Click the Polygons option (). Change the fill color and adjust the transparency to around 20%. Accept these changes and inspect the map.
11. Rename the layer to Earthquakes per Country (size).
Notice that there were errors when we imported the data. Some countries were not interpreted properly. Let’s inspect the errors and try to fix what we can.
12. Click View Errors in the Map Contents.
Most of the missing earthquakes errors came from the Russian Federation. Let’s try replacing the data in our spreadsheet and adding the table once again.
13. In the spreadsheet, do a find and replace (Ctrl+F). Replace Russian Federation with Russia.
14. Using what you have learned, add the data again. Use the Counts and Amounts (Color) style and adjust the settings to your liking.
15. Rename the layer to Earthquakes per Country (color).
Which style do you prefer the most for these data? Why?
Is it possible to visualize your data with more than one style simultaneously?
Mapping Temporal Data
If your data contain a date and time, you can create animations to visualize temporal changes. Our data have a field called date which lists the date as text, but Excel needs them in a specific format before it will interpret properly. Use the functions LEFT() and DATEVALUE() to convert the text to a date Excel can use.
1. In cell C2, enter the formula =datevalue(left(B2,10)). This will take the first 10 characters of the time field and convert it to the Excel date format.
2. Copy the formula to all the other cells in the column.
3. With all of the column selected, go to the Home tab on the ribbon and set the Format to Short Date. Save the spreadsheet.
4. Add the magnitude data to your map once again using the coordinates, displaying with a style you prefer, e.g. Counts and Amounts (Size).
5. Turn off clustering for the layer. Rename the layer Magnitudes (Size) and turn off the other layers.
5. Click More( ) to display the Layer Options and choose Time animation. The Time settings pane opens.
6. Click the slider to turn on Time animation. In the Time Field menu, select date (if not already selected). Click OK. Notice the clock icon next to the layer name.
7. Click the up arrow next to the word Time in the map panel to expand the Time Slider.
8. Click the play button to begin the animation.
9. Click to pause the animation. Then click the Configure button ().
10. Adjust the playback speed to the fastest setting.
11. Set the interval to 1 month intervals. Click OK, then view the animation.
Try setting the configuration to cumulatively display all the data and view the animation again.
Adding data from ArcGIS Online
Maps for Office enables you to access the data available to ArcGIS Online users and use it in your map. This is a great way for colleagues to share their map data with one another or to enhance your maps. Note that in some cases the ability to modify the style of the data and use the data for analysis may be limited by the data publisher! Let’s look at how to add data from ArcGIS Online.
On the ribbon, click From ArcGIS ().
Search for GSN. Note the different options: search all results, restrict to my organization (Clemson University), or show maps provided by Esri for a variety of categories.
Add the GSN Seismic Network Stations layer to the map.
Close the Add Data panel and open the Layer Style for the new data set. Notice that you can change the style of these data. View them in your map and then remove them by clicking the at the bottom of the Map Contents panel.
Once again search seismic for data from ArcGIS.
Add the data set called Sensitive Areas and open the layer style. What do you notice about this layer?
Remove the layer from the map.
Publishing Your Map To ArcGIS Online
The ArcGIS Maps for Office app enables you to share your layers and maps with others in or outside of your organization through ArcGIS. When you share a map in Excel, a web map is created on ArcGIS. This published map can be viewed in a browser or you can continue to work on it in ArcGIS Online or Desktop. This process also allows you to add a dynamic, interactive map to a PowerPoint slide. You can also publish individual layers from your map as geospatial datasets.
Note that publishing a layer as a feature service will not include the other data in your map. Publishing your map will automatically publish each of your layers as a feature service that will be available to you and anyone you give permissions.
On the ArcGIS Maps ribbon, click Share Map .
Enter a title for you map, Earthquakes, 2014-present (***). Enter your initials instead of the asterisks.
Add tags to the map: earthquake, magnitude
Enter a description for the map: Earthquake locations and magnitudes, from January 1, 2014 to present.
Check the box to share with Clemson University.
Click on Share Map.
Press the link at the bottom of the Map Contents pane to view the published map.
Note: You can update the map and republish it to ArcGIS by pressing Update shared map under the Tools menu and modifying the information you entered as needed.