Anaconda Code: Create and Use User Defined Functions for Excel with Python

We’re excited to announce a powerful new feature in Anaconda Code: Python User-Defined Functions! With UDFs, you can write Python functions and use them just like native Excel functions, bringing the full power of Python’s rich data science ecosystem directly to your spreadsheet formulas. Let’s look at a real-world example that showcases how powerful this integration can be.

From Cross-Tab to Long Format in One Function Call

Consider a common data reshaping challenge: converting cross-tabulated (wide) data into a long format. You’re probably thinking, “Well, I can do that with Power Query no problem.” Yes, true. But the resulting Table isn’t dynamic. Every time the input data changes, the query needs to be refreshed. And the query is tied to that one workbook. With Anaconda Code and Anaconda Toolbox, you can create a Python UDF, save it as a Code Snippet, and have it available in any workbook you open. Plus, the code needed to quickly unpivot a wide-format table is surprisingly simple. Here’s how you can create a custom UNPIVOT function that works just like any built-in Excel function:

# decorate the function with the UDF decorator
@UDF(name='UNPIVOT', nested=False)
def unpivot_data(
    data: UDF.Range, # the input data from the Excel sheet
    id_vars, # the column(s) to keep fixed in the output
    var_name='variable', # the column name to hold the unpivoted column headers
    value_name='value' # the column name to hold the unpivoted values
    ) -> UDF.Range: # output the result as an Excel spilled array
    
    # convert the input data into a Pandas DataFrame 
    df = to_df(data)
    
    # pass the arguments through to the melt function and return the result
    return df.melt(id_vars=id_vars, var_name=var_name, value_name=value_name)

Once defined, you can use this function directly in Excel. For example, with sales data spread across years:

Figure 1: The UNPIVOT User-Defined Function is called directly from the Excel grid

If you want to follow along with this example, here’s a formula that will give you the input data. Just put this in cell B2 of a worksheet.

={"Category",1996,1997,1998;"Confections",29685.549981647,82657.7504128219,55013.9244483569;"Dairy Products",40980.4499421209,115387.639834883,78139.194964755;"Meat/Poultry",28813.6599777699,80975.1079431361,53233.591471141}

After you’ve set up the input data, open Anaconda Code and load the environment. This will make sure the Python environment is running in the add-in:

Figure 2: To use Python in Anaconda Code, you first need to load the environment

After the environment is loaded, go to the Imports and Definitions tab of Anaconda Code and paste the UDF code from the code snippet into the script. For reference, here it is again:

# decorate the function with the UDF decorator
@UDF(name='UNPIVOT', nested=False)
def unpivot_data(
    data: UDF.Range, # the input data from the Excel sheet
    id_vars, # the column(s) to keep fixed in the output
    var_name='variable', # the column name to hold the unpivoted column headers
    value_name='value' # the column name to hold the unpivoted values
    ) -> UDF.Range: # output the result as an Excel spilled array
    
    # convert the input data into a Pandas DataFrame 
    df = to_df(data)
    
    # pass the arguments through to the melt function and return the result
    return df.melt(id_vars=id_vars, var_name=var_name, value_name=value_name)

After you’ve pasted that into the Imports and Definitions script, hit the Save and Run button to register the UNPIVOT function in Excel.

When that’s done, the green Save and Run button will change to say Run. This means your function is now registered in Excel.! Go ahead and use it to unpivot the sales data!

=UNPIVOT(B2:E5,"Category", "Year", "Revenue")

Now you’ve seen how to create a Python user-defined function in Anaconda Code, here’s a quick video showing a more complicated example. Hopefully this will spark some ideas about the kinds of things you can do with this new feature.

Why UDFs in Anaconda Code Are Game-Changing

• Native Excel Integration: Your Python functions work just like built-in Excel functions
• Range Support: Work with Excel ranges naturally using the UDF.Range type hint
• Flexible Parameters: Support for variable arguments and optional parameters with default values
• Built-in Documentation: Your Python docstrings become Excel function help text
• Local Execution: Functions run locally in your secure Anaconda Code environment

Getting Started with UDFs

Creating your own UDF is as simple as decorating a Python function with @UDF. Here’s what makes them special:

1. Type Hints for Excel Ranges: Use UDF.Range to work with cell ranges
2. Optional Parameters: Add default values to make function arguments optional in Excel
3. Helpful Documentation: Add docstrings to provide Excel users with function help
4. Variable Arguments: Support for *args to accept multiple inputs

Start Creating Your Own Functions Today

The possibilities are endless – from simple data transformations to complex statistical analyses, you can now bring Python’s capabilities directly into your Excel formulas. 

Ready to get started? Download Anaconda Toolbox from Microsoft AppSource and begin creating your own custom Excel functions with Python today!

If you’re looking to enhance your data visualization skills, register for our upcoming live course on January 23, 2025, Data Visualization with Python in Excel. During this interactive live course by Owen Price, you’ll learn how to use both Python in Excel and Anaconda Toolbox for Excel to craft compelling data visualizations. If you’re a paid subscriber, you can attend this live course for FREE. To register, click here.

New to Python in Excel? Join us for our free upcoming webinar, Introduction to Python in Excel. During this webinar we will cover Python in Excel basics, understanding data types and output modes, a brief introduction to Pandas DataFrames, custom functions, and a simplified Python charting experience using the Anaconda Toolbox for Excel.