What Every Engineer Should Know About Excel

Understanding the powerful computational and graphics capabilities of Microsoft Excel is an enormous benefit to engineers and technical professionals in almost any field and at all levels of experience. What Every Engineer Should Know About Excel is a practical guide to unlocking the features and functions of this program, using examples and screenshots to walk readers through the steps to build a strong understanding of the material. This second edition is updated to reflect the latest version of Excel (2016) and expands its scope to include data management, connectivity to external data sources, and integration with "the cloud" for optimal use of the Excel product. It also introduces the ribbon bar navigation prevalent in Microsoft products beginning with the 2007 version of MS Office. Covering a variety of topics in self-contained chapters, this handy guide will also prove useful for professionals in IT, finance, and real estate.

Chapter 1: Introduction Getting the Most from Microsoft Excel Conventions Introduction to the Microsoft Office Ribbon Bar Outline of Contents Chapter 2: Miscellaneous Operations in Excel and Word Introduction Generating a Screen Shot Custom Keyboard Setup for Symbols in Word or Excel Viewing or Printing Column and Row Headings and Gridlines in Excel Miscellaneous Useful Tips and Shortcuts Moving Objects in Small Increments (Nudging) Formatting Objects in Word, Including Wrapping Formatting Objects in Excel Copying Formulas by Dragging the Fill Handle Copying Cell Formulas: Effect of Relative and Absolute Addresses Shortcut for Changing the Status of Cell Addresses Switching and Copying Columns or Rows, and Changing Rows to Columns or Columns to Rows Built-In Functions in Excel Creating Single-Variable Tables Create Two-Variable Tables Problems Chapter 3: Charts and Graphs Introduction Moving Dialog Windows Excel Choices of x-y Scatter Charts Selecting and Adding Data for x-y Scatter Charts Changing / Replacing Data for Charts Adding Data to Charts Adding Trend Lines and Correlation Equations to Scatter Charts Equation for R2 Correlation of Experimental Data with Power Relation Use of Logarithmic Scales Correlation with Exponential Functions Use of Different Scatter Graphs for the Same Data Plot of a Function of Two Variables with Different Chart Types Plots of Two Variables with and without Separate Scales Charts Used for Calculation Purposes or G&A Format Stretching out a Chart Calculation and Graphing of Moving Averages Bar and Column Charts Chart Format and Cosmetics Surface Charts An Exercise in 3-D Visualization Problems Chapter 4: Line Drawings, Embedded Objects, Equations and Symbols in Excel Introduction Constructing, Moving and Inserting Straight Line Drawings Drawing Line Segments in Precise Angular Increments Inserting Equation Templates and Symbolsusing Excel and Word Symbol Insertion Equation Template Insertion Inserting Equations and Symbols in Excel using Equation Editor Construction of Line Drawings from Plotted Coordinates Problems Chapter 5: Solution of Equations Introduction Solutions to Nonlinear Equations Using Goal Seek Solutions to Nonlinear Equations Using Solver Iterative Solutions to Simultaneous Linear Equations Solutions of Simultaneous Linear Equations Using Matrix Inversion Error Messages Solutions of Simultaneous Nonlinear Equations Using Solver Solver Results Dialog Box Comparison of Methods for Solution of Simultaneous Linear Equations Copying Cell Equations for Repetitive Calculations Creating and Running Macros Problems Chapter 6: Other Operations Introduction Numerical Evaluation of Integrals Use of Logical IF Statement Histograms and Cumulative Frequency Distributions Normal Error Distributions Calculation of Uncertainty Propagation in Experimental Results Fractional Uncertainties for Product Functions of Primary Variables Multivariable Linear Regression Multivariable Exponential Regression Problems Chapter 7: Financial Functions and Calculations Introduction Nomenclature Compound Interest Formulas Investment Accumulation with Increasing Annual Payments Payout at Variable Rates from an Initial Investment Problems Chapter 8: Optimization Problems Introduction Graphical Examples of Linear and Nonlinear Optimization Problems Solutions Using Solver Solver Answer Reports for Examples Nomenclature for Sensitivity Reports Nomenclature for Answer Reports Nomenclature for Limits Reports Problems Chapter 9: Pivot Tables Introduction Other Summary Functions for Data Fields Restrictions on Pivot Table Formulas Calculating and Charting Single or Multiple Functions ?(x) vs. x Using Pivot Tables Working around Charting Limitations of Excel 2016 – Scatter Charts Calculation and Plotting Functions of Two Variables Problems Chapter 10: Data Management Resources in Excel Introduction Organizing Data in Excel Worksheets and Tables Filtering, Sorting and Use of Subtotals Filtering Sorting Subtotals Useful Data Functions in Excel Connecting Excel to External Data General Concepts Connecting to MS Access Connecting to MS SQL Server Connecting to MySQL Connecting to Other Data Sources Microsoft Power Query Problems Chapter 11: Office 365 and Integration with Cloud Resources Introduction What is Office 365 Leveraging Excel on Premise and in the Cloud Integrating Excel with Cloud Resources Microsoft Azure Amazon Web Services Excel and Microsoft Power BI References Index

Blake Holman is Chief Information Officer at St. David’s Foundation in Austin, TX. Blake joined the Foundation in 2015 and is responsible for strategic and operational Information Technology (IT) activities, enhancing the Foundation’s ability to be efficient and effective in fulfilling its mission. Prior to St. David’s Foundation, Blake spent 10 years as the Chief Information Officer of Ryan, LLC, the largest independent Tax Consulting Firm in the world. Blake has over 25 years of experience leading Information Technology Strategy, Development and Operations for several public and private companies in the Consulting, Telecommunications and Financial Services industries. Blake holds a Bachelor of Science degree in Mechanical Engineering from Southern Methodist University, and a Strategic IT Management Certificate from the Scandinavian International Management Institute in Copenhagen, Denmark. Blake is currently working on a Master of Science degree in Information Security and Assurance at Western Governor’s University. In 2011 and 2012, under Blake's leadership, Ryan, LLC was ranked in the InformationWeek 500 listing of the most innovative business technology companies in the United States. In 2011, Ryan was ranked 130th, and in 2012, Ryan's ranking rose to 98th. In both cases, Ryan was the highest ranked corporate tax services firm on the list. In December 2012, Blake was named by Computerworld magazine as one of its 2013 "Premier 100 IT Leaders." The Computerworld Premier 100 IT recognition is an international lifetime award that shines a spotlight on technology.