Retrieve data from the Census Bureau API in Excel

From data.census.gov to Excel

For many use cases, a simple download from data.census.gov is sufficient.

Downloading as Excel or CSV creates a table that looks very much like the table on your screen. Download as ZIP creates a .zip file with Metadata, annotations and a .csv datafile with variables as columns and geographies as rows. Often this is not the end of the workflow and values for different variables need to be summed and/or geographies need to be combined.

Census Bureau's APIs

The Census Bureau APIs (API stands for Application Programming Interface) allow for a very targeted data retrieval. In one API call, you can retrieve data from one or more datatables and precisely define for what geographies. Getting that data into Excel could make the workflow a lot more efficient than working with whole table downloads.

Getting results from a single API call into Excel

• Starting in the browser:
  
  1. Make sure to add "&outputFormat=csv" to the API call.
  2. This creates a .csv from the API results and automatically downloads it into your default download folder.
  3. This .csv file can be opened in Excel.
• Starting in Excel:
  
  1. The Data ribbon contains an option to Get Data From the Web ("From Web").
  2. Clicking this opens a dialog where you can type the API call. Make sure to add "&outputFormat=csv" to this API call.
  3. After clicking OK, Excel retrieves the data and presents the result with an option to make some changes.
  4. Accepting the default (click Load) puts the API results in an Excel Table.
  5. Excel also creates a Query. This query contains step by step instructions for Power Query that resulted in this Table. The Data ribbon allows to see the current queries linked to your workbook and editing a query opens the Power Query Editor. Power Query is built into Excel and allows for all kinds of data manipulation (optional). One example of use is preventing the transformation of FIPS codes into numbers.
• Starting in Power Query:
  Power Query is built into Excel and allows for all kinds of data manipulation. If you know you want to calculate new columns, filter your rows, aggregate your rows (geographies) before loading into Excel, Power Query might be a good option. One advantage is that all steps are retained in the Query which can be good for validation and repeat use.
  1. On the Data Ribbon is a drop down menu Get Data, select Launch Power Query Editor.
  2. In the editor select New Source -> Other Sources -> Web.
  3. Clicking this opens a dialog where you can type the API call. Make sure to add "&outputFormat=csv" to this API call.
  • View -> Query Settings adds a panel displaying the Query steps
  • Advanced Editor opens an editor with all the steps using a Power Query syntax
  • Transform lets you Group rows and calculate statistics (e.g. sums)
  • Add Column lets you calculate new columns
  • The drop down arrow in the columns lets you sort columns and filter by value

Getting results from multiple API calls into Excel

A single API lets you retrieve data from a single year and a single data product, for example ACS Detailed tables, ACS Data Profiles or 2020 DHC. The options to combine data retrieval from multiple geogragraphic summary levels in a single API is also limited, for example you can not combine getting data for the state and all counties in the state in a single API.

This means that often there is a need for multiple API calls.

Another drawback of many workflows described above is that information about where the data originate is not automatically saved with the result.

One way to get results from multiple API calls is creating multiple worksheets and repeating the steps above for each worksheet. The Power Query option allows for combining results from multiple sources (API calls).

Excel macros can automate some of the steps and the API2Excel Demo Excel file that can be downloaded here is an example of how that could work.

API2Excel Demo Excel file

The ultimate goal of this project was to develop a set of Excel macros that call on the Census Bureau APIs and create a table with a single indicator (direct or derived) from each API call in the columns and a list of geographies as rows.

• The user can define one or more geography calls that will be used to define the rows. Currently all these calls call on a single data product and the choice of data product deserves some consideration. What year, ACS or Decennial, the 2023 geography dataset? The macros will automatically generate a total row for each geography call that contains more then one geography. This can be helpful to get data on regions, neighborhoods etc.
• A second worksheet focuses on the columns. Each row in the settings defines a column name, contains information to define the data set and year to retrieve data from, a list of variables to retrieve and the calculation needed to transform the downloaded variables into a single indicator. There is an option that instructs the macro to generate an aggregate indicator for each group of geographies. The macro will sum the fields in the API and than apply the same formula to create the variable.

The worksheet makes it easier to create data profiles, time series, combine geographies and retrieve data from different sources. Because the settings and results are in the same workbook, it is easier to reuse and verify.

Some experience with working with the Census Bureau APIs is recommended before starting with this workbook.

The workbook itself contains some instruction on how to use it, and so does the presentation given at the 2025 ACS data user conference.

In some cases Excel will block macros from running. Microsoft has a page with some things to try to allow this workbook to run. The first step is of course trusting the source and/or scan for malicious code.

DISCLAIMER: The workbook can be used freely as-is and further developed for your own purposes. It is a proof of concept and not guaranteed to work under all circumstances. Sharing your work with the data user community is encouraged if you make significant improvements.