Four Excel Functions Explained and How Your Nonprofit Can Benefit

Blog_image_Effective_Peer_to_Peer_Fundraising_550x300

As a CRM consulting firm, Heller Consulting encourages organizations to ditch their Excel spreadsheets in favor of a more dynamic data management tool. But we’re not going to tell you to ditch Excel as a data manipulation tool. In fact, some might say that Excel is the greatest data manipulation tool. Almost everyone has access to it, it has some extremely robust data manipulation functions, and for the most part it is easily self-taught – many functions can be learned by Googling, reading and testing.

So to help you get re-acquainted with the power of Excel, here are a few of my favorite functions:

EXACT statements

This function allows you to compare two columns of data and show whether they match one another. I recently used this to double-check report totals. I pulled donation totals with a particular set of criteria and placed them in one column and with slightly different criteria in a different column. In a third column I used the EXACT function to tell me if they matched (TRUE) or not (FALSE). That way I could see how pulling the data slightly differently affected my results.

Example: =EXACT(A2, B2)
In English: If cell A2 matches cell B2, return TRUE, if not, return FALSE

IF statements

IF statements also compare columns, but slightly more robustly. An IF statement can compare one column of data to multiple columns, and instead of only showing whether they match or not, you can have the function return a value if one column is more, less or equal to another column. Nested IF statements allow you to create multiple comparisons.

I used this function recently to create donor segments for a mailing (this is not how we would normally suggest you create these segments). I had three columns of donation totals, and each segment depended on a comparison between these three columns: If column A was greater than $5K put them in Category A, if column A was less than $5K but column B was greater than $1K, but them in Category B, and so on. Note below that the text values I’ve included are in quotation marks.

Example of a simple IF statement: =IF(A1>B1, “A”, “X”)
In English: If A1 is greater than B1, return A, otherwise, return X

Example of a nested IF statement: =IF(A1>B1, “A”, IF(AND(B1>A1, B1<5), “B”, “X”)) In English: If A1 is greater than B1, return A, but if B1 is greater than A1 but less than 5, return B, if none of the conditions are true, return X

VLOOKUP

VLOOKUP is a function that intimidated me for years, but once I had it explained to me, I realized it is not only relatively easy, but also extremely useful! In short, you’re telling Excel to compare two worksheets. If the same value occurs in both sheets, return another value that’s on the same vertical line (hence, VLOOKUP) as the matching value. For example, if you had two spreadsheets that both contained a unique ID per constituent, and one spreadsheet contained event registrations, and the other contained gift information, you could use a VLOOKUP function to add the gift information from the gift spreadsheet to the another column in the registration spreadsheet. Viola! Now you have a spreadsheet of who attended your event AND how much they gave. You can create VLOOKUPs between worksheets within the same workbook as well as between different workbooks.

Example: =VLOOKUP(A1, Sheet2!A1:B1, 2, FALSE)
In English: Look at the value in cell A1 (unique ID), if you see that value anywhere in Sheet 2, return the value in the 2nd column in Sheet 2. FALSE means make sure it’s an exact match. TRUE means approximate match (this is used for matching text values because it disregards upper and lowercase matching).

Pivot Tables

Pivot tables are also one of those functions that seem intimidating at first glance. However, once you get the hang of working with them, they will become one of the most heavily used tricks in your data manipulation toolbox.

Pivot tables allow you to take a large amount of raw data and “slice and dice” it in many ways. For example, let’s think about the spreadsheet with all of our event donors again. Now we have their RSVP and their gift amount. But it also contains the amount they paid for the ticket, their address information, the name of their sponsor, and their last gift date and amount. From this data you could create a pivot table of:

  • Number of Yes or No RSVPs
  • Amount of giving per city (or zip code)
  • Amount of giving by Sponsor

Unfortunately this blog post is much too short to go through an extensive lesson on pivot tables, but there are tons of tutorials and videos, and the more you practice, the easier it will become.

So don’t ditch Excel entirely! Just use it wisely.

Are there any other Excel functions that you find most helpful?

Molly Redding

About Molly Redding

Molly brings over seven years of nonprofit experience to her work at Heller Consulting, both in direct services and fundraising. Prior to Heller she worked at the Lucile Packard Foundation for Children’s Health and Room to Read, where she was responsible for maintaining accurate records, managing direct mailing and phone-a-thon data, and creating yearly fundraising reports. At Room to Read she was a part of two major data restructuring projects. First, she helped her organization transition to Blackbaud NetCommunity, which prompted a redesign of their coding structure and a major recoding project. Secondly, she helped convert and integrate a projects database into an established Salesforce database. She enjoyed this project so much that she went on to help with Salesforce projects in a volunteer capacity at FrontlineSMS and Inveneo, two organizations focused on using information and communication technologies for international development. She has a Bachelor of Arts degree from Boston College and an Msc from the London School of Economics. She is also a certified Salesforce.com Administrator

6 comments

  1. Susan Kenna Wright

    Dude, I love Excel. I think that no matter what your job is and no matter what industry you’re in, knowing Excel will help you excel. (haha) It’s true though. Knowing these tricks makes you so much faster and more accurate, you will definitely impress people with these skills.

  2. Molly, this is such a great article. I use these functions ALL the time. You mention this in the beginning of your article, but many of my clients that are just starting out on Excel don’t realize what a great resource Google can be. When I am trying to figure out if it is possible to do something with an Excel function, I type a description of what I am trying to do in Google. E.g. “Highlight Duplicate Values Excel.” More often than not, you will get a lot of useful results.

  3. Yes, Peter, absolutely. I’ve found using the Excel Connector to be useful for that, because you can use the built in tools for that. I also use the CASESAFEID() option in a formula field–it’s not something users see on their layouts but I can export it for cases like this.

  4. Another workaround is to just create your own unique ID for entities or events, using the CONCATENATE function or & operator. I have a blog post on how to do so here: http://odyscope.com/dates/rearrange-data-into-a-table-with-the-vlookup-function/

  5. Peter, I have definitely run into that issue before, so thanks for pointing that out and providing the solution!

  6. Peter Churchill

    Re VLookup and Salesforce, it is important to note that Excel does not respect Case Sensitivity…so an Id like this 003K000000f44aj and this 003K000000f44aJ are considered equal. That can cause problems if people export a report from Salesforce, and then use VLookup – because you will get false matches. Either use Vlookup with Exact, or always use the 18 Char version of the Id – (003K000000f44ajIAA vs 003K000000f44aJIAQ) to avoid any nasty data mixups. The Excel Connector has a utility to convert the standard 15 Character Ids to be the case safe version.

Leave a Reply to hellermolly Cancel reply

Your email address will not be published. Required fields are marked *