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.