Skip To Content

Add layers from Excel

In this topic

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 the currently selected Excel table or range of cells and suggests the best ways to represent it on the map, offering a selection of styles from which to choose. 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 and format considerations

Number of features

When you're creating a map, it's easy to get carried away and try to add a 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 may negatively impact the performance of ArcGIS Maps for Office.

For this reason, ArcGIS Maps for Office restricts the number of features you can add to a map at one time. Data import limits per layer are as follows:

  • Areas (polygons)—15,000
  • Lines—15,000
  • Features (points)—50,000

A map layer can contain a maximum of 1024 data columns.

If your data contains a large number of features, you can add them to the map in subsets; for example, if you have 100,000 features, create two layers containing 50,000 points each. To mitigate performance issues when panning and zooming, ArcGIS Maps for Office automatically clusters points on a layer that has more than 500 features. You can turn off clustering using the Cluster Points button on the map ribbon.

Data formats

  • Use Excel tables—In most cases, especially if you plan to enrich your data, it's recommended that you format your data as an Excel table before you add it to the map. Using an Excel table allows ArcGIS Maps for Office to add columns containing new information to the dataset. For more information, see Tables and named ranges.
  • 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 Maps 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 on ArcGIS will display in the format set by the map author before creating the layer.

    Some ArcGIS Maps 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 Maps 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

Add a quick map layer

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

  1. If you're not already signed in to your ArcGIS account, click the ArcGIS Maps tab on the Excel ribbon and click Sign In.
  2. Click anywhere on the map that you want to edit.

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

    Map editing tools

    Tip:

    If the map ribbon isn't visible, click HOME to expand it.

    Click HOME to expand map ribbon

  3. Click From Excel.

    The Add data from worksheet window opens, displaying styling options specific to your data.

    Add data from worksheet

  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 style options. 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.
      Note:

      Click Location Format to verify that ArcGIS Maps for Office found all the columns necessary to map your data. If you're having trouble getting your data on the map, it's likely that all your location columns were not identified correctly. You can manually select the location columns and try again.

    • 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 revenue, choose the column that contains the sales information. The style options list 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. Scroll through the available styling options.

    The currently selected styling option is highlighted, and a check mark appears beside it. Click another option to choose it.

Choose a data source

If your spreadsheet contains multiple Excel tables or 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 for your map, do the following:

  1. On the map ribbon, click From Excel.

    The Add Map workflow window opens.

    Add Map workflow window

    Note:

    You can also open this window from the Add data from worksheet window; click the Data drop-down arrow and choose More.

  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.

    The Add data from worksheet window opens. From here, you'll continue the workflow for adding data to your map by choosing a location type and styling option for your 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.

ArcGIS Maps for Office automatically uses the default geocoder (location type) set by your organization's administrator. Multiple geocoders 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 data from worksheet window, choose one of the default location types from the Location Type drop-down menu.

    If the location type you want isn't listed in the defaults, click More to display the Select a location type window.

    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.

    Note:

    Because county names may be duplicated across different states, if you choose the Counties (United States) location type, it is recommended that you also define the column that contains States to ensure accurate results on the map.

  4. Click Location Format.

    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 State/Province column in your data.

    Match columns from your worksheet to the chosen location type

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

    Note:

    For some location types, the information pane may extend beyond the visible area of the Add data pane. Be sure to scroll to the bottom of the pane to access the OK button.

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 worksheet 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 by location only, choose <None>.

    The styling options update to reflect suggested styles based on the specified location type.

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

    A confirmation window opens, listing the data to use to create the map layer, the location type, and the chosen styling option.

    Tip:

    To turn off confirmation for each new layer, check the Don't show this again check box. You can reenable confirmation at any time by changing the option in the map settings.

  3. Click Add data.

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

    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.

Note:

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

To add a location type, do the following:

  1. In the Add data from worksheet 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 box next to 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.

    Note:

    If the name already exists, the Add location type button is not available.

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

    Note:

    To remove a location type from the My Locations list, click the location type to select it and click Remove location type.

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, do the following:

  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 worksheet window, choose Coordinates from the Location Type drop-down menu and click Location format.
  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 text box, 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.

Fix data errors

If some of your data is invalid or missing when you create a map layer, ArcGIS Maps for Office creates the layer using all the valid data and reports any rows that failed to load. You can correct these errors directly in the map's Contents pane.

To fix errors in your data, do the following:

  1. In the Contents pane, click Fix errors in the message below the layer name.

    Feature failed to load

    The Fix error pane opens.

  2. Type the new information or correct the invalid field.

    Fix data errors

  3. Click Find again.

    ArcGIS Maps for Office searches for the updated information and refreshes the map.