Formatting an Excel table for use in ArcGIS

By Aileen Buckley, Esri Cartographer

To use an Excel table in ArcGIS, it should first be formatted correctly so that ArcGIS can read the table without errors. This blog entry contains links to a table with guidelines for how to format your Excel table, as well as a couple of other useful related resources.

If you don’t format the Excel table correctly, you might run into problems when using tables from Excel when you:

• Add an Excel table to ArcMap as data
• Convert an excel table to a geodatabase table using the Excel to Table geoprocessing tool.
• Create a Join or Relate with an Excel table or a table converted from Excel.

The first two of these three cases were tested and are reported on here.

View the table with the guidelines.

The guidelines include references to special characters you cannot use when naming fields in ArcGIS and reserved words you cannot use when naming fields in Excel.

The guidelines are for the use of geodatabases and feature classes, not shapefiles. When using shapefiles, additional dbase-based restrictions will apply, such as limiting field names to 10 characters.

View the list of Special Characters in ArcGIS.

View the list of Excel Reserved Words.

Note that the Excel reserved words were extracted from the driver via ArcObjects. We have a function that returns the Keywords for a data source. In the case of Excel, it gets the list from the driver itself.

Excel Tables and ArcGIS Tables

This is an overview of the relationship between Excel tables and ArcGIS tables:

• A column in an Excel table is a field in ArcGIS. All rows in a table have the same columns. In a spatial table, columns store categories of information about the features, such as names, ID numbers, and area or length.
• A row in an Excel table is a record in ArcGIS. In a spatial table, each row represents one feature.
• A cell in an Excel table is a value in ArcGIS.

These are a few things to note about using Excel tables in ArcGIS:

• ArcGIS supports both Excel 2003 and earlier .xls files and Excel 2007 .xlsx files. One advantage of Excel 2007 is that it allows much larger worksheets (1,048,576 rows by 16,384 columns) than you can have in Excel 2003 (65,536 rows by 256 columns.)
• If you have an .xlsx file you want to use in ArcGIS but do not have Excel 2007 installed, you will need to install the 2007 Office System Driver. It can be downloaded from the Microsoft Download Center. If you have Microsoft Excel 2010 or no version of Microsoft Excel installed, you must install the 2007 driver before you can use either .xls or .xlsx files.
• Excel tables are read-only in ArcGIS as well as in Excel when you have a workbook open in ArcGIS.
• Excel workbook and worksheet names should not contain spaces.
• Field names are derived from the first row in each column of the worksheet.
• You can view the properties of fields, set aliases for the field names, and set field visibility on the Fields tab of the table’s Properties dialog box.

• Online Help: Understanding how to use Microsoft Excel files in ArcGIS
  http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/understanding-how-to-use-microsoft-excel-files-in-arcgis.htm
• Online Help: Formatting a table in Microsoft Excel for use in ArcGIS
  http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/formatting-a-table-in-microsoft-excel-for-use-in-arcgis.htm
• Online Help: Excel to Table Tool
  http://desktop.arcgis.com/en/desktop/latest/tools/conversion-toolbox/excel-to-table.htm
• Defining fields in tables
  http://desktop.arcgis.com/en/desktop/latest/manage-data/tables/design-defining-fields-in-tables.htm
• What are the reserved words for Esri’s file geodatabase? http://support.esri.com/en/knowledgebase/techarticles/detail/37763

Thanks to Veronica Rojas for helping me to test the various issues. Thanks to Lance Shipman and Colin Zwicker, Projects Engineers on the ArcMap Team, who helped in the review of this article. Any errors, however, are solely the responsibility of the author.

About the author

Aileen Buckley

Dr. Aileen Buckley has been making maps since she was an undergraduate student. She has a Bachelors in Geography and Spanish from Valparaiso University, a Masters in Geography from Indiana University, and a Ph.D. in Geography from Oregon State University. She is a senior product engineer on the Living Atlas team, and her work focuses on determining and sharing best practices for mapping and analysis with modern GIS. She publishes and presents world-wide on many aspects of mapping and GIS. She is a co-author of Map Use: Reading, Analysis, Interpretation, and she is a co-editor for the Atlas of Oregon. Aileen is a former president of CaGIS (the U.S. cartographic association) and is actively involved with the International Cartographic Association in which she is the lead delegate for the United States.

Connect: