How To Create a GIS Ready Excel Spreadsheet

The following is a list of simple, easy to follow instructions for preparing an Excel spreadsheet that can be readily imported into ArcGIS. A small part of my job includes publishing GIS data to a read-only end-user map service for people in my organization to view. I created these instructions for the non-GIS savy individual who would like to see their data published in GIS. A lot of my time has been saved, I hope you will find this useful as well.

How to Prepare an Excel Spreadsheet for Use in ArcGIS

1. File Types & Naming

a. Save the file as .xls file type
b. Excel files in the Microsoft Office 2007 (.xlsx) format will not be recognized
c. Use a short file name without spaces, use an underscore (_)
d. Example: data_points.xls

2. Spreadsheet

a. The spreadsheet must NOT contain cell borders, shading, fill, pivot tables, hidden rows/columns, frozen rows/columns, locked rows/columns, frozen/split panes, cells with green triangles in the corners, etc. DO NOT merge rows or columns.
b. There should be NO header, footer or sidebar comments in the document. This includes summary lines, totals, references, citations, etc.

3. Font

a. Use a simple sans serif font, such as Arial
b. Do not underline, bold, italicize, highlight, or color text

4. Column Width

a. Set all column widths to auto-size
b. Select all cells > CTRL A
c. Choose Format > Column > Auto Fit Selection

5. Column Headings

a. File can contain only one header row; don’t create stacked headings
b. The first row must contain ONLY the names of the fields
c. Column names must be 64 characters or less; brief headings are preferable
d. Column names must begin with a letter
e. Columns names can contain ONLY letters, numbers, and underscores (_)
f.  NO spaces in column names
g. Use ALL CAPS with underscores (_) as spacers or use capital letters to distinguish words; example BLDG_NO or BldgNo; either method is OK but you must be consistent
h. NO punctuation except underscores (_)

6. Column One of Your Spreadsheet

a. Name this column ID
b. Number each row (1, 2, 3 . . . ) beginning with Row 2

7. Hyphens

DO NOT use hyphens. NEVER use hyphens. Hyphens are a NO_NO.

8. Columns & Cells

a. All cells in a column must be of the same data type
b. Cells can contain a maximum of 255 characters
c. Look for cells with lots of data and truncate the data if necessary

9. Cells with Text

a. Cells with text should be formatted as Text
b. If you wish to label features on the map with text, write the text exactly as you would like it to appear on the map. For example, Main Avenue or MAIN AVENUE or Main Ave
c. Be CONSISTENT

10. Cells with Number Values

a. Cells with numbers should be formatted as Number
b. DO NOT use 1000 separators
c. You can change cell format by highlighting the column(s), right clicking and selecting Format > Cells… > Number, Uncheck “Use 1000 Separator (,)”
d. If you are using whole numbers set the number of decimal places to 0

11. Latitude and Longitude Coordinates

a. Longitudinal coordinates should be under the column heading LONG
b. Latitudinal coordinates should be under the column heading LAT
c. Coordinates should be in decimal degrees; Example: 35.654923
d. Cells should be formatted as Number, with 6 decimal places
e. If you are West of the Prime Meridian, please include a negative (-) sign before the longitudinal coordinates; this negative sign is not a hyphen. Example: -87.512689
f. If you are South of the Equator, please include a negative (-) sign before the latitudinal coordinates; this negative sign is not a hyphen. Example: -26.325989

12. Linking to Existing GIS Data

a. If you wish to link your data to existing GIS data you must create a column (JOIN_ID) that will be used to “Join” the Excel spreadsheet to GIS data
b. Each value in the column that will be used for the Join must be (1) absolutely unique and (2) correspond to an existing data field in GIS
c. Please coordinate with your GIS point of contact to learn more about joining your data to GIS data