These are the most interesting PY function tasks in Microsoft Excel that’ll surely make you a fan!

The PY function in Microsoft Excel opens up an exciting world where Python meets spreadsheets. After using it for a year, I’ve realized how powerful it can be for solving real-world problems. It could be from quick data analysis to advanced automation of spreadsheet calculations.

If you’ve been experimenting with the PY function too, you’ve probably stumbled upon some surprising and unspoken capabilities along the way. I know, because I did!

Here are 6 fascinating examples that show just how creative and versatile the PY function can be in Excel. Let’s dive in!

Predict Future Sales With Linear Regression

You can use the sklearn.linear_model library to train a data analytics model in Excel PY engine to understand past patterns and forecast future sales. The Python library is open-source, and it’s free to import when you run a PY function that predicts sales. Let me show you below, in step-by-step, how to use linear regression to predict sales:

Firstly, import reference sales values with dates from a source database or copy and paste from another worksheet.

Let’s say your source sales data is in a cell range A2:B8.

Select A1 and enter the following PY function into the cell to create the first dataframe, df. To be able to use this function, you must first type =PY( , and the PY engine will get activated. Now, you can enter all of the PY functions mentioned in this exercise.

df=xl("A2:B8", headers=True)   

Press Ctrl + Enter to execute the PY function.

This dataframe refers to historical sales figures for a product or business. This dataframe will be used as the reference for the final future sales prediction code written in Python.

Now, create another table for the date ranges for which you would like to generate predicted sales figures. It could be in the cell range E2:E5.

Select E1 and type in the following PY function into the cell:

df1=xl("E2:E5", headers=True)

Don’t forget to press Ctrl + Enter keys together to execute the PY function.

Now, you have a second dataframe, df1, that refers to the dates for which you need to predict sales.

Finally, you’re ready to create the sales prediction dataframe using the PY function that will use the linear regression model.

This will be a spill range PY function, so choose the destination cell or cell range accordingly.

In this exercise, I’ve selected H1 as the destination cell.

In F1, I entered the following PY function code:

import pandas as pd
from sklearn.linear_model import LinearRegression

# df is reference data (dates and sales)
# df1 is prediction dates (dates to predict)

# Get the column names from both dataframes
date_col = df.columns[0]  # Dates from reference df
sales_col = df.columns[1]  # Sales from reference df
prediction_date_col = df1.columns[0]  # Dates from prediction df1

# Convert dates to numeric values in reference data
df['date_numeric'] = (pd.to_datetime(df[date_col]) - pd.to_datetime(df[date_col]).min()).dt.days

# Prepare data and fit model
X = df['date_numeric'].values.reshape(-1, 1)
y = df[sales_col].values

model = LinearRegression()
model.fit(X, y)

# Create predictions for all dates in df1
predictions = []
for date_str in df1[prediction_date_col]:
    target_date = pd.to_datetime(date_str)
    days_since_start = (target_date - pd.to_datetime(df[date_col]).min()).days
    predicted_sales = model.predict([[days_since_start]])[0]
    # Format with $ symbol and 2 decimal places
    formatted_sales = f"${predicted_sales:,.2f}"
    predictions.append({'Date': date_str, 'Predicted_Sales': formatted_sales})

# Convert to DataFrame for output
result_df = pd.DataFrame(predictions)
result_df

After pressing the Ctrl + Enter keys, I get the third dataframe, result_df.

To visualize the results, click on the dataframe, and a small icon will appear on the right side of the cell. This is the Insert Data tool.

Click on Insert Data and select Show Data Type Card from the right-side context menu.

You should now see the predicted sales table for the required date list in an Anaconda PY rendering pop-up.

If you wish to get a table that can be used directly in Excel, click on the Python Object drop-down arrow in the formula bar and select Excel Value from the context menu.

You should now see the predicted sales table as a spill range.

Copy the spill range table and select a different destination cell.

Press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select the Values option in the Paste Special dialog box and click OK to convert the spill range to a permanent data table.

Since I have used two different dataframes for the historical sales data and future dates, you can seamlessly use this PY function for a database of any length.

All you need to ensure is to create the dataframes above the target table and keep separate dataframe names for the historical sales figures and the predicted dates data.

📒 Read More: How to Use the PY Function in Microsoft Excel

Calculate Distance Between Zip Codes

Often, you need to calculate the distances between multiple ZIP codes in your worksheet. So far, you might be using Google Search for that. If you’re using the PY function in Excel, you can stay in your worksheet and calculate the distances without getting distracted. Let me show you the way step by step:

Firstly, create a data table in your Excel worksheet containing the following column headers. In the current exercise, the cell range reference for this table is A2:C4.

• ZIP1
• Lat1
• Long1

In the above data table, you’ll copy and paste the first set of ZIP code and their latitudes and longitudes. You can easily find latitudes and longitudes for a ZIP code in the USA by visiting the web tool Free Map Tools.

Now, create another data table for the second set of ZIP codes and name the columns as listed below. The second data table is situated in the G2:I4 cell range in this exercise.

• ZIP2
• Lat2
• Long2

Now, for the first table, select A1 and type in =PY( to initiate the PY function engine. You can change the cell references according to your own worksheet.

Enter the following formula to create the first dataframe, df1:

df1=xl("A2:C4", headers=True) 

Press Ctrl + Enter to execute the PY function.

If you click on the Insert Data tool of the dataframe, you’ll see the Show Data Type Card. Click on that to visualize the ZIP dataset inside the Python dataframe.

Now, select G1 and use the following formula to create the second dataframe, df2:

df2=xl("G2:I4", headers=True) 

Hit Ctrl + Enter to run the PY function to create the dataframe.

The second dataframe will look like the one shown above in the image.

Now, simply select the destination cell where you wish to fetch the ZIP code distances and enter the following PY code inside the PY function:

import math
import pandas as pd

# Assuming df1 and df2 are already created with same number of rows
# df1 has columns: ZIP1, Lat1, Long1
# df2 has columns: ZIP2, Lat2, Long2

def calculate_distance(lat1, lon1, lat2, lon2):
    R = 3959  # Earth radius in miles
    dlat = math.radians(lat2 - lat1)
    dlon = math.radians(lon2 - lon1)
    a = math.sin(dlat/2)**2 + math.cos(math.radians(lat1)) * math.cos(math.radians(lat2)) * math.sin(dlon/2)**2
    return R * 2 * math.atan2(math.sqrt(a), math.sqrt(1-a))

# Calculate distances for each row pair
distances = []
for i in range(min(len(df1), len(df2))):
    lat1 = df1['Lat1'].iloc[i]
    lon1 = df1['Long1'].iloc[i]
    lat2 = df2['Lat2'].iloc[i]
    lon2 = df2['Long2'].iloc[i]
    
    distance = calculate_distance(lat1, lon1, lat2, lon2)
    distances.append({
        'ZIP1': df1['ZIP1'].iloc[i],
        'ZIP2': df2['ZIP2'].iloc[i],
        'Distance_Miles': round(distance, 2)
    })

# Create result dataframe
result_df = pd.DataFrame(distances)
result_df

Press Ctrl + Enter to get the ZIP code distances as a Python Object in the result_df dataframe.

You need to click on the Insert Data tool on the right side of the dataframe and then select Show Data Type Card to view the calculation table.

If you need output as Excel values, follow the steps mentioned earlier in this guide.

The PY function given above is a dynamic code. It’ll calculate the distances between only one set of ZIP codes or thousands. It depends on the df1 and df2 dataframes that you created earlier.

If there are hundreds of ZIP code sets, select the cell range reference in your dataframe formulas accordingly.

Stock Price Percentage Change

If you’ve got historical pricing information for a certain stock, you can use the pandas library to calculate the price percentage change. This method is much faster than conventional mathematical functions in Excel. Let me show you how in a step-by-step:

Suppose you want to calculate the price change percentage of the stock AMZN from 10/13/2025 to 10/17/2025.

Create a table in Excel with Dates and Prices as the column headers.

Now, populate the table with data from trusted financial websites, like Yahoo Finance.

In the current exercise, I created the reference table in the cell range A2:B7.

Select A1 and enter =PY( to start using Python scripts in Excel.

Enter the following PY function into the formula bar to create the dataframe df, which will be used in further calculations:

df=xl("A2:B7", headers=True)    

Press Ctrl + Enter to execute the PY function.

Now, select the destination cell where you want to fetch the price change calculation value, and enter the following Python script into the formula bar after activating the PY function.

import pandas as pd

# df already contains all the data with Dates and Prices columns

# Check if we got the data properly
print("DataFrame shape:", df.shape)
print("DataFrame columns:", df.columns.tolist())
print("DataFrame data:")
print(df)

# Calculate percentage change
if len(df) >= 2:
    start_price = df.iloc[0, 1]  # First row, second column (Prices)
    end_price = df.iloc[-1, 1]   # Last row, second column (Prices)
    pct_change = ((end_price - start_price) / start_price) * 100
    result = f"Price Change: {pct_change:.2f}%"
else:
    result = "Insufficient data"

result

You must press Ctrl + Enter to execute the Python script.

The script will generate a Python Object that clearly shows ‎‎Price Change: 3.30%.

However, if you need the output as an Excel value, go to the formula bar and click on the Python Output drop-down menu.

Select the Excel Value from the context menu.

The output in D2, the Python Object, will be changed into an Excel value in text format.

Categorize Ages Into Groups

Another interesting task you can achieve using the PY function in Excel is categorizing things into groups, like random ages into a group referencing a table. Let’s learn the simple process step-by-step below:

Create a references table containing the Age Group and Age Range columns. Populate data in the table according to your database.

For example, I’ve created one in A2:B8 for referencing.

Now, select A1, or any cell above the reference table in your worksheet, and press the Ctrl + Alt + Shift + P keys to activate Python scripting in the formula bar.

Enter the following formula into the cell A1 to create the dataframe df_ref:

df_ref=xl("A2:B8", headers=True) 

Hit Ctrl + Enter to execute the PY function.

Similarly, go to the age table that you need to categorize, such as the table shown above in D2:D8.

Select a blank cell above the table and activate the PY function in the formula bar.

Copy and paste the following PY function into the cell to create the df_ages dataframe. You must modify the cell range based on your own dataset.

df_ages=xl("D2:D8", headers=True) 

Use the keyboard key combination Ctrl + Enter to create the dataframe.

Now, select the destination cell and press the Ctrl + Alt + Shift + P keys to activate Python scripting.

Inside the PY scripting space, copy and paste the following Python script:

import pandas as pd

# Assuming df_ref contains A1:B7 (Age Group, Age Range)
# Assuming df_ages contains D1:D7 (Age)

def categorize_age(age, ref_df):
    age = int(age)
    for _, row in ref_df.iterrows():
        age_range = row['Age Range']
        
        if age_range.endswith('+'):  # Handle "60+" case
            if age >= int(age_range.replace('+', '')):
                return row['Age Group']
        elif '–' in age_range:  # Handle range case
            start, end = map(int, age_range.split('–'))
            if start <= age <= end:
                return row['Age Group']
    
    return "Unknown"

# Apply categorization to all ages
df_ages['Age Group'] = df_ages['Age'].apply(lambda x: categorize_age(x, df_ref))

# Return the results
df_ages[['Age', 'Age Group']]

Press Ctrl + Enter to commit the Python script.

You’ll see the Python Object dataframe in the destination cell.

Go to the formula bar and click on the Python Output drop-down menu, and select Excel Value.

The dataframe will be converted to an Excel data table.

Loan EMI Calculation

Let’s look at the steps below to learn how to calculate loan EMI using the PY function:

Structure your input data using the following columns in a tabulated format in your Excel worksheet:

• Loan_Amount
• Interest_Rate
• Loan_Term_Years

I have created the above table in the cell range A2:C6.

Now, convert this input data table into a dataframe, named df_loans, by entering the following PY function in A1. The cell reference will change based on your worksheet. However, it must always be above the source data table.

Before entering the PY formula, you must activate the PY function in Excel using the methods described so far in this guide.

df_loans=xl("A2:C6", headers=True)

To commit the Python script, you must press Ctrl + Enter.

Now, select the cell where you want to calculate EMI details, cell E1, and activate the PY function.

Copy and paste the following PY formula into the cell:

import pandas as pd
import math

# df_loans already contains the loan data

def calculate_emi(principal, annual_rate, years):
    # Convert percentage to decimal and calculate monthly rate
    monthly_rate = annual_rate / 100 / 12
    months = years * 12
    
    # EMI formula
    emi = (principal * monthly_rate * (1 + monthly_rate)**months) / ((1 + monthly_rate)**months - 1)
    return round(emi, 2)

# Calculate EMI and other metrics for each loan
df_loans['Monthly_EMI'] = df_loans.apply(
    lambda row: calculate_emi(row['Loan_Amount'], row['Interest_Rate'], row['Loan_Term_Years']), 
    axis=1
)

# Calculate additional metrics
df_loans['Total_Payment'] = df_loans['Monthly_EMI'] * df_loans['Loan_Term_Years'] * 12
df_loans['Total_Interest'] = df_loans['Total_Payment'] - df_loans['Loan_Amount']

# Round the financial columns
df_loans['Total_Payment'] = df_loans['Total_Payment'].round(2)
df_loans['Total_Interest'] = df_loans['Total_Interest'].round(2)

# Return the enhanced dataframe
df_loans

Press Ctrl + Enter to execute the Python script.

You’ll get a Python Object as an output.

Go to the formula bar, click on the Python Output arrow, and select the Excel Value in the context menu.

You should now get the detailed EMI calculation data table created by the Python script.

Generate Password

You can create strong passwords for Excel, websites, emails, apps, net banking, and more using the PY function. Let me walk you through the steps below:

Enter the length of the password you need in the cell A1, like 12.

Now, select another cell and type in =PY( to enable the Excel PY formula engine.

Enter the following Python script in the formula bar:

import random
import string

# Get password length from Excel cell (e.g., A1)
pwd_length = xl("A1")
if pwd_length < 8: pwd_length = 12  # Minimum length

lowercase = string.ascii_lowercase
uppercase = string.ascii_uppercase
digits = string.digits
punctuation = '!@#$%^&*()_+-=[]{}|;:,.<>?'
all_chars = lowercase + uppercase + digits + punctuation

password = [
    random.choice(lowercase),
    random.choice(uppercase), 
    random.choice(digits),
    random.choice(punctuation)
] + [random.choice(all_chars) for _ in range(pwd_length - 4)]

random.shuffle(password)
''.join(password)

Press Ctrl + Enter to execute the Python script.

The PY function will generate a random and strong password instantly.

Each time you run the script, you’ll get a new password.

Find Prime Numbers in a Range

Suppose you got a list of random numbers in a table and need to extract all the prime numbers from the data. You can easily accomplish this using the pandas library in the PY function.

Create a dataframe, say df_prime, in the worksheet using the formula given below. You’ve already learned how to create a dataframe in the previous sections.

The dataframe must be created above the Excel table.

df_prime=xl("A2:A12", headers=True)

Now, select the destination cell and enter the following Python script:

import pandas as pd

# Assuming df_prime already exists with numeric data
# Extract all prime numbers from the dataframe

def is_prime(n):
    if n < 2: 
        return False
    for i in range(2, int(n**0.5) + 1):
        if n % i == 0:
            return False
    return True

# Get all unique values from the dataframe and check for primes
all_values = df_prime.values.flatten()
prime_numbers = [x for x in all_values if pd.notna(x) and is_prime(int(x))]

# Create output dataframe
output_df = pd.DataFrame({'Prime Numbers': prime_numbers})

Press Ctrl + Enter to commit the Python script.

Click on the Excel Value option in the Python Output context menu.

You should see the table containing all the prime numbers extracted from the reference dataframe.

Sort a List

Yet another cool use of the Python pandas library is sorting a dataset contained in a Python dataframe in Excel. In this example, I’ll follow a similar approach, where I’ll wrap the input data in a dataframe, df_sort, and refer to this dataframe in another Python script that will generate the sorted dataframe. Let’s get started!

Save the input data list in an Excel table.

Select the cell just above the table. Don’t forget to enable the PY engine by typing =PY( syntax in the formula bar. Now, use the following formula to create the dataframe:

df_sort=xl("A2:A12", headers=True)

Now, select the destination cell and enter the following Python script into the cell:

import pandas as pd

# Sort the dataframe df_sort
output_df = df_sort.sort_values(by=df_sort.columns.tolist())

You must press the Ctrl + Enter keys together to execute the PY script.

Go to the formula bar, click on the Python Output drop-down menu, and click on the Excel Value option.

You’ll get the alphabetically sorted data list.

Employee Age From Birth Year

Suppose you have a data table in your Excel spreadsheet containing employee names and birth years. You want to calculate their age as of today. You can quickly accomplish this using the pandas library in the Excel Python engine.

Convert your data table into a Python dataframe using the following formula:

df=xl("A2:B8", headers=True) 

Now, select the destination cell where you want to calculate the age and enter this PY script into it:

import pandas as pd
from datetime import datetime

# Calculate age from birth year
current_year = datetime.now().year
df_output = pd.DataFrame({
    'Employee Name': df['Employee Name'],
    'Age': current_year - df['Birth Year']
})

Execute the script by pressing the Ctrl + Enter keys.

Convert the Python Object into an Excel Value to get the computed age data table.

Conclusions

If you’re here, congratulations! You’ve learned the 6 most exciting tasks you can achieve using the PY function in Excel.

Do you know a better scenario? Use the comment box below to share your experience related to Python coding in Microsoft Excel.