Episode 8 of What I Can Do With Python This week, I tried something different. This time, instead of using the Streamlit Web App, I extended Microsoft Excel functionality by creating my own custom Excel formula powered by Python. Yes, a Python-Powered Excel function. For a long time, I considered learning VBA so I could build custom formulae tailored to my workflow. But the more I worked with Python, the harder it became to commit to VBA. Compared to VBA, Python just has more tools and is easier to work with, debug and maintain. Inspired by Felix Zumstein's idea in his book, 'Python for Excel', where he stated that "....by learning how to combine Excel with Python, you can have the best of both worlds.", I used the xlwings library to integrate Python directly into Excel. With this, I was able to build a custom Excel formula designed for something many researchers and data analysts deal with often, analysis of demographic characteristics of respondents... In academic research, we usually present the demographic characteristics of respondents as frequency and percentage distributions if categorical variables. Normally, this is done with Pivot Tables, but I wondered, "What if this entire summary could be generated with just one formula?" So I built exactly that. How the Formula Works The first argument is the Excel range containing the categorical data. Once you pass that range, the formula automatically generate a frequency and percentage distribution table. Other optional arguments allow you to: • Control the output • Include or exclude totals • Add or remove percentage signs • Combine summaries or generate them separately • Specify the number of decimal places for percentages. Controlling Category Order By default, the results appear in alphabetical order. But I also implemented a simple trick inspired by SPSS-style value labels. For example, if your dataset contains: Childhood Adolescent Early Twenties Late Twenties Adulthood You can structure the data like this: 1_childhood 2_adolescent 3_early twenties 4_late twenties 5_adulthood The numbers control the order while the prefixes are automatically removed in the final summary. What Happens Behind the Scenes? This project goes beyond basic Python. It involves: • Data manipulation with Pandas • Table reshaping and sorting • Data aggregation • Understanding how xlwings converts Excel ranges into Python objects. The result is a workflow where Python handles the heavy logic while Excel remains the interface. This is the beauty of it, you keep Excel's familiarity, but gain Python's power. This time, instead of a Streamlit app, I'm sharing a demo video showing how the formula works directly inside Excel. Watch the demo and let me know what you think.👇🏼 Is there a business logic or automation you've always wish Excel could do for you? Let's talk about it! See you in Episode 9. #Python #Excel #DataAnalysis #Automation #Researchers #xlwings #BuildingInPublic
This is great indeed 👏👏👏
Nice