Category Archives: Excel

Dicas sobre Excel e utilização desta planilha eletrônica, que é a mais utilizada no mundo

Python in Excel, What about it?

Python no Excel
Python on Excel

In August 2023, we were surprised by the announcement of Python integration in a future Excel update. Wow! This is a big deal, and we need to pay attention to what it means, especially to dispel any misconceptions that may be arising around it.

As a prelude, I must clarify that everything I write here is entirely based on my opinion and public information about the subject, with references at the end of the text.

Python vs. VBA

Every fiber of my being, especially those dedicated to programming, wants Python to be the replacement for VBA. Let me be clear on this point: it’s not. There is nothing, or very little for VBA application programmers in this Python integration with Excel, at least from what has been presented.

So, dear VBA programmer, don’t get too excited. The most that will happen is that you can avoid creating UDFs in VBA by solving everything within a concise Python block in a Excel cell.

Who is Python in Excel for?

It’s essential to make this clear: nobody (except Microsoft and the Office product team) has any idea at the moment who the target audience for this implementation is.

If I had to guess, I would consider two fronts:

  1. The Office team is targeting the data mining/data science audience, in other words, data analysts. When tools like Excel, the Powers*, Tableau, among others, start struggling with large amounts of data, these professionals often turn to Python. There’s much to unfold here, as it’s not just Python but rather a micro-ecosystem created to serve this audience.
  2. Since Python is becoming an “almost” universally used programming language, there might be an intention to provide a familiar environment for the market and its users. What makes me a bit skeptical of this theory is that they already tried this with ScriptLab using JavaScript, another widely-used language. Who knows?

But again, for whom is Python in Excel?

I’ve tried to answer this above, as have many other Excel enthusiasts. The fact is, we don’t yet know where this will lead.

Microsoft is an expert at making mistakes, but when they get it right, it’s for real. There are plenty of examples, and Excel itself is one, as it has carried this company for decades.

That’s why it’s too early to predict the acceptance this feature will have. Until about five years ago, Power Query and Power BI were beyond the reach of mere formula writers. Today, they are here to stay.

As an enthusiast, I’m excited. As a programmer, I’m skeptical. As an Excel user, I’m indifferent. As a data analyst, I’m intrigued.

Is Python in Excel for me?

This one’s easy. No. But why?

I am a programmer; I like code. More than that, I like creative freedom. Opening VBA gives me such a great sense of creativity that it’s impossible to compare to the Python prompt within a cell replacing it.

Additionally, Python doesn’t run locally. This is where the success or perhaps the failure of this implementation lies: security, the cancer that has plagued the Office suite for decades that Microsoft wants to get rid of at all costs.

In summary…

It’s early; we can’t conclude much, especially from my perspective as a programmer who likes VBA and has little experience with the Data Science side of Excel.

I am indeed very curious to hear what Power BI colleagues have to say about this. For those of you needing a Python tip, we’re here.

Stay tuned for the next chapters. I have a feeling that Python in Excel is here to stay.

References

StackOverFlow 2023 Survey: https://survey.stackoverflow.co/2023/#section-most-popular-technologies-programming-scripting-and-markup-languages
Python in Excel by Leila Gharani: https://youtu.be/FbBXtqtRnWU?si=f2X3wolD9c0fN-MP
Microsoft Excel just got Python by Fireship: https://youtu.be/8ofsE7xiGho?si=JvyPZ236Oi-O1VF4

Excel – 3 super cool tips to improve you productivity in Microsoft Excel

Excel is a powerful tool that is used by millions of people all around the world. Despite its popularity, many people still use Excel only for basic calculations and data entry. In this article, we will show you three cool Excel tricks that will help you take your spreadsheet skills to the next level.

Freeze panes

Do you ever find yourself scrolling down a large spreadsheet and forgetting which column represents which data? If so, the Freeze Panes feature in Excel can be your best friend. This feature allows you to freeze certain rows or columns in place while you scroll through the rest of the spreadsheet. To do this, simply select the row or column that you want to freeze, go to the View tab, and click Freeze Panes. This will keep the selected row or column in view no matter how far you scroll.

AutoSum

If you are working with a large dataset and need to quickly add up a column or row of numbers, the AutoSum feature can save you a lot of time. Simply select the cell where you want the sum to appear, click the AutoSum button, and Excel will automatically add up all the numbers in the selected column or row. You can also use this feature to find the average, count, and other basic calculations.

Conditional formatting

If you want to make your spreadsheet more visually appealing and easier to read, Conditional Formatting can help. This feature allows you to format cells based on certain criteria. For example, you can highlight all the cells in a column that contain a certain value, or all the cells that are above or below a certain number. To use this feature, simply select the cells that you want to format, go to the Home tab, and click Conditional Formatting. From there, you can choose from a variety of formatting options to make your data stand out.

In conclusion, Excel is a powerful tool that can do much more than basic calculations and data entry. By using these three cool Excel tricks, you can save time, make your spreadsheets more visually appealing, and take your spreadsheet skills to the next level.