We know that data management isn’t always the top priority at your organization — but it is key for determining your effectiveness, making smart decisions, and building capacity. That’s why we’ve compiled this list of simple nonprofit Excel tips that will make data management a lot easier.
Although your nonprofit’s CRM or donor database should help you with data management, there are some aspects of data cleanup that Excel is just better at. It’s important to approach your data strategy with both tools.
Here’s what we’ll cover:
- Finding bad data in your system
- Cleaning bad data in Excel
- Uploading & batch updating account data in your system
- Advanced Excel tips for managing data
- Other ways to manage and clean data
And by the way, all of the tips we’ll cover are applicable for both Excel and Google Sheets. We know that the G Suite for Nonprofits is a popular tool for many orgs, so we’ll point out any differences in functionality or process for Google Sheets users.
Finding bad data in your nonprofit CRM
Before we start identifying the bad data in your system, let’s start with the basics — what is bad data?
There are three main types of bad data:
Here’s how you can spot each type of bad data in your system.
Inconsistent data is any data that is not standardized across all records.
An example of inconsistent data could be your organization’s salutation field. Do all records have a salutation field with a formal title and last name? Or do some records have just a title or just a first name? Whatever format you decide, you’ll want to make sure it’s the same for across all of your constituents.
How to spot it: Pay close attention to capitalization, abbreviations, &/and, and standardized formats for things like phone numbers and addresses.
Incomplete data is any data that is missing or not available in your database.
For example, you might have a salutation field filled out for only some of your records while the rest are blank. This can create problems in mail merges, email communications, and segmentation where these values might be used to personalize your messages.
How to spot it: When you export your data, keep an eye out for rows and/or columns that are missing large amounts of data. You can also sort or filter your exported spreadsheets to identify any missing data.
Inaccurate data is just what it sounds like — data that’s wrong.
This is the hardest type of bad data to identify, because there’s often no way to tell if your data is right or wrong. For example, a supporter’s address may be outdated after moving, but you may not be able to tell by just looking at the data — especially if you’re new to the organization or unfamiliar with the data.
How to spot it: If you’re working with data that’s unfamiliar, just ask somebody at your organization who might be more familiar to take a look at it for you. This is especially useful for staff who work with specific groups of people, or may have a better idea of past supporters. They’ll be able to identify specific details (i.e. that donor moved, got divorced, etc.) that you wouldn’t know otherwise. Also, regular NCOA updates can go a long way in keeping your organization’s data accurate.
Cleaning bad data in Excel
Now that you’ve identified all of your bad data, it’s time to clean it with Excel. Here are some of our favorite Excel formulas that you can use to make your data as clean as possible.
1. Standardize capitalization
Use the proper function to rewrite names in a regular, uppercase style. A lot of the capitalization errors in your system will come from your supporters themselves when they fill out a form on your website. And although some of your constituents might like writing in ALL CAPS, it can sometimes make it look like you’re YELLING AT THEM when you use an all caps name in communications. The formula will take into account names that have multiple uppercase letters (ex: McDonald), but you’ll want to spot check those names to make sure they’re correct.
2. Remove excess space
Unnecessary spaces can make your communications look awkward, and take away the personalized feel of a seamless mail merge. Use the trim function to remove that spacing, and only keep the data that matters.
3. Combine multiple pieces of data
When you need to combine a few fields (let’s say title, first name, and last name for a formal salutation), it’s often easier to do that in Excel than creating a separate field.The concatenate formula will pull multiple pieces of data into one, just be sure to leave spaces between values!
Pro Tip: Nest functions within one another
If you need to do a few things at once, you can nest multiple functions within one another. This can help you cut down on steps to save time and keep your data organized.
=PROPER(TRIM(H2&” “&I2&” “&J2))
This example would combine the contents of cells H2, I2, and J2 in one cell — while removing excess space and converting to regular, uppercase format.
Pro Tip: Find and replace
If there’s a certain value that needs to be updated or standardized, you can do that with the find and replace tool in Excel.
For example, if you wanted to replace “Mr” (without a period) with “Mr.” (with a period), here’s what you would do:
- Highlight the area (specific row, column, or area)
- Find any Mr (with no period), replace with Mr. (with a period)
- Options: To be sure you only switch values that match exactly, be sure to hit the check box to match the entire cell contents. That will prevent fragments or words that start with that value being replaced.
Uploading clean data into your system & batch updating accounts
Now that you’ve cleaned your data, it’s time to put it back into your CRM system and update your constituent records with accurate data.
Here are some data upload tips to maximize data integrity:
- Make sure every entry has a unique account ID. This will help you seamlessly update existing accounts, without creating duplicates.
- Save your spreadsheet as CSV file. This is the format that most nonprofit CRMs will require when uploading data. Click here for a tutorial on CSV files.
- Use your database Import Manager to upload that data into your system.
- Match your fields to existing system standard fields
- Check out this video and guide on using the Import Manager in NeonCRM
Advanced Excel Tools
Text to Columns
Use the text to columns wizard to split a single cell of text into columns. You’ll be able to choose a delimiter (space, comma, tab, etc.) to split the text. This can be especially useful for breaking up formal names or addresses.
Find and replace a line break
This is a great tool for finding values that might include multiple lines — like an address — and removing that line break to consolidate everything on one line. Use the find and replace tool, and search for “ctrl key + j” in Excel, or “\n” in Google Sheets. You can then replace with a comma, vertical line, or anything you want.
This formula can help you identify specific values that meet a set of criteria in your spreadsheet. For example, if you wanted to highlight all donation values above $50, you could create a formula to put “Yes” in a new column next to donation amounts. If statements are also useful for more advanced data program, if your organization needs that.
Other ways to clean and manage data
Excel is a useful data management tool for nonprofits, but it’s not the only resource you have at your fingertips.
Many nonprofit software providers do offer data hygiene services, like bulk data cleaning or periodical NCOA updates. Be sure to take advantage of these services when you need them, especially if you’re not a data expert yourself. You can save a lot of time by having a pro do it for you instead of struggling through it yourself. Learn more about Neon’s data services.
If you don’t want to use Excel, a lot of software systems have tools to help you manage and clean data in your own database. Take a look at duplicate management and bulk account update features in your database, but know that you might be limited based on your system.
Whatever tools you use, data management is a very important for every nonprofit. We hope these tips will get you closer to becoming a data management pro, and making your nonprofit as effective and efficient as possible.