Add layers from Excel

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

Considerations

When adding layers from Microsoft Excel, keep in mind the following recommendations.

Data formats

The following are best practices for formatting data:

• Use Excel tables—In most cases, it is recommended that you format the data as an Excel table before you add it to the map. When you use an Excel table, ArcGIS for Excel adds columns containing new information to the dataset. For more information, see the Prepare data section below.
• With a .csv file, you can either add features to the map by selecting cell ranges in the worksheet or format the data as an Excel table before adding it to the map.
• Use text values—Columns in a dataset that are used for location data (ZIP Codes, for example) must be formatted as textual values, not numerical values. If the data contains a number that includes a leading zero, Excel identifies these fields as numerical values and removes the leading zero, changing the original value. Formatting such columns as text ensures that the data remains accurate.
• Use time formats—When the data contains time-only fields (as opposed to date and time fields), ArcGIS for Excel converts the values to strings to display them in pop-ups. Because of this, unlike 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 and apply the time format to all relevant cells before creating the layer.

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

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

  Excel time format	ArcGIS for Excel 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 Excel automatically detects tables in the workbook for use in the Add data wizard.

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

Tip:

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

PivotTables are a type of table in Excel that are used to summarize and analyze data. When adding data, ArcGIS for Excel 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 the worksheet, click the map that you want to edit, 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 .

   The layers 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. The Data drop-down menu lists tables and cell ranges from the worksheet. The Location types drop-down menu lists the available location types.

   

5. From the Data drop-down menu, select the correct table or cell range.
6. From the Location types drop-down menu, select a location type.
7. Click Add to map.

Choose a dataset

By default, ArcGIS for Excel displays the dataset currently selected in the workbook. The drop-down menu lists the available data in Excel tables or selected cells. If the spreadsheet contains multiple tables, or if ArcGIS for Excel 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, click the Data drop-down menu.
2. Select a table or cell range to specify the areas in the worksheet that contain the data 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 Data 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.

   Tip:

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

Choose a location type

To add Excel data to a map, the 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 in ArcGIS.

The location type selected determines how the data is mapped and the available style options. If you choose Address or Coordinates, the locations are mapped as points. If you choose Geography or ESRI JSON Geometry, the data is mapped as polygons. You can also map the 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 Excel automatically uses the default location type set by your organization's administrator. Multiple location types can be configured, and any one of them 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 location types from the Location types drop-down menu.

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

2. Use the following options to choose and format the location type:

   • Coordinates—Use the Longitude (X) and Latitude (Y) drop-down menus to match columns in the data that contain coordinates. If necessary, use the Spatial Reference drop-down menu to change the selected 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 the table or cell range that contains address data. Use the Address drop-down menu to choose a column or columns containing 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 the data as a polygon layer. Provide a geography type, such as US states or Canada provinces, in the search field and press Enter. Select the geography type and click Add .

     In the Add from Excel pane, use the drop-down menus to choose a field from the feature service and match it to a column in the data. Select the appropriate column from the drop-down menu for the attributes to summarize on the map.

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 first. 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:

&	@	#
<	=	*
>	$	(
"	~	)
'	^
?	`

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 the 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 the data to match the x- and y-fields.
3. Choose one of the available existing systems from the Spatial Reference drop-down menu, or provide a spatial reference system in the text box.