Transcript

00:02In this video, I’m going to show you how to use the ESRI Maps for Office Add-in...

00:05...to create a map layer and publish it as a feature service directly...

00:08...from a Microsoft Excel spreadsheet into ArcGIS Online.

00:12Creating interactive maps directly from Excel is a breakthrough for Office users because...

00:16...of the sheer volume of mappable data that is out there available in Excel format.

00:22For example, many of the tabular data sets compiled by the U.S. federal government...

00:26...here at data.gov contain a geographic variable like state name or ZIP code...

00:31...that can be mapped and they’re all instantly downloadable in Excel format.

00:36This is a companion video to a detailed tutorial that...

00:38...you’ll find in the Getting Started section of the ArcGIS Resource Center.

00:43If you want to do the complete workflow yourself, you’ll need membership in an...

00:46...ArcGIS Online organization -- that’s free trial or paid subscription -- Esri Maps for Office...

00:52...which is included in your ArcGIS Online subscription, Publisher or Administrator...

00:56...rights within your ArcGIS Online organization, and finally Microsoft Office 2010.

01:02Okay, so let’s explore what the spreadsheet data actually describes.

01:06Here we are in Excel.

01:07Notice up here on the top right that I’ve got a new tab called Esri Maps.

01:12This is the result of installing the Add-in that is part of an ArcGIS Online Subscription.

01:16Clicking the tab exposes this ribbon of mapping functions that...

01:19...have been developed specifically for ArcGIS Online users working in Excel.

01:23We’ll get into some of these tools in just a moment.

01:26So what we have here is a year’s worth of data showing by country of origin...

01:30...the number of persons naturalized into U.S. citizenship.

01:32This is data downloaded from the data.gov website in Excel format...

01:37...and cleaned up a little to suit our purposes.

01:40There are 207 countries represented.

01:44We have three columns: the name of the country, the total number of persons...

01:49...naturalized, and the percent of total for the year represented by that number.

01:54So, one thing I can do is just scroll through the data, and I can see some of the...

01:58...higher values, like India and Mexico, both contributing over 10% of the total.

02:02I also have these D values for a handful of countries which represent unavailable data.

02:08Okay, now I have a sense of what this data is about...

02:11...so I’m going to plot them on a map of the world.

02:14I’m already logged into ArcGIS Online...

02:16...so I’ll just click Insert Map and immediately a map window appears on the spreadsheet...

02:20...which I’ll just move out of the way for a moment.

02:23I click Add Excel Data, and then for the Data Source, I’m going to specify a cell range.

02:30So all I need to do here is click the first cell of the actual data and drag across and down...

02:35...until I’ve selected the entire list and all three columns.

02:38Next, I choose Country as the Location Type.

02:42When I click Add Data to Map, the program begins the process of matching names...

02:46...in the spreadsheet to the countries on the map.

02:49After it’s all done, it shows the found locations in green.

02:52It actually did very well, locating 203 out of the 207 records.

02:57Now, three of these remaining four can be resolved by fixing the spelling of the names.

03:01The last error has to do with whether or not the disputed territory of Western Sahara...

03:05...is considered a country -- I won’t worry about that one for now.

03:10Zooming into central Africa, I have a blank spot here...

03:12...because the new country of South Sudan was not on our spreadsheet.

03:19I can fix this easily enough by just inserting a row for South Sudan into the spreadsheet.

03:28I don’t have actual data values so I’ll put in D's...

03:31...but least I won’t have a gaping hole in the middle of Africa.

03:35Because I’m connected to ArcGIS Online, I have access to all the Esri basemaps.

03:40I’m going to pick the Oceans basemap which looks pretty good.

03:43The basemap you choose has no effect on how your data will look after it’s published...

03:48...we’re only going to be publishing the country layer.

03:51Over here in the Map Contents panel...

03:53...I’ll rename the layer to something more descriptive.

03:59By clicking a few different places on the map, I confirm that indeed a link has been made...

04:03...between the countries and the Excel data that I started with.

04:06Next, I’m going to group and style my data.

04:09So grouping is the way we classify data and we have options as far as...

04:13...the number of groups and where they are divided.

04:15I have three grouping options.

04:17I’m going to pick Natural Breaks which looks for clusters and logical gaps.

04:22I could also use this slider to experiment with different numbers of groups.

04:26Notice that the map and the legend both update in the background...

04:29...even as I’m making changes.

04:30The last thing I’ll do is pick this blue color ramp.

04:34Okay, I’m ready to publish this layer as a feature service -- I click Share Layer.

04:40And here I specify that I want to share with my Organization and then I click OK.

04:45That’s all there is to it -- when the process is complete...

04:48...a green arrow appears next to the layer name and I can leave Excel.

04:52Now, over in my ArcGIS Online organization...

04:55...I’m going to locate this new service in My Content folder.

04:58I click the thumbnail to open it in the map viewer -- so here it is.

05:02If I show map Legend, I can confirm that the layer...

05:05...is grouped and symbolized the way I set it up Excel.

05:08If I wanted to, I could actually change the color ramp here as well.

05:13I’m going to go ahead and switch to this yellow-to-red...

05:15...so that the countries with the highest numbers are red.

05:18So I can click on any country in the map to bring up a pop-up.

05:22In the written tutorial, you’ll have the option of editing the pop-up configuration...

05:26...so that instead of looking the way it does now, it will look like this.

05:31So to wrap things up, I now want to embed my data layer into a web map that...

05:34...looks just the way I want it and that I can then save and share with the world.

05:38I’m going to switch over to the Terrain with Labels basemap and make the feature layer...

05:42...slightly transparent so that the terrain and labels are visible beneath the countries.

05:48Terrain basemaps work especially well in mountainous regions like here the Himalayas.

05:54Now I click to save my map, give it a descriptive name...

05:59...and I'll put in some meaningful keywords so the people searching for it can easily find it.

06:07When I’m done, I save the map and click the Share button.

06:13I’ll choose to share it with my organization, and now...

06:18...I have a focused web map in ArcGIS Online that tells simple but interesting story.

06:24Thanks for watching.

Copyright 2014 Esri
Auto Scroll (on)Enable or disable the automatic scrolling of the transcript text when the video is playing. You can save this option if you login

Publish Excel Spreadsheet with ESRI Maps for Office

This video describes how to publish a feature service from Microsoft Excel with Esri Maps for Office.

 

  • Recorded: Apr 25th, 2013
  • Runtime: 06:27
  • Views: 1747
  • Published: Apr 29th, 2013
  • Night Mode (Off)Automatically dim the web site while the video is playing. A few seconds after you start watching the video and stop moving your mouse, your screen will dim. You can auto save this option if you login.
  • HTML5 Video (Off) Play videos using HTML5 Video instead of flash. A modern web browser is required to view videos using HTML5.
Download VideoDownload this video to your computer.
<Embed>Customize the colors and use the HTML code to include this video on your own website
480x270
720x405
960x540
Custom
Width:
Height:
Start From:
Player Color:

Right-click on these links to download and save this video.

Comments 

Be the first to post a comment
To post a comment, you'll need to login.
If you don't have an Esri Global Login ID, please register here.