A Step-By-Step Guide to Analyzing COVID-19 PPP Data by State

In this set of video tutorials, the National Press Foundation’s director of training, Chris Adams, leads viewers through the mechanics of using Excel to analyze the millions of records made available by the U.S. Small Business Administration’s Paycheck Protection Program. The PPP awarded loans to help small businesses stay afloat after COVID-19 ravaged the U.S. economy.

The three Excel tools to use:

Filter: The SBA has only released partial information on loan recipients. Loans are broadly divided into large and small, with slightly different information on each. The first step is to filter data by state and merge the small and large loans into one state-specific file.

Pivot table: Using this function allows a user to analyze loans by category – such as finding the total amount of loans by ZIP code or industry group. Other mathematical functions can be applied to the pivot table, such as the mean (average) or median of the category.

VLOOKUP: This function stands for “vertical lookup” and can be used to join two different databases. For PPP, that meant joining the NAICS code numbers for industry categories with their actual names.

Chris Adams
Director of training and content, National Press Foundation
Filters in Excel
Pivot Tables in Excel
VLOOKUP in Excel
Subscribe on YouTube
Help Make Good Journalists Better
Donate to the National Press Foundation to help us keep journalists informed on the issues that matter most.
You might also like
A New Tool for Tracking COVID Cash
Data Visualizations with Tableau
Mining Employment Data
Scraping Data From the Web