Python in Excel

Aim of course – to teach fundamental theory and knowledge of Microsoft’s latest innovation, Python in Excel, including familiarising the learner with the input, calculation and output process in Excel. The course predominantly focuses on the Pandas library, which is concerned with data manipulation, transformation and analysis, to instill a wide range of relevant Python knowledge. It also introduces the Matplotlib library, creating some simple visualisations of the course data.

Course data – is comprehensive and realistic and comprises of various sales, product and customer data, relating to a fictional gym equipment wholesaler, Workout Warehouse. The data is designed to merged, transformed and cleaned up in Excel using Python code, before being analysed.

Practical element & quizzes – the learner is encouraged to follow along, putting into practice the Python code and techniques to transform, analyse and visualise the course data, and to test their knowledge with regular quizzes.

Data Types & Getting Started – the basics of entering Python code in Excel and an introduction to the coding language’s data types.

Operators – a run through of the most common operators used in Python code, covering arithmetic, comparison, assignment and logical operators.

DataFrames – an introduction to one of the principal components of the Pandas library and one we’ll use extensively in the course – the DataFrame. We cover what it is, its purpose, and several ways to create them.

Series – another fundamental object within Pandas library that features regularly throughout the course.

Indexing & Slicing – useful techniques to extract subsets of data from both DataFrames and Series.

Transforming Data & Strings – useful transformations such as cleaning text data and splitting, renaming, reordering and removing columns.

Merging DataFrames – techniques to merge multiple DataFrames together, specifying which columns we want to include and which to remove.

Working with Columns – ways to add to our data by creating and calculating new columns in defined positions, as well as more advanced techniques to remove and reorder columns.

Sorting –multiple ways to sort Series and DataFrames to present data in the way we want.

Filtering –techniques to filter DataFrames, including by multiple criteria in multiple columns utilising logical operators and methods.

GroupBy – useful technique to aggregate DataFrame data by one or more criteria to answer simple commercial question or provide summaries for further analysis.

Unpivoting & Pivoting Data – transforming, aggregating and summarising data to provide better information or for further processing.

Rank – ranking data in Series and DataFrames and combining with other learnings in the course to answer commercial questions.

Intro to Matplotlib Online Documentation – an introduction to Python’s most popular visualisation library, Matplotlib, and the extensive online documentation that can be leveraged to learn more and help create very customised visualisations.

Creating visualisations – creation of simple pie, bar and lines charts to visualise the course data, referring to the documentation along the way to learn for support.