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.

2018 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)

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

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.
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 [2016 AOU 57 order is a good choice if you've been using the 2017 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 Tufted Puffin and Swainson's Hawk.
Merge your records for Thayer's and Iceland Gull into a single row. For most people this will require only transferring the occasional [old] Iceland Gull tick into any counties where you had [old] Iceland but not [old] Thayer's.
Delete the row for [old] Iceland Gull from your old sheet, once the records have been transferred into [old] Thayer's Gull.
Check the row number for Scott's Oriole 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 [2017 AOU 58].

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. 2016 AOU 57 sort order might be the easiest, since the 2017 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 Tufted Puffin (for Swallow-tailed Gull) and below Swainson's Hawk (for Zone-tailed Hawk).

5. On your [old] file, add ticks for any [old] Iceland Gull sightings into the row for [old] Thayer's Gull. For most people, there will be few [or no] counties where you have seen just Iceland but not Thayer's, but if that is the case for you in any counties, take this step to make sure these are included after the merge of these two species.

6. On your [old] file, delete the row for [old] Iceland Gull from your old sheet, once the records have been transferred into [old] Thayer's Gull. To do this, highlight the entire Iceland Gull row [by clicking the row number left of the name], and then selecting 'delete' from the 'edit' menu.

7. After adding 2 rows and deleting one row, you should be set to move your data. Check the row number for Scott's Oriole in both the new sheet and your modified data sheet to make sure both are the same.

8. 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 AT524.

9. 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.

10. 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 King County Swallow-tailed Gull to confirm the square is now colored red instead of blank (white)].

11. Check the Iceland Gull rows to be sure all your 'ticks' are included for what used to be both Thayer's and Iceland Gull.

12. Sort the new sheet into the newest sort order [2017 AOU 58] 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 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