Let’s be honest, data management in your organization is probably not the most important step in your nonprofit’s hierarchy of needs. And love it, or hate it, there’s a good chance your organization relies on Excel for many tasks, including data management. Here are three useful tips that may make life inside the spreadsheet a bit more manageable.
Data can get messy, and it often gets worse over time. Some Causes:
- How donors enter information on your website
- User input
- PDF to Excel – It may be a result of copying information into Excel from a PDF, especially one that’s been scanned by who knows what.
This may often leave your data looking like this:
- Names are not all capitalized
- Spaces before, after, and in-between names and numbers
Step 1: TRIM
Excel’s TRIM function will clean this up in no time. It removes extra spaces from text, leaving only a single space between words and no space at the start or end of the text. The result is much better already. But one name is still incorrect.
Step 2: FIND and REPLACE
FIND and REPLACE is useful for many things in Excel. It’s great, but only with values. It will NOT work if you try to use it on cells that contain formulas.
To quick change formulas to values – Copy the data (in the example below, copy cell G3), and right-click Paste Special à Paste Values
CTRL+H or navigate to the “Find” button on the Home tab and select Replace
Step 3: PROPER
PROPER capitalizes words in a given text string. Numbers and punctuation are not affected. Our Result is a set of names that is consistent, and manageable:
Now the Contribution column. We will use the same steps for the Contribution column that contains numbers:
FIND AND REPLACE
It’s a bit hard to see, but the Find What field has a single space. The Replace With field is empty. This tells Excel to Find a single space in the Result column and replace it with nothing, therefore removing those pesky spaces.
The result after FIND and REPLACE, and formatting the cells:
The before and after:
Key Points to Remember:
TRIM: Cleans up data, removing most spaces before and after text.
FIND and REPLACE: Useful for fixing large sets of data
PROPER: Capitalizes words in given text string.
For more on using Excel, check out this article that explains using Microsoft Excel and Access to manage and analyze data.
If you have any questions on the above, or on excel and data management in general, feel free to contact a Henry+Horne professional. For more on Henry+Horne’s work with nonprofits, check out our Industries page.