Excel Automation Using Python

Name: Excel Automation Using Python
Rating: 4.6 (1634 reviews)

Transition from excel to python and enhance your productivity

Highest Rated

Created byMayank Rasu

Last updated 1/2024

English

What you'll learn

Excel automation using python

Course content

6 sections • 54 lectures • 8h 1m total length

Excel vs Python6:08
Compare Excel and Python, highlighting Excel's ubiquity, pivot tables, macros and VB, and show how Python's open-source libraries offer scalable, free alternatives that can replace Excel.
Course Objectives & Prerequisites5:25
Anaconda Distribution Introduction8:24
Creating Virtual Environment (Optional)9:15

The Power of Pandas (Teaser Video)9:47
Explore using pandas to analyze the US flight database, group by origin, and compute mean departure delays, illustrating how pandas handles large data more efficiently than Excel.
Pandas Series8:10
Pandas Dataframe15:45
Explore how Pandas data frame combines multiple series with shared indices into a two-dimensional tabular structure and how to create and index it manually.
Importing/Exporting Data from Excel Spreadsheet20:32
Handling Python/Dataframe Data Types11:36
Handling NaN Values13:28
Combining DataFrames (Concatenate, Merge & Join)20:38
Data Aggregation/Summarization using Groupby11:20
Numpy Assignment3:35
Numpy Assignment Solution15:21
Pandas Assignment2:21
Pandas Assignment Solution13:33

Vlookup - I17:33
Vlookup - II7:25
Convert a Vlookup template into a reusable Python function that merges left and right data frames by left and right keys and returns the right value column.
Pivot Table - I3:24
Pivot Table - II9:10
Implement a Python pivot table by selecting rows (segment, category, subcategory) and columns (region), aggregate profit with groupby, unstack to columns, prune columns, and export to Excel or HTML.
Pivot Table - III4:44
IF Function - I3:55
IF Function - II4:44
Text Manipulation in Excel7:11
String Manipulation in Python8:23
Explore how to replicate Excel string functions in Python using Pandas dot str, indexing, and split to extract year and state codes, then concatenate, trim, and adjust case.
Assignment - SUMIF(s) and COUNTIF(s)6:59
Replicate four popular Excel functions—countif, countifs, sumif, and sumifs—in Python with pandas indexing for automated data analysis, as part of Excel automation using Python.
Assignment Solution - SUMIF(s) and COUNTIF(s)8:01

Excel Visualization5:57
Python Visualization Intro4:40
Pandas Visualization8:22
Explore pandas visualization with dataframe dot plot, grouping sales and profit by region, customizing plots with kind, title, subplots, layout, legend, and stacked options.
Matplotlib Object Oriented Interface3:13
Hands on With Matplotlib OO Interface - I14:22
Hands on With Matplotlib OO Interface - II8:24
Hands on With Matplotlib OO Interface - III10:08
Combining Pandas Visualization with OO Capabilities - I10:23
Combining Pandas Visualization with OO Capabilities - II13:40
Visualization Assignment 13:35
Recreate the two side by side pie charts using the sales data: category-level sales breakdown with actual sales in thousands of dollars in a legend, and segment-level sales breakdown.
Visualization Assignment 1 Solution - I11:40
Visualization Assignment 1 Solution - II15:50
Visualization Assignment 21:30
Visualization Assignment 2 Solution15:01

Requirements

Python basics (data types, loops, functions etc.)

Description

Excel spreadsheets are ubiquitous and no corporate job is possible without them. Like you, I have been working with them since I started my career. However, I rarely use excel now since I have automated most of my excel based tasks using python which has done wonders to my productivity and I want to help you do the same. I have created this course to help you automate your excel spreadsheets based tasks using python and improve your productivity manifold.

#############################################################################################

Are you someone whose day job requires a lot of manual handling of buggy macros and working on voluminous excel files?
Are you fed up with the tyranny of vlookups, Sumifs, pivot tables and excel dashboards and looking to upskill?
Are you the type of person who would prefer the convenience of python over the unsightliness of VBA code?

If the answer to any of the above questions is yes then you should consider this course. This course will start from the basics and will help you automate reasonably sophisticated excel based tasks. The course will also provide you with enough preparation to explore more advanced topics pertaining to automation/data analysis.

The course covers following topics

Creating python environment
Importing excel into python
Aggregating data from multiple files
Splitting data into multiple files
Interacting with your OS programmatically
Automating popular excel functions such as vlookup, sumif, countif etc
Replicating excel visualizations
Automating pivot tables

#############################################################################################

Important note - Course prerequisites:

Please note that this course requires basic python proficiency. At the minimum, you should be comfortable with:

basic python data types and format
basic python data structures such as list, dictionary, tuple etc.
how to create python functions
how to implement loops in python

#############################################################################################

Who this course is for:

Heavy users of excel curious about automating their work using python
People who are tired of working with buggy macros and voluminous spreadsheets

Excel Automation Using Python

What you'll learn

Explore related topics

Course content

Introduction4 lectures • 29min

Numpy Intro5 lectures • 54min

Pandas Intro12 lectures • 2hr 26min

OS Interaction Using Python8 lectures • 44min

Implementing Excel Functions in Python11 lectures • 1hr 21min

Visualization14 lectures • 2hr 7min

Requirements

Description

Who this course is for: