Data Science 101: Python Plus Excel

Learn excel and python with real world case study.

What you'll learn

Write excel advanced conditional, text, and lookup functions

Excel automation using python

Learn Microsoft Excel 2016 and many of its advanced features

Learn data science skills using Python and Excel

Excel features using numpy and pandas

Visualization using Excel and Python

Requirements

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

Install Microsoft 2016, 2013 or 2010

Description

For many years, and for good reason, Excel has been a staple for working professionals. It is essential in all facets of business, education, finance, and research due to its extensive capabilities and simplicity of use.Over the past few years, python programming language has become more popular. According to one study, the demand for Python expertise has grown by 27.6 % over the past year and shows no indications of slowing down. Python has been a pioneer in web development, data analysis, and infrastructure management since it was first developed as a tool to construct scripts that "automate the boring stuff."Why python is important for automation?Consider being required to create accounts on a website for 10,000 employees. What do you think? Performing this operation manually and frequently will eventually drive you crazy. It will also take too long, which is not a good idea.Try to consider what it's like for data entry workers. They take the data from tables (like those in Excel or Google Sheets) and insert it elsewhere.They read various magazines and websites, get the data there, and then enter it into the database. Additionally, they must perform the calculations for the entries.In general, this job's performance determines how much money is made. Greater entry volume, more pay (of course, everyone wants a higher salary in their job).However, don't you find doing the same thing over and over boring?The question is now, "How can I accomplish it quickly?"How to automate my work?Spend an hour coding and automating these kinds of chores to make your life simpler rather than performing these kinds of things by hand. By just writing fewer lines of Python code, you can automate your strenuous activity.The course covers following topics:1. Excel basics2. Excel Functions3. Excel Visualizations4. Excel Case study (Financial Statements)5. Python numpy and pandas6. Python Implementations of Excel functions7. Python matplotlib and pandas visualizationsThe evidence suggests that both Excel and Python have their place with certain applications. Excel is a great entry-level tool and is a quick-and-easy way to analyze a dataset.But for the modern era, with large datasets and more complex analytics and automation, Python provides the tools, techniques and processing power that Excel, in many instances, lacks. After all, Python is more powerful, faster, capable of better data analysis and it benefits from a more inclusive, collaborative support system.Python is a must-have skill for aspiring data analysts, data scientist and anyone in the field of science, and now is the time to learn.

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Python vs Excel

Lecture 3 Limitation of Excel

Lecture 4 Python

Lecture 5 who can benefit from learning Python?

Lecture 6 What makes Python a better option than Excel?

Lecture 7 Excel vs Python: Who wins?

Section 2: Download Resources for Excel [IMPORTANT]

Lecture 8 Download Excel Lecture content!

Section 3: Introduction to Basics of Excel

Lecture 9 Structure of Excel sheets

Lecture 10 The Ribbon

Lecture 11 Rows and Columns

Lecture 12 Enter, Edit, Delete in Excel

Lecture 13 Excel basic formatting: border, font, color

Lecture 14 Align Left, Right, Center

Lecture 15 Arithmetic operations

Lecture 16 Excel formulas introduction

Lecture 17 Copy and Paste

Lecture 18 Formatting cell

Lecture 19 Formatting worksheet

Lecture 20 Moving and selecting contents in Excel sheets

Lecture 21 [IMPORTANT] Fixing cell references

Lecture 22 ALT+ENTER

Lecture 23 Text to Column

Lecture 24 Wrap Text

Lecture 25 Select special

Lecture 26 Dynamic Naming

Lecture 27 Custom Formatting 1

Lecture 28 Custom Formatting 2

Lecture 29 Multiple Formats

Section 4: Excel Tools and Tips

Lecture 30 Macros

Lecture 31 Data Validation

Lecture 32 Sort and Filter

Lecture 33 Hyperlinks

Lecture 34 Freeze Panes

Lecture 35 Tell me what you want to do

Lecture 36 Keyboard Shortcuts

Section 5: Excel Functions

Lecture 37 Count, countif and countifs

Lecture 38 Sum, sumif and sumifs

Lecture 39 average and averageif

Lecture 40 Text functions

Lecture 41 max and min functions

Lecture 42 round function

Lecture 43 vlookup function [IMPORTANT}

Lecture 44 hlookup function

Lecture 45 index and match function

Lecture 46 iferror function

Lecture 47 pivot tables

Lecture 48 data tables

Section 6: Excel Visualizations

Lecture 49 Excel charts

Lecture 50 Basic formatting for charts

Lecture 51 Designing charts

Lecture 52 Bridge charts

Lecture 53 Treemap

Lecture 54 Spark Lines

Section 7: Excel Case Study

Lecture 55 Introduction to data

Lecture 56 Preprocessing data

Lecture 57 Create unique code (primary key)

Lecture 58 Creating database

Lecture 59 Populate database 1

Lecture 60 Populate database 2

Lecture 61 Mapping each row to category

Lecture 62 Income statement

Lecture 63 Format statement

Lecture 64 Format statement more

Lecture 65 Populate Income (P&L) statement

Section 8: Excel Functions in Python

Lecture 66 vlookup function in excel

Lecture 67 Implement vlookup functionality in Python

Lecture 68 Pivot tables in excel

Lecture 69 Implement pivot tables functionality in Python

Lecture 70 Pivot tables using pandas

Lecture 71 IF function in Excel

Lecture 72 IF functionalities in python

Lecture 73 Text manipulation in Excel

Lecture 74 Text manipulation in Python

Lecture 75 count, countif, countifs, sum, sumif, sumifs

Lecture 76 count, countif, countifs, sum, sumif, sumifs in Python

Section 9: Python Visualizations

Lecture 77 pivot charts in Excel

Lecture 78 Python pandas visualization

Lecture 79 Matplotlib

Lecture 80 Formatting charts

Lecture 81 More on matplotlib

Lecture 82 matplotlib and pandas together

Excel users curious about automating their work using python,Python Developer wanting career switch in Data Science