I’m not sure about your experience, but personally find Excel’s charting features antiquated at times. They can be quite finicky to use, particularly when it comes to customization. Iterating between various plots and executing tasks like combining different plot types or coloring by group proves to be challenging.

On the other hand, Python provides an alternative toolkit for plotting. In some aspects, it simplifies the process, although at the same time it adds complications to the basic Excel plots.

To illustrate what I mean, let’s proceed with this demonstration. Please download the following starter file to follow along:

Creating a basic plot

This dataset contains basic information about the five boroughs of New York City, so our visualization options are somewhat restricted. Let’s create a bar plot to represent the population of each borough using the seaborn package. Before proceeding, ensure that you convert the nyc table into a Pandas DataFrame using the xl() function:

By default, Excel will display the Python object type associated with the output. To view the actual image, select the cell and press Ctrl + Alt + Shift + M:

Python plots and images fit to cell

In this new feature of Excel, when you insert an image into a cell, it is automatically sized to fit within the cell’s dimensions. As you adjust the cell size—either expanding or shrinking it—the image adjusts accordingly. Importantly, the image moves with the grid, meaning it stays within the cell and shifts position when you insert rows or columns.

This functionality can be particularly useful if you’re creating a dashboard or report on a dedicated worksheet where cells are generally larger. However, for ad-hoc analysis on a standard worksheet, trying to resize a single cell to accommodate the image might not be practical.

So, how do we address this issue? Let’s explore the options to make these images more visible and functional within your Excel workflow, especially when dealing with smaller cells or ad-hoc analyses.

Resizing the Python plot

To address the issue of small images in Excel cells, there are a couple of approaches we can consider. To understand the pros and cons of each, let’s start with an experiment. First, make a copy of the barplot image located in cell E1 and place this copy further away in the worksheet, for example in cell E10.

You might need to adjust the size of some cells later to clearly see both plots. The objective here is to observe the effects of enlarging the plot using two different methods. This comparison will help us identify the most effective way to enhance the visibility of images in your Excel workflow.

Placing image over cells

For the first plot, begin by right-clicking on the cell that contains the image. Then navigate to Picture in Cell and select Place over Cells. This action will detach the image from the grid, allowing you to freely move it to any desired location:

Once you’ve done this, slightly reduce the size of the image so that the other image remains visible for comparison.

It’s important to note that when you click on this adjusted image, the underlying Python code is no longer visible. This aspect is crucial in understanding how this method functions and in evaluating its advantages and disadvantages.

Creating a reference

The next option is located in the same menu as the previous one. To access it, right-click on the image select Picture in Cell followed by Create Reference:

When you select this option and click on the plot, you’ll notice a reference pointing back to the original cell; in this case, cell E10. Go ahead and resize or move this plot. As you do so, observe that the original, smaller plot remains intact in its original cell.

This feature allows you to manipulate the image while maintaining a direct link to its source cell, providing flexibility in presentation without altering the original embedded plot.

Replaced versus referenced images and data updates

Both methods mentioned – Place over Cells and Create Reference – significantly improve the visibility of the plot in your Excel worksheet. However, it’s important to note that only one of these methods will allow the cell to update in response to changes in the source data.

For the next part of this demonstration, I’ve arranged the plots side by side to facilitate easy comparison in a screenshot.

Now, let’s conduct an experiment by altering the data. I will increase the numbers for Staten Island (perhaps giving some overdue recognition to the “Forgotten Borough”). After making this change, let’s observe the impact on both plots. This will help us understand which method is more dynamic and responsive to data changes, a crucial aspect for real-time data analysis and reporting in Excel.

The image we placed over cells remains static and is no longer connected to the Python code. However, the reference is still linked to that small cell, and the plot updates.

We can even modify the Python code within that original cell, not just the source data, and the referenced plot will update. For instance, I might want to include a title in this plot and color all the bars blue:

Which method is better?

Ultimately, it boils down to a fundamental trade-off between weight and maintainability. By simply overlaying the plot onto cells, you sever the connection to the data source. This might be acceptable if you require a one-time visualization and are not concerned with reproducibility or repeatability.

However, if these aspects are important, then the additional effort of establishing that link becomes significant. Perhaps this involves creating a concealed Python plot preparation worksheet or discreetly managing your plots. You might even opt to hide them behind larger plots.

You can download a complete file of this demonstration below:

Which method do you find more effective, or have you discovered an even better approach to managing the resizing of Python plots in Excel? I welcome your thoughts in the comments.

The post How to resize Python plots in Excel first appeared on Stringfest Analytics.