Import GEOID as text, preserve original square meters, and distinguish checking a published rank from recalculating a rank after filtering.
A county CSV is easy to open and surprisingly easy to damage. A spreadsheet can remove the leading zero from a geographic identifier, treat a number as text, or sort a displayed rounded value instead of the more precise field. The worksheet may still look plausible after each mistake.
This workflow turns the US County Area download into a reproducible analysis. It uses the actual exported field names and gives you checks you can perform before trusting a chart or joining another dataset. No particular spreadsheet brand is required: use its text/CSV import command and inspect the column types before loading.
1. Import the file with the identifier protected
Download the CSV and use an import dialog rather than relying on a double-click. Set geoid to text. Keep names and state fields as text, and set area and rank columns to numeric types. The file contains a header and 3,222 data records, so the expected worksheet has 3223 rows including its header.
A county GEOID has five characters: a two-digit state code followed by a three-digit county code. For example, Autauga County, Alabama, is 01001. If your imported value is 1001, fix the import before building joins. Applying a five-digit display format may make the cell look correct without turning its stored value into a text identifier. Census explains the identifier structure.
2. Keep source fields and derived fields separate
landSqM and waterSqM preserve the original integer square-meter fields. landSqMi and waterSqMi preserve the source’s published square miles. totalSqMi is their sum at three decimal places. The national and state land ranks are calculations added by US County Area.
Preserve these original columns and put your own formulas in new columns. That makes a discrepancy traceable: you can compare the imported value, the formula, and the displayed result. Do not overwrite the square-meter field with a rounded square-mile conversion just because it is easier to read. Also retain the source vintage and retrieval date in a notes sheet; a saved CSV filename alone is a weak record of provenance.
3. Reproduce a total and a water share
With the current CSV header order, land square miles are in column G, water square miles in H, and total square miles in I. On the first data row, =ROUND(G2+H2,3) should match I2. For water share, use =IF(I2=0,0,H2/I2) and apply percentage formatting. Confirm the headers before copying a formula into a differently arranged worksheet.
Use Harris County, Texas as a spot check. Its 1,707.345 land square miles plus 69.694 water square miles equal 1,777.039 total square miles, with approximately 3.9% water. A correct result for one record does not prove every row is correct, but a failed spot check immediately reveals a column, type, or formula problem.
4. Sort the exact field and state the tie rule
To reproduce national land order, sort the entire table by landSqM descending, then by geoid ascending. Do not sort only the area column, which would separate measurements from their county names. Do not use the rounded display field if exact order matters. The site assigns sequential positions, including when exact values tie; the secondary identifier sort makes that ordering deterministic.
The largest original land value should belong to Yukon-Koyukuk Census Area. The smallest should belong to Falls Church city. You can compare your row positions with nationalLandRank. A built-in rank function may assign shared ranks to ties, so its behavior is not automatically identical to the site’s sequential ordering. Record the rule you choose rather than treating every ranking function as interchangeable.
5. Distinguish filtering a rank from creating a new rank
If you filter the file to Texas, the existing nationalLandRank column still describes the complete national dataset. It will contain gaps. The stateLandRank column describes the order within Texas. If you filter to a custom collection of counties, neither column becomes a rank within that collection unless you recalculate one.
Before joining population, economic, or other county tables, check that each dataset has one intended record per GEOID and compatible geography. Count unmatched identifiers in both directions and investigate them; do not silently discard them. Repeated identifiers may represent different years or subgroups rather than accidental duplicates. Select the intended observation before joining, or the join can multiply rows and inflate totals.
6. Save an audit trail with the result
Check record count, five-character identifiers, unique keys, nonnegative numeric areas, and the agreement between land plus water and total. Keep a list of excluded records and the reason for each exclusion. If you sum county areas into a state figure, describe it as a sum of this file’s county records rather than silently presenting it as a separately published state statistic.
Save the original download alongside your working copy and a short methods note. That note should name the release, columns, filters, formulas, units, tie rules, and output date. The methodology and the checksum on the Data page make it possible to identify the source archive. A trustworthy spreadsheet is one whose result another person can reconstruct, not merely one whose formatted table looks finished.
Sources and calculation notes
County examples and calculations use our pinned 2026 Census Gazetteer dataset. Ratios use published square-mile values; land rankings use original square meters. Examples labeled hypothetical are teaching examples, not population estimates.
- Original 2026 Census county measurements (ZIP)
- U.S. Census Bureau: Gazetteer files, coverage, and vintage notes
- U.S. Census Bureau: understanding geographic identifiers
How we calculate and verify the data · Download and reproduce the examples · Suggest a correction