Nonprofit GPS

Your Personal Navigation System Through Not-for-Profit Accounting Issues

Excel and data management: making information usable

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.

Don’t miss: Distinguishing direct donor benefits from fundraising expenses

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:

TRIM

 

 

 

 

 

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.

=TRIM(text)

 

FIND and REPLACE: Useful for fixing large sets of data

Shortcut: CTRL+H

 

PROPER: Capitalizes words in given text string.

=PROPER(text)

 

Kyle Tevis

Get in on the conversation.

Unfortunately, we cannot give free advice to non-clients by email, comment response, or phone call. Thank you! Read our disclaimer.