3 new-to-me spreadsheet capabilities (plus a bonus)

The past couple of weeks, I have been working on analyzing survey responses for a national, progressive organization. For this project, I expanded a methodology that I documented here. It’s been SO fun and rewarding to sink my fingertips into a spreadsheet-only project (CRMs be damned! jk!). There’s something so IMMEDIATELY GRATIFYING about spreadsheet formulas and formatting that other database platforms just can’t do. Gushing aside, here are three techniques that you can use for making meaning out of your spreadsheet data:

1: =Large()

The dataset I am working with is extremely WIIIIIIIIDE (goes all the way out to column AAA! Is this the widest spreadsheet I’ve ever worked with? Hard to remember but it very well may be. In some cases, we wanted to compare the popularity of different social justice tactics to each other. It was relatively simple to create SUMIF or COUNTIF totals, but comparing them with the naked eye becomes difficult in a sea of numbers. Also, the different segments of data had different numbers of people in them, so the range of values was quite broad. Therefore, we wanted to find the largest in the set (this indicates the most popular social justice tactic) and divide it by the number of respondents in the segment. This result represents the degree of popularity (or degree of consensus, perhaps) so that we could compare how strongly different segments felt about their most popular tactic.

In the screenshot above, 171 represents the total number of people in the segment. the next 5 cells represent the number of people who prioritized the given tactic in that column. The percent shows the number of people who selected the winning tactic divided by total number of people.

Usually if I need to see the largest value, I sort a dataset or a column from largest to smallest. However, this would not work here, since I needed the largest of each row, and then the next row, and then the next.

Switching from a visual workflow (sort and rearrange spreadsheet) to a formula was a big breakthrough for me!

2: =Search()

This formula SERIOUSLY came in handy when I was trying to find certain key words in various columns of data.

My survey platform produced results in a format with – basically – 2 header rows. One stated the overall question and the second stated the multiple choice selections.

This format was good in some cases, but challenging in other cases. In the example below, I was trying to find the column that had the “magic text” in it that powered my dynamic dashboards. The formula “searched” for the text in Row Two (that’s Data!2:2 in the screenshot below). If it didn’t find an answer, the formula resulted in an error. Boo! So, I wrapped it in an IFERROR() formula and followed the same procedure, this time searching in Row One (that’s Data!1:1)!

This was a very technical and specific use case for the =Search() formula, but there are many other practical applications for it! Some examples could be searching for key words in political text replies, searching for city names in address data, or searching for, well, any text in any other text!

The formula will generally return [a number] (the character count where the magic text begins) or error (text does not exist). If you want the formula to DO something as a result of finding/not finding text, you can incorporate the Search() formula into a string of formulas as I did in the example above.

3: Conditional Formatting

When you meet me in person, you’ll see that I love to wear bright colors and stretchy/flowy fabrics. However, I am pretty color-conservative when it comes to my spreadsheets (I find conditional formatting generally distracting – or worse, misleading!). Also, I find it concerning that I can’t tell the difference between conditional formatting (ie data rules) or someone’s own graphic design (ie coloring cells manually) which can quickly become out of date if the data change.

Perhaps I am also a bit lazy … I find data transformations and formulas more fun than conditional formatting rules.

But I’m eating my humble pie on this one!

I used conditional formatting rules (this time in Excel, not Google Sheets) to highlight the top 3 values in a row of data (yellow) and the top 1 value in a row of data (green) so that we could create a sort of “heat map” of popularity. In the few times that I have used Conditional Formatting professionally, it has mostly been to show negative numbers in red or something along those lines. In other words, I compared numbers to a static criteria, not a variable criteria (ie the biggest number in [random set of numbers] is different than [is this number bigger than 0?].

Truthfully, I didn’t know Excel had this capability but it was SO easy to set up!

The Conditional Formatting menu has Top/Bottom Rules where you can highlight cells based on “top” “bottom” or even “percents!” (Queers – if you are chuckling at this, you are in good company with yours truly – spreadsheets are so queer, but that’s a topic for another day!)

Bonus!

I was also playing around with Conditional Formatting rules in Google Sheets and got great visual and analytical pleasure from the “Color Scale” option which, again, creates a gradient or a heat map based on the values in the dataset. You can see the menu for this option, and the results, in the screenshots below.

the So What of it all

If you are going to put time into conducting a survey (and asking for time from survey responders!) I hope you have some ideas about how you will analyze, learn, and implement findings from the survey! Spreadsheets might just be your best friend for doing this. Depending on the complexity of the survey, your survey tool may be able to give you summarized results to get you started. However, if you want to look at how people with specific identities or experiences responded to subsets of questions (and as activists, we usually do, or should!), you will likely need some spreadsheet action to get your information into a usable, actionable format.

I believe that all activists can learn these skills and that learning them can be deeply powerful and empowering! That’s why I take the time to write out what I’m learning, and I know that’s why so many of you are here. Thanks for sticking with TDAA and for loving up on spreadsheets and each other! The combination of people power and spreadsheet power is… unstoppable!