O'Reilly logo
live online training icon Live Online training

Python-Powered Excel: Take Control of Your Data by Automating Excel Spreadsheets

enter image description here

Topic: Business
George Mount

Excel spreadsheets are the tool of choice for data analysis and reporting for a majority of business professionals. But Excel has its limitations for building repeatable, dynamic data processes, which templates and complex formulas fail to solve.

Join expert George Mount to learn how to fully automate Excel workbooks using Python. This popular open source programming tool is a catalyst for fast, error-prone spreadsheet reports and analysis. While the thought of learning a programming language may be intimidating, you know more about data and coding than you think.

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

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

  • When to use Excel for data analysis and reporting and when to use Python
  • How Python can automate common data preparation and manipulation tasks often done in spreadsheets
  • Where Python differs from Excel in how it stores and operates on data

And you’ll be able to:

  • Navigate and execute code in Jupyter notebooks
  • Load, view, and write workbooks to and from Python
  • Add custom formats, protections, and metadata to workbooks using Python
  • Navigate and execute code in Jupyter notebooks
  • Create Excel charts and graphs using Python

This training course is for you because...

  • You're an analyst responsible for collecting, analyzing, and interpreting data for business insights.
  • You’d like to speed up, automate, and validate your reporting and analysis using open source software.
  • You’re a spreadsheet user interested in learning more about data science or software development.

Prerequisites

  • A working knowledge of basic tasks and functions in Excel, including sorting and filtering, IF statements, conditional aggregates like SUMIF() and COUNTIF(), and PivotTables and VLOOKUP()
  • Familiarity with programming concepts like variables, arrays, functions, methods, and iterators (in any language, not necessarily Python)

Recommended preparation:

Recommended follow-up:

About your instructor

  • George Mount develops data analytics curriculum and training programs through his company Stringfest Analytics. He’s been featured on industry outlets such as Excel TV and the MyExcelOnline podcast through his website Georgejmount.com. His blog features content on Excel and, more generally, business analytics, training, and career development. He holds a master’s degree in information systems with a certificate of achievement in quantitative methods from Case Western Reserve University.

Schedule

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

Up and running with Python in Excel (55 minutes)

  • Presentation: Reading in data from Excel—working with Jupyter notebooks, assigning Python objects to imported Excel data, setting column names and attributes, working with multiple worksheets; beginning the workbook do-over—adding rows, columns, and formulas to a workbook from Python, customizing workbook settings (changing fonts and sizes, adding borders, freezing panes, etc.)
  • Jupyter Notebook exercise: Read Excel data into Python and customize the workbook
  • Q&A

Break (5 minutes)

Managing workbooks (55 minutes)

  • Presentation: Customizing cells and ranges—defining names and ranges, adding cell comments, setting conditional formatting; customizing worksheets—adding data validation and worksheet protection, hiding and grouping rows, columns, and worksheets
  • Jupyter Notebook exercise: Customize ranges and worksheets from Python
  • Q&A

Break (5 minutes)

Python for data analysis (60 minutes)

  • Presentation: Using pandas with Excel—exploring and summarizing Excel data in Python, operating on tabular data in Python with pandas; data visualization—adding Excel sparklines and charts from Python, inserting Python visualizations into Excel
  • Jupyter Notebook exercise: Analyze and visualize Excel data from Python
  • Q&A