Python in Excel: Data Outputs in Custom Data Visualizations and Algorithms

2h 3mAdvanced2024-06-21

Authors

Helen Wall

Data analytics and business analysis expert

Course details

Excel is a powerful tool for data and business analysis, and Python is one of the world’s most popular and dynamic programming languages. Python in Excel works as a sandbox environment. It enables developers and business users to test small parts of code by creating visuals and running algorithms on existing data. In this course, data analytics and business analysis expert Helen Wall focuses on how Python can expand the existing capabilities of Excel. Explore the process and framework of setting up Python to create DataFrame objects and other outputs in Excel. Dive into ways you can use these outputs and objects in custom data visualizations and algorithms that Excel does not have natively, but which Python can create with code. This course highlights ways you can harness the strengths of both Excel and Python in one interface.

Learning objectives
Navigate Python in Excel and its setup.
Create visuals with Python code.
Run algorithms with Python code.
Skills covered

Skills covered

Data VisualizationSpreadsheetsMicrosoft ExcelPythonProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceMicrosoftSoftware DevelopmentOne-Off

Concepts

0. Introduction

01 - Introducing the power of Python in Excel
02 - What you should know
03 - Enabling Python in Excel

1. Introducing Excel and Python

04 - Breaking down Excel and Python processes
05 - Leveraging Power Query
06 - Using the PY Excel function
07 - Using the XL Excel function and Python variables
08 - Determining calculation order
09 - Importing Python libraries into Excel
10 - Managing errors
11 - Working with Python objects
12 - Transforming DataFrame objects
13 - Challenge - Creating table objects in Excel
14 - Solution - Creating table objects in Excel

2. Applying Algorithms

15 - Introducing AI and machine learning algorithms
16 - Determining trends for linear regression with Excel functions
17 - Leveraging Excel Solver for logistic regression
18 - Determining trends for logistic regression with Python code
19 - Grouping data with hierarchical clustering
20 - Grouping data with the K-Means algorithm
21 - Determining anomalies with anomaly detection algorithms
22 - Challenge - Running algorithms with Python in Excel
23 - Solution - Running algorithms with Python in Excel

3. Creating Visuals

24 - Visualizing data
25 - Leveraging Excel line charts
26 - Leveraging Excel scatter plots
27 - Configuring Python in Excel with dynamic parameters
28 - Creating Python visuals
29 - Visualizing hierarchical clustering with dendrograms
30 - Breaking down time series models into components
31 - Challenge - Comparing time series components to anomalies
32 - Solution - Comparing time series components to anomalies

Conclusion

33 - Continuing on with Python in Excel