Python in Excel: Best tools for Data Analysis and Visualization

Microsoft has recently introduced a new feature called Python in Excel that allows you to use Python code in Excel. You can manipulate and explore data in Excel using Python plots and libraries, and then use Excel’s formulas, charts, and PivotTables to further refine your insights .

What is Python in Excel?

Python in Excel is a way of using the popular programming language Python to automate tasks, analyze data, and create interactive applications within the spreadsheet software Excel. Python in Excel can offer many benefits, such as:

• Access to a large and diverse set of libraries and modules that can perform various functions, such as data manipulation, visualization, machine learning, web scraping, and more.
• Ability to write and execute Python code directly in Excel cells, using tools like xlwings or PyXLL.

Python in Excel can enhance the functionality and productivity of Excel users, especially those who work with large and complex data sets or who need to perform advanced analysis and automation.

Why Python in Excel?

Excel is a popular and powerful tool for working with data, but it has some limitations that can make data analysis and visualization challenging. For example, Excel can only handle a limited number of rows and columns, it does not support complex data types or structures, it has limited options for data cleaning and manipulation, and it can be slow and prone to errors when working with large or dynamic datasets. Python is a general-purpose programming language that can help overcome these limitations of Excel. Python can handle any amount of data, support any data type or structure, offer a wide range of libraries and packages for data analysis and visualization, and enable faster and more reliable data processing and automation. Python can also interact with Excel files, so you can use the best of both worlds.

Getting Started with Python in Excel

Python in Excel is gradually rolling out to users running Beta Channel on Windows. This feature will roll out to Excel for Windows first, starting with build **16.0.16818.20000**, and then to the other platforms at a later date. To use Python in Excel, join the Microsoft 365 Insider Program. Choose the Beta Channel Insider level to get the latest builds of the Excel application.

Python Libraries for Data Analysis and Visualization

Python is a popular programming language for data analysis and visualization. It has a rich ecosystem of libraries that can help you create powerful and interactive visualizations. Here are some of the most popular Python libraries for data visualization and how they can be used in conjunction with Excel.

• Matplotlib: Matplotlib is a low-level library that provides a comprehensive set of tools for creating static, animated, and interactive plots. It can be used to embed graphs into applications, or to customize the visual style and layout of the plots. Matplotlib can also export to many file formats, such as PNG, PDF, or SVG. You can use Matplotlib to create charts in Excel by saving them as images and inserting them into your spreadsheet.
• Seaborn: Seaborn is a high-level library that builds on Matplotlib and provides a simpler interface for creating statistical graphics. It aims to make visualization a central part of exploring and understanding complex datasets. Seaborn can also integrate with pandas, a library for data manipulation and analysis, and create plots directly from DataFrames. You can use Seaborn to create charts in Excel by saving them as images and inserting them into your spreadsheet.
• Plotly: Plotly is a library that enables you to create interactive and web-based visualizations. It supports a wide range of plot types, such as scatter plots, bar charts, pie charts, maps, and more. Plotly can also generate HTML files that can be embedded in web pages or shared online. You can use Plotly to create charts in Excel by exporting them as HTML files and inserting them into your spreadsheet.
• Pygal: Pygal is a library that focuses on creating scalable vector graphics (SVG) that can be easily rendered in browsers. It supports many plot types, such as line charts, pie charts, radar charts, box plots, and more. Pygal can also create interactive plots that respond to mouse events. You can use Pygal to create charts in Excel by saving them as SVG files and inserting them into your spreadsheet.
• Folium: Folium is a library that leverages the power of Leaflet.js, a JavaScript library for interactive maps, to create geospatial data visualizations in Python. It allows you to create maps with various layers, such as markers, polygons, heatmaps, choropleths, and more. Folium can also export the maps as HTML files that can be viewed in browsers or embedded in web pages. You can use Folium to create maps in Excel by exporting them as HTML files and inserting them into your spreadsheet.

Use Cases for Python in Excel

Python in Excel is a new feature that allows you to integrate Python and Excel analytics within the same Excel grid. Here are some use cases for Python in Excel:

• You can use Python's powerful data analysis and visualization libraries, such as pandas, statsmodels, Matplotlib and seaborn, to manipulate and explore data in Excel, and then use Excel's formulas, charts and PivotTables to further refine your insights .

• You can use Python to perform calculations in Excel that are too slow or cumbersome to do in VBA, such as querying large datasets or pulling in data from external systems .
• You can use Python to expose internal or third party libraries to Excel users, or to build beautiful Excel dashboards with Python.
• You can use Python to stream real time data from other systems to Excel, or to replace VBA with Python.

The benefits of using Python in Excel over other tools are:

• You can work seamlessly in the familiar Excel environment by accessing Python directly from the Excel ribbon. No set up or installation is required.
• You can share workbooks and Python analytics in your favorite tools like Microsoft Teams and Outlook. Collaborate easily with comments and @ mentions, and co-author with colleagues as you normally would in Excel.

• You can protect your data with enterprise-level security as Python in Excel runs on the Microsoft Cloud with an M365 connected experience .

Python in Excel Conclusion

Python is a powerful and versatile programming language that can enhance your productivity and creativity in Excel. If you are interested in learning more about Python in Excel, you can check out some of the resources listed below. We hope you enjoyed this tutorial and found it useful. Why not give Python in Excel a try and see what you can do with it?