Voter Roll Clean-Up:
List Maintenance for the Masses
Or
How to Get All Your Friends to Call You ERIC
By
Greg Buck
Minnesota Election Integrity Solutions
February 2024
---
“The most basic election integrity starts at the county level with clean and accurate voter rolls.” J. Christian Adams, President of Public Interest Legal Foundation (PILF) on successfully resolving a voter rolls dispute with Ramsey County, the second largest county in Minnesota.
---
Indeed, the condition of voter rolls has profound implications for the fairness of elections. The prospective impact is of such concern that “List Maintenance,” the official terminology for voter roll clean-up, is addressed in two federal statutes and countless state election laws. The two federal statutes are the National Voter Registration Act (NVRA) and the Help America Vote Act (HAVA), which define the minimum standards.
The condition of voter rolls in America came into focus after the 2020 Presidential election and particularly after the release of the documentary “2000 Mules”, showing what purported to be ballot box, or in this case, drop box stuffing on a massive scale. Countless viewers wondered how this could happen. If ballots could only be issued to legitimate, duly registered, living and breathing voters, how could ballot stuffing occur? Upon further review, it was determined that there were many “registrants” on the voter rolls that may not legitimately belong there - if they ever did in the first place.
Voter rolls legitimately change daily as voters move, marry and change names, die, are convicted of felonies and the like. Keeping the rolls current is a challenge for all jurisdictions, but an attempt to keep them up to date is required by law. “How up to date” is not specified in statute however, and as one might expect, some states and counties perform this task better than others. Some states and counties perform the needed database analysis and maintenance themselves while others rely on third-party entities for assistance identifying questionable registrants. One such third-party organization is the Electronic Registration Information Center (ERIC).
A testimony to the power of self-promotion, ERIC was long considered the “gold standard" of list maintenance. Recently, however, a number of states have begun to take a closer look at the service level provided and the security of confidential registrant information entrusted to ERIC. As a result of these concerns, some nine member states comprising approximately one third of the membership have recently terminated their associations with ERIC.
Organizations such as Judicial Watch and the Public Interest Legal Foundation (PILF) have investigated the condition of voter rolls in numerous states and found, in some cases, hundreds of thousands of ineligible voters on the rolls. These findings have awakened many election integrity groups, large and small, to the risk of their state elections being tainted by hundreds or even thousands of ineligible registrants. Knowing these ineligible registrants can result in illegal ballots being cast drives many to want to assess the validity of their own voter rolls.
Sophisticated database platforms exist to aid in evaluating the rolls and many are in use today. These solutions, while effective, are not for every group or every individual interested in assuring voter roll integrity. The purpose of this writing is to offer a simpler and less technologically intense alternative for the average citizen or citizen organization.
The system operates on a commonly used spreadsheet software program called Microsoft Excel. Many users of the Windows operating system may already have Excel installed on their home computer. It is a featured program in the Microsoft Office Suite.
The following is a “How To” discussion for assessing one’s local county or state voter rolls. It consists of three parts:
First, it must be understood that election laws vary greatly from state to state – as do the methods of obtaining the voter database and the cost of that data. In some states it is free and available online; in other states, like Minnesota is available for a nominal fee of $46 per day and in still others, like Wisconsin, it can cost as much as $2,400 per day.
Voter Data
The first step is to become familiar with your state’s public information laws. Most states acknowledge that the information they possess, unless it contains confidential, private citizen information is considered public information. That does not mean however, it is readily available. It may be available to you, but only upon presenting the government entity involved with an appropriate request, sometimes called a “Data Practices Request.” These are similar in some respects to the request for federal agency public information called a Freedom of Information Act request, or “FOIA.” The protocol for requesting public information in some states is simple and in other states, it can be rigorous. That process and how government officials treat it can tell you a lot about the character of your environment.
Under HAVA, the state’s chief election official, usually the Secretary of State, is charged with maintaining a single state-wide voter registration database. In most cases, that is the office to which you will direct your request for a copy of the database. Information on how to obtain it, the cost, the data available publicly, and the data format in which it is provided will be found on the Secretary of State's website. If possible, you should request the file(s) in Excel format (.xls or .xlsx) or CSV (comma separated value) format (.csv). CSV files can be easily converted to Excel format simply by Saving As “xslx format.”
The size of your state’s voter rolls may exceed Excel’s capacity. If that is the case, you may be able to receive the entire database in sections, as individual files. For example, in Minnesota, basic voter information is one file and the voter history data is produced in a separate file. For efficiency in manipulating the data, it may be better to further reduce those files to the county or senate district or precinct of interest. While this produces a bit of a file organization and management challenge, it keeps the file size workable.
Sorting the Data
Whether breaking the state voter rolls into manageable parts or looking for data attributes of interest, one key data function is “the sort”. Before the file is manipulated, it is usually safest to make a copy of it. One way to do this is to place the cursor on the tab containing the data you wish to copy. You can do this as follows:
You now have an exact copy of the spreadsheet. This is the data you will be manipulating, leaving the original data untouched.
The first step is to “scrub the data.” What you received is likely to contain a number of errors and inconsistencies. To effectively sort, the fields and the information in them must be consistent. A cursory review will show certain inconsistencies, most of which come from manual data entry. For example, a database may have two fields for house number, an apartment number field, street name, city, state, and zip code. The address might be in address field number 1 or number 2, the apartment number may be in address field #2 or in the apartment field. If either of those are sort variables, inconsistencies will appear.
Once the data is clean it is time to sort attributes of interest. The first step here is to decide on which variables represent the greatest risk to the election and which type of anomaly can be most easily investigated by the election officials and removed or adjusted. In some cases, those may be variables that can build your credibility as a service provider to the election officials.
Some possibilities include:
One example may be the discovery of deceased registrants on the voter rolls. This could result from a review of the oldest individuals in the county. Using the Excel sort function may work as follows:
The result should be a list of registrants in alphabetical order by last name, then first name, with the oldest of same name listed first.
If registrants of extraordinary age appear, one could investigate further by looking at their voter history to see if they voted recently. Other databases could be consulted, for example, what does the address show? Is it a single-family residence, a care facility, or a memory care facility? Has there been an obituary written for this person? While it is not unheard of for 100-year-olds to be actively engaged and voting, it is also conceivable that a ballot cast for a deceased individual, still on the voting rolls, would go unnoticed and unchallenged.
Reporting the Findings
Let us say the results of the sort for elderly voters turns up a number of registrants for which obituaries have been written. They all seem to be deceased. Most have not voted for several election cycles, but a couple of them voted in the last election. What should one do with those findings?
Reports from election integrity groups across the country agree they are most effective in their efforts to improve transparency in elections when a climate of trust is established between them and the election official. There is no substitute for the understanding there is a common goal – free, fair, accurate and transparent elections. With that trust, there can be improvement; without it, for whatever reason, there will be none.
Well, in the best of all possible worlds, you or your group has already established a relationship with the local election official responsible for maintaining the accuracy of the voter rolls. Hopefully, you have established you are a citizen with reasonable concerns for fair and transparent elections and who recognizes the complexity and difficulty of managing all the elements that go into delivering a timely election. Even more important, you realize these election officials perform their duties though they are under-resourced in everything from staff to an adequate budget. Hopefully, you have positioned this voter roll clean-up effort is a service to that official that would ordinarily go unmet.
The report should be a formal document submitted to the official for their investigation. There is no established form or format, but the documents should be standardized within reason. All the key information should be present, including:
It can be helpful to the Administrator if the report was organized according to the type of anomaly. All potential duplicate registrations should be classified together, as should all deceased, invalid addresses, etc.
The most helpful guide for the information to provide the election official is to remember our respective roles. As volunteers, our role is to do the dirty work to find potential problems in the voter rolls. We must provide them with enough information to validate our concern and to minimize the amount of work they must perform to either validate our finding or disprove it.
It is the responsibility of the election administrator to do the official investigation and decide the ultimate voter status. For example, some states provide only birth year as public information. The election official has the exact birth date. We may submit a concern that it appears there is a duplicate record, or one voter who has moved and now has two active registrations. The election official may note the two individuals have the same name but have different birthdates and conclude they are two distinct people.
Finally, some sort of follow-up should be initiated with the Election Administrator to assess how effective efforts have been. Did the efforts result in voter roll changes? Were there patterns in the changes? Do they reflect on upstream voter registration processes? Can adjustments be made to reduce data entry or other errors? Was the Administrator satisfied with the efforts?
This document was intended as a primer on how to begin an Excel based voter roll examination and clean-up. It is one of the first but by no means the last word in how the process can or should work. Comments on it and suggestions to improve it are heartily encouraged. Please include them in the comments section below or address them to me at [email protected].
List Maintenance for the Masses
Or
How to Get All Your Friends to Call You ERIC
By
Greg Buck
Minnesota Election Integrity Solutions
February 2024
---
“The most basic election integrity starts at the county level with clean and accurate voter rolls.” J. Christian Adams, President of Public Interest Legal Foundation (PILF) on successfully resolving a voter rolls dispute with Ramsey County, the second largest county in Minnesota.
---
Indeed, the condition of voter rolls has profound implications for the fairness of elections. The prospective impact is of such concern that “List Maintenance,” the official terminology for voter roll clean-up, is addressed in two federal statutes and countless state election laws. The two federal statutes are the National Voter Registration Act (NVRA) and the Help America Vote Act (HAVA), which define the minimum standards.
The condition of voter rolls in America came into focus after the 2020 Presidential election and particularly after the release of the documentary “2000 Mules”, showing what purported to be ballot box, or in this case, drop box stuffing on a massive scale. Countless viewers wondered how this could happen. If ballots could only be issued to legitimate, duly registered, living and breathing voters, how could ballot stuffing occur? Upon further review, it was determined that there were many “registrants” on the voter rolls that may not legitimately belong there - if they ever did in the first place.
Voter rolls legitimately change daily as voters move, marry and change names, die, are convicted of felonies and the like. Keeping the rolls current is a challenge for all jurisdictions, but an attempt to keep them up to date is required by law. “How up to date” is not specified in statute however, and as one might expect, some states and counties perform this task better than others. Some states and counties perform the needed database analysis and maintenance themselves while others rely on third-party entities for assistance identifying questionable registrants. One such third-party organization is the Electronic Registration Information Center (ERIC).
A testimony to the power of self-promotion, ERIC was long considered the “gold standard" of list maintenance. Recently, however, a number of states have begun to take a closer look at the service level provided and the security of confidential registrant information entrusted to ERIC. As a result of these concerns, some nine member states comprising approximately one third of the membership have recently terminated their associations with ERIC.
Organizations such as Judicial Watch and the Public Interest Legal Foundation (PILF) have investigated the condition of voter rolls in numerous states and found, in some cases, hundreds of thousands of ineligible voters on the rolls. These findings have awakened many election integrity groups, large and small, to the risk of their state elections being tainted by hundreds or even thousands of ineligible registrants. Knowing these ineligible registrants can result in illegal ballots being cast drives many to want to assess the validity of their own voter rolls.
Sophisticated database platforms exist to aid in evaluating the rolls and many are in use today. These solutions, while effective, are not for every group or every individual interested in assuring voter roll integrity. The purpose of this writing is to offer a simpler and less technologically intense alternative for the average citizen or citizen organization.
The system operates on a commonly used spreadsheet software program called Microsoft Excel. Many users of the Windows operating system may already have Excel installed on their home computer. It is a featured program in the Microsoft Office Suite.
The following is a “How To” discussion for assessing one’s local county or state voter rolls. It consists of three parts:
- How to obtain the voter data,
- How to use Excel to perform a simple “sort” function, and
- What to do with your findings.
First, it must be understood that election laws vary greatly from state to state – as do the methods of obtaining the voter database and the cost of that data. In some states it is free and available online; in other states, like Minnesota is available for a nominal fee of $46 per day and in still others, like Wisconsin, it can cost as much as $2,400 per day.
Voter Data
The first step is to become familiar with your state’s public information laws. Most states acknowledge that the information they possess, unless it contains confidential, private citizen information is considered public information. That does not mean however, it is readily available. It may be available to you, but only upon presenting the government entity involved with an appropriate request, sometimes called a “Data Practices Request.” These are similar in some respects to the request for federal agency public information called a Freedom of Information Act request, or “FOIA.” The protocol for requesting public information in some states is simple and in other states, it can be rigorous. That process and how government officials treat it can tell you a lot about the character of your environment.
Under HAVA, the state’s chief election official, usually the Secretary of State, is charged with maintaining a single state-wide voter registration database. In most cases, that is the office to which you will direct your request for a copy of the database. Information on how to obtain it, the cost, the data available publicly, and the data format in which it is provided will be found on the Secretary of State's website. If possible, you should request the file(s) in Excel format (.xls or .xlsx) or CSV (comma separated value) format (.csv). CSV files can be easily converted to Excel format simply by Saving As “xslx format.”
The size of your state’s voter rolls may exceed Excel’s capacity. If that is the case, you may be able to receive the entire database in sections, as individual files. For example, in Minnesota, basic voter information is one file and the voter history data is produced in a separate file. For efficiency in manipulating the data, it may be better to further reduce those files to the county or senate district or precinct of interest. While this produces a bit of a file organization and management challenge, it keeps the file size workable.
Sorting the Data
Whether breaking the state voter rolls into manageable parts or looking for data attributes of interest, one key data function is “the sort”. Before the file is manipulated, it is usually safest to make a copy of it. One way to do this is to place the cursor on the tab containing the data you wish to copy. You can do this as follows:
- Right click on the spreadsheet tab at the bottom of the screen,
- Select “Move or Copy”
- Put a check in the “Create a copy” box.
You now have an exact copy of the spreadsheet. This is the data you will be manipulating, leaving the original data untouched.
The first step is to “scrub the data.” What you received is likely to contain a number of errors and inconsistencies. To effectively sort, the fields and the information in them must be consistent. A cursory review will show certain inconsistencies, most of which come from manual data entry. For example, a database may have two fields for house number, an apartment number field, street name, city, state, and zip code. The address might be in address field number 1 or number 2, the apartment number may be in address field #2 or in the apartment field. If either of those are sort variables, inconsistencies will appear.
Once the data is clean it is time to sort attributes of interest. The first step here is to decide on which variables represent the greatest risk to the election and which type of anomaly can be most easily investigated by the election officials and removed or adjusted. In some cases, those may be variables that can build your credibility as a service provider to the election officials.
Some possibilities include:
- Identifying duplicates – sort by voter ID, Name, etc.
- Identify Deceased – start with a sort by age.
- Number of registered voters at a single address (single family residential address)
- Addresses at multi-family residences without apartment numbers.
- Voters listing an illegitimate address (PO Boxes, UPS Store addresses, Extended Stay Motels, RV Parks, etc.
- Active voters with no voter history for >2 federal cycles (not voting is not a reason for removal, but it may indicate a voter who has moved)
One example may be the discovery of deceased registrants on the voter rolls. This could result from a review of the oldest individuals in the county. Using the Excel sort function may work as follows:
- From the earlier sort, highlight all the voter data in the county (or senate district or precinct) you wish to sort, including column headings,
- On the menu bar, select “Data,” then “Sort”
- Select the column you want to sort first, in this case, “Last Name,” then A-Z
- Then select “Add a Level” and select "First Name,” then A-Z
- Then select "Add a Level” and select “Date of Birth or Birth Year,” then small to large.
- Then select “ok.”
The result should be a list of registrants in alphabetical order by last name, then first name, with the oldest of same name listed first.
If registrants of extraordinary age appear, one could investigate further by looking at their voter history to see if they voted recently. Other databases could be consulted, for example, what does the address show? Is it a single-family residence, a care facility, or a memory care facility? Has there been an obituary written for this person? While it is not unheard of for 100-year-olds to be actively engaged and voting, it is also conceivable that a ballot cast for a deceased individual, still on the voting rolls, would go unnoticed and unchallenged.
Reporting the Findings
Let us say the results of the sort for elderly voters turns up a number of registrants for which obituaries have been written. They all seem to be deceased. Most have not voted for several election cycles, but a couple of them voted in the last election. What should one do with those findings?
Reports from election integrity groups across the country agree they are most effective in their efforts to improve transparency in elections when a climate of trust is established between them and the election official. There is no substitute for the understanding there is a common goal – free, fair, accurate and transparent elections. With that trust, there can be improvement; without it, for whatever reason, there will be none.
Well, in the best of all possible worlds, you or your group has already established a relationship with the local election official responsible for maintaining the accuracy of the voter rolls. Hopefully, you have established you are a citizen with reasonable concerns for fair and transparent elections and who recognizes the complexity and difficulty of managing all the elements that go into delivering a timely election. Even more important, you realize these election officials perform their duties though they are under-resourced in everything from staff to an adequate budget. Hopefully, you have positioned this voter roll clean-up effort is a service to that official that would ordinarily go unmet.
The report should be a formal document submitted to the official for their investigation. There is no established form or format, but the documents should be standardized within reason. All the key information should be present, including:
- Voter ID
- Name and Address
- Age or Date of Birth or Birth Year
- Description of the Anomaly
- Address zoning, residential, non-residential, commercial, etc.
- Real Estate Photo, if helpful (Google Earth, Street, etc.)
- Screen shots of Death Certificate, Obituary, or other relevant documents.
It can be helpful to the Administrator if the report was organized according to the type of anomaly. All potential duplicate registrations should be classified together, as should all deceased, invalid addresses, etc.
The most helpful guide for the information to provide the election official is to remember our respective roles. As volunteers, our role is to do the dirty work to find potential problems in the voter rolls. We must provide them with enough information to validate our concern and to minimize the amount of work they must perform to either validate our finding or disprove it.
It is the responsibility of the election administrator to do the official investigation and decide the ultimate voter status. For example, some states provide only birth year as public information. The election official has the exact birth date. We may submit a concern that it appears there is a duplicate record, or one voter who has moved and now has two active registrations. The election official may note the two individuals have the same name but have different birthdates and conclude they are two distinct people.
Finally, some sort of follow-up should be initiated with the Election Administrator to assess how effective efforts have been. Did the efforts result in voter roll changes? Were there patterns in the changes? Do they reflect on upstream voter registration processes? Can adjustments be made to reduce data entry or other errors? Was the Administrator satisfied with the efforts?
This document was intended as a primer on how to begin an Excel based voter roll examination and clean-up. It is one of the first but by no means the last word in how the process can or should work. Comments on it and suggestions to improve it are heartily encouraged. Please include them in the comments section below or address them to me at [email protected].