Vadim Frolov's Blog

Python functions in Excel

Vadim Frolov's photo
Vadim Frolov

Excel now supports Python. The documentation doesn’t describe how one can use python functions and I spent some time figuring this out. Imagine that you would like to create a set of your own function that you’d like to apply to values in your Excel sheet. Here is what you need to do:

  1. Best place to put your python code is in the first cell of the first row. Python execution goes row by row. If you’d like, you can add a first sheet and call it something like PythonInit, then proceed with your regular Excel data on another sheet.

  2. To call a function with the content of cell A4 write =PY(your_func_name(xl(“A4”)))

Here is an illustration. I put this code in the first cell:

import base64

def base64_converter(input_text):
    """
    Converts text to base64 or decodes base64 to text.
    Automatically detects the appropriate direction.

    Args:
        input_text (str): Input string to be encoded or decoded.

    Returns:
        str: Encoded or decoded result.
    """
    try:
        # Attempt to decode input as Base64
        decoded_text = base64.b64decode(input_text, validate=True).decode('utf-8')
        return decoded_text
    except (ValueError, UnicodeDecodeError):
        # If decoding fails, encode as Base64
        encoded_text = base64.b64encode(input_text.encode('utf-8')).decode('utf-8')
        return encoded_text

The result of this cell is a Python object.

Then I can call my function, which results in this:

The regular Excel’s magic like auto fill with increment works with python functions as well. Neat!

excel