Control Excel with Python & OpenPyXL from Udemy

Requirements

Basic Python knowledge - Don't worry, we will cover each code snippet
You will need a computer with Python 3 installed, then we will cover how to install OpenPyXL
Some form of spreadsheet program. I use Excel, but you can also use OpenOffice or similar applications

Description

Become an Excel Power User - Learn how to control & automate Excel with Python and OpenPyXL

Do you have a bunch of Excel files that you need to format or put some value in a cell on each page? Do you need to create Charts on your spreadsheets or insert formulas? Maybe you just want to show off at the office. Whatever your needs, you can expect a comprehensive guide going through the nuts and bolts of how automating Excel works. The course dives straight into OpenPyXL, so you will be up and running creating and manipulating spreadsheets in no-time.

I have had so much use of OpenPyXL when dealing with spreadsheets. You can create advanced sorts and filters, insert and delete rows, copy cells, set custom formatting and much more. I even got a job based on my OpenPyXL knowledge. I am sure you will benefit from learning OpenPyXL if you have even the slightest interest in making your everyday life easier.

After taking this course you will:

Know how to create and manipulate Workbooks and Sheets
Read cell data with absolute and relative references
Iterate over cells and perform actions on each cell
Know how to delete rows and columns
Insert formulas and tables
Format your spreadsheets with fonts, colors and cell types
Understand how you could apply this knowledge to your own work
Create files and folders
Interact with open and save file dialogs

Whether it's gaming, business, engineering, or data you're passionate about, this course will give you everything you need for working on spreadsheets with Python. Take this course today, and begin your journey to having a full-fledged career as an Excel Power User.

Who this course is for:

Anyone interested in controlling and automating Excel with Python - with or without coding experience. People who want to get rid of manually editing hundreds of spreadsheets by hand
Especially suitable for those who would like to extract or add data on multiple spreadsheets or automate the creation of spreadsheets from input data
People who would like to know more about solving Excel problems with Python
People who would like to start consulting services by automating Excel tasks

What's inside

Learning objectives

Controlling excel with python
Automate excel with python

How to use openpyxl

Controlling excel with python
Automate excel with python
How to use openpyxl

Syllabus

Install Python and OpenPyXL, how to read the documentation.

Welcome to the course!

Getting started

Installing OpenPyXL

Reading the documentation

Be careful with your workbooks, always backup!

This quiz will examine if you understood the Introduction session correctly.

How to create, open and save Workbooks and Sheets

Workbooks and Sheets introduction

Creating Workbooks

Opening Workbooks

Writing and saving to Sheets, Creating and Deleting Sheets

Copying Sheets

Getting sheet by index and name

Workbooks and Sheets Summary

Summary quiz for Workbooks and Sheets

How to read Cell data, and how to use Absolute and Relative referencing

Reading cells introduction

Reading Cells with absolute and relative referencing

Offset

Reading a Range of Cells

Summary quiz for Reading data from Cells.

Looping over cells, using iter_rows and iter_cols with max_row and max_col

Iterating over Cells introduction

Looping over Cells

iter_rows and iter_cols

max_row and max_col

Summary quiz for Iterating over Cells.

How to delete rows and columns, append rows, move and copy ranges, insert formulas and tables.

Manipulating Cells introduction

In this lecture, we are inserting and deleting rows and columns. Note that OpenPyXL has changed from a 0-based index to a 1-based index when inserting and deleting rows. The source code is updated to reflect these changes but the video shows the 0-based index usage.

The append method for rows

Moving and copying Ranges of Cells

Inserting formulas

Tables

Halfway through!

Summary quiz for Manipulating Cells

How to format Cells with Fonts, Colors, and Cell types. How to merge and unmerge Cells, freeze panes and apply Page Setups. How to apply Excel filters and Folds.

Formatting & filtering introduction

Formatting cells, fonts, colors, cell type (date, text etc)

CORRECTION: In the video we are setting the old_cell font to

old_cell.font = Font(name='Arial', size=18, color=colors.RED).

Since then OpenPyXL has updated to not use the colors constants RED, BLUE etc.

Instead we have to write the colors as a HEX value. The code for red is "FF0000" and thus the new line becomes:

old_cell.font = Font(name="Arial", size=18, color="FF0000")

https://www.rapidtables.com/web/color/RGB_Color.html has a picker as well as a table for HEX colors.

Merge and unmerge cells

Learn how to apply autofilter. Sorting cannot currently be done with OpenPyXL. I have attached the file 6.4_resources.py that contains the Win32 library that can apply the sort.

Freeze panes

Page setup

Fold

Summary quiz for Formatting and Filtering.

CHow to create Charts, Chartsheets and insert images.

Visuals introduction

Creating charts

Chartsheets

Inserting Images

Summary quiz for the Visuals section.

How to use file dialogs, create files and folders, open files, time scripts and start up Excel with Python

Useful Python code introduction

Tkinter file dialogs

Creating files and folders

Getting file path and name

Opening multiple files

Timing your scripts

Start Excel for viewing your work

Thanks for taking the course!

Your next steps

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Covers Excel data transformation, useful for business professionals with spreadsheet analysis work

Students should have basic knowledge on Python

Students should have access to Python 3 and OpenPyXL to follow along

Students could apply this knowledge to personal projects to automate their own work

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Control Excel with Python & OpenPyXL with these activities:

Refresh understanding of core Excel concepts to strengthen foundation for automation

Show steps

Ensure a strong foundation in core Excel concepts to maximize learning in the automation course.

Browse courses on Excel Basics

Show steps

Review online tutorials or manuals to refresh understanding of basic Excel functions.
Solve practice exercises to reinforce essential Excel skills.

Explore the latest trends in Excel automation with "Excel Power Programming"

Show steps

Gain insights into the latest Excel automation techniques to enhance your skills and stay current with industry practices.

View Excel VBA Programming For Dummies 5th Edition... on Amazon

Show steps

Read the book to expand understanding of advanced Excel automation concepts.
Apply new techniques learned from the book to existing automation projects.

Collaborate with peers to solve complex Excel automation challenges through discussion and knowledge sharing

Show steps

Enhance understanding and problem-solving skills by collaborating with peers on challenging Excel automation tasks.

Show steps

Identify a complex Excel automation challenge or project.
Form or join a peer group with complementary skills.
Work together to solve the challenge, sharing ideas and leveraging diverse perspectives.

Six other activities

Expand to see all activities and additional details

Show all nine activities

Master advanced formatting techniques with guided tutorials on conditional formatting and pivot tables

Show steps

Enhance data presentation and analysis skills by learning advanced formatting techniques through guided tutorials.

Show steps

Identify specific formatting issues or areas for improvement in existing spreadsheets.
Search for tutorials on advanced conditional formatting and pivot tables.
Follow the tutorials to implement advanced formatting techniques.

Enhance problem-solving skills with advanced Excel automation challenges

Show steps

Strengthen your grasp of spreadsheet automation and problem-solving techniques by tackling advanced Excel challenges.

Show steps

Find online resources or join communities offering advanced Excel automation challenges.
Solve challenges involving complex data manipulation, formula creation, and VBA integration.
Review solutions and discuss approaches with peers to improve understanding.

Expand knowledge and connect with experts at a dedicated Excel automation workshop

Show steps

Engage with industry professionals, learn from experts, and stay abreast of the latest Excel automation trends and techniques.

Show steps

Identify and register for a relevant Excel automation workshop.
Actively participate in the workshop sessions and discussions.
Connect with speakers and other attendees to exchange ideas.

Automate data extraction from a spreadsheet to enhance work productivity

Show steps

Build automation skills for common spreadsheet tasks to increase efficiency in data extraction and manipulation at your workplace.

Show steps

Identify a repetitive data extraction task in your current workflow.
Research online tools and tutorials to automate the task using Python and OpenPyXL.
Implement the automation solution for the identified task.

Foster critical thinking and communication skills by creating a blog post on automating Excel tasks

Show steps

Develop clear and concise technical writing skills while sharing Excel automation knowledge with others.

Show steps

Identify a specific topic or problem related to Excel automation.
Research and gather information on the topic.
Write a blog post that provides valuable insights and solutions.
Share the blog post on relevant platforms to reach a wider audience.

Create a portfolio of Excel automation projects to showcase your skills to potential employers or clients

Show steps

Build a tangible portfolio of projects that demonstrates your proficiency in Excel automation, increasing your credibility and employability.

Show steps

Identify a range of projects that showcase diverse Excel automation skills.
Develop and implement the projects to solve real-world problems.
Document the projects and their outcomes in a professional portfolio format.

Career center

Learners who complete Control Excel with Python & OpenPyXL will develop knowledge and skills that may be useful to these careers:

Spreadsheets Specialist

Spreadsheets Specialists are responsible for the creation and management of spreadsheets that support day-to-day business tasks. This course can help build a foundation for these tasks by teaching how to format and manipulate spreadsheets, including cell manipulation, reading data from cells, and working with formulas. Additionally, it will be helpful to learn how to format spreadsheets to make the data more understandable to end-users.

See salaries and explore the career path for Spreadsheets Specialist

Spreadsheet Developer

Spreadsheet Developers create spreadsheet applications that support specific operations in a business. This course will help build a foundation for spreadsheet development by teaching how to create, format, and work with spreadsheets using Python and OpenPyXL. This is especially helpful as some organizations have specialized spreadsheet applications that perform custom functions not found in standard spreadsheet programs like Excel.

See salaries and explore the career path for Spreadsheet Developer

Data Engineer

Data Engineers design, build, and maintain the systems that collect, store, and process data. They often work with Python to deliver specific functionality. This course will help build a foundation for working with Python, as well as managing and manipulating data. This knowledge may be useful as data is frequently used by Data Engineers.

See salaries and explore the career path for Data Engineer

Data Analyst

Data Analysts use various forms of data to derive insights and meaning. A Data Analyst may collect data manually or through technologies like Python scripts. This course can help build a foundation for writing these scripts, as Python is one of the most popular languages for working with data. Additionally, an understanding of how to manage spreadsheets using Python will allow a Data Analyst to quickly clean and manipulate data for analysis.

See salaries and explore the career path for Data Analyst

SQL Developer

SQL Developers develop, troubleshoot, and maintain the databases that store a company's data. They often work with Python to deliver specific functionality. This course will help build a foundation for working with Python, as well as managing and manipulating data. This knowledge may be useful as data is frequently stored in databases.

See salaries and explore the career path for SQL Developer

Systems Analyst

Systems Analysts use various programming languages, system tools, and software packages to help businesses improve efficiency. Some of these languages include Python, a popular language for automating tasks in spreadsheets. Proficiency in Python scripting can improve one's overall proficiency as a Systems Analyst, as spreadsheets are frequently used in data-related business processes.

See salaries and explore the career path for Systems Analyst

Risk Analyst

Risk Analysts use data to help businesses identify and manage risks. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it will help build a strong foundation for using Python to manipulate data, construct formulas, and format spreadsheets. This knowledge may be useful as data is frequently used by Risk Analysts.

See salaries and explore the career path for Risk Analyst

Business Analyst

Business Analysts use data to help businesses make informed decisions. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it will help build a strong foundation for using Python to manipulate data, construct formulas, and format spreadsheets. This knowledge may be useful as data is frequently used by Business Analysts.

See salaries and explore the career path for Business Analyst

Financial Analyst

Financial Analysts use data to help businesses make informed decisions. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it will help build a strong foundation for using Python to manipulate data, construct formulas, and format spreadsheets. This knowledge may be useful as data is frequently used by Financial Analysts.

See salaries and explore the career path for Financial Analyst

Marketing Analyst

Marketing Analysts use data to help businesses understand their customers and make informed decisions. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it will help build a strong foundation for using Python to manipulate data, construct formulas, and format spreadsheets. This knowledge may be useful as data is frequently used by Marketing Analysts.

See salaries and explore the career path for Marketing Analyst

Data Scientist

Data Scientists use data to help businesses make informed decisions. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it will help build a strong foundation for using Python to manipulate data, construct formulas, and format spreadsheets. This knowledge may be useful as data is frequently used by Data Scientists.

See salaries and explore the career path for Data Scientist

Programmer Analyst

Programmer Analysts design, implement, and test computer programs. They work with business stakeholders to identify and define needs, then create programs or systems that meet those needs. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it covers topics on data manipulation, formula construction, and formatting, which are all applicable to the work of a Programmer Analyst.

See salaries and explore the career path for Programmer Analyst

Database Administrator

Database Administrators manage and maintain databases, which store and organize data for various applications. This course may be useful for learning how to work with Python, a very common language in programming. Additionally, it covers topics on data manipulation, formula construction, and formatting, which are all applicable to database management.

See salaries and explore the career path for Database Administrator

Software Engineer

Software Engineers design, develop, test, and maintain software systems. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it covers topics on data manipulation, formula construction, and formatting, which are all applicable to the work of a Software Engineer.

See salaries and explore the career path for Software Engineer

Quantitative Analyst

Quantitative Analysts use mathematical and statistical techniques to analyze data and make predictions. This course may be helpful for learning how to work with Python, a very common language in programming. Additionally, it covers topics on data manipulation, formula construction, and formatting, which are all applicable to the work of a Quantitative Analyst.

See salaries and explore the career path for Quantitative Analyst