Our "all in one" Excel manual for engineering and science that covers everything we know.
Over 2200 + pages of formulas, modeling, data processing, charting, data integration, dashboards along with our complete Excel VBA write up, it's everything we know.
Questions? 1.805.498.7162 Customers >
Microsoft Excel Manual Specifics
| For: |
Engineers and Scientists |
| Format: |
ADOBE PDF and Excel Files
as zip file download |
| Details: |
Covers beginning general Excel to
advanced Excel VBA topics, everything from all of our manuals |
| Book: |
2200 + pages with examples |
| Platform: |
Windows 7 - Windows 10 |
| Sent: |
Sent within 3 hr after purchase Mon-Fri 8:30 AM - 5:00 PM PT |
| Versions: |
Excel for Windows 2010-2019, Microsoft 365 |
Manual Introduction. This all in one
self study manual covers fundamental Excel concepts along with the
advanced Excel topics needed for creating adaptable, problem solving
engineering and science Excel tools.
The first half of the manual covers
fundamental Excel topics like workbook and worksheet operation, cell
data entry, worksheet formatting, formula design, cell naming, worksheet functions,
Conditional Formatting, Data Validation, table design, sorting, filtering,
using Excel tables, PivotTable design, technical chart creation and linking Excel with other programs like Word and PowerPoint.
The advanced topics covered include data integration using Power Query
and MS Query, designing reports that adapt to changing data, integrating
design teams, creating advanced array formulas, performing design
optimization using Solver, advanced modeling, and creating state of the
art dashboards. Our manual also covers unique advanced topics like
solving numerical techniques on the worksheet including linear systems,
Runge Kutta, Newton's method, integration, differentiation and boundary
value problems.
The second half of
the manual includes everything we know about using Excel VBA in
engineering and science, from the fundamentals to the most advanced
topics we have encountered, from data processing to designing advanced
interfaces. The topics are so broad, we refer you to the book outline
below for all the details.
Price: $169.00
Add to Cart
Our Refund Policy >
Learning Module 1:
Creating, Opening, Saving Workbooks; Controlling Workbook Views; Using
the Ribbon and Using Help
- How to use the manual and terminology used in the manual
- Mouse terminology in the manual
- Introduction to Microsoft Excel
- Excel program interface review including how to use Excel on-line help
- How to open and close workbooks
- How to save workbooks
- How to use the Ribbon, shortcut menus, dialog boxes and hot keys
- How to arrange workbook windows
- How to create a new workbook
- How to freeze a worksheet row/column
- How to split a worksheet screen
- Overview of the main Excel elements that make up a workbook
Learning Module 2:
Using Microsoft Excel in Engineering and Science
- Discusses how Excel can be used in areas of analysis, information integration,
VBA program development, dashboard development, rapid analysis, team integration and data processing.
Learning Module 3: Formatting, Formula Construction,
Data Analysis, Problem Formulation and Construction Methods for the
Excel Worksheet
- How to add a new worksheet in Excel,
how to delete a worksheet in Excel, how to rename a worksheet in
Excel
- How to type data in Excel on a
worksheet and how data is really stored in Excel
- How to create formulas in Excel and
edit them
- How to design input and output ranges on the worksheet
- How to name cells in Excel and use them in formulas
- How to type a worksheet function in
Excel and debug it
- How to use Logical, Lookup, Match, Statistical, Reference and Information
worksheet functions to create "smart" engineering and science worksheets
- How to create array formulas in Excel
and manage them
- How to format worksheets (i.e. color
a cell, change a cell's font, change a cell's border...)
- How to create worksheet forms in
Excel that can later be used with VBA
- How to use Data Validation in Excel to
control what a user types on a worksheet
- How to use Conditional Formatting to
highlight engineering and science data in Excel
- How to create logic formulas in
Conditional Formatting to highlight engineering and science data in
Excel
- How to protect a worksheet or
workbook with a password
- How to perform numerical integration and differentiation on the worksheet
- How to solve a system of linear equations on the worksheet
- How to solve non-linear equations on the worksheet
- How run Runge Kutta and boundary value problems on the worksheet
- How to construct proper data tables in Excel that can be later queried using SQL and accessed using VBA
- How to use Excel's Sort, Auto Filter and Advanced Filter tools
to analyze, sort and filter worksheet data
- How to import text files into Excel
using the Text Import wizard
- How to link Microsoft Excel with Word and PowerPoint
- How to use drawing shapes to make
engineering and science diagrams on the Excel worksheet
- How to insert pictures in Excel on a
worksheet or chart and edit them
- How to layout technical diagrams in
Excel using drawing shapes and pictures
- How to link a drawing shape to a cell
value to create a diagram that updates dynamically
- How to create Excel tables from worksheet data and use their structured references in formulas
- How to link to external data in Excel
and display the data on a worksheet
- How to use slicers with your
Microsoft Excel tables to create interactive worksheet displays
- How to create and edit PivotTables on the
Excel worksheet
- How to perform statistics in Excel PivotTables
- How to use slicers and timelines with your
Excel PivotTables
- How to create PivotCharts from your PivotTables
- How to create, edit and use ActiveX and Form controls on the worksheet
- How to create an outline on a worksheet
to show and hide engineering and science information
Learning Module 4: Creating Technical Charts and
Chart Reports in Excel
- What kind of charts are available in
Excel for an engineer and scientist to use
- What is the difference between an
embedded chart and a chart sheet in Excel
- How to create a technical chart in
Microsoft Excel
- How to format a technical chart in
Excel (axis limits, series colors, data labels,...)
- How to create chart trendlines for
engineering and science charts in Excel and get the curve fit
equations
- How to create engineering and science charts
in Excel with a primary and
secondary Y-Axis
- How to create engineering and science trade study charts
in Excel
- How to create limit lines for a chart
in Excel
- How to create an xy scatter chart
in Excel from multiple
series with different x-axis values
- How to print a chart sheet in Excel
- How to layout a chart report on a worksheet and
print it
Learning Module 5: VBA Editor and Module Basics
- VBA overview, what is VBA, how is it used
- VBA project and module overview
- What is a procedure and its basic structure?
- How to use key procedure types used in VBA
- VBA statement, syntax and keyword overview
- VBA editor overview, operation and window review
- Macro protection review
- How to manage VBA modules and type in them
- How to turn off and on key VBA Editor features
- VBA procedure calling rules
- Referencing and integrating code from other VBA projects
- How to debug your code using the VBA Editor
Learning Module 6: VBA Language Review | Controlling Key Excel Elements with VBA
- VBA variables, constants and arrays (declaring, scope and data typing)
- How to create and use expressions in VBA that perform calculations
- How to use different operators in your expressions (logical, arithmetic, string, comparison...)
- How to run VBA and Excel functions in your procedures
- How to use VBA arrays in your procedures
- How to create and use logic in your procedures
- How to create and use For and Do loops in your procedures
- What is an object, a property and a method
- How to correctly build object expressions that identify and command Excel objects
- How to run properties and methods in your VBA procedures
- How to create and effectively use Set statements in your VBA code
- How to build For…Each Next loops to loop through objects scanning for items
- How to use Excel/VBA on-line help
- How to use the VBA macro recorder to record Excel engineering and science tasks in VBA
- How to use the Object Browser to look up Objects and their code
- How to provide error protection in your procedures
- Controlling Excel, Workbooks, Worksheets, Windows, Page Setup and PivotTables with VBA
- Controlling Ranges, Range Fonts, Range Borders, Range Colors with VBA
- Controlling Conditional Formatting, Data Validation, Sorting, Excel Tables and Queries with VBA
- How to lock on to changing worksheet data using VBA
- How to use VBA to control and build charts
- Basic data processing strategies for processing worksheet data using VBA
- How to build basic dashboards in Excel by automating charts, drawing shapes and worksheet cells
Learning Module 7: VBA Function Procedure Design
- Benefits of using Function procedures and User Defined Worksheet Functions (UDF's) for engineering and science tasks
- Function procedure and UDF differences
- How to declare Function procedures and UDF’s in VBA
- How to create arguments for Function procedures and UDF’s
- How to call UDF’s from a worksheet formula
- Calling Function procedures from other VBA procedures
- Function procedure and UDF procedure interface design
- How to pass worksheet ranges as arguments to UDF's
- Returning scalar and array values from UDF’s to worksheet formulas
- Using VBA and Excel Worksheet functions in your VBA procedures
Learning Module 8: VBA Sub Procedure Design
- Benefits of using Sub procedures for engineering and science tasks
- Declaring Sub procedures in VBA
- Creating arguments for Sub procedures
- Calling Sub procedures from other VBA procedures
- Executing Sub procedures via the Macro dialog box
- Executing Sub procedures via shortcut keys
- Executing Sub procedures via graphics
- How to run Sub Procedures by assigning them to drawing shapes and pictures
- How to assign Sub procedures to shapes and pictures using VBA
- How to tell what picture or shape button was clicked using VBA
- Sub procedure interface design
- Sub procedure design tips
- Property procedure overview
Learning Module 9: Using Events, ActiveX Controls, Userforms and VBA to Create Advanced Program Interfaces
- Developing engineering and science GUI's and applications in Excel
- Creating graphics that automatically display technical data on the worksheet
- Assigning VBA Sub procedures to worksheet based graphics
- Creating event procedures that run automatically when something happens to Excel, a chart, a workbook or a worksheet
- Using ActiveX and Form controls to create GUI’s on the worksheet
- Creating event procedures for controls placed on a worksheet
- Review of creating userforms for technical programs
- How to create and control userforms in Excel
- Creating controls and graphics on userforms
- Commanding userforms and their controls with VBA
- Userform design tips
- Creating toolbars from userforms to control your Excel tools
- Creating a tab and button in Excel's Ribbon
Learning Module 10:
Calling Fortran DLLs from Excel VBA
- Why use Fortran with Excel VBA
- How to call a Fortran DLL from a VBA procedure;
Declare statement and calling syntax rules
- How to design a Fortran routine to be
called from Excel VBA; compiler directives, data types, strings and
arrays
- How to pass different argument types from Excel VBA to a Fortran DLL;
data type rules, passing conventions, strings and arrays
- How to Pass Data from Fortran Back to Excel VBA
- How to compile and debug a Fortran DLL in Visual Studio
- How to pass data to/from Fortran
using text files in Excel VBA
- How to manage Fortran DLL files in
Windows
- Fortran DLL design tips; using old
Fortran code and writing new code
Learning Module 11: Team Linking in Excel
- Integrating technical teams and information using workbook links
- Creating/managing linked workbooks on a single computer and network
- Linked workbook distribution techniques
- Controlling linked workbooks with VBA
Learning Module 12: Packaging Team Tools in Excel
- Storing and distributing Excel based tools in various Excel file types
- Constructing verified, standardized engineering and scientific tools in Excel
- Packaging/distributing engineering and scientific tools in workbook files
- Engineering and scientific uses of Add-Ins and workbook templates
- Constructing/packaging/distributing engineering and scientific tools in templates
- Constructing/packaging/distributing engineering and scientific tools in Add-Ins
- Distributing/maintaining Excel engineering and scientific tools on networks
- Engineering and scientific procedure library design (i.e. rapid prototyping and analysis)
- Developing security for Excel based software
Learning Module 13: Commanding Databases and Other Applications Using VBA
- Automation overview
- How to command other applications from Excel using VBA
- How to use CreateObject to control other applications
- Using DDE with VBA
- How to use DAO to communicate with Microsoft Access
- How to use ADO to communicate with Databases
- How to use ADO to communicate with Microsoft Excel
- How to use ADO to communicate with Text Files
- How to use the FileSystemObject in VBA to control the Windows file system
- How to loop through 100's of workbooks in folders using the FileSystemObject
- How to control Window's Shell
- How to use VBA to control Microsoft Word for reporting purposes
- How to use VBA to control Microsoft PowerPoint for reporting purposes
Learning Module 14: Integrating VB.Net and VBA (Coming Soon)
Learning Module 15: Design Optimization in Excel Using Solver
- Overview of design optimization
- How to layout an optimization problem in Excel
- Command and control worksheet overview
- How to drive multiple workbooks using Solver
- How to use Solver in Microsoft Excel
- Other design optimization packages for Excel
Learning Module 16: Constructing Large System Models in Excel
- How to determine your model equations and how they interact with each other
- Using Excel’s structure to organize system teams
- Laying out the system model in Excel
- Library workbook introduction and design
- Single and multi-workbook system model layout
- Using custom VBA worksheet functions and DLL's in your model
Learning Module 17: Microsoft Excel Dashboard Design
- Excel dashboard formula design, debugging and
linking review
- Key logic formulas and functions that your Excel
dashboards will use
- Constructing, managing
and using range and cell names in your Excel dashboard
- How to create proper tables and
use Excel tables in your Excel dashboards
- How to create dynamic names that
automatically adjust to changing data and use them in various Excel
features
- Key worksheet functions and array
formulas used in Excel data processing
- Metric formula construction
techniques for Excel dashboards
- Learn about the different
dashboard types, benchmarks and KPI's
- Learn how to use ActiveX controls
and Data Validation to create Excel dashboard interfaces
- Learn how to link drawing shapes
with cells and pictures with ranges to create adaptive dashboard
displays
- Learn how to create and format key
Excel dashboard charts like sunburst, tree map, bubble and radar
- Learn how to create speedometer,
linear and doughnut gauge charts
- Learn how to create milestone
charts
- How to use Conditional Formatting
on the worksheet to build various status, project and timeline
displays
- How to create custom map displays
on the worksheet
- Learn how to use Microsoft Query,
Power Query and various OLEDB tools to automatically query and
filter external dashboard data
- Learn how to utilize PivotTables,
PivotCharts, slicers, timelines and various PivotTable
calculations to build dashboard displays
- Learn how to create custom VBA
worksheet functions that can perform amazing calculations
Learning Module 18: Using MS Query and Power Query to Query External Data
- Overview of relational databases
- Overview of ODBC and MS Query
- How to use MS Query to access a database
- How to use MS Query to query an Excel workbook
- How to use MS Query to query a text file
- How to dynamically link data on a worksheet to a database
- Overview of how to use Power Query
- How to use Power Query to query an Access Database
- How to use Power Query to query a text file
- How to use Power Query to query an Excel file
_EMAGENIT_2019.jpg)