Today happens to be the fabulous Jewish holiday of Purim where we celebrate the bravery and leadership of heroines, Vashti and Esther. The more famous aspects of Purim are dressing up in costumes, making political theatre, eating Hamantaschen cookies, making donations and boo-ing every time we say the bad guy’s name (Haman! Boo!!!!). A lesser-known part of Purim is v’nahafoch hu… a celebration of things being turned upside down.
Sometimes we even need to turn our spreadsheets upside down — or at least rotate the rows and columns! That’s the topic I’m going to feature in this blog post. But first, some explanations of v’nahafoch hu, from source 1 and source 2:
The story of Purim is the story of v’nahafoch hu—literally, of events fundamentally and profoundly reordered. It is a story of carefully made plans turned topsy-turvy by Divine intervention; of plots, and subplots; of hopes unrealized and destiny fulfilled. It is a story of miraculous transformations…
Haman [boo!!] builds gallows for Mordechai, and is hung on those same gallows. Haman plans a fantastic reward for himself, and instead has to bestow this reward on Mordechai. [and more….]
Bear with me while I make a leap from the Purim story (arguably about liberation!) and data for changemakers. What happens when you need to turn your data upside down and rotate rows and columns? Uh oh, this sounds like a lot of re-typing and copy-ing and pasting (or throwing your computer out the window?) – unless you know this special trick.
When would you need to use this tool? Here are two scenarios:
- You realize late in the game that you have more columns than rows and it would be easier to see your data if the columns were arranged as rows. For example, if you have data arranged by month. It’s much more economical to arrange these data with months going down instead of across. Don’t retype, just transpose!
- You downloaded your data from another source (like a Voter database or a financial database like Quickbooks) and in order to manipulate the data, you need to swap the rows and columns. This was really common for me at my last job! Data would come out of one system with categories going down the first column, but we actually needed the categories going across as columns. Don’t give up! Just transpose!
How To in Excel:
- Select the data that you need to transpose.
- There should be a black border around the cells, and they should be highlighted blue
- Copy the data (use CTRL+C or Command+C, or use the Copy menu)
- Decide where you want to paste the data (usually in a new Tab)
- Right-click with your mouse, and hover over to Paste Special options
- Select “Transpose” (see image)
How To in Google Sheets:
(image borrowed from Google Sheets tutorial)
- Decide where you want to paste your Transposed data
- Type the formula =Transpose(
- Select the data that you want to transpose, then close your parenthesis (“), then hit ENTER.
However you do your work to turn our political system upside down, I hope you can make good use of this post, whether it’s inspiration from the Purim story or the technical tips about rotating your data that most resonates with you. I’ve been doing movement work for long enough to know that we desperately need both!