If only

“If only, if only,” the woodpecker sighs, “the bark on the trees was as soft as the skies.” And the wolf waits below, hungry and lonely. He cries to the moon, “if only if only.”

Holes, by Louis Sachar

Have you ever raged at your technology and shouted “IF ONLY YOU COULD JUST DO THIS ONE THING?” Another quote I use a lot, “YOU HAVE ONE JOB!” No? Ok, well I guess it’s just me then 😉 If my experience proves true, lots of people who read this blog have felt this way! In fact, I think this is a pretty universal feeling. We’ve all felt powerless and frustrated when faced with tech limitations. One of these situations occurred at my job today.

One of my co-workers came to me with a dilemma. One of our reports exports from our database into a spreadsheet. So for all intents and purposes, her issue was a spreadsheet problem, not a database problem. Ok, moving on…

The data in the spreadsheet are organized as a Table, which allows for simple filtering of columns. This is absolutely essential, since the spreadsheet has an entire organization’s worth of data, and most of the time, you just want to see your slice of the pie.

When this colleague is working with the data, she has trouble with sorting. The “table” format is great for filtering or sorting from A->Z, but there’s no intuitive path for “sub sorting” like… first sort by region, and within each region, sort by donation from largest to smallest. Sorting is a different set of features in Excel, and while you can layer Sorting on top of a Table, that requires some extra steps and zero prompts. I have complete compassion for this co-worker! What an ordeal to feel like she was stuck with data that was completely useless, or daunting-ly time consuming, to manage. Ugh!

Speaking of filtering, that is good and fine, but all of the Excel formulas don’t respond to filter criteria. If you filter down to only donations “this year,” your Excel sum will still take the Sum of the whole entire data set. So, now you’re faced with copying your filtered data into a new tab where you can take the sum of just those rows. Blech, that sounds like a recipe for disaster! Duplicate datasets almost always cause confusion and discrepancies. My recommendation is to avoid as much as possible!

So. Where does that leave my beleaguered program officer? This was definitely an “If Only” moment! “If only I could sort and summarize my data!”

… then …

then what? Here’s where I want to encourage you to pause and reflect (and I wish I had asked her to do the same!). Then… will you have more time? Less stress? An answer to a quantitative question? A tool that is actually working as designed? Something else? Knowing WHY you need a solution is as much as 80% of the solution itself! Plus, once we get an answer, I want to confirm with you that you now HAVE that thing you needed. This will help us agree that we’ve successfully cleared the hurdle.

This was one of those magical, teachable moments – every spreadsheet nerdette’s dream. First, I showed her how to “Table-ify” data using “CTRL+A/CTRL+T” commands. Then, I showed her how to un-table by going to the “Design” tab and clicking “convert to range.” Next, I showed her how to use the Sort button on the Data tab to create sophisticated sorting logic (table or no table!). If only… YES! YOU CAN!

Last but not least, it was time to discuss her dreaded subtotals. Can you add up the results of a filtered Table? You-bet-your-bottom you can! I didn’t go into SumIf formulas, since this colleague is already using filters to drill down to just the data she needs (but SumIf(s) might be just right in other scenarios). However, she will DEFINITELY need the Subtotal function, which is magical in a bunch of ways that would double the length of this blog post. Suffice it to say, the Subtotal function was just what the doctor ordered. Want to learn more about this powerful and versatile formula? Check out this great tutorial!

All of this was possible in 10 minutes or less! Sometimes you need a spreadsheet guide to teach you the basics and encourage you on your data empowerment journey. This week, *I* certainly did! I reached out to my trusty friend Emily to talk through a thorny spreadsheet quandary and I ended up coming to the conclusion that i could do “most” of what I was trying to accomplish by using the “Slicers” feature in Excel. Spreadsheets have SO much potential! I hope that after reading this post, you feel more able to dream big and ask “if only…..” because while that phrase often signals “end-of-rope” level frustration, it can also be the first step in untangling a tricky puzzle, leading to an awesome answer.