Excel with Python: Performing Advanced Operations


Overview/Description
Expected Duration
Lesson Objectives
Course Number
Expertise Level


Overview/Description

Learners can explore complex operations in Microsoft Excel workbooks, including the use of conditional formatting, named ranges, and merged cells, in this 17-video course. Microsoft Excel is the best prototyping tool for data analysis, an interactive functional programming environment, and a forerunner of Python. Begin by exploring how Python and its ecosystem of libraries are fast emerging as a popular choice for easy spreadsheet automation. Then observe the formatting, alignment, and other aesthetics in Python. You will work with the Python library openpyxl; examine data analysis, the use of pivot tables, and the locking of cell references by using the $ operator; and learn how to perform complex data analysis operations using pivot tables, sorting and filtering, and formulae with both absolute and relative cell references to enable efficient copy paste. You will learn to control the workbook appearance using conditional formatting and styles. Finally, this course demonstrates how to leverage the Python Pandas library to read a spreadsheet, to group and analyze data.



Expected Duration (hours)
1.5

Lesson Objectives

Excel with Python: Performing Advanced Operations

  • discover the key concepts covered in this course
  • apply styling elements to control the display of data in cells
  • apply sophisticated styles and alignments to format cell contents
  • use number formats to represent currencies and add comma separators
  • apply formatting that varies based on the value contained in a cell
  • choose from different in-built icon sets and rules to control cell formatting at a granular level
  • insert images into Microsoft Excel files and control their size and location
  • insert formulae into Excel workbooks
  • use openpyxl to programmatically construct formulae in workbooks
  • use the $ operator to convert relative cell references into absolute ones
  • use openpyxl to construct both absolute and relative cell references
  • use VLOOKUP to lookup specific values from a range in Excel
  • assign names to groups of cells and use those names in formulae to enhance readability
  • use Excel pivot tables to dynamically analyze and group data
  • use Pandas to read data from Microsoft Excel and perform pivoting operations
  • use Pandas to perform multi-level indexing and access individual row values as well as index values
  • summarize the key concepts in this course

    • Course Number:
    it_pyexpydj_02_enus

    Expertise Level
    Intermediate