Returning dataframes from Python code to fill Excel ranges

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Calling Python code from within Excel, and the vast functionality it unlocks Part two of a five-part series of blogs
Excel and Python are getting ever more intertwined: you can now use the PY function to write (and call) Python code from within an Excel worksheet Writing Python code within an Excel workbook to fill cells Returning dataframes from Python code to fill Excel ranges (this blog) Manipulating images in Python within Excel How does Python reference modules within Excel? Creating and calling your own functions in Python

Calling Python code from within Excel, and the vast functionality it unlocks

Part two of a five-part series of blogs

Excel and Python are getting ever more intertwined: you can now use the PY function to write (and call) Python code from within an Excel worksheet

Writing Python code within an Excel workbook to fill cells
Returning dataframes from Python code to fill Excel ranges (this blog)
Manipulating images in Python within Excel
How does Python reference modules within Excel?
Creating and calling your own functions in Python

Posted by Andy Brown on 27 October 2025

In this blog

Showing a dataframe
Returning individual series (rows)
Returning a single row

If you know how to work with dataframes in Python, showing their data in Excel is a doddle (although picking out individual rows can be a bit trickier).

All of the examples on this page assume that our source data is this range B2:D7.

Showing a dataframe

Let's start with just showing this data elsewhere on your worksheet using Python. To do this, click on a blank cell and start your Python code:

Another way to insert Python code: from the Formulas tab on the ribbon.

You can now type in Python code like this:

This code will return a dataframe containing the same data as in the original block of data.

Note that there's no need to explicitly convert the block of data in Excel into a dataframe: this happens automatically.

Experienced Python programmers will wonder how this code imports the Pandas module. To see the answer to this, read the last part of this series of blogs on how Python code within Excel runs in a virtual environment in the cloud with certain modules pre-loaded.

Press Ctrl + Enter to see the results of your code:

This shows that the Python code is returning a dataframe; but where is the data?

You can now ask Excel to show the data returned:

Choosing to show the Excel values will reveal your data.

You can now see that the code returns a spill array:

Exactly what you would want a dataframe in Excel to look like!

Returning individual series (rows)

Suppose that for reasons best known to yourself you want to return the penultimate row from a dataframe:

This is the row we want to return.

You could try to do this with this Python code:

This picks out the last but one row from the dataframe, showing all the columns.

However, this is returned as a series:

Python has converted the single dataframe row into a series.

This now displays like this:

We wanted a row of just data, but we've got a column and it includes the field names too.

Returning a single row

One solution to this problem (and as always with dataframes, there are many) is to use this Python code in a cell to take the row returned, pick out the values only and present them in an array:

# get a dataframe from above table

travel_data = xl("B2:D7", headers=True)

# get the last but one row as a series

penultimate_row = travel_data.iloc[-2,:]

# to ensure this appears as a row, turn this into

# an array with 1 row and n columns

row_as_array = np.array([penultimate_row.values])

# show this array

row_as_array

This would then give our penultimate row:

We've finally lost the field headings, and are presenting the row as a row, not a column.

What this means is that you don't have to choose to manipulate tables of data in Excel or Python now. You can do simple processing in Excel, but use Python code that you've written to do more sophisticated analysis, presenting your results in Excel for further sorting, filtering, pivoting or reporting.

Parts of this blog
Writing Python code within an Excel workbook to fill cells Returning dataframes from Python code to fill Excel ranges (this blog) Manipulating images in Python within Excel How does Python reference modules within Excel? Creating and calling your own functions in Python

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS

EXCEL BLOGS

Showing a dataframe

Returning individual series (rows)

Returning a single row

Head office

London

Manchester