Dear Analyst #67: Automating tedious tasks with scripts and solving problems software can’t fix

This episode is actually a recap of a talk I gave at a meetup. After reflecting a bit about the subject matter, I wanted to discuss some other topics that are more important than writing VBA scripts or doing stuff in Excel. At the meetup, I discussed a VBA script and Google App Script I wrote for filling values down in a column. I actually published these scripts in a previous episode, but went in-depth during the meetup on how the scripts work. If I step back for a minute and ask myself: “why did I create these scripts in the first place?” To solve a simple problem that I’m sure many analysts come across. More importantly, it’s a problem that doesn’t have a clear solution which the our current software (Excel and Google Sheets) can fix easily.

Software that fixes your problems

For those of you who are:

Using a recent version of Excel
On a PC
Have a Microsoft 365 subscription (depending on your package)

Congratulations! You are able to use Power Query to transform and clean “dirty” data and the problem described in this episode is easily solved with the software. All you have to do is click this option in Power Query to fill values down:

For the rest of us (Mac Excel or Google Sheets users), you’re stuck doing this manually. Why does this feature have to be reserved to a small group (relatively speaking) when this problem is faced by thousands of people who may not have the same access as someone who works in the enterprise?

Cleaning data is part of any analyst’s job and we should be able to do these tasks as quick as possible so that we can move onto more interesting projects. The fact that you need to have Power Query to fill values down like this is annoying to me. Do you ever go out of your way to prove a point; even if it’s an extremely inefficient use of your time? Creating these VBA and Google App Scripts was just that for me. Instead of relying on the software to do the job for me, I ~~created~~ hacked up an inelegant but simple solution to hopefully give people more access to simple tools for cleaning up data.

Building for an audience of one

I might be over-estimating the number of people who have this fill values down problem. Maybe it’s a few hundred people? Maybe less than 100? Who knows. The important thing is that I had the problem and needed to solve the problem for myself.

Perhaps you are in a position where you can’t spend a few hours to learn how to write a script to automate one aspect of your job. That’s understandable. You need to crank out reports and time spent away from cranking means you’ll have to work after hours to get your job done.

I used to be on that hamster wheel, until I stepped back and saw the forest for the trees. Excel is just one tool in your vast array of tools to analyze and visualize data. There’s a whole world of databases, data pipelines, machine learning, and more for you to explore. Just staying in the “Excel lane” is how one gets pigeonholed into a job, a career, a life.

Learning how to write scripts changed my perspective on more than just Excel. I realized I could build tools that help others save time because I knew it saved me time. By building for an audience of one, you are in fact building for an audience of many.

Meetup recap

This write-up definitely meandered a bit but I think that’s ok. You can watch the recap of the meetup below and get lost in the details on how I loop through arrays to make the script work. The important lesson I hope you’ll walk away with is thinking outside of what Excel or Google Sheets has to offer into the other platforms and tools that come before or after your spreadsheet.