The other day at work, I almost did something amazing. I was in the middle of a series of tedious, onerous spreadsheet manipulations, the sort of thing I’ve tended to accept with a smile as part and parcel of the job. Detail-oriented, repetitive, thankless tasks…that’s just kind of always been my wheelhouse.
It’s a weakness of mine that, faced with a job like that, I tend to hunker down, martyr up, and just do it, keeping myself interested by imagining it as some kind of cruel reality show challenge. A normal person would say to herself, “There has to be an easier way to do this.” And then she would find that easier way and be on to bigger and better things.
I’ve been in a new job the last year, and in it, I’ve learned many things that would’ve saved me much time and heartache in my previous position. In my defense, back then I was in a newly-created position, an office of one, with no one to ask, largely making up processes as I went. Nobody told me about Excel filters or pivot tables or that you can use formulas for things beyond simple number crunching. Add to that my natural tendency toward Luddism, and it was a recipe for Doing Things the Hard Way.
So on this particular day, about an hour into this latest crazy-making item on the to-do list, I started wishing I had a robot who could do this for me. So mindless and repetitive! Then I remembered that there exists in the universe a thing called a macro. But “macro”…it’s one of those words that always made me tune out and write off as something that is hopelessly beyond me, like calculus or amortizing something on your tax return. Lalala. Best leave that to the experts!
But another thing I’ve learned in this Year of Ten Thousand Lessons is that, while computer programmers and IT people are generally smart, they are not supernatural beings with impossibly unattainable levels of genius understanding. They use a wide range of abilities, tools, and resources to solve problems. And sometimes, when all else fails, they Google it.
I know how to Google it! Google it I did, and in two shakes of a data analyst’s tail, I had recorded my first macro!
With shaking finger, I clicked run macro, and my screen flashed from worksheet to worksheet with eerie superhuman speed, painlessly creating a department-specific version of this master interactive pivot table.
It was beautiful. Life-changing, even. For a moment, I looked at my faint reflection in the computer monitor and saw myself with new eyes, not fully believing what I had just achieved. I took a minute to savor my victory and to text my husband excitedly to sing my praises.
Then I looked more closely at the output and went from hero to zero.
I realized my lovely macro didn’t actually work right. Instead of replicating just the cell references, it seemed determined to replicate the actual content of the cells. So now, instead of having a Political Science version and a Psychology version of the table, I had two Political Science versions. Not at all helpful. Did I mention we have 27 different departments?
I tinkered with a few theories, attempted more Googling—much harder when you’re not sure exactly how to properly word your problem—and finally got a 98-pound-weakling version of the macro to work by paring it down from its original heavyweight status till it only did about a third of the work I was originally asking it to do.
But, since I do have to create 27 different versions of this file every semester, I think I still win for automating 33% of the workload. Like I said. Almost amazing. The glory is all the more bittersweet having slipped through my fingers just when I thought I had it firmly grasped.
Meanwhile, if you need to find me, I’ll be the one entering data duplication parameters and updating pivot table references ad nauseum until my eyeballs have fallen out of my skull and I’ve decayed into a dusty skeleton.