Published byO'Reilly Media, Inc.
Since the early 1990s, Microsoft Excel has been the dominant spreadsheet application for business users and personal use alike. This longtime-ubiquitous application is, for a vast number of organizations and data professionals, still the go-to tool for analyzing data and creating reports for everything from personal budgets to mini-databases to complex data analysis. But depending on what you want to do with it, Excel can be quite cumbersome and tedious to use; for example, for weekly reports it can take quite an effort to build, format, and check your reports for errors. Of course, you can use templates, complex Excel formulas, and even VBA to automate certain tasks. But none of those options provide the ideal solution: quick, dynamic, automated, nearly error-free reports that you can build in a timely fashion. That’s where Python comes in.
In this learning path, designed for those who have intermediate-level Excel skills and some knowledge of basic programming concepts like variables, arrays, functions, methods, and iterators, your host, software engineer and Excel expert, Jason Graham, shows you step by step how to create complex Excel workbooks in Python. And the beauty is that you don’t even need to know Python (although some familiarity helps); just as long as you have some familiarity with programming fundamentals, Jason can guide you in Python. You’ll see how to easily automate processes like conditional formatting, generating graphs and charts, and reading and writing data that would otherwise take tremendous time and effort to do using Excel alone. When you’ve completed this learning path, you’ll boost your efficiency, improve productivity, and be able to take real control of your data.
What you’ll learn—and how you can apply it
- 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
This learning path 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
- You should have an intermediate-level familiarity with Excel
- You should have some familiarity with basic programming concepts such as variables, arrays, functions, methods, and iterators (in any language, not necessarily Python)
Materials or downloads needed in advance:
- Code repository for the course: https://resources.oreilly.com/binderhub/python-powered-excel
- Python for Data Analysis , 2nd Edition , Chapters 1–3
- The Official Python Tutorial , Part 1–5
- Pandas Documentation : Pandas is the Supercharged Excel of the Python world and is used heavily in this Course. The Pandas official documentation page should be your go-to reference.
- XlxsWriter Documentation : XlsxWriter is the bridge between Python and Excel. The documentation page is the single best reference for its use and capabilities.
- Python for Data Analysis , 2nd Edition : The author of this book, Wes McKinney, is the creator of Pandas. Chapters 1 through 3 are recommended reading before taking this course, but it is highly recommended that you read the book in full when you have time.