Microsoft Excel Engineering (Part 1)

Explain the Excel object structure from application to workbook to worksheets to range and cells, illustrated by two workbooks open within a single Excel instance.

Learn to build excel formulas by two methods: typing cell references and selecting cells with the mouse, using an equals sign to calculate a box volume.

Explore how Excel autofill recognizes numeric patterns, days, and custom functions, enabling automatic continuation across rows and columns by dragging the bottom right corner.

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.

Learn to create custom variable names in Excel by defining names for cells and ranges, using the name box and defined name manager in formulas and dashboards.

Explore built-in Excel functions, including the average, using manual calculations, embedding functions, and the function editor to work with ranges like A1 to A5.

Explore excel's logic capabilities using if statements with logical tests, value if true or false, and nested conditions, illustrated by heads, tails, and young, old, or very old outcomes.

Explore Excel's spreadsheet audit capabilities to map dependencies with trace precedents and dependents, and evaluate formulas step by step to debug complex calculations in a multi-cell model.

Master Excel shortcuts for fast navigation and selection: arrow keys, ctrl+arrow, ctrl+home, ctrl+end, home, page up/down, shift+arrow, and ctrl+shift combinations, plus quick copy and fill.

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.

Learn to apply Excel's auto filter to quickly filter, sort, and search data, using text filters, color-based sorting, and multi-criteria conditions to reveal targeted records.

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.

Explore range count functions in Excel by using count, counta, and countblank on a named data range to count numbers, non-empty cells, and empty cells.

Explore the Excel match function with named ranges to find the largest value less than or equal to a lookup value and return its index in a time series.

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.

Learn to enable the analysis tool pack in Excel and run descriptive stats, including mean, median, and standard deviation, then create histograms with bins to analyze patient data.

Create an xy scatterplot, link titles to cells, and apply trendlines with regression analysis. Explore heat maps, surface plots, and iterative calculation for dynamic dashboards.

Contrast unstructured and structured spreadsheets, highlighting organized inputs, named parameters, defined input/output ranges, units, and readable formulas using user defined functions for clarity.

Design a reusable input-output structured spreadsheet template, defining input and output ranges, saving as an Excel template, and loading it for new projects to keep data organized and visible.

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.

Treat your workbook and worksheets as subsystems to organize aircraft design data. Structure the system into subsystems like fuselage and power plant so teams locate architecture, calculations, and data easily.

create and apply style collections in excel to structure input and output data with custom styles like input parameter heading and cost input, using built-in and custom options.

Implement data validation to restrict a cost input to 500–1200 with custom input and error messages, and use a drop-down list from a range or named range like car models.

Learn what VBA is, where to access the Visual Basic Editor in Excel, and how to open it via Alt+11, right-click view code, or the developer tab to write functions.

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

Explore VBA modules in Excel, using the VBA editor to create user forms and modules, export and import libraries, and build procedures, functions, and class modules.

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.

Explore sub procedures in VBA to control worksheets and workbooks, trigger macros, and interact with shapes, buttons, and message boxes.

Learn to debug VBA code by stepping through sub procedures and functions, using the VB editor, F8, breakpoints, watches, and the immediate and locals windows.

Discover how to automate tasks in Excel by recording macros, editing VBA in the VB Editor, and using shapes or form controls to run sub procedures and insert sheets.

Explore how goal seek in Excel uses what-if analysis to predict revenue by adjusting shoes sold and cost, and set the cell to a target value.

Enable the solver add-in, define a design variable set, and configure objective functions to minimize cost or maximize performance under inequality and equality constraints with upper and lower bounds.

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.