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.

Copy Practice Sheet

Relevant Sections

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:

TopicMeasureAPI VariableData Type
DemographicTotal populationDP05_0001EValue
DemographicMedian age (years)DP05_0018EValue
DemographicUnder 18 (%)DP05_0019PEPercent
Demographic65 and over (%)DP05_0024PEPercent
SocialTotal householdsDP02_0001EValue
SocialHigh school grad or higher (%)DP02_0067PEPercent
SocialBachelor’s degree or higher (%)DP02_0068PEPercent
EconomicUnemployment rate (%)DP03_0009PEPercent
EconomicMedian household income ($)DP03_0062EDollar
EconomicPer capita income ($)DP03_0088EDollar
EconomicPersons in poverty (%)DP03_0119PEPercent
EconomicMean travel time to work (min)DP03_0025ETime
HousingHousing unitsDP04_0001EValue
HousingOwner-occupied housing rate (%)DP04_0046PEPercent
HousingRenter-occupied housing rate (%)DP04_0047PEPercent
HousingMedian home value ($)DP04_0089EDollar
HousingMedian gross rent ($)DP04_0134EDollar

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.

Leave a Comment