Add layers from Excel

After you've added a map to your worksheet, you can add layers from the data in your Excel spreadsheet or from ArcGIS. When you begin the Add Data workflow, ArcGIS for Office analyzes the data in the currently selected Excel table or range of cells and suggests the best ways to represent it on the map. You can quickly add a layer to your map by using the default options, or you can specify your data source, location type, and styling options manually.

Considerations

Data formats

  • Use Excel tables—In most cases, it's recommended that you format your data as an ExcelExcel table before you add it to the map. Using an Excel table allows ArcGIS for Office to add columns containing new information to the dataset. For more information, see Prepare data.
  • Use text values—Columns in your dataset that will be used for location (ZIP Codes, for example) should be formatted as textual values, not numerical values. If your data contains a number that includes a leading zero, as is common with ZIP Codes, Excel interprets these fields as numerical values and strips out the leading zero, changing the original value. Formatting such columns as text ensures that your data will remain accurate.
  • Use time formats— When your data contains time-only fields (as opposed to date and time fields), ArcGIS for Office converts these values to strings to display them in pop-ups. Because of this, unlike true date and time values, time-only values cannot be used in time animations. To ensure that time values display properly, use the Format Cells option in Excel to choose the time format to apply to all cells in that column before creating the layer.

    Time values in pop-ups and in layers or maps shared in ArcGIS will display in the format set by the map author before creating the layer.

    Some ArcGIS for Office time formats may contain slight differences from the standard Excel time formats. The following table shows some of these format differences:

    Excel time formatArcGIS for Office time format

    13:30:55

    13:30:55

    30:55:2

    37:30:55

    *1:30:55 PM

    1:30:55 PM

    1:30:55 PM

    13:30

    13:30

    1:30 PM

    1:30 PM

Prepare data

ArcGIS for Office automatically detects tables in the workbook for use in the Add data wizard. It is highly recommended that you convert data to a table before adding it to a map.

To create a table, click the Insert tab on the ribbon and click Table. Choose the range that contains your data and click OK. Now you can use this table to add a layer to a map. If you are unsure whether your workbook contains tables, use Name Manager on the Excel Formulas tab to see a list of tables.

Tip:

When selecting cell ranges, drag the mouse pointer over the desired group of cells. Do not select cells using the column headers or row numbers, as that selects the entire worksheet and may impact performance. See Select cell contents in Excel for more information.

PivotTables are a special type of table in Excel that are used to summarize and analyze data. When adding data, ArcGIS for Office automatically detects pivot tables for use in the Add data wizard and includes them if appropriate.

Add a layer from Excel data

To add a layer from Excel data, do the following:

  1. In your worksheet, click the map that you want to edit to select it, or add a new map.
  2. Sign in to ArcGIS using your ArcGIS credentials or click Continue to proceed as a standard user with basic functionality.
  3. From the map tools, click Layers Show layer cards.

    The layer list appears on the map.

  4. Click Add from Excel.

    The Add from Excel pane appears, displaying the available datasets and location types for the layer. In the Dataset field, ArcGIS for Office displays tables and cell ranges in the worksheet. In the Location types field, the available location types are displayed.

    Add data from Excel

  5. Use the Dataset drop-down menu to select a table or cell range. Use the Location types drop-down menu to select a location type.

    Click Add to map.

Choose a dataset

By default, ArcGIS for Office displays the dataset currently selected in the workbook. The drop-down menu lists available data in Excel tables or selected cells. If your spreadsheet contains multiple tables, or if ArcGIS for Office cannot find location-based information, you must specify the data to use to create the map.

To choose a different dataset, do the following:

  1. In the Add from Excel pane, open the Dataset drop-down menu.
  2. Select a table or cell range to specify the areas in your worksheet that contain the data you want to use to create the map layer.

    To choose a cell range, click Select a cell range and select the cells in the Excel worksheet. The Dataset drop-down menu is automatically populated with the range ID (for example, $A$1:$D$5). If the selected range contains headers, check the First row contains headers check box.

    Note:

    If you plan to share your layer on ArcGIS, ensure that the layer's name doesn't contain any unsupported characters; see Layer names for more information.

Choose a location type

To add Excel data to a map, your worksheet 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 use a custom location type based on a hosted service in your ArcGIS organization, such as park boundaries or sales territories for your organization, or specify a coordinate system from a map or feature service on ArcGIS.

The location type determines how your data will be mapped and controls what's displayed in the style options. If you choose Address or Coordinates, your locations will be mapped as points. If you choose Geography or ESRI JSON Geometry, 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.

ArcGIS for Office automatically uses the default location type set by your organization's administrator. Multiple location types can be configured, and any one of these can be set as the default. For more information, contact your ArcGIS administrator.

To choose a location type, do the following:

  1. In the Add from Excel pane, choose one of the default location types from the Location types drop-down menu.

    Depending on the location type you choose, different options appear. Use the drop-down menus to choose the appropriate columns in your data to match with the location fields.

  2. Use the following options to choose and format your location type:
    • Coordinates—Use the Longitude (X) and Latitude (Y) drop-down menus to match columns in your data that contain coordinates. If necessary, use the Spatial Reference drop-down menu to change the spatial reference system.

    • Address—Use the Geolocator and Country drop-down menus to specify these options. Select Single column or Multiple columns to identify the data in your table or cell range as containing address data. Use the Address drop-down menu to choose a column or columns with address data.
    • ESRI JSON Geometry—Use the Geometry column drop-down menu to identify which column contains the geometry. Esri JSON encodes both geometry and feature information into objects. An Esri feature set is a collection of features with the same geometry type and coordinate system. In a JSON document, a feature set is represented by a JSON object.
    • Geography—When this option is selected, you can use an ArcGIS feature service depicting geographic boundaries to map your data as a polygon layer. Enter a geography type, such as US states or Canada provinces, in the search field and press Enter. Select the geography type you want to use and click Add Add.

      In the next pane, use the drop-down menus to choose a field from the feature service and match it to a column in your data.

      In the next pane, choose the attributes you want summarized on the map by selecting the appropriate column in the drop-down menu.

  3. Click Add to map.

Layer names

If you add data from Excel to a map using the cell range option, the layer name is automatically populated using the selected range—for example, $A$1:$I$21. If you plan to share the map or layer on ArcGIS, you must manually change the layer name before doing so. Otherwise, when the layer is published with the hosted feature service, unsupported characters in the layer name may cause unexpected issues when you perform various tasks in ArcGIS.

Unsupported characters include the following:

&

@

#

<

=

*

>

$

(

"

~

)

'

^

?

`

Use 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.

Note:

For information about configuring a custom location type for your organization, see Configure a custom locator in the ArcGIS for Office Installation Guide.

To add a location type, do the following:

  1. In the Add from Excel pane, open the Location types drop-down menu and choose Geography.

    An ArcGIS content pane appears, allowing you to search and browse for custom geographies and ArcGIS feature services.

  2. In the drop-down menu, select My organization or another option, such as My content.

    The ArcGIS content pane opens, listing items that are available in your ArcGIS organization.

  3. Search 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.
    • Use the drop-down menu to view and search for items in your content, groups, favorites, ArcGIS Living Atlas, ArcGIS, or the curated Boundaries list.
  4. Click Add Add to select the hosted service you want to use.

    A pane appears, instructing you to select a layer and a field to be joined with each of the attributes you want to map.

  5. From the first drop-down menu, choose a layer from the feature service to use as the location type. If there is only one layer, this layer is selected by default.

    From the second drop-down menu, choose a field in the layer that will be used to match your table data to the features in the hosted service.

    From the third drop-down menu, select a column in your table to join to the layer. Be sure the values in both places (table 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.

  6. Click Add to map.

    The Summarize attribute pane appears. In this pane, choose the attribute in your data that you want to summarize on the map. You can add multiple attributes. You can summarize integer attributes by Sum, Average, or Count. You can summarize string attributes by Mode.

  7. Click Add to map.

    The data is added to the map using the custom location type you selected.

Choose a 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 from Excel pane, choose Coordinates from the Location Types drop-down menu.
  2. Use the Longitude (X) and Latitude (Y) drop-down menus to choose the appropriate columns in your data to match to the x- and y-fields.
  3. Choose one of the available existing systems from the Spatial Reference drop-down menu, or manually enter a spatial reference system in the field.