This is an archive related to a previous version of ArcGIS Maps for Office. If you need the current version go to http://doc.arcgis.com/en/maps-for-office/.

Add layers from Excel

After you've added a map to your spreadsheet, you can add layers from the data in your Excel spreadsheet. When you begin the Add Data workflow, ArcGIS Maps for Office analyzes the data in your spreadsheet and suggests the best ways to represent it on the map, offering a selection of styles for you to choose from. You can quickly add a layer to your map by choosing one of the suggested styles. You can choose one of the quick map styles, or specify your data source, location type, and styling options manually.

Data import 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 business 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 contains 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

Add a quick map layer

To add a layer from the suggested styles, do the following:

  1. Click Sign In on the ArcGIS Maps ribbon to sign in to your ArcGIS account.
  2. Click anywhere on the map that you want to edit to select it.

    The Home ribbon on the map window shows the map editing tools.

    Map editing tools

  3. Click From Excel.

    The Add data from my spreadsheet window opens, displaying a carousel loaded with options specific to your data.

    Add data from a spreadsheet

  4. Review the Data, Location Type, and Style by Column options to ensure that they are correct.

    • Data—Allows you to choose the data in your spreadsheet that will be used to create the map. Choose data from a table, a cell range, or a named range. For more information, see Tables and named ranges.

      If your workbook contains multiple spreadsheets, tables, or named ranges, you must specify which data to use to create the map.

    • Location Type—Determines how your data will be mapped and controls what's displayed in the carousel. If you choose address, city, or coordinates, your locations will be mapped as points. If you choose state, province, or other area location type, your data will be mapped as polygons. You can also map your data to custom location types, such as park boundaries or sales territories, that aren't included in the default list. For more information, see Location types.
    • Style by Column—Allows you to choose the column used to compare the points, lines, or areas on your map. For example, to compare all the stores in a franchise based on sales, choose the column that contains the sales information. The carousel will display different ways you can compare the records on the map. Typically, you compare records by varying the size or color of icons. To map your records without comparison, choose None from the drop-down menu.

  5. Using the forward and back arrows on the carousel, scroll through the available styling options. Hover over a style to view its description.
  6. Choose the style you want for your layer and click Add data.

    The data from your spreadsheet is added as a layer to the map.

Choose a data source

If your spreadsheet contains multiple tables, named ranges, or if ArcGIS Maps for Office cannot find location-based information, you can manually specify the data source to use to create a new map layer.

To choose a data source, do the following:

  1. In the Add data from my spreadsheet window, click the Data drop-down arrow and choose More.

    The Add Map workflow window opens.

    Add Map workflow window

  2. Choose the table, cell range, or named range that contains your location-based data.

    For more information, see Tables and named ranges.

    • To choose a table, click the drop-down arrow in the Table section and choose one of the available tables.
    • To choose a range of cells in your spreadsheet, click Cell range to display the range field. Click Select range, select the desired range of cells in your spreadsheet, and click OK. If your cell range contains headers, check the First row contains headers check box.
    • To choose a named range, click Named ranges to display the range field. Click the drop-down arrow and choose the desired named range. If your named range contains headers, check the First row contains headers check box.
  3. Click OK to close the Add Map window and return to the Add Data workflow.
  4. Choose the style you want for your layer and click Add data.

    The data from your spreadsheet is added as a layer to the map.

Choose a location type

To add Excel data to a map, your spreadsheet data must contain at least one of these location types: 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 postal code. You can also add a location type based on a hosted service in your ArcGIS organization, such as park boundaries or sales territories for your organization, or specify a custom coordinate system from a map or feature service on ArcGIS.

To choose a location type, do the following:

  1. In the Add data from my spreadsheet window, click the Location Type drop-down arrow and choose More.

    The Select a location type workflow window opens.

    Select a location type window

  2. Choose the location type appropriate for your data.

    For example, if your spreadsheet includes a state attribute, choose States. A check mark appears on the selected location type.

  3. Click Confirm.

    ArcGIS Maps for Office automatically tries to correlate the selected location type with the matching column in your data. If the location information is not found, or to verify that all the correct fields are identified, you must provide additional information.

  4. Click Confirm location columns.

    Depending on the location type you chose, a different dialog box appears.

  5. Use the drop-down menus to choose the appropriate columns in your data to match to the location fields.

    For example, if your spreadsheet contains both state and province information, choose the States location type and match it to the Province-State column in your data.

    Match columns from your spreadsheet to the chosen location type

  6. Click OK to return to the Add Data workflow window.

    The carousel updates to reflect options based on the specified location type.

  7. Choose the style you want for your layer and click Add data.

    If your data contains duplicate areas, you have the option of aggregating the data before you create the layer. See Aggregate areas for more information.

Add a custom location type

If your organization has its own boundaries shared on ArcGIS, you can map your data using those boundaries by adding them as a location type. Custom boundaries can include sales districts, zoning boundaries, or other defined areas.

To add a location type, do the following:

  1. In the Add data from my spreadsheet window, click the Location Type drop-down arrow and choose More.

    The Select a location type workflow window opens.

  2. Click the My Locations tab and click Add location type.

    The Add location type window opens.

  3. Search across ArcGIS or browse your groups or content for the hosted service to use as a location type.
    • Type your search query in the search field and press Enter (or press the magnifying glass icon).
    • Click a folder from your groups or contents to view available services.
  4. Click the desired hosted service.

    The Add location type window displays the layers available in the hosted service.

    Layers available in hosted service

  5. From the Choose the location layer drop-down menu, choose a layer to use as a location type.
  6. Check the check box beside the column or columns that will be used to match your spreadsheet data to the features in the hosted service.

    Be sure the values in both places (spreadsheet and hosted service) are of the same type (number or text) and formatted identically or you won't be able to use your custom location type.

  7. Provide a unique name for the location type and click Add location type.

    The new location type is now available for use on the My Locations tab.

Aggregate areas

If your data contains duplicate areas, you have the option of aggregating the data for those areas to summarize the information in a way that is easier to analyze—by average, sum, or count. For example, if you choose to create a map using the State location type, but your data contains sales results for many ZIP Codes within each state, you can summarize the information so that clicking on a state polygon on the map displays a total sum of sales for all ZIP Codes within that state.

To aggregate data:

  1. After you choose a location type, if your data contains duplicate areas, the Choose an option window opens.

    Choose an aggregation option

  2. In the Aggregate areas section, click Select.
  3. Choose how to summarize the values in the location column. Choose Sum, Average, or Count.
  4. Optionally choose additional columns to include as attributes, and specify the operation for each one.
  5. Click Next.

    A confirmation window opens.

  6. Click Add data.

    ArcGIS Maps for Office creates a PivotTable in your Excel workbook and uses this table to create a new layer on the map.

Add a custom coordinate system

To use a spatial reference other than the standard WGS, Web Mercator, or other well-known coordinate system, you can specify a map service or feature service from your ArcGIS organization and import its coordinate system to display your data on a map.

To add a custom coordinate system, do the following:

  1. In the Add data from my spreadsheet window, choose Coordinates from the Location Type drop-down menu and click Confirm location columns.
  2. Use the Longitude (X) and Latitude (Y) drop-down menus to choose the appropriate columns in your data to match to the location fields.
  3. In the Choose spatial reference pane, choose Other and click Import.

    A new window displays the content available in your ArcGIS organization.

  4. Browse or search to find a map or feature service that uses the custom spatial reference as your spreadsheet data. Click Details to view more information about a map or feature service.
  5. Click Select to choose the map or feature service.

    The Import custom spatial reference window opens, showing detailed information about the selected spatial reference.

  6. In the Alias field, type a unique name for the custom coordinate system.
  7. Click Import to import the spatial reference.

    The new coordinate system appears at the top of the Other drop-down menu.

  8. To set the custom spatial reference as the default spatial reference when adding data to the map using coordinates, check the Use as default check box.
  9. Click OK to return to the Add Data workflow window.

Style by column

When your data contains numerical information, such as sales information, ArcGIS Maps for Office lets you choose which column will be used to compare the points, lines, or areas on your map. For example, to compare all the stores in a franchise based on revenue, choose the column that contains the sales revenue information. The carousel displays different ways you can compare the records on the map.

To style by column, do the following:

  1. In the Add data from my spreadsheet window, click the Style by column drop-down arrow and choose the column to use to style the layer.

    To map your records using individual features, chose <None>.

    The carousel updates to reflect options based on the specified location type.

  2. Choose the style you want for your layer and click Add data.

    The data from your spreadsheet is added as a layer to the map.