How to Automate Tasks with Python Scripts in Excel

This guide will explain how to start automating tasks with Python scripts in Excel.

Python is an excellent programming language for automation. One major feature that Python and other languages have is the ability to define custom functions. With functions, you can perform an algorithm or series of steps in order to return some output or end result.

The newly launched Python in Excel feature will allow users to define their own custom functions, which they can use to automate specific procedures in their Excel workflow.

In this guide, we will provide a step-by-step tutorial on how to use Python in Excel to automate tasks through a custom function.

The Anatomy of the PY Function

The syntax of the PY function is as follows:

=PY(python_code,return_type) 

Let’s look at each argument to understand how to use the PY function.

• PY() refers to our PY function. This function allows us to run Python code on a secure Microsoft Cloud runtime.
• python_code refers to the Python code you wish to run. This value must be static text.
• The return_type parameter allows us to specify the desired return type. A value of 0 indicates an Excel value, while a value of 1 indicates a Python object.
• Do note that the PY function cannot be used with any other Excel functions.

A Real Example of Automating Tasks with Python in Excel

Let’s explore a simple example where we use Python in Excel to automate a specific task.

The table above contains daily sales data for the months of January to December. Using this data, we want to generate a chart for each month of the year that shows the fluctuation of daily sales.

While this is possible with the built-in Chart editor, it will take time to manually set up the charts for each month. Now that Python in Excel is available to the public, we can take advantage of Python libraries such as seaborn and matplotlib to create charts programmatically.

We can use the following Python script to define a function to help with our task:

import seaborn as sns

import matplotlib.pyplot as plt

def plot_sales_for_month(df, month_name):

    # Filter the dataframe for the given month

    filtered_df = df[df[‘month’] == month_name]

    # Plot using seaborn

    plt.figure(figsize=(10, 6))

    sns.lineplot(x=’date’, y=’sales’, data=filtered_df, marker=’o’)

    # Formatting the plot

    plt.title(f’Daily Sales for {month_name}’)

    plt.xlabel(‘Date’)

    plt.ylabel(‘Sales’)

    plt.xticks(rotation=45)  # rotate x-axis labels for better visibility

    plt.tight_layout()

    return plt

The plot_sales_for_month function takes our dataset and a specific month as input and returns a chart object.

Once we’ve defined our helper function, we can call it in another cell:

plot_sales_for_month(xl(“sales_data”,headers=True),xl(“Sheet2!A2”))

The Python code above can retrieve spreadsheet data through the xl() function. In this case, we’re retrieving the named range “sales_data” and cell A2.

After generating our first chart, we can simply drag the formula downward to generate a chart for the succeeding months.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to automate tasks with Python scripts in Excel.

How to Automate Tasks with Python Scripts in Excel

1. Set up the table we’ll use to automate the chart generation.
   In this example, we’ll refer to the indicated month in column A when we add our PY function in column B.
   
2. Select an empty cell outside the table and use the keyboard shortcut Ctrl+Shift+Alt+P to enter the Python editor.
   
3. We’ll use this cell to define the custom function to generate a chart for us.
   After defining the function, hit Ctrl+Enter to run the code.
   
4. We can use named ranges to make it easier to refer to our dataset in our Python code. To start, select the range with your dataset and right-click on the selection. In the context menu, select the Define Name… option.
   Provide a new name for the range and set the scope to Workbook. Click on OK to proceed.
   In our example above, we created the named range sales_data to refer to our dataset.
   
5. Select the cell where you want to output the chart. Use the shortcut Ctrl+Shift+Alt+P again to enter the Python editor. We’ll use this cell to call the function we defined earlier.
   Our Python code here should just call the plot_sales_for_month() function with the sales_data range and cell A2 as our input.
   
6. Hit Ctrl+Enter to evaluate the function.
   Our Python script returns an image containing the chart we want.
   
7. We can copy the formula down the column to generate line charts for the rest of the months in our table.
   
8. Click on the Create Reference icon to return an image object that can be placed over cells.
   
   These are all the steps you need to know to automate generating charts with Python in Excel.

FAQs

1. Is it possible for Python in Excel to access local files?
   Unfortunately, Python in Excel is not able to read or write to local files due to security concerns. This means that automating tasks that involve creating, modifying, or deleting other Excel files is not possible at the moment.

If you want to learn more about using Python in Excel, you can read our post on how to use the scikit-learn library in Excel. You may also be interested in our guide for how to use ChatGPT to automate sending emails in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!