What Every Engineer Should Know About Excel

J. P. Holman, Blake K. Holman

• Publisher: CRC Press
• Year: 2017
• ISBN:
  9781351976473
• Edition: 2
• Topic: Technology & Engineering
• Subtopic: Engineering General
• Index: Technology & Engineering

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.

1 Introduction

1.1 Getting the Most from Microsoft Excel

Microsoft Excel is a deceptive software package in that it offers computation and graphics capabilities far beyond what one would expect in a spreadsheet tool. Its capabilities remain unknown to many engineers and technical persons, although more engineers are adopting its use. This book is written for the person who is casually familiar with Excel but is unaware of its broad potential. Although a novice will find the material useful, it will be most attractive to those who have the following:

1. A basic knowledge of both Excel and Microsoft Word, including procedures for entering equations in Excel, editing fundamentals, and some experience with creating graphs
2. A basic knowledge of differential and integral calculus
3. For some sections, a familiarity with solution techniques for single and simultaneous equations
4. For some sections, a familiarity with basic statistics, including the concepts of standard deviation and probability

Many of the sections in this book resulted from small instructional sets that were written as stand-alone packages for engineering students enrolled in a mechanical engineering curriculum. In addition, some of the sets and example problems are related to applications in the thermal and fluids sciences areas of mechanical engineering. Although these application examples are retained, they are presented as part of more general procedures that are applicable to other engineering and technical disciplines.

Unless a person works with a software package such as Excel on a continual basis, it is easy to forget some of the shortcuts and nuances of operation that accomplish calculation or presentation objectives, namely procedures for viewing all equations on a worksheet, inserting symbols in equations, etc. Such hints are presented in compact form for the reader’s convenience.

The title of this book refers to Excel, but the reader will find several applications that call for a combination of features of Word in conjunction with the capabilities of Excel. Microsoft PowerPoint is also a powerful tool for presentations but is not covered in this book.

The Help features of both Excel and Word are of obvious practical utility in working with the software. When appropriate, the reader’s attention is directed to the Help facility for further information. Many books written on Excel and many specialized references pertain to particular engineering examples. A list of all references for this book is given in the appendix, and callouts to this list are made at appropriate times in the book. Separate reference lists are not provided at the conclusion of each chapter.

Many worked examples are presented throughout the book. For the reader’s convenience, each example is given a title. In some cases, the example title also specifies the calculation principle or technique that is being demonstrated. The book makes extensive use of graphs and figures, as well as printouts of specific spreadsheet segments employed in the examples. Screenshots that show worksheet and dialog window contents in perspective are also displayed.

The reader will find that many sections in the book can be used independently. This stand-alone nature results from the manner in which many of the topics were initially generated, as well as from an expectation that many readers want information in a compact self-contained form without having to move back and forth from section to section. To further achieve a compact presentation, explanatory notes are sometimes displayed as embedded text on the pertinent worksheet. When a topic relates to other sections, appropriate notes and references are given.

1.2 Conventions

As described earlier, many of the presentations are in a compact form, which allows for more rapid or convenient use. All references to Excel or Word in this book refer to the 2016 versions of these products.

When specifying a procedure that consists of a sequence of menu or ribbon bar operations, we will use the following convention

VIEW/SHOW/Gridlines

instead of the more cumbersome set of instructions:

1. Click the View tab of the ribbon bar
2. Go to the Show section of the ribbon
3. Click Gridlines

Another example in Excel is

INSERT/ILLUSTRATIONS/Pictures

which is equivalent to the following:

1. Click on the Insert tab of the ribbon bar
2. Go to the Illustrations section of the ribbon
3. Click on Pictures

Embedding of text boxes and descriptive statements in the example Excel worksheets is freely employed to express the instructions in a compact form. In many cases, this results in a font size smaller than that in the main body of the text, but is usually not objectionable.

1.3 Introduction to the Microsoft Office Ribbon Bar

Beginning with Office 2007, released in 2006, Microsoft introduced the ribbon bar for navigation within each Office application. In addition to being a new user interface, Microsoft introduced a level of consistency across the Office applications that was not previously seen.

The ribbon bar occupies a material amount of space on the top of the screen, but affords the user the opportunity to have many useful options available at the click of a button. The ribbon bar is organized into tabs, and each tab has several sections that present the various choices available under that tab. To insert a picture into a document, whether it be Word or Excel, the user must only click on the Insert tab of the ribbon bar, go to the Illustrations section of the ribbon and click on Pictures to accomplish their task. As noted in Section 1.2, we would note this operation as INSERT/ILLUSTRATIONS/Pictures.

If the reader has been using Office versions later than 2007, the ribbon bar should be familiar. For those who might still be using Office 2003 or earlier, they are encouraged to use an Internet search engine to obtain a more extensive introduction to the Office ribbon bar that allows them to navigate within the Microsoft Office applications.

1.4 Outline of Contents

Chapter 2 presents a potpourri of miscellaneous topics in Word and Excel that are applicable to the other chapters. Chapter 3 describes several graphing techniques that may be employed in engineering applications. Chapter 4 discusses the use of line drawings and other graphics in Word and Excel. Chapter 5 presents a variety of Excel techniques for solving single and multiple linear or nonlinear equations, along with numerical examples of each technique. Chapter 6 presents other numerical applications, including histograms and multivariable regression analysis, whereas Chapter 7 is devoted to the discussion and use of financial functions built into Excel. Chapter 8 presents optimization techniques that may be exploited with Excel Solver. Chapter 9 presents some basic, but very useful, operations with pivot tables. Chapter 10 presents techniques for interfacing Excel with external data sources and using such data in various operations. Finally, Chapter 11 presents an introduction to the use of Excel in “the cloud” as well as integration with other cloud applications that the engineer or technology professional might find useful.

2 Miscellaneous Operations in Excel and Word

2.1 Introduction

This chapter contains a collection of useful operations for editing or formatting, or simply a shortcut to doing something. The reader should take particular note of Section 2.3, which offers detailed suggestions for customizing the keyboard for direct typing of math and Greek symbols. Using these shortcuts will greatly simplify typing many equations and mathematical expressions, particularly if typing symbols and equations is a frequent task. For those that require significant entry of equations, Excel and Word have advanced equation-editing capabilities easily accessible from the Insert tab of the ribbon bar. Section 4.3 provides more in-depth explanation of this capability.

Some of the formatting and editing instructions are repeated from time to time when they are needed in a particular example or discussion.

2.2 Generating a Screenshot

It is often very useful to generate a picture of the contents (or a portion of the contents) of the computer screen at a particular moment in time. This can involve contents of the entire screen, a single window on the screen, or a subset of a window (or windows) on the screen.

Microsoft operating systems continue to support full screen capture and current window capture via the Print Screen and ALT+Print Screen key sequences. Using the Screen Clipping capability in Excel or Word, however, the user can get a full screen capture, a single window capture, or a large or small portion of the screen of the user’s choice.

Before beginning the screen capture process, have a target document open and ready for insertion of the screen...

Table of contents

• Cover
• Halftitle Page
• Title Page
• Copyright Page
• Contents
• Preface to the First Edition
• Preface to the Second Edition
• About the Authors
• 1. Introduction
• 2. Miscellaneous Operations in Excel and Word
• 3. Charts and Graphs
• 4. Line Drawings, Embedded Objects, Equations, and Symbols in Excel
• 5. Solution of Equations
• 6. Other Operations
• 7. Financial Functions and Calculations
• 8. Optimization Problems
• 9. Pivot Tables
• 10. Data Management Resources in Excel
• 11. Office 365 and Integration with Cloud Resources
• References
• Index