O'Reilly logo
live online training icon Live Online training

Python-Powered Excel

Powered by Jupyter logo

Take Control of Your Data by Automating Excel Spreadsheets

Jason Graham

Excel is ubiquitous in the business world, and whether we admit it or not, we all have spreadsheets that are “critical” to the organization. When it comes to analyzing and reporting on data, it’s the tool of choice for a majority of business and data professionals. We use it for a range of projects--from personal budgets to mini-databases to complex data analysis tools.

But Excel, by itself, doesn’t solve every problem, like weekly reports that take too long to build, format, and check for errors. Perhaps you’ve tried templates, complex formulas, even VBA. None of those options provide the ideal solution: Quick, dynamic, automated, nearly error-free reports that can be built in a timely fashion. That’s where Python comes in.

You don’t need to be a programmer, you don’t need months of training, and you don’t need to know Python (although some familiarity helps). In this four-hour hands-on course, you’ll learn step by step how to create complex Excel workbooks in Python and discover how to automate that process. You’ll boost your efficiency, improve productivity, and take real control of your data.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • Basic Python and Pandas
  • Reading data with Python
  • Writing data to Excel from Python
  • Basic Excel Formatting with Python
  • Conditional Formatting in Excel with Python
  • Creating Excel Graphs and Charts with Python

And you’ll be able to:

  • Update those old, complex-but-critical spreadsheets, and turn them into dynamic reports
  • Write Python scripts that quickly build formatted Excel spreadsheets from multiple sources with little more than a few mouse clicks
  • Eliminate the need for complex Excel formulas and VBA
  • Take the “human error” factor out of your spreadsheets

This training course is for you because...

  • You are a business professional who relies on Excel for reporting and analytics
  • You have spreadsheets that are more “formula” than data
  • You need the power of formulas and VBA without the complexity
  • You’re interested in automating reports but don’t know how
  • You combine data from multiple sources for your workbooks
  • You want to reduce the amount of time you spend creating and editing Excel workbooks

Prerequisites

  • An intermediate level of familiarity with Excel
  • Some familiarity with basic programming concepts like variables, arrays, functions, methods, and iterators (in any language, not necessarily Python)

Recommended preparation:

Recommended:

Required Setup:

  • This course will be using O’Reilly’s JupyterHub so no setup is required.

About your instructor

  • Jason Graham is Software Engineer at GoMoto, Inc. He's been using Excel for more than a decade to perform Data Analysis and Information Management and builds web-based Data Analysis tools for end-users with Python. When he isn't building analysis tools, he trains others in Data Analysis and Python. Jason is an eight-year veteran of the U.S. Marines, 12-year veteran of the U.S. Army, and a graduate of the Marines' Formal School Instructor Course. Prior to coming to GoMoto, he was a Senior System Engineer at Cerner, and four years teaching Civil Information Management in the Army Reserves.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Course Overview (15 min)

  • Introductions and Welcome
  • Jupyter Notebook Overview
  • Demo of the final product

The Sales Report (40 min):

  • Reading a simple spreadsheet
  • Cleaning Bad Data
  • Grouping and Calculations
  • Building a simple layout - Headers and Background Colors
  • Writing the data to a file
  • Exercise: Building the Sales Report
  • Q&A
  • Break (5 min)

The Accounting Report (40 min):

  • Reading multiple sheets
  • Conditional Formats
  • Dealing with erroneous data
  • Writing multiple sheets.
  • Exercise: Adding the Accounting Report
  • Q&A
  • Break (5 min)

The Supply Report (40 min)

  • Transposing Data
  • Assembling multiple tables into one
  • Python Functions
  • Exercise: The Supply Report
  • Q&A
  • Break (5 min)

Human Resources (40 min)

  • Python Conditionals
  • Filtering DataFrames
  • Conditional Values
  • Exercise: The HR Report
  • Q&A
  • Break (5 min)

The Final Report (45 min)

  • Exercise: Build a “dashboard” page with some summary data and simple graphs, and add in all of the other reports in the desired format.
  • Q&A