Official websites use .gov
A .gov website belongs to an official government organization in the United States.
Secure .gov websites use HTTPS
A lock (
) or https:// means you’ve safely connected to the .gov website. Share sensitive information only on official, secure websites.
Topics
Data & Maps
Surveys & Programs
Resource Library
USA Counties: File Layout
USA Counties: File Layout
- Parts of the Excel Spreadsheet
- Four Excel Columns in Reference to One Data Source
- Introduction to MASTGROUPS
- Introduction to MASTDATA
- Introduction to Flag_Reference
- Introduction to Footnote_Reference
- Introduction to Unit_Reference
- Introduction to Source
- How do Excel Data Sources Relate?
- Additional Detail Information
| Part: | Description: |
|---|---|
| NOTE: More detail about data source (Excel column) names is available in II. Four Excel Columns in Reference to One Data Source and more detail about table (column) groups is available in III. Introduction to MASTGROUPS. | |
| A, B, C, & D | Four columns providing flag, datavalue, footnote1, and footnote2 data of original AFN110197D data source. Each additional data source will require four Excel columns, e.g. [Excel columns G, H, I, and J], or [Excel columns K, L, M, and N], etc. (Excel spreadsheet columns C and continuing right) |
| A | Flag column header - e.g. AFN110197F - flag data stored in this column (Excel spreadsheet column C) from rows 2 to 3197. |
| B | DataValue column header - e.g. AFN110197D - data values stored in this column (Excel spreadsheet column D) from rows 2 to 3197. |
| C | Footnote1 column header - e.g. AFN110197N1 - footnote1 values stored in this column (Excel spreadsheet column E) from rows 2 to 3197. |
| D | Footnote2 column header - e.g. AFN110197N2 - footnote2 values stored in this column (Excel spreadsheet column F) from rows 2 to 3197. |
| E | Column E header "Areaname" located in cell A1 - Areaname values stored in Excel cells A2 to A3197. |
| F | Microsoft standard column references, e.g. A, B, C, D, etc. |
| G | Code column header - FIPS State and County Code stored in this column (Excel spreadhseet column B) from rows 2 to 3197. |
| H | Microsoft standard row numbers, e.g. 1, 2, 3, 4, 5, ... |
| I | Sample of areaname values - values refer to 1) UNITED STATES, 2) a state name, or 3) a county name followed by comma and state abbreviation. (Excel spreadsheet column A) |
| J | Sample of STCOU values - text 5 positions - first two positions refer to state code and last three positions refer to county code. It should be noted that all states have "000" in their last three positions, e.g. "01000". Moreover, the UNITED STATES code is always "00000". (Excel spreadsheet column B) |
| K | Sample of FLAG values - integer value of 0, 1, 2, 3, 4, 5, 6, 7 or 8.(Excel spreadsheet column C) |
| L | Sample of Data values - long integer format e.g., 545068, 6955, 50, etc. (Excel spreadsheet column D) |
| M | Sample of Footnote1 values - text 4 positions - each unique footnote code has corresponding note which is available in the MASTFOOT table. (Excel spreadsheet column E) |
| N | Sample of Footnote2 values - text 4 positions - each unique footnote code has corresponding note which is available in the MASTFOOT table. (Excel spreadsheet column F) |
Deciphering data group names
Data groups names have four components:
- 3-letter abbreviation/acronymn
- 3-digit subgroup-code
- 3-digit year code
- First number represents the century (1 = a year in the 20th century, ie. 1997; 2 = a year in the 21st century, ie. 2002).
- Last two numbers represent the year.
- data type: F = flag, D = data value, N1 = Footnote 1, N2 = Footnote 2
For example the data group, AFN110197D, would be:
- AFN - Accommodation and Food Services - NAICS Only
- 110 - subgroup 110
- 197 - 1997
- D - data value
| Part: | Description: |
|---|---|
| NOTE: More detail about data source (Excel column) names and descriptions is available in IV. Introduction to MASTDATA. | |
| A | The first three characters make up a three-character abbreviation (acronym) for a particular data group. In this case, "AFN" stands for "Accommodation and Food Services - NAICS Only." All acronyms and their corresponding title are available in the Excel file MASTGROUPS.xls. |
| B | The first three digits (positions 4-6) makes up a subgroup-code of the group (positions 1-3). There is no one pattern; each group has its own series of sub-groups, e.g. 110, 120, 130, 200, 210, etc. |
| C | The next three digits (positions 7-9) represent the year of the data. Position 7 may be 1 or 2; 1 is for a year in the 20th century and 2 is for a year in the 21st century. Positions 8-9 are the last two digits of a particular year. For example, 197 stands for 1997 and 207 stands for 2007. |
| D | The last character "D" (position 10) stands for "data". However, data from one data source file is loaded into four Excel columns; as an example, for the data source AFN110197D, there would be four Excel columns AFN110197F, AFN110197D, AFN110197N1, AFN110197N2 (used for Flag, DataValue, Footkey1 data, Footkey2 data) |
| E | This column is FLAG data (Excel spreadsheet column C) |
| F | This column is the data value (Excel spreadsheet column D) |
| G | This column is FOOTKEY1 data (Excel spreadsheet column E) |
| H | This column is FOOTKEY2 data (Excel spreadsheet column F) |
Q: What is MASTGROUPS.xls?
A: MASTGROUPS.xls is an Excel file that has two columns KeyAbr and Descript. A three character abbreviation (acronym) for its official title is listed under KeyAbr, and the official title is listed under Descript.
Q: Can I download MASTGROUPS? What does MASTGROUPS data look like?
A: Yes. The MASTGROUPS data information can be downloaded as an Excel file or as a ZIP file.
Listed below are examples of standard groups.
Key Abbreviation - Description
AFN - Accommodation and Food Services - NAICS only
AGE - Age
AGN - Agriculture - NAICS
AGS - Agriculture - SIC
ANC - Ancestry
BNK - Banking
BPS - Building Permits
BZA - Business, Private Nonfarm - (U.S. Census Bureau) - Total
BZN - Business, Private Nonfarm - (U.S. Census Bureau) - NAICS
BZS - Business, Private Nonfarm - (U.S. Census Bureau) - SIC
CLF - Civilian Labor Force (Bureau of Labor Statistics)
CRM - Crime
EAN - Earnings, Total and Selected Industries (Bureau of Economic Analysis) - NAICS
EAS - Earnings, Total and Selected Industries (Bureau of Economic Analysis) - SIC
EDU - Education
ELE - Elections (CQ Press)
EMN - Employment (Bureau of Economic Analysis) - NAICS
EMS - Employment (Bureau of Economic Analysis) - SIC
FED - Federal Government
GAN - Government (Bureau of Economic Analysis) - Earnings - NAICS
GEE - Government (Bureau of Economic Analysis) - Earnings/Employment - SIC
GEN - Government (Bureau of Economic Analysis) - Employment - NAICS
HEA - Health
HIS - Hispanic or Latino Population
HSD - Households
HSG - Housing
INC - Income, Money (U.S. Census Bureau)
IRS - Internal Revenue Service - Individual Taxes
IPE - Income and Poverty
LFE - Labor Force (U.S. Census Bureau)
LND - Land Area
LOG - Local Area Government
MAN - Manufactures - NAICS
MAS - Manufactures - SIC
NES - Nonemployer Statistics
PEN - Personal Income (Bureau of Economic Analysis) - NAICS
PIN - Personal Income (Bureau of Economic Analysis) - SIC
POP - Population - Total and Selected Characteristics
PPQ - Population - Group Quarters
PST - Population Estimates
PVY - Poverty
RHI - Race data
RTN - Retail Trade - NAICS
RTS - Retail Trade - SIC
SBO - Survey of Business Owners
SEX - Sex data
SPR - Social Programs - Social Security and Supplemental Security Income (SSI)
VET - Veterans
VST - Vital Statistics - Births, Deaths, and Infant Deaths
WAT - Water Usage
WTN - Wholesale Trade - NAICS
Q: What is MASTDATA.xls?
A: MASTDATA.xls is an Excel file that has six columns Item_id, Item_description, Unit_indicator, Decimal_indicator, US_Total, and Source. To help identify what specific Excel data you are looking for, the Item_Id identifies the table series (as discussed in Part II.)
Q: What are Item_id and Item_description?
A: Item_Id AFN110197D refers to the Excel columns (AFN110197F, AFN110197D, AFN110197N1, AFN110197N2). The title of AFN110197D is provided in the Item_description column.
Q: What are Unit_indicator, Decimal_indicator, US_Total, and Source?
A: Unit_indicator refers to the measurement units applied to the data values (in a D column, e.g. AFN110197D); see section VII. for more information. Decimal_indicator refers to number of decimal places involved in data-value (e.g. with a decimal_indicator = 1 and with a data-value = 102, this implies 10.2 is the actual value). The US_Total is the sum total of all state and county values in a table. The Source value specifies the provider of the data information.
Q: Can I download MASTDATA? What does MASTDATA data look like?
A: Yes. The MASTDATA data information can be downloaded as an Excel file or as a ZIP file.
| Item_ID | Item_Description | Unit_indicator | Decimal_indicator | US_Total | Source |
|---|---|---|---|---|---|
| AFN110197D | Accommodation and Food Services: total (NAICS 72) - establishments with payroll 1997 | ABS | 0 | 545068 | CENSUS |
| AFN110202D | Accommodation and Food Services: total (NAICS 72) - establishments with payroll 2002 | ABS | 0 | 565590 | CENSUS |
| AFN120197D | Accommodation and Food Services: total (NAICS 72) - sales of establishments with payroll 1997 | TH$ | 0 | 350399194 | CENSUS |
| AFN120202D | Accommodation and Food Services: total (NAICS 72) - sales of establishments with payroll 2002 | TH$ | 0 | 449498718 | CENSUS |
| AFN130197D | Accommodation and Food Services: total (NAICS 72) - annual payroll 1997 | TH$ | 0 | 97007396 | CENSUS |
| AFN130202D | Accommodation and Food Services: total (NAICS 72) - annual payroll 2002 | TH$ | 0 | 127554483 | CENSUS |
| AFN140197D | Accommodation and Food Services: total (NAICS 72) - paid employees for pay period including March 12, 1997 | ABS | 0 | 9451226 | CENSUS |
| AFN140202D | Accommodation and Food Services: total (NAICS 72) - paid employees for pay period including March 12, 2002 | ABS | 0 | 10120951 | CENSUS |
Q: What is Flag_Reference.xls?
A: Flag_Reference.xls is an Excel file that has two
columns Id and Description.
Q: What are Id and Description?
A: Excel Flag_Reference.xls column, e.g. AFN110197F has an ID value of 0, 1, 2, 3, 4, 5, 6, 7, or 8. These
are the nine valid "flag" values. The Description field provides
an explanation of its corresponding "ID" value. However, the
Flag_Reference.xls file currently defines the flag values of 3, 4, 5, 6, 7, and
8.
Q: Can I download Flag_Reference data? What does Flag_Reference data look like?
A: The Footnote_Reference data information can be downloaded as an Excel file or as a ZIP file.
| ID | DESCRIPTION |
|---|---|
| 3 | Data not enumerated, tabulated, or otherwise available separately |
| 4 | Figure withheld to avoid disclosure pertaining to a specific organization or individual. |
| 5 | Figure not applicable because column heading and stub line make entry impossible, absurd, or meaningless. |
| 6 | Figure does not meet publication standards for reasons other than that covered by the ID 8 below. |
| 7 | Entry would amount to less than half the unit of measurement shown. |
| 8 | Base figure too small to meet statistical standards for reliability of a derived figure. |
Q: What is Footnote_Reference.xls?
A: Footnote_Reference.xls is an Excel file that has two columns, Id and Description.
Q: What are ID and Description?
A: Excel footnote columns, e.g. AFN110197N1 and AFN110197N2 usually have default values of "0000" in each cell. However, some cells have a non-zero footnote value in their cell, e.g. 5002, 5016, etc. To help understand what these values mean, you can use the Footnote_Reference.xls file. The Id field holds the unique footnote (4 text characters) and the Description field provides a definition of the footnote code.
Q: Why are there two footnote columns?
A: In reference to each Item_id in MASTDATA, e.g. AFN110197D, AFN110202D, etc, each of their records is entitled to a maximum of two footnotes at the most. However, very few use two footnotes. Most records (federal, state, county) only have one footnote or else a default value of '0000'. Because it is possible to have two footnotes, this is why each Item_id in MASTDATA has two Excel columns, e.g., AFN110197N1 and AFN110197N2 for the Item_id of AFN110197D.
Q: Can I download Footnote_Reference data? What does Footnote_Reference data look like?
A: The Footnote_Reference data information can be downloaded as an Excel file or as a ZIP file.
| ID | DESCRIPTION |
|---|---|
| 0000 | (No Description) |
| 5002 | Independent city of Bedford included with Bedford County; data not available separately. **51515 incl. with 51019. |
| 5003 | Independent city of Bristol included with Washington County; data not available separately. **51520 incl. with 51191. |
| 5004 | Independent cities of Buena Vista and Lexington included with Rockbridge County; data not available separately.**51530 and 51678 incl. with 51163. |
| 5005 | Independent city of Charlottesville included with Albemarle County; data not available separately. **51540 incl. with 51003. |
| 5006 | Independent cities of Clifton Forge and Covington included with Alleghany County; data not available separately. **51560 and 51580 incl. with 51005. |
| 5007 | Independent city of Colonial Heights included with Chesterfield County; data not available separately. **51570 incl. with 51041. |
| 5009 | Independent city of Covington included with Alleghany County; data not available separately. **51580 incl. with 51005 |
| 5010 | Independent city of Danville included with Pittsylvania County; data not available separately. **51590 incl. with 51143 |
Q: What is Unit_Reference.xls?
A: Unit_Reference.xls is an Excel file that has two columns, Id and Description.
Q: What are Id and Description?
A: First, data values may occur as a long integer or float (decimal) number. However, in reference to different Item_ids, e.g. AFN110197D, POP110202D, WAT110202D, etc. data may vary in reference to unit being used. The acronyms for the different units are available in the column Id. The descriptive meaning of Id is stored in the column Description.
Q: Can I download Unit_Reference data? What does Unit_Reference data look like?
A: The Unit_Reference data information can be downloaded as an Excel file or as a ZIP file.
| ID | DESCRIPTION |
|---|---|
| ABS | Absolute Number |
| ACR | Acres |
| AVG | Average |
| DOL | Dollars |
| GLD | Gallons per Day |
| MGD | Millions of Gallons per Day |
| MIL | Millions |
| MIN | Minutes |
| ML$ | Millions of Dollars |
| PCT | Percent |
| RNK | Rank |
| RTE | Rate |
| SQM | Square Miles |
| SYM | Symbol |
| TH$ | Thousands of Dollars |
| THO | Thousands |
| YRS | Years |
Q: What is Source?
A: Source.xls is an Excel file that has two columns Id and Description.
Q: What are Id and Description?
A: First, Id values are acronyms for the values in Description. Description provides the full-title of an organization providing data. Both of these fields are text data. The Source values also are used in the MASTDATA.xls file.
Q: Can I download Source data? What does Source data look like?
A: The Source data is located in the Source.xls file and can be downloaded as an Excel file or as a ZIP file.
| ID | DESCRIPTION |
|---|---|
| AMA | American Medical Association |
| BLS-CPS | Bureau of Labor Statistics-Current Population Survey |
| CDC-NCHS | Center for Disease Control-National Center for Health Statistics |
| CENSUS-1940 | U.S. Census Bureau-Census 1940 |
| CENSUS-1950 | U.S. Census Bureau-Census 1950 |
| CENSUS-1960 | U.S. Census Bureau-Census 1960 |
| CENSUS-1970 | U.S. Census Bureau-Census 1970 |
| CENSUS-1980 | U.S. Census Bureau-Census 1980 |
| CENSUS-1990 | U.S. Census Bureau-Census 1990 |
| CENSUS-2000 | U.S. Census Bureau-Census 2000 |
| CENSUS-2010 | U.S. Census Bureau-Census 2010 |
| CENSUS-ACS | U.S. Census Bureau-American Community Survey |
| CENSUS-AGRIC | U.S. Census Bureau-Census of Agriculture |
| CENSUS-CBP | U.S. Census Bureau-County Business Patterns |
| CENSUS-CONST | U.S. Census Bureau-Construction Division |
| CENSUS-ECON | U.S. Census Bureau-Economic Census |
| CENSUS-GOVTS | U.S. Census Bureau-Governments Division |
| CENSUS-NONEMP | U.S. Census Bureau-Nonemployer Statistics |
| CENSUSPOPEST | U.S. Census Bureau-Population Estimates |
| CENSUS-SAHIE | U.S. Census Bureau-Small Area Health Insurance Estimates |
| CENSUS-SAIPE | U.S. Census Bureau-Small Area Income and Poverty Estimates |
| CQ Press | SAGE Publications |
| DOC-BEA | Department of Commerce-Bureau of Economic Analysis |
| DOE-NCES | Department of Education-National Center of Educational Statistics |
| DOJ-FBI | Department of Justice-Federal Bureau of Investigation |
| FDIC | Federal Deposit Insurance Corporation |
| HHS-CMS | Health and Human Services-Centers of Medicare and Medicaid Services |
| SSA | Social Security Administration |
| Treasury-IRS | Department of Treasury-Internal Revenue Service |
| USDA-NASS | U.S. Department of Agriculture-National Agriculture Statistics Service |
| USGS | United States Geological Survey |
Sections I. and II. introduced the parts of an Excel spreadsheet and how four Excel columns relate to one data source.
Section III. - Section VII. introduce the MASTGROUPS.xls, MASTDATA.xls, Flag_Reference.xls, Unit_reference.xls, and SOURCE.xls files. How do these files relate to each other? To help simplify the relationship between an Excel data sheet (e.g. AFN01.xls, BZA01.xls, POP01.xls, etc.) and the MASTGROUPS.xls, MASTDATA.xls, Flag_Reference.xls, Unit_Reference.xls, and SOURCE.xls files, see the diagram below.
Q: Where can I find additional information on MASTDATA, MASTGROUPS, data type, file formats, finding correct data file, finding corresponding Excel columns, etc.?
A: For information on standard USA Counties data, Flag_reference, Footnote_reference, Mastdata, Mastgroups, Source, and Unit_Reference files, please visit ==> Basic Data File Information
Q: Where can I find information on data quality?
A: For comments on data quality, see Appendix A: Source Notes and Explanations [PDF, <1.0 MB] from the 2007 edition of the County and City Data Book
Q: Where can I find additional information?
A: For further information visit https://ask.census.gov
Q: What is the Margin of Error (MOE)?
A: Some American Community Survey (ACS) products provide an Margin of Error (MOE) instead of confidence intervals. An MOE is the difference between an estimate and its upper or lower confidence bounds. Confidence bounds can be created by adding the MOE to the estimate (for the upper bound) and subtracting the MOE from the estimate (for the lower bound). All published ACS MOEs are based on a 90 percent confidence level.
Source: American Community Survey - Methodology - Glossary of Terms [PDF <1.0 MB]
See Also: American Community Survey - Methodology
Source: U.S. Census Bureau
Page Last Revised - December 16, 2021
✕
Is this page helpful?
Yes
No
Yes
No✕
NO THANKS
255 characters maximum
255 characters maximum reached
255 characters maximum reached
✕
Thank you for your feedback.
Comments or suggestions?
Comments or suggestions?
