Excel County Checklists

Below is a Microsoft Excel spreadsheet showing county occurrence of Washington birds. The spreadsheet is designed to allow you to maintain your own county lists, by simply typing a 1 into each appropriate cell. In 2013, with help from Michael Hobbs, we introduced an enhanced version of the basic spreadsheet. Please download the instructions to learn about the many new features of the spreadsheet.

2017 Enhanced Macro Spreadsheet
(Requires Excel 2007 or later to use all the features on this version of the spreadsheet)

Instructions for using the Macro Spreadsheet
(Definitely worth reading through for details on the new spreadsheet)

2017 Traditional, non-macro checklist
(This simpler version should still work with Excel version 5/95)

Notes on Jan 2017 update:
No major spreadsheet enhancements, but a big sort order update, many county firsts and state firsts added, and a little cleaning up of the formatting:

1. Six additions to the state list - Least Auklet, Dusky-capped Flycatcher, Gray Wagtail, Field Sparrow, Blue Grosbeak, and the addition of one species by the split of Western Scrub-Jay into California and Woodhouse's Scrub-Jay.
2. Taxonomic updates published by the AOU's 57th checklist supplement. This update included a major shuffling of parts of the taxonomic sequence.
3. County firsts through December 2016.
4. Several additional code updates for Lincoln, Chelan and Clallam Counties.

For detailed instructions on how to begin using the spreadsheet or how to transfer data from an older copy to the newest one, continue reading below.
(notes from previous updates are stashed down the page, below the instructions on transferring data)


Instructions on transferring your data from an older copy of your spreadsheet into the new one:

Short version: Use the same tax sort order to sort both your old file and the new one [2015 AOU 56 order is a good choice if you've been using the 2016 version of the list].
Add blank rows into your old file the where new species will be inserted. For this update, that would mean inserting a row below Parakeet Auklet, Vermilion Flycatcher, Blue Jay, Eastern Yellow Wagtail, Brewer's Sparrow and Black-headed Grosbeak.
Check the row number for House Sparrow in both the new sheet and your modified data sheet to make sure both are the same.
Copy color-coded cells portion of old file, and paste "values" only into the new file [using paste-special].
Once your data is in the new sheet, double-check totals, then re-sort the new spreadsheet according to the new sort order [2016 AOU 57].

Longer Step-by-step Explanation:
1. Download newest file from webpage and save under a different name than your own file. Save a copy of your old file and use the only one copy for the following changes.

2. Open both the new file and your old one.

3. Make sure both old and new files are sorted according to the same taxonomic order. To do this, select the entire file and click 'sort' from the 'Data' menu. Check the box for Header Row, then select the desired sort order in the top box, with the default setting of Ascending selected. 2015 AOU 56 sort order might be the easiest, since the 2015 list was likely already in that order.

4. On your [old] file, insert blank rows [using the 'rows' option on the Insert menu] where new species will be added. For this update, that would mean inserting rows below Parakeet Auklet (for Least Auklet), Vermilion Flycatcher (for Dusky-capped Flycatcher), Blue Jay (for Woodhouse's Scrub-Jay), Eastern Yellow Wagtail (for Gray Wagtail), Brewer's Sparrow (for Field Sparrow) and Black-headed Grosbeak (for Blue Grosbeak).

5. After adding 6 rows you should be set to move your data. Check the row number for House Sparrow in both the new sheet and your modified data sheet to make sure both are the same.

6. On your [old] file, highlight all of the cells in the region with the color coding and press control-C to copy this section [or use the 'copy' option from the Edit menu]. In this version (once you've added the blank rows for the new state species) this would be all the cells from G4 to AT523.

7. Click on the G4 cell in the new file, and then choose 'paste special' from the edit menu. In the dialogue box that opens, select 'values' under the paste section [instead of 'all']. It is important to paste only the values -- otherwise you will bring over all the old color codes too!

All your data should be pasted into the new file.

8. Do some double-checking to ensure the formulas are adding up to the same numbers they did in the old file. Also check to be sure you haven't brought over the old color codes [check a 'county first' like the Clallam County Prothonotary Warbler to confirm the square is now colored red instead of blank (white)].

9. Check California & Woodhouse's Scrub-Jay rows to be sure all your 'ticks' are in the right place. For every sighting except a 2002 one in Asotin Co., all our Scrub-Jays have been California Scrub-Jays, so that's where all the ticks should most likely land.

10. Sort the new sheet into the newestsort order [2016 AOU 57] by repeating the directions in step 3.


That should do it. Let me know if you have any questions or suggestions.

Any questions or comments should be addressed to Matt Bartels, mattxyz@earthlink.net.


Notes on January 2016 update:
The latest update to the excel version of the WA County Checklist incorporates the latest county checklist updates at WA Birder and the taxonomic updates published by the AOU in 2015. This version of the checklist is based on the latest updates of county-firsts compiled by Ken and Laurie Knittle for Washington Birder, and covering information gleaned through December 2015.

For 2016, in addition to adding county and state firsts we've made a couple improvements.

First, we've added an 'eBird sort order' option. Because eBird's taxonomy does not match the AOU taxonomy, sorting the sheet by this column should make it easier to go back and forth between both options. For that matter, this ability to toggle between the two sort orders might be handy even if you don't fill in anything else on the spreadsheet!

In addition, we've added abundance codes for the entire state as well as for eastern and western Washington. These are by-and-large just reflections of the most-common level of abundance a species has for the region. For example, if a species was present at code 1 [blue] levels in some counties of western WA, but in eastern WA was most abundant only at code 3 [yellow] levels, then the Western WA code would be Blue, the Eastern WA code would be Yellow and the entire state code would be Blue.

Much as you could previously generate county 'need' lists with all the abundance codes, you can now also create needs lists for eastern WA, western WA and the entire state.

Other changes, coinciding with changes to the pdf files also on this site include:
1. Addition of two new species to the state list: Red-flanked Bluetail and Little Bunting, and removal of one species, Sedge Wren.

2. The taxonomic updates published by the AOU's 56th checklist supplement. This update included very little shuffling of the Washington taxonomy (two buteos switch places, and American Tree Sparrow gets a new scientific name)

3. County Firsts through December 2015. 4. Code updates for all counties. This past year, Laurie & Ken Knittle have worked with county listers across the state to update many abundances codes in every county.




Note on the enhanced 'macro' checklist, introduced in 2013:

In 2003, I made available an excel spreadsheet for use in easily tracking county life lists. It is based on the abundance codes maintained by Washington Birder [aka Ken and Laurie Knittle, with help from local birders], and I've updated it whenever new taxonomical changes and county firsts have come out. In 2013, Michael Hobbs put together this new version of the spreadsheet, using his magical skills as a programmer. The new version starts with the same basic format as the previous spreadsheet, but goes way beyond it in creating helpful tools for the county lister.

To give a few examples, the new spreadsheet will: Generate your 'county needs' and 'county life' list for every county; show you a color-coded map that illustrates your county list totals across the state; generate maps for any species that shows the county abundance county-by-county & which counties you do or do not yet have the species.

To get all these features, you only need to enter your data the same way as previously, on the main spreadsheet.

For those not ready for the "Enhanced" version, the standard old version of the spreadsheet [updated for the new year] is also still available.

My strongest recommendation: If you liked the old version you'll love the new version. Take a little time to plow through the instructions with a copy of the new spreadsheet open. Once you've got the hang of the various bells and whistles, it is easy to use. If you've never used the old version, there's no reason not to just dive in with the new one.


Contact Washington Birder: info@wabirder.com


Contact the web master at
Michael Hobbs