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.
