i'm trying to create a shapefile from an excel spreadsheet. the spreadsheet contains some data such as "unknown record" for which no lat/long can be obtained while the rest of the data is related to states of a country.when i add spreadsheet to arcgis it displays "null" value under lat long field in front of unknown record and when i create the shapefile the null values are replaced by "0" which causes an inconsistent map extent. i don't want to create a csv as it distorts my rest of the data. i tried placing "0" for the unknown record in lat long fields but in that case lat long wont show in add xy dialog box.please suggest a workaround so that my problem of inconsistent map extent is resolved.
Shapefiles use dBase databases, which do not support null values. Any null values in numeric fields are stored as 0 and strings as a blank space (regardless of software). If you want true null value support, you need storage format that is null capable (such as a file geodatabase).
Alternatively, you can import the records that do have coordinates and create the shapefile, then paste in/append the records that don't have coordinates to make sure they are created with null geometries (related: Joining blank shp with dBASE Table).
Note this resulting shapefile won't be valid, as a null geometry in a shapefile is technically an error and if repaired that record will be deleted (related: Polygon shapefile with null values? and Difference between empty and NULL geometry in file geodatabase?). Or you can just set the lat/long values to a known 'false' value, like Null Island.