County map Excel

There are several objects in Excel you can make a map with. If you want to use shapes, you can follow my tutorial. You can also use a scatter plot. The map above uses cells and conditional formatting.

So, here is how to do it (this example used population data):

  1. Get a table from the US Census Bureau with latitude, longitude and population data by county.
  2. In a new sheet, calculate longitude intervals and use them as column headers (I’m using a 0.25 interval: lower means more resolution but more columns);
  3. Calculate latitude intervals and use them as row headers;
  4. With the SUMPRODUCT() function calculate total population, total area and density for each latitude and longitude class;
  5. Set column width to 5 or 6 pixels and do the same with row height;
  6. Select the entire table and add conditional formatting as you like;

Since you can’t add visible text to such small cells, you can merge them and add titles, legend, source, etc. A better option is to enter them in a separate sheet and use the camera tool to add an image to the map.

When you select a cell a small macro identifies the county and displays the data in the table below. At this resolution level, a cell usually contains a single county, but in densely populated areas there may be more than one county in a cell, as shown in the table (I’m also using the camera tool to display the table below the map).

I actually prefer to use a scatter plot but this is fun, and you can always ask your mother to cross stitch the map into your pillow!