Copilot for Excel: How to work with Python code

One of the key insights Excel users should appreciate about Copilot is its vital role in keeping Excel current in the AI era. It achieves this by innovatively integrating multiple intelligence sources, expanding Excel’s functions beyond traditional spreadsheets. Today’s era is characterized by growing system interoperability.

In particular, I’m excited about how Copilot can Python more accessible to everyday Excel users. With Python’s integration into Excel and with the help of Copilot, even those new to coding can easily tap into its powerful data analysis, visualization, automation, and AI capabilities.

To follow along with this exercise, download the file below:

 

This is a modified and simplified version of a famous mileage dataset. Ensure this file is properly configured to work with Copilot before proceeding.

Why use Python in Excel?

Before we proceed with incorporating Python code into Excel, it’s essential to understand the benefits of doing so, a topic I’ve frequently discussed on this blog. For this post, we’ll concentrate on two key advantages:

• Handling missing values: Unlike Excel, which struggles with identifying and summarizing missing values due to the ambiguous nature of blank cells, Python offers clearer methods for dealing with this issue.
• Enhanced data visualization: While Excel provides basic charting tools, these can often be inflexible and challenging to modify. Python, on the other hand, enables the integration of advanced chart types that have emerged in the field of data visualization, offering more robust and adaptable visualization options.

To address these Python coding tasks, we’ll be utilizing Copilot as our assistant. For more quick wins on using Python with Excel, check out this post.

Working with missing values

My initial objective with Copilot is to determine the percentage of missing values in each column of my dataset. I posed the question to Copilot, but it seems quite unclear on how to proceed.

There is, however, a workaround for this unhelpful answer. Instead of asking Copilot to address the question in Excel, I’ll see if it can provide a solution using Python.

That actually worked!

Remember, Copilot is trained on OpenAI’s GPT model, which draws from a wide range of text sources, including the extensive collection of Python resources available. Given that Python is one of the most popular programming languages, it stands to reason that Copilot works will with it.

One of the keys to elevating a good prompting session with Copilot into a great one lies in the act of refining and following up on prompts. For instance, I’m going to ask it to sort these results from high to low. To realize this need, you might have to execute the Python code and examine the outcomes firsthand. Ideally, Copilot would simplify the process of running and exploring Python code that it generates, but it hasn’t fully achieved this capability yet. For the time being, we’ll manage with the available tools.

OK, now that Copilot has generated a considerable amount of code to address this issue, I’m going to import it into Excel. As it stands, Copilot doesn’t directly facilitate the execution of Python code within your workbook. This means you’ll need to manually incorporate and run the code yourself. Additionally, I’ll use this opportunity to make a few adjustments to the code according to my preferences.

It’s important to view the AI-generated results as a starting point; you’re always encouraged to apply your own expertise and preferences. This underscores the value of having foundational knowledge in a programming language like Python, especially when leveraging generative AI more effectively.

Creating visualizations

Let’s move on to our second example. Excel struggles with generating multiple plots simultaneously. In fact, if you ask Copilot to create relationships across all variable pairs, it will typically only plot one pair.

Therefore, this task is better suited for Python, specifically aiming to visualize all pairwise relationships. I’ll even specify the name of the DataFrame I created in Excel, making it easier to transfer the code into the workbook. I also plan to differentiate the results of my plot by color based on the origin column.

Achieving such precise results requires specific, clear language, a combination of familiarity with Python’s basics, and a willingness to interact with the code generated by Excel. In the next section I’ll offer some tips for effective Python prompt engineering in Excel.

But first, let’s look at our resulting plot. Again, keep in mind that I have truncated the resulting code based on my own familiarity with the Python programming language and the Python in Excel environment.

This is an incredibly informative plot, created with minimal Python code, something that would have been quite challenging to achieve in Excel alone.

If you’re following this example, remember that integrating Python plots into Excel has its quirks. For more information on how to resize them effectively, I recommend checking out this blog post.

Tips for generating Python code with Copilot in Excel

Copilot is a relatively new tool in Excel that users are still getting accustomed to. Introducing the capability to request results in Python on top of it all may seem overwhelming at first. However, this integration presents an exciting opportunity to maximize the utility of Copilot, especially given its proficiency with Python. It’s noteworthy that Python functionality was introduced in Excel a few months prior to Copilot, suggesting a strategic move towards their integration.

In light of this, here are some practical tips for effectively generating Python code with Copilot. While these recommendations may resemble standard prompt engineering practices, they are especially valuable for typical Excel users venturing into Python code generation within this context. Additionally, for more comprehensive advice on code generation through generative AI tools, consider exploring this post.

• Be specific about your task: Clearly describe what you want the code to accomplish. Include details like the data you’re working with, the expected output, and any specific libraries or frameworks you prefer. This should be straightforward in Copilot, as you can only work with one table at a time and must directly reference it. However, I suggest specifying what you’d like the resulting Python DataFrame to be named because Copilot often doesn’t pick up on this itself.
• Provide context: When crafting your prompt, include an overview of your project or task. This helps Copilot understand the overall objective and how the current request fits into it. Detailing the broader scope enables a more targeted and relevant response.
• Mention preferred libraries: If you have a preference for specific Python libraries, such as Pandas for data manipulation, NumPy for numerical computations, or Matplotlib for visualizations, please specify. This allows for tailored code examples that align with your project requirements and familiarity.
• Iterate and refine: Copilot’s initial response may not perfectly meet your needs. Use it as a foundation to build upon. Feel free to request modifications, optimizations, or clarifications. Iterative refinement helps in honing the solution to better match your specifications.

With additional practice in using Python code and Copilot prompts, you’ll refine these skills further. It all boils down to effective problem-solving: pinpointing the issue, clarifying your goals, and identifying what you need to reach them. By leveraging Copilot and Python, you can iteratively make adjustments until you achieve your desired outcome.

What questions do you have about getting started with Copilot, using Python in Excel, or the convergence of the two? Feel free to share your inquiries in the comments.

The post Copilot for Excel: How to work with Python code first appeared on Stringfest Analytics.