Excel Operations in Python Competency (Intermediate Level)

  • 25m
  • 25 questions
The Excel Operations in Python Competency (Intermediate Level) benchmark evaluates your ability to demonstrate the use of Python and the openpyxl library to create and modify Microsoft Excel spreadsheets. You will be assessed on your skills in performing complex data analysis operations using pivoting and formulae, controlling workbook appearance using conditional formatting and styles, building various types of visualizations using Python, and manipulating chart properties to customize their appearance. A learner who scores high on this benchmark demonstrates that they have the skills to manage Excel data, perform advanced operations, and construct data visualizations using Python.

Topics covered

  • access entire rows and columns and manipulate them programmatically
  • access individual cells programmatically using openpyxl
  • alter the data displayed on the axes of a graph
  • alter the weight and line style of data series to customize the appearance of a visualization
  • apply styling elements to control the display of data in cells
  • assign names to groups of cells and use those names in formulae to enhance readability
  • choose from different in-built icon sets and rules to control cell formatting at a granular level
  • construct and modify bar chart visualizations in Excel
  • create a Microsoft Excel workbook using the openpyxl library
  • filter data in a range based on various attributes
  • insert images into Microsoft Excel files and control their size and location
  • merge and unmerge groups of cells
  • plot financial data containing open, high, low, close, and volume information about stock prices
  • sort data in a range using different columns and sort criteria
  • specify rows and columns that always ought to be on-screen as a user browses an Excel file
  • tweak the title, formatting, and legend of a graph
  • use bubble charts to represent three dimensions of data in a two dimensional visualization
  • use lists and other Python containers to read to and write from Excel files
  • use number formats to represent currencies and add comma separators
  • use openpyxl to construct visualizations in Excel workbooks
  • use openpyxl to programmatically construct formulae in workbooks
  • use Pandas to perform multi-level indexing and access individual row values as well as index values
  • use Pandas to read data from Microsoft Excel and perform pivoting operations
  • use the $ operator to convert relative cell references into absolute ones
  • use VLOOKUP to lookup specific values from a range in Excel