Microsoft Excel Engineering (Part 1)

Learn how to use relative and absolute cell references in Excel, anchor constants like pi with $ or F4, and copy formulas across rows and columns.

Import and export text and comma separated value files in Excel, using comma or tab delimiters, then load, filter, and save as a CSV for data sharing.

Connect Excel to web data sources via the Data tab using From Web, paste a URL, load a table, and set automatic refresh for real-time updates, filters, and dashboards.

Convert a data range into a proper data table by inserting a table with headers, then use table tools for design, filters, and a self-expanding, database-like row growth.

Explore Excel's range structure functions for matrices and arrays, including a 3x3 matrix named matrix A, using columns, rows, index, transpose, and ctrl+shift+enter for array copies.

Learn to combine index and match to fetch vitamin A, B, and C data at a given time from a named time series, using exact matches and dynamic lookups.

Create advanced spreadsheets by defining named ranges and using user defined functions to compute volume from length, width, and height, while organizing inputs and outputs in a structured workbook.

Explore the VBA editor, navigate the project explorer, and understand workbook and worksheet objects, properties, and methods to control Excel with Visual Basic.

Create and use user defined functions in VBA to replace cluttered worksheets, building and calling function procedures in modules, returning values to the worksheet with a reusable library.

Use Excel solver to minimize the cost of a rectangular box by tuning length, width, and height under a volume constraint and variable bounds, optimizing the surface area cost.