2nd Edition

What Every Engineer Should Know About Excel

By J. P. Holman, Blake K. Holman Copyright 2018
    247 Pages 203 B/W Illustrations
    by CRC Press

    192 Pages
    by CRC Press

    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

    Biography

    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.

    "What Every Engineer Should Know About Excel is a valuable reference material that can be readily utilized by Engineers, Financial Comptrollers, Investors and Technology professionals in all industries. This second edition to Dr. Holman’s original collection of materials allows today’s Excel users to sharpen their current Excel skills, and take them to the next level, even integrating with cloud resources."
    —David A. Schmidt, SCHMIDT & STACY® Consulting Engineers, Inc., Dallas, Texas, USA

    "This book unleashes the incredible power Excel offers for engineers and scientists. Reviewing the book, I found myself simply browsing the many features of Excel that I had previously not explored. Every engineer and scientist will benefit from this book at some level and I only regret that I didn’t have access to a guide such as this earlier in my career. This book is extraordinarily well-organized. Any topic of interest can be quickly and easily accessed simply by referring to the Table of Contents. Once located, the topic is clearly and concisely presented with helpful illustrations and examples."
    —Stephen M. Collard, St. David's Foundation, Austin, Texas, USA

    "What Every Engineer Should Know About Excel is a timely, practical, thorough, and well-written book. It provides clear step-by-step directions on how to utilize Excel to solve simple and complex engineering-related tasks. The book displays excellent insight into the ‘guts’ of Excel’s presuppositions (i.e., how Excel defines terms and formulas) and its powerful built-in functions. It demonstrates concepts in such a manner that a reader is enticed to explore Excel’s virtues. The book serves the needs of both the novice and the expert. It can be used as either a university textbook or as a practical reference guide."
    —Mark A. Stone, Business Associate, Bryan, Texas, USA

    "What Every Engineer Should Know About Excel is a book that every engineer will find valuable for his or her work. The book begins with an overview of commonly used spreadsheet functions in Excel, including writing and copying formulas, built-in functions, and creating visually appealing charts, graphs, and drawings. Advanced calculation methods are clearly described, with explanations of how to solve nonlinear problems, iterative solution methods, matrix methods, and optimization problems. Practicing engineers will also find the overview of financial functions valuable for engineering financial analysis. The second edition includes new chapters on data management and integrating Excel with the Cloud. The book presents material in a practical step-by-step approach using examples from engineering practice."
    —David A. Willis, Southern Methodist University, Dallas, Texas, USA