Excel Operations in Python Competency (Intermediate Level)

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