U.S. Census Data API Template in Google Sheets

The U.S. Census Data API Template lets anyone pull reliable, up-to-date demographic, economic, social, and housing data directly from the Census Bureau—no coding required.
Users simply enter their free API key, choose a year, state, county, and measures, and the sheet automatically retrieves clean, formatted results from the American Community Survey (ACS) 5-Year Data Profile.

Get your free copy of the template below and start exploring U.S. Census data today.

Relevant Sections

• Step-by-Step Overview
• What is the American Community Survey?
• How the Template Works
• Included Measures
• Why the Census API Matters

Step-by-Step Overview

1. Request your API key
   Get a free key at api.census.gov/data/key_signup.html and paste it into the API Key sheet.
   Your key stays private in your copy of the template—Simplify Sheets never accesses it.
2. Select a year
   On the Census Data sheet, pick the end-year (for example 2023).
   The sheet automatically treats this as a five-year period (2019–2023).
3. Select a region
   Choose a State and County for each row you want to analyze.
4. Select census measures
   Each column header offers a dependent dropdown: choose a Topic (Demographic, Social, Economic, Housing) and then a Measure.
5. Review your results
   Data automatically populates the table and formats correctly; population as a value, education as a percentage, median household income as dollars, and commute time as minutes.
   • Value → comma-formatted number
   • Percent → whole %
   • Dollar → $ with commas, no decimals
   • Time → minutes (e.g., 33 min)
6. View data source
   Each row includes a clickable link:
   View source: ACS 5-Year {Year} – {County, State}
   The link opens the raw API response so users can verify metrics directly.

What is the American Community Survey?

The American Community Survey (ACS) 5-Year Estimates are one of the most reliable sources of community-level data in the United States. Collected continuously by the U.S. Census Bureau, the ACS 5-Year combines five years of responses to provide detailed, statistically stable insights on population, housing, education, income, and employment for every county, city, and neighborhood—large or small.

This matters because it lets researchers, businesses, and policymakers make informed decisions using consistent, comparable data across time and geography.

The Simplify Sheets – U.S. Census Data API Template makes this information instantly accessible in Google Sheets. Instead of manually searching datasets or downloading tables, users can pull live ACS data directly from the Census API, visualize it in seconds, and build custom dashboards that update automatically as new data is released each year.

We made this sheet to help make this data more accessible and use this opportunity to help users understand how powerful Google Sheets can be!

How the Template Works

The Core Formula

=LET(
  yr,$A$2,
  var,XLOOKUP(E$3,Variables!$L:$L,Variables!$M:$M,""),
  st,TEXT(XLOOKUP($C4,Variables!$E:$E,Variables!$F:$F,""),"00"),
  co,TEXT(XLOOKUP($C4&"|"&$D4, Variables!$H:$H, Variables!$I:$I, ""),"000"),
  key,'API Key'!$A$4,
  dtype,XLOOKUP(E$3,Variables!$L:$L,Variables!$N:$N,""),
  IF(OR(yr="",var="",st="",co=""),"",
    LET(
      t,IFERROR(IMPORTDATA("https://api.census.gov/data/"&yr&
        "/acs/acs5/profile?get=NAME,"&var&
        "&for=county:"&co&"&in=state:"&st&
        "&key="&key),""),
      val,VALUE(INDEX(t,2,3)),
  IF(t="","",
    SWITCH(dtype,
      "Percent", TEXT(val/100,"0%"),
      "Dollar",  TEXT(val,"$#,##0"),
      "Time",    TEXT(val,"0") & " min",
      "Value",   TEXT(val,"#,##0")
          )
        )
      )
    )
  )

Our goal

At a high level, this formula automates an API request to the U.S. Census Bureau and formats the returned value inside Google Sheets. It takes a few key inputs selected by the user (year, state, county, and chosen metric) and turns them into a live query that fetches the selected ACS 5-Year data. The logic can be summarized as:

• Inputs: the user sets the ACS end-year, picks a state and county, and selects a measure (such as population or income).
• Goal: use those inputs to build a valid API URL, retrieve the corresponding number from the Census API, and format it based on its data type.

Functions in action

• LET – defines local variables so the long expression stays readable and efficient.
• XLOOKUP – matches the selected measure label to its Census API variable ID and identifies its data type for formatting.
• FILTER – returns the correct county FIPS code based on the selected state and county.
• IMPORTDATA – calls the Census API endpoint and imports the JSON-style CSV response directly into the sheet.
• INDEX + VALUE – extract the numeric value from the API response and convert it from text to a number.
• TEXT – formats the result according to the data type: comma-separated Value, whole-number Percent, Dollar with $ symbol, or Time in minutes.

Together these functions build a fully dynamic API call where each cell acts independently. No scripts, no add-ons, no manual refresh. It’s a lightweight, formula-only way to connect live government data to an interactive Google Sheet.

Included Measures

The hidden Variables sheet includes a curated set of commonly used ACS metrics:

Topic	Measure	API Variable	Data Type
Demographic	Total population	DP05_0001E	Value
Demographic	Median age (years)	DP05_0018E	Value
Demographic	Under 18 (%)	DP05_0019PE	Percent
Demographic	65 and over (%)	DP05_0024PE	Percent
Social	Total households	DP02_0001E	Value
Social	High school grad or higher (%)	DP02_0067PE	Percent
Social	Bachelor’s degree or higher (%)	DP02_0068PE	Percent
Economic	Unemployment rate (%)	DP03_0009PE	Percent
Economic	Median household income ($)	DP03_0062E	Dollar
Economic	Per capita income ($)	DP03_0088E	Dollar
Economic	Persons in poverty (%)	DP03_0119PE	Percent
Economic	Mean travel time to work (min)	DP03_0025E	Time
Housing	Housing units	DP04_0001E	Value
Housing	Owner-occupied housing rate (%)	DP04_0046PE	Percent
Housing	Renter-occupied housing rate (%)	DP04_0047PE	Percent
Housing	Median home value ($)	DP04_0089E	Dollar
Housing	Median gross rent ($)	DP04_0134E	Dollar

Users can easily expand this list by adding new rows with:

• Topic | Measure | API Variable | Data Type

The API Key sheet includes a direct link to the official ACS API variable list — for example, Explore Available ACS 2023 5-Year API Variables. From there, users can discover all available metrics in the Census API. You can easily extend the template by adding new Topics, defining a Measure description, including the official API Variable (NAME), and specifying its Data Type (Value, Percent, Dollar, or Time). This flexibility makes the sheet fully dynamic and customizable, allowing it to adapt to your specific data and analysis needs.

Why the Census API Matters

The U.S. Census Bureau API gives direct, consistent access to the same data used in federal research and policy.
With this sheet, you can:

• Track demographic or economic changes by county
• Compare markets for business expansion
• Build dashboards that update annually
• Explore the API documentation to design your own queries

Each API call uses a transparent URL so users can click and verify the exact data source.

Who This Template Is For

• Analysts who need consistent, source-verified Census data
• Small-business owners exploring local markets
• Students and educators learning data analysis
• Anyone building dashboards with reliable government data
• Anyone looking to better understand Google Sheets or API functionality

Have questions or suggestions?

Leave a comment below if you’d like help using the sheet or want to see additional features or data added in future updates.