How to visualize data in Excel with openpyxl
Want to share your content on python-bloggers? click here.
In a previous post, I discussed Python’s role in the modern Excel stack. Another advantage of using Python with Excel is the wealth of data visualization options available.
When working with Excel data visualizations using Python, you have two options: automating the production of an Excel chart entirely from Python, or creating a Python visual and inserting it as an image into Excel. In this tutorial, we will explore both, considering the pros and cons of each. To accomplish this, we will utilize the openpyxl and seaborn packages, although alternative packages exist both to build plots and insert them into Excel.
Take a look at the following Jupyter notebook for how to create charts for Excel with openpyxl:
To each their own chart
A summary of the pros and cons of these two methods follows:
| Pros | Cons | |
|---|---|---|
| Building a native Excel plot | – Plot will update with changes in Excel data – User can interact with and customize plot – Plot can integrate with other Excel features like formulas and PivotTables | – Limited number of Excel plot types exist – Some complex calculations or statistical functions maybe be easier to build in Python |
| Inserting an image of a Python plot | – Access to several powerful plotting libraries such as matplotlib and seaborn– Plot is easily audited and reproduced through the source code | – The plot is a static image and lacks interactivity – Updating or refreshing the chart from Excel is not possible |
The choice between methods depends on different factors, such data refresh needs and the availability of specific chart types in Excel. That said, the flexibility and range of options available in itself highlight Python’s powerful capabilities for working with Excel.
Have you automated the production of charts or other Excel features from Python? What do you think about Python’s capability for building plots? Let me know in the comments.
Want to share your content on python-bloggers? click here.