The IllumiLab is excited to bring you this five-part blog series from one of our clients, Megan Ondr-Cooper, the Manager of Analysis & Outcomes at CASA of St. Louis. Since we began working together, I’ve been so encouraged and impressed by how much Megan is able to do with so little when it comes to data analysis and visualization. In this series, she’ll share some of her best tips and tricks for getting the most out of Excel. Without further ado, here’s Megan…
This blog series will be comprised of five posts. Today, I will cover first steps in data analysis and my favorite Excel shortcuts. Each of the upcoming posts will feature the Excel tools I use most often to help my program understand and use its data. Each post will illustrate the ways you can apply Excel to your key tasks and questions and will include links to step-by-step instructions to use each feature.
The Value of Excel
Nonprofits collect a lot of data. If the data they are collecting are meaningful and trustworthy, they want to use it for decision-making and improvement efforts, not just funder reporting. To do so requires data be transformed into knowledge, through analysis and reflection. (The Wisdom Cycle by Deborah Elizabeth Finn does a great job of illustrating the ways we can use technical and practical knowledge to transform data into wisdom that informs great decisions, and Idealware’s Data Sanity for Nonprofits expounds on those ideas.)
Many people believe the key to using data is fancy software. (P.S. Check out our blog series “It’s Just a Database” to keep the value of databases in perspective.) However, even the best software won’t analyze your data in all the ways you need (at least not without a hefty bill for all the custom reports you’ll need!). Besides, no database will know your data better than you do. Conducting your own, hands-on analysis might be the best way to answer your stakeholders’ important evaluation, learning, reporting, and strategic questions. Lucky for you, you probably have a powerful data analysis tool already installed on your computer – Microsoft Excel!
Before You Dig In
Before jumping into your dataset, there are a few steps you should take.
- Determine the purpose of your analysis. What do you want to know? This will help you determine what data you need.
- Make a copy of the original dataset before changing or deleting any data. I’ve had to start my work over from scratch far too many times because I deleted a column I ended up needing later!
- Check your data for errors before you begin slicing and dicing. Common errors that can undermine your analysis are outliers, blanks, duplicates, and data entry errors (spelling, format, etc.). Posts two and three will include tools you can use to check for errors.
- Keep a list of your steps as you go if you will be repeating this analysis again, say for a quarterly report to a funder and so you can justify and explain all of your findings.
- Identify additional variables you may need to answer your analysis questions. For example, your data may include date of birth but you need ages. Posts four and five will cover tools you can use to create new variables in your dataset.
Avoid the Mousetrap
One of the easiest ways to make Excel more accessible and usable is to use keyboard shortcuts instead of endlessly scrolling and clicking.
- Insert a cell, row, or column = Ctrl + Shift + Plus sign
- Navigating the workbook
- Move to edge of data region = Ctrl + Arrow
- Move to corner of data region = Ctrl + End or Ctrl + Home
- Move to the next/previous worksheet = Ctrl + Page Down or Page Up
- Selecting data
- You can add the Shift key after the Ctrl key in navigation shortcuts to select the data between the active cell and destination cell.
- Select row = Shift + Spacebar
- Select column = Ctrl + Spacebar
- Select entire data region = Ctrl + A
To learn more keyboard shortcuts, check out Exceljet’s comprehensive shortcut guide (including shortcuts for both Mac and PC users).
Bottom Line
With some additional knowledge and skills, you can start to use Excel to answer the questions your organization cares about most. Try implementing a few keyboard shortcuts to save time navigating your spreadsheet and reduce your risk of carpal tunnel syndrome (wink)! Next time, we’ll take a look at how filtering can help you get familiar with your data, identify errors, or examine subgroups.