New tools come out every day claiming to help us work more efficiently and deliver better results, but there's one that's been around for years and is still largely unknown: Excel macros.

Maybe you've heard the office geek (🙋🏻‍♂️that's me) talk about them, but it went in one ear and out the other because you already do what you need with Python. Maybe you've noticed that it's hard to edit the information you process with Python or similar tools.

Before the AI boom, this post would be a 3-hour video tutorial covering specific use cases. Today, with powerful programming tools like Claude, you don't need the tutorial. I get that it's a bit scary at first, but with AI's help you'll be able to do what you need, and over time you'll keep finding more use cases and making your life easier. You can also forward this email to friends and make their life easier :)

One use case I've encountered in most projects I've worked on is generating configuration files for data acquisition systems, where we define hundreds or thousands of parameters, each with its valid value range, units, and sampling frequency. We can enter this configuration into the program manually, and unfortunately that's still what happens sometimes, dedicating one person to typing for a week and another to review it for another week. Normally, you can also import a text file with the configuration. When the changes are small it's acceptable to do it by hand, but when we need to copy more than 10 numbers manually, the chances of making mistakes without noticing keep growing.

If this information is complex enough, I'm not capable of working with it directly. I'd love to copy the file to Excel and modify it, but if I have to do it manually I could lose a thousand hours. And then convert it back to text? Kill me. Plus, this isn't a one-time thing, it's work I'll have to do every other week.

The solution? Easy! I just need to look at the basic structure of the file and explain it to Claude. Then I ask it to generate an Excel macro that reads the text file and displays it in an organized and clear way in Excel so I can modify it. And to keep in mind that after modifying it I'll want to generate the text file again.

Once we have a functional base, we can ask it to add colors, change the column format, add extra information from another file, compare with another table... pretty much whatever you need.

To show you that automation is also in the small actions, some simple examples are things you can do by pressing a key combination instead of having to go up and down through menus with the mouse. For example, merge the cells you have selected, or unmerge them if they already are. Or mark all cells that contain a formula with a color, or insert the date and time automatically, or paint a cell with an error a certain color, without having to move the mouse.

I get that automating these repetitive or long tasks seems like a waste of time, especially if you've never done it. But that's precisely why the potential for improvement is greater, and in this case you don't even need to learn to code, you just need to explain to the AI what you need and it'll guide you.

Already using macros? I’d love to hear your use case!
If not yet using them, subscribe to receive more information like how-tos and use cases:

See you next week,

Sol