How to use CSV Spreadsheets to Calculate Ranked-Choice Voting Results
If you've ever used a spreadsheet program before, you've likely come across the comma-separated value (CSV) format. It's as close to a universal file format for spreadsheets as you can get. Whether you use Microsoft Excel, Google Sheets, or Apple's Numbers, you can open, edit, and save CSV files. Many online survey and polling tools also allow you to download data you collect as a CSV.
This makes it an ideal format for storing any ranked-choice voter ballots that you have — no matter where you collect them. But, that data won't tell you much without being able to do the ranked-choice voting results calculation.
Microsoft Excel doesn't calculate ranked-choice voting results. Google Sheets doesn't either. And neither does SurveyMonkey, JotForm, Qualtrics, TypeForm, Alchemer, Formstack...you get the point.
But RankedVote does.
With RankedVote's CSV Import Tool, you can quickly and easily take your data from any service and calculate the ranked choice results. RankedVote will then automatically visualize, animate, and explain what happened. You don't even need to create an account to get started.
Potential Uses
Here are just a few examples where RankedVote's CSV Import can broaden your use of ranked choice voting:
- Take Ranked Choice Voting (RCV) Anywhere: As long as you can get a CSV into the right format, you're good to use RCV. Feel free to include ranked choice questions in any survey tool you use. You can even transcribe paper ballots to your spreadsheet to save yourself hours of manual calculation.
- Simulate Different Scenarios: What happens if a candidate drops out after voting has occurred? What if someone's vote is worth many than another voter's? Adding voters, removing candidates, and changing the number of winners is as simple as adding a row, deleting a column, and changing a drop down. You can quickly adjust your CSV, save it, and import it again in RankedVote.
- Extend RankedVote's Flexibility: Using the RankedVote Data Download feature, you can get voter data captured by RankedVote into a CSV that can be opened in any spreadsheet program. Change votes, multiply votes, remove candidates, and more with the ease of a spreadsheet.
Step-by-Step Walkthrough
Collect Your Data
The great thing about interacting with RankedVote through CSV is that allows you to use whatever tool you're most familiar with or whichever one your organization recommends.
Regardless of the tool, try to set it up so the data is output with the Choices as Columns and the Voters as Rows. This will simplify getting the data into the correct format. If that's not an option, seek out the "transpose" feature in your spreadsheet program.
Get Your CSV in the Correct Format
Basic Format
The Import Tool requires the CSV to be in a specific format to accurately interpret the data. In simple terms, the Choices are the columns, the Voters are the rows, and the Rankings are the numeric values in a row under a specific column. More precisely...
- Choices: The name or description of each choice needs to be in the first row of a column starting in cell B1.
- Voters: A unique identifier for each voter needs to be in the first column of a row starting in cell A2.
- Rankings: These are numeric values under a choice's column in a voter's row.
That may sound a bit complicated, but the picture below usually makes things clear. You can also make a copy of this correctly formatted .csv file to get a better feel for how it should look.
Important Things to Know About Formatting the Data
Choices
- If Row 1 of a column is blank (aka there's no name), any rankings in that column will be ignored
- Each column in Row 1 that is not empty is treated as unique. If the same value is used in multiple columns, a designator (#2, #3, etc.) will be appended to it.
Voters
- Each row (after Row 1) is treated as unique by default. This setting can be changed after your import the CSV, but before you calculate the results.
- In the event you do not want to treat each row as unique, make sure that each row has an identifier in Column A.
- If more than one row has the same identifier in Column A, the first row is counted and the subsequent rows are ignored.
- If Column A of a row is blank, any rankings in that row are ignored.
Rankings
- The CSV Import only understands cells that start with numeric data. So, "1" and "1st" will work, but "First" will not.
- If a cell is blank, no ranking is captured
Calculate the Results in RankedVote
Alright...it's the magic moment! You've got your data. It's formatted correctly. Let's calculate the results! Here's how you do it 95% of the time:
- Go to the RankedVote CSV Import Tool
- Click the "Choose CSV File..." button and select the .csv file you want from your device
- Click Calculate Results and you're done!
- Click the green "Change Settings" button in the lower right if you want to import a different file, adjust winners, or treat uniqueness differently
IMPORTANT NOTE: All calculations are performed on your computer. Your CSV is not uploaded anywhere.
What about the other 5% of the time?
It's pretty straightforward, but here's an explanation of what you can change about how the CSV Import Tool processes your data:
Number of Winners
You can change how many choices are declared "winners" using this setting. Any number greater than 1 will use a "multi-winner" calculation method to find choices with the most support across all your voters.
Treat Each Row as a Unique Voter
Your can turn this setting to "No" using its dropdown. It makes the most sense to use if your data has multiple rows that could be from the same voter.
Change File
Click the purple "Change" text button near your CSV's filename to import a new file from your device.
Data Issues
If RankedVote comes across data that it can't process and ignores (usually due to a cell having a ranking that's not numeric), a "Data Issues" section will display. If will give examples of what was ignored with the "Row" and "Column" labels as well as the "Cell Data" that was contained within it. This should give you enough info to find the cell, fix the issue, save your CSV, and then import it again.
Bonus: Tips & Tricks
Play With Your RankedVote Voter Data
By downloading voter data captured in the "RankedVote Extension" format, you now have an extremely flexible way of working with your data. Here are some examples of what you could do:
Remove Candidates
If you want to see what happens if a candidate were to drop out, simply delete the column in the CSV, save the file, and import it again.
Weighted Voting
If you have certain voters whose votes count more than others, you can copy the row of that voter and paste it as many times as necessary to reflect the "weight" of that voter. For example, if the leader of a committee has a vote that counts 5x more than regular members, you'd copy the leader's vote and paste it four more times.
Use RankedVote Anywhere
While it would be great if everyone used the RankedVote web application 100% of the time, it's not always feasible. RankedVote specializes in having a realistic ranked-choice ballot experience and voting calculation. It does that one thing and it does it well.
But, that can be limiting. Any time you're asking non-ranked-choice questions alongside ranked ones, you'll likely need to combine tools. Here's how you can do that with other survey/polling tools and services:
SurveyMonkey
After configuring your SurveyMonkey for ranked choice voting, you can download the results as a CSV.
Jotform
You can use a template that captures ranked preferences and then download the results as a CSV.
