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.

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

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

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

Notes on Jan 2020 update:
Enhancements, a modest set of sort order changes, several county firsts, three state firsts.
1. Enhancements: Ability now to create one page county checklist w/ all your species checked off, reached by selecting 'control-k.'
2. Three addition to the state list this year: Ruby-throated Hummingbird, Crested Auklet and Red-footed Booby.
3. Taxonomic updates published by the AOU's 60th checklist supplement. Small shuffles in the taxonomic sequence in the plovers, swallows and sparrows.
5. Several county firsts, and updates especially to the Clallam, Clark, Cowlitz, Ferry, Lewis, Skamania, & Wahkiakum county lists.
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 [the AOU 59 order is good if you updated in 2019].
Add blank rows into your old file the where new species will be inserted. For this 2020 update, that would mean inserting a row below White-throated Swift, Least Auklet, and Brown Booby.
Check the row number for Dickcissel in both the new sheet and your modified data sheet to make sure both are the same after these additions and deletions. Be sure the new file is sorted in the same order before pasting in your data.
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 [2019 AOU 60].

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 only the 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. If you updated last year, '2018 AOU 59' sort order would be the easiest.

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 White-throated Swift (for Ruby-throated Hummingbird), below Least Auklet (for Crested Auklet), and below Brown Booby (for Red-footed Booby).

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

7. 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 C4 to AP523.

8. Click on the C4 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.

9. 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 Clark County Cape May Warbler to confirm the square is now colored red instead of blank (white)].

10. Sort the new sheet into the newest sort order [2019 AOU 60] 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 Jan 2019 update:
A few tweaks and enhancements, a modest set of sort order changes, several county firsts, two state firsts, and six deletion from the list.
1. Tweaks: A little clean-up of the main sheet for cleaner read - the taxonomic sort order columns are moved right, and state averages moved to bottom. "Hypothetical" code retired.
2. Two addition to the state list this year: Purple Gallinule and Painted Redstart.
3. Six deletions from the list: Ruby-throated Hummingbird, Little Curlew, Great Knot, Whiskered Auklet, Red-faced Cormorant, Gray-cheeked Thrush. This follows recent re-evaluations by the WBRC.
4. Taxonomic updates published by the AOU's 59th checklist supplement. Small shuffles in the taxonomic sequence in the tubenoses, raptors, woodpeckers and flycatchers.
5. Several county firsts, and updates especially to the Clallam county list.

Notes on Jan 2018 update:
A few tweaks and enhancements, another big sort order change, several county firsts, two state firsts, and one deletion from the list.
1. Tweaks: Addition of a 'date updated' note when generating county needs lists and the statewide map. Addition of total county ticks and east/westside totals to the map page.
2. Two addition to the state list this year: Swallow-tailed Gull and Zone-tailed Hawk.
3. One deletion from the list: Thayer's Gull is merged into Iceland Gull, reducing the counts for many counties by one.
4. Taxonomic updates published by the AOU's 58th checklist supplement. Once again, many shuffles in the taxonomic sequence.
5. Several county firsts, and updates especially to the Yakima and Cowlitz county lists.

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.

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