Excel with Python: Performing Advanced Operations

Python 3.7    |    Intermediate
  • 17 videos | 1h 29m 33s
  • Includes Assessment
  • Earns a Badge
Likes 77 Likes 77
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.

WHAT YOU WILL LEARN

  • 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

IN THIS COURSE

  • 2m 12s
  • 6m 57s
    In this video, you will learn how to apply styling elements to control the way data is displayed in cells. FREE ACCESS
  • Locked
    3.  Working with Borders and Colors
    5m 9s
    To format cell contents with sophisticated styles and alignments, find out how to apply them. FREE ACCESS
  • Locked
    4.  Applying Number Formats
    8m 4s
    Learn how to use number formats to represent currencies and add commas as separators. FREE ACCESS
  • Locked
    5.  Applying Conditional Formatting
    4m 31s
    During this video, you will learn how to apply formatting that varies based on the value contained in a cell. FREE ACCESS
  • Locked
    6.  Using Advanced Conditional Formatting
    4m 49s
    In this video, you will learn how to choose from different in-built icon sets and rules to control cell formatting at a more granular level. FREE ACCESS
  • Locked
    7.  Working with Images
    3m 5s
    In this video, you will insert images into Microsoft Excel files and control their size and position. FREE ACCESS
  • Locked
    8.  Working with Formulae
    6m 56s
    Learn how to insert formulas into Excel workbooks. FREE ACCESS
  • Locked
    9.  More Operations Using Formulae
    3m 29s
    Learn how to use openpyxl to programmatically construct formulas in workbooks. FREE ACCESS
  • Locked
    10.  Using Absolute and Relative Cell References
    5m 52s
    Learn how to use the $ operator to convert relative cell references into absolute references. FREE ACCESS
  • Locked
    11.  Programmatically Constructing Absolute References
    5m 28s
    In this video, you will use openpyxl to construct both absolute and relative cell references. FREE ACCESS
  • Locked
    12.  Using VLOOKUP
    6m 46s
    Find out how to use VLOOKUP to look up specific values from a range in Excel. FREE ACCESS
  • Locked
    13.  Working with Named Ranges
    6m 8s
    To make your formulas more readable, learn how to assign names to groups of cells. FREE ACCESS
  • Locked
    14.  Working with Pivot Tables
    5m 42s
    In this video, you will learn how to use Excel pivot tables to dynamically analyze and group data. FREE ACCESS
  • Locked
    15.  Using Pandas for Pivoting
    7m 48s
    In this video, you will learn how to use Pandas to read data from Microsoft Excel and perform pivoting operations. FREE ACCESS
  • Locked
    16.  Leveraging Multi-level Indexing in Pandas
    5m 10s
    In this video, find out how to use Pandas to perform multi-level indexing and access individual row values as well as index values. FREE ACCESS
  • Locked
    17.  Course Summary
    1m 27s

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Book Test Your Skills in Python: An interactive way to introduce the world of Computer Programming, 2nd Edition
Course Python Development: Performing Operations with Complex Data Types
Likes 51 Likes 51  
Book Enterprise Automation with Python: Automate Excel, Web, Documents, Emails, and Various Workloads with Easy-to-code Python Scripts

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course Creating PivotTables and PivotCharts in Excel 365
Course Excel with Python: Working with Excel Spreadsheets from Python
Likes 136 Likes 136  
Course Complete Guide to Excel 365: Getting Started
Likes 219 Likes 219