Supercharge your Excel skills with Python and OpenPyXl

Course Description

This course is for people who are proficient at Excel and want to add in the power of Python, the world's best programming language for data analytics. It assumes no experience with programming and will start by demonstrating how to set up a development environment. It will then cover the basic programming required to work with Python, followed by a walk-through of the facilities that are available in OpenPyXl - the main library for working with Excel from Python. It will conclude with some practical examples to demonstrate the extra power that Python can give you when working with Excel.
1 Day

Who Should Attend

This course is aimed as people with strong Excel skills who want to go to the next level of being able to using the python programming language to create, read, write and manipulate Excel workbooks.
It does not assume any prior programming knowledge.

Setting Up A Development Environment

Jupyter Notebooks
Installing OpenPyXl
Testing your environment

Enough Programming To Get By

Arrays and Lists

OpenPyXl Intro

Loading OpenPyXl
Specifying file location
Opening a workbook
Viewing the list of sheets
Selecting a sheet
Reading the contents of a cell
Writing to a cell

OpenPyXl Functions

Inserting and deleting rows and columns
Working with ranges
Filters and Sorts
Adding a chart


Styling and formatting cells and ranges
Freezing rows and columns
Conditional formatting

Practical Examples

Scraping data from a website into Excel
Using Pandas to analyse data from Excel
Data import and sanitisation using Numpy

PythonExcelOpenPyXlData AnalysisAdvanced ExcelExcel 365Microsoft ExcelExcel 2021Microsoft Excel 2021Data Analytics