This compact reference guide is designed to help people develop basic skills to better manage their own circle of influence to build contact lists for crowd building, volunteers, phone banking, canvassing, fundraising etc. This guide uses free tools like Open Office and Google GMAIL.  However, the instructions will also work with popular applications like Microsoft Excel.

 

Open Office is a FREE (Open Source) office suite which includes a powerful spreadsheet (Calc) and word processor (Writer) which read and save in many different file formats, including Microsoft Office.  

Open Office - http://www.openoffice.org/

 

Google GMAIL is a FREE email service that provides massive storage space.  GMAIL allows you to import and export contact lists (CSV) to and from different email systems, spreadsheets, databases, cell phones and PDAs.  You can also create distribution groups and send emails with up to 500 addresses at once.  

Google GMAIL - http://mail.google.com/


TOPICS:

  • Database vs. Spreadsheet
  • State Voter Data
  • Import Data
  • Export Data
  • Isolate Data
  • Split Data
  • Combine Data
  • Format Data
  • Remove Formatting
  • Create an Email Distribution List
  • Slicing and Dicing in Microsoft Excel

Open Office Calc with a typical voter spreadsheet format where the file or workbook represents a 
Congressional District and each tab or worksheet represents a different precinct.

 
Database vs. Spreadsheet

The primary reason custom relational database systems (like Prevail) were developed in the 1980s and '90s was because spreadsheet applications were unable to handle the large number of records needed.  That has changed.  Spread sheet applications like MS Excel and Open Office Calc (free) have grown larger and more powerful and significantly more reliable and easier to use than relational databases.  The problem with a database system is they are useless at a grassroots level.  There are hundreds of different custom database systems all over the country

For every database expert you can find to help manage a custom database, you can find about a hundred people who can manage spreadsheet data.  Spreadsheets are more straight forward and easier to use.  If you keep your formatting simple, spreadsheet data can easily be imported into a lager database.
 

State Voter Data

The most common data format states use to supply voter data is Comma Separated Value (.csv) which imports very well into a spreadsheet (If formatted properly).  See “Importing Data” below.  Use the following topics to format, isolate, find and manage your data.

Importing Data

Use a spreadsheet like Open Office Calc or MS Excel to import an email contact list.

Text import wizard (similar to MS Word). Selecting different
options will preview how the data will be divided in columns.

 
To import formatted data (like .csv or .txt files) open a blank spreadsheet:

  1. Choose File - Open and select "Text CSV" from the File type combo box.
  2. Select the file and click Open.
  3. The Text Import dialog appears (above).
  4. Decide how data should be divided by selecting different "Separator options."
  5. Click OK

NOTE: The first row is used to label each column.

 
 

Exporting Data

Use a spreadsheet to export contact data that can be imported into a database, PDAs, and Email systems.  If you keep your formatting simple, exporting data is easy.  Just select "File" then "Save As" and then select the "Save as type" dropdown list and select the format you want to export to.  Text CSV (.csv) or Comma Separated Value is recommended.  Then select the "Save" button. 

NOTE: To make sure your formatting will match up with another system, export contact data out of the other system and import it into a spreadsheet to see how it is organized.  You can then organize your data to match before exporting the data you want to transfer.

Isolate Data

Use a spreadsheet to isolate specific types of data.  For example if you have a column with blank or duplicate cells and you want to group those records together so you can either focus on completing them or delete those records, you can sort all the records by that column.

To sort by columns:

  1. Select all of the cells you want to sort.
  2. Click "Data" then "Sort."
  3. Under the Sort by dropdown list, select the column letter or header you want to sort by.
  4. Blank or duplicate cells will now be grouped together in the column you sorted by.

NOTE: You can also quickly select the whole worksheet by typing "Ctrl" + "A" or by clicking the blank gray cell to the left of the column letters and above the row numbers.

Splitting Data

Use a word processor like Open Office Writer or MS Word to split lists into multiple columns.


Word processor with a list of names (left) and the same list split in two columns (right)

To split a list of first and last names or address, city, state and zip code, into multiple columns that can be copied and pasted into a spreadsheet, use a word processor to convert the text to a table and separate the text at the space between the first and last name.

  1. Copy the list of names into a word processor
  2. Select the list of names
  3. Click "Table" then "Convert" then "Text to Table"
  4. At "Separate text at" select "Other" and replace the comma with a space.
  5. Click OK

You can now copy and paste one or both of the columns into a spreadsheet.

Combining Data

Use a word processor to combine multiple columns into one list.  Combining text is about the reverse of splitting text.  To combine text that is already split into columns in a spreadsheet or table, copy the cells you want to combine and paste them into a word processor. You may need to use paste special and select “Formatted text [RTF]” to end up with a proper table. 

  1. Select the table (in word processor)
  2. Click "Table" then "Convert" then "Table to Text"
  3. At "Separate text at" select "Other" and replace the comma with a space.
  4. Click OK

You can now copy and paste the column of text back into a spreadsheet.

Formatting Data

Use the Format Painter on the Standard toolbar to quickly copy the formatting of cells (such as borders, fills, font, or number formats) and apply that formatting to other cells.

1.      First, select a cell that has the formatting that you want to copy and apply to other cells.
2.      Click the Format Painter  button.
3.      Click and drag the mouse pointer across the cell or range of cells you want to format.

NOTE: To copy formatting to several areas of a spreadsheet, double-click the Format Painter button, and it will stay selected until you click it again or press ESC to turn it off.

NOTE: To display the Standard toolbar click View > Toolbars > Standard

Remove Formatting

Delete Contents dialogue box.

Quickly Clear All Spreadsheet Formatting.

1.      Type Ctrl + A to select all cells in the worksheet.
2.      Type "Delete" and then select "Formats" in the Delete Contents dialogue box.
3.      Click "OK"

NOTE: You can also use Microsoft Notepad to remove formatting from various sources of data like web pages or other documents.  Open Notepad (Start > All Programs > Accessories > Notepad).  Copy and paste data to Notepad and then copy and paste the data where you want it.  Notepad automatically removes all formatting.

Add / Remove Borders

Format Cells wizard with “Borders” tab selected.

To add or remove borders, select the cells you want to change.

1.      Under the Format menu select Cells.
2.      Select the Borders tab and then select the options you want.
3.      Click the OK button.

 
 

Create an Email Distribution List

Convert this:
 

Into this:

kbailey@gmail.com,
mjohnson@aol.com,
sellison@yahoo.com,
bstephens@gmail.com,
psmith@verizon.net,
jedwards@aol.com,
eharris@yahoo.com,

To make an Email distribution list from a spreadsheet, you will need to add comas (or semicolons) and remove the table from the list of email addresses.

  1. Copy a column of email addresses to an empty spreadsheet.
  2. In the empty cell to the right of the top email add a comma and click outside that cell.
  3. Click the cell with the comma and then click and drag the lower right corner of that cell down to the last row with an email address.  You should have commas in every cell.
  4. Select and copy the cells with emails and commas.
  5. In an empty word processing document click "Edit" > "Paste Special"
  6. In the Paste Special dialogue box select "Formatted Text (RTF)" and then "OK."  You should have a two column table with emails and commas.
  7. Select the table and then click "Table" > "Convert" > "Table to Text"
  8. In the Convert Table dialogue box, select "Other" and type a comma.
  9. Click the OK button.  You should have a list of emails with two commas at the end of each.
  10. Select the list of emails
  11. Use Find and Replace to find two commas and replace with one comma.
  12. Copy and paste the list into an email address box.

NOTE: If your email system uses semicolons just replace commas with semicolons in the above directions.

TIP: Use Blind Copy (BCC) to protect each persons email address from the others on the list.

Slicing and Dicing in Microsoft Excel

The following animated demos show off many of the handy features of Excel including merging, splitting and finding data.  All demos have audio narration.

Merge cells or combine their contents 
http://office.microsoft.com/en-us/assistance/HA012004951033.aspx

Split cell contents across multiple cells
http://office.microsoft.com/en-us/assistance/HA011973581033.aspx

Keep column names in sight when you scroll
http://office.microsoft.com/en-us/assistance/HA012001021033.aspx

Find specific information using AutoFilter
http://office.microsoft.com/en-us/assistance/HA100823141033.aspx

Keep your data confidential
http://office.microsoft.com/en-us/assistance/HA012183731033.aspx

More Resources

Fundrace Neighbor Search
http://www.fecinfo.com/http://www.fundrace.org/neighbors.php

Political Money Line
http://www.fecinfo.com/

2006 Texas Democratic Party Grassroots Handbook
http://www.txdemocrats.org/getinvolved/grassrootsguide/

Get Out the Vote (Youth Focus)
http://www.bonner.org/resources/modules/modules_pdf/BonCurGetOutVote.pdf

Youth Vote Coalition
http://www.youthvote.org/indexcf.cfm