Download this Population Change data file

Getting the FIPS Codes Right in Excel

The county FIPS codes will be used to join attribute data to your county geometries.

FIPS code needs to be 5-digits

Insert a blank column to the right of the original FIPS column.

Select the blank cell next to the first FIPS number. Type "=B2+1000000".

Then double-click on the green box in the bottom-right corner of the cell.

This is to add zeros to the beginning if some FIPS are not 5 digits.

Insert another blank column to the right of the new column (also format as text), take the text characters of the 5 right-most in the string.

Select the blank cell next to the first number of the last column we made. Click the fx button.

Select Right. In the formula builder, click into the Text box and click cell C2: this should be the first FIPS number plus 1000000. In the num_chars box, type 5. Click done.

Then double-click on the green box in the bottom-right corner of the cell.

Title this new column "FIPSTxt" and the first column we made "math"

Make the FIPSTxt column a Text column. Save as an .xlsx file and close.


Open QGIS. Save as datajoin.qgs.

Bring in the counties layer, state outlines layer, and the spreadsheet we just edited. Give the state outlines a transparent fill.

Right-click on the county layer, go to properties, click Joins, click the plus button.

In the Join window, select the spreadsheet we just edited for number 1, select FIPSTxt for number 2, and select FIPSTxt for number 3.

Check Dynamic Form. Check editable join layer. Check Joined Fields and check the fields you want to add from spreadsheet to the county layer. Check Custom Field Name Prefix. Write "E18_"

Click Join and wait a minute to make sure you do not overload the program while the join is occurring.

Double click on the county layer and go to the symbology tab, change single symbol to graduated. Use one of the newly joined columns to style your map. Remember to normalize if needed.

Choose a color ramp, change classes to five, and choose a classification method.

Get your highest values lined up with your most intense color. Represent your values as percentages. And, set the decimal places to 2.

Project to Albers Equal Area Conic for Contiguous 48, Alaska, and Hawaii and save. Export US 48, Alaska, Hawaii, and legend as .pdf files.

Design in Illustrator

Create an aesthetically pleasing map layout in Illustrator. Remember to release clipping masks.