Top 10 Spreadsheet Secrets From A Nonprofit Data Nerd | Beth’s Blog

Top 10 Spreadsheet Secrets From A Nonprofit Data Nerd

Guest Post, Measurement

Note from Beth: I made a personal goal for myself to get past my dislike of spreadsheets and become more fluent.  First I had to “Stop Fearing the Spreadsheet” and then started to interview nonprofit data nerds about their Excel secrets.   Why get good at Excel?  Even if you are using a paid tool measure your results, knowing how to use a spreadsheet well will be invaluable to your measurement program.    While writing the “Measuring the Networked Nonprofit,” and now teaching workshops on strategy and measurement for networked nonprofits, I realize that  knowing how to use excel to set up and automate a dashboard, integrate data from exported from different programs, get insights, and make it visual are basic data literacy skills.    And, knowing a few secrets will help you save time.    I was lucky enough to find a generous data nerd who excels at Excel who was willing to share a few.  Enjoy.

Top 10 Secrets of Excel Data Nerds

By Ann Emery

I read Beth’s post, Help! My Nonprofit Needs a Data Nerd and How to Find Them! Although nonprofits often have lots of data and a desire to use it, I agree, we data nerds are in short supply! That’s why I’m sharing the tips, tricks, and insider advice to transform you into a data nerd.

Here are the top 10 things you need to know in Excel to become a data nerd:

Secret #10. Data isn’t just for statisticians, accountants, and economists.
Anyone and everyone, even beginners, can learn how to analyze their own data in Excel. I created Excel for Evaluation, a series of more than 25 video tutorials with real examples from nonprofits, to share my favorite techniques with nonprofit leaders like you. Each video is only 1-4 minutes long, so you can even watch a few videos while eating lunch. You’ll be a data nerd in no time!

Secret #9. Data analysis is a process, not a one-time thing.
There are several steps in the process. Data nerds know how to clean and recode data, look for patterns, calculate key statistics, and then show off the most important information in graphs and charts. You can follow this syllabus to boost your skills at all stages of the data analysis process.

Secret #8. Don’t underestimate the power of high school statistics.
If you took a high school or college course in statistics, you’re on the right track. Remember your good ol’ friends, the mean and median? What about the minimum and maximum? These calculations have a fancy name (data nerds call them “descriptive statistics”) but the calculations themselves are easy. Here’s an example where I calculated the mean, median, and standard deviation of the age of people who were served by a nonprofit organization.

Secret #7. Organized spreadsheets are easier on the eyes.
When you’re staring at a spreadsheet for hours on end, a little organization can go a long way. To stay organized, I create new sheets, freeze panes, and insert filters. I call these “housekeeping skills” because these techniques keep my data neat and tidy. My favorite housekeeping skill is freezing panes. I like to freeze my panes so that my first column and my top row stay in view as I scroll through my data.

Secret #6. Pay attention to what’s not there—your missing data.
Collecting and using data is hard work! You’re bound to run into some situations where you are missing data. Maybe the program participants skipped a survey question because they didn’t understand the wording, or maybe the program staff forgot to enter the information into your nonprofit’s database. Either way, you need to know how much missing data you’re dealing with so you can find a solution. In this example, I used conditional formatting to automatically color-code my empty cells:

Secret #5. Quickie 60-second analyses can give you as much information as 60-minute analyses.
The ultimate purpose of data analysis and evaluation is to share findings with other leaders at your organization and use that information to make adjustments and improvements. You don’t need a lot of data, and the analyses don’t have to be complicated or time-consuming. Sometimes the simplest data are the most useful. Here’s an example where I created data bars—miniature within-cell bar charts—to quickly compare each youth’s pretest score and posttest score.

Secret #4. Pivot tables will change your life.
Pivot tables are the fastest, most accurate way to analyze your data – and they’re easier than you think. You can use pivot tables to summarize anything from demographic information to satisfaction survey responses. Check out these videos about pivot tables.

If you’re new to pivot tables, start here. In this video, I explain how to insert a pivot table:

Secret #3. Pictures are worth a thousand words.
Charts can showcase your organization’s most important findings. I have three go-to resources for graphing data in Excel: Stephanie Evergreen’s blog about intentional data visualization, Cole Nussbaumer’s blog about storytelling with data, and Naomi Robbins’ blog about effective graphs.

Secret #2. Don’t waste your time.
Excel contains hundreds of time-saving tricks. These techniques will save blood, sweat, and tears down the road. The lower, upper, and proper formulas are my favorite time-saving techniques.

Secret #1. Ask for help.
I’ve been analyzing data for a decade, but I still get stuck. Here’s some advice for those moments when you hit a wall. First, you won’t break Excel. If you click something and it doesn’t work, simply click “undo.” Second, Excel does a lot more than you think! Have you noticed all those little icons along the top of the Excel screen? Keep reading the icons until you find what you need. Third, go easy on yourself. You won’t become a data ninja overnight. It’s okay if you have to re-watch the videos a few times. Finally, ask for help. Tweet questions to me at @annkemery, comment on Excel for Evaluation to request tutorials, or email me to request in-person training.

Ann Emery works for Innovation Network in Washington, D.C. Innovation Network is a nonprofit evaluation, research, and consulting firm. They help nonprofits make sense of their data, learn from their work, and improve their results.

 

Tags: ,

33 Responses

  1. Ruth Schubert says:

    Awesome, helpful post. thanks!

  2. [...] Top 10 Spreadsheet Secrets From A Nonprofit Data Nerd | Beth’s Blog. [...]

  3. Becky says:

    Thank you very much. I helped teach excel (long, long ago), and still use it daily as a simple, flat file database for all sorts of purposes. I’m delighted to have a link to share with those afraid of it, rather than have to hand-hold them through their challenges. We computer literate data nerds are in short supply, and online tutorials allow us to make better use of our time. Thanks again!

  4. [...] Note from Beth: I made a personal goal for myself to get past my dislike of spreadsheets and become more fluent. First I had to "Stop Fearing the Spreadsheet" and then started to interview nonprofit data nerds about their Excel secrets.  [...]

  5. Andrew says:

    Thanks for the round up! Super useful.

    My favorite formula is =CONCATENATE (It puts things together.)

    FYI – There is some sort of html typo in the “lower, upper and proper” link. Goes to http://www.youtube.com/embed/yvA9AXUCt94 but formatting is wrong so doesn’t work.

  6. Lisa says:

    Where have you been all my life? Just spent the last week creating Excel charts for my headquarters office staff (I’m a lowly plebe!), and could have used much of this! But let me tell you, after doing 75 charts, I’ve learned a bunch. Now, on to pivot tables!

  7. Mario Morino says:

    Great practical advice!

  8. [...] Note from Beth: I made a personal goal for myself to get past my dislike of spreadsheets and become more fluent. First I had to "Stop Fearing the Spreadsheet" and then started to interview nonprofit data nerds about their Excel secrets.  [...]

  9. Mark Hallman says:

    Great article. Having worked in the business world for most of my life, I am always surprised at how little we measure things in the non-profit world. The need to measure and test is increasingly important as funding tightens up. We need to measure and understand what works, and what gives results, and not just trust our gut feelings. We should all be Data Nerds!

  10. Ann Emery says:

    Greetings all,

    Thanks for your kind words about the tips and tutorials! I hope you find the advice to be practical for you and your colleagues in the nonprofit world.

    If you’d like to learn about charts in Excel… Is anyone going to NTEN’s 13NTC conference in Minneapolis next week? I’m giving an Ignite presentation on Thursday, April 11 from 7-9pm about going “Beyond Boring Bar Charts.” I’m going to share strategies for fooling Excel into making up and down bar charts, side by side bar charts, and back to back bar charts. With a few tweaks in Excel, you can produce modern and streamlined charts yourself (aka without having to hire a graphic designer). Hope to see you there!

    Ann Emery (guest author)

  11. [...] as nonprofit expert Beth Kanter explained in a recent blog post, it doesn’t have to be that way. There are big advantages in knowing how to put [...]

  12. Beth Kanter says:

    Ann thanks again for a fantastic post and hope to see your presentation!

  13. All I need to know about Excel I learned from Ann K. Emery! Thrilled to see her guest post on this blog! :-)

  14. John says:

    Great tips. I have been using Excel since it first came on the scene. Just keep in mind that most of the above tips are generic to spreadsheets, not specific to Excel. There are other options out there, like Open Office’s Calc (which is open source and free).

  15. Lisa Miller says:

    Wonderful article and well timed for me. I was just scanning the web yesterday for this exact info! Thanks.

  16. Bob Filbin says:

    Even with R, SPSS, STATA, etc., Excel is still the tool I use most often. IF-THEN statements are my bread and butter!

  17. Fran Hamilton says:

    Brilliantly helpful thanks. I intend to put much of this into practice as soon as possible.

  18. [...] Note from Beth: I made a personal goal for myself to get past my dislike of spreadsheets and become more fluent. First I had to "Stop Fearing the Spreadsheet" and then started to interview nonprofit data nerds about their Excel secrets.  [...]

  19. Bonnie Zink says:

    Thank you for sharing, Beth. I am amazed! Everything I wanted or needed to know about Excel and more is right here. Brilliant!

  20. Glen Kendell says:

    Great post, Beth. Getting real about data is an emerging trend in the nonprofit world, and best practices such as these will be useful for those who are getting started.

  21. [...] geht an die Sammlerfirma 20min: Erste Jahresspende geht komplett an Fundraiser Beth’s Blog: Top 10 Spreadsheet Secrets From A Nonprofit Data Nerd 101Fundraising: The ask that got the fundraiser Far Edge of Promise: 5 Things Effective Fundraisers [...]

  22. I can’t thank you enough for compiling this very useful excel information! I seriously owe you some money for the amount of time you have saved me. Who knew pivot tables were this easy to work with!

    Thanks again!

  23. [...] http://www.bethkanter.org/excel-data-nerd/ Share this:PrintEmailTwitterFacebookPinterestMoreDiggStumbleUponRedditTumblrLike this:Like Loading… [...]

  24. [...] Top 10 Spreadsheet Secrets From A Nonprofit Data Nerd | Beth’s Blog. [...]

  25. [...] Note from Beth: I made a personal goal for myself to get past my dislike of spreadsheets and become more fluent. First I had to "Stop Fearing the Spreadsheet" and then started to interview nonprofit data nerds about their Excel secrets.  [...]

  26. [...] Excel tips from a data nerd. [...]

  27. Oz says:

    Awesome article!

    #2 and #1 go hand-in-hand … stop fiddling around trying to figure out the SUMIFS syntax and wasting time. Go ask for help.

  28. You’ve covered a lot of my favorites, and there are some other must-haves in the responses (Concatenate and If-then). The one I can’t live without not yet mentioned is VLOOKUP. This is a huge time saver if you have two lists that share a certain attribute (like a column for email addresses) and you need to pull the data from one list into the other list. Huge, huge time-saver.

  29. Ann Emery says:

    Hunter,

    Great additions! I’m also a big fan of vlookup, if, and concatenate (although I tend to use & most often than concatenate).

    Here are some videos about those skills:
    vlookup: http://emeryevaluation.com/excel/importing/
    if: http://emeryevaluation.com/excel/cleaningrecoding/#video5
    &: http://emeryevaluation.com/excel/extras/#video15

    Thanks for reading!
    Ann

  30. [...] Beth Kanter is a self-described “data-nerd”, check out her top 10 Excel tips. [...]

  31. [...] blog shares Ann Emry’s tips for creating charts with your nonprofit’s [...]

  32. [...] Beth Kanter is a self-described “data-nerd”, check out her top 10 Excel tips. [...]