What Every Engineer Should Know About Excel

248 Pages·2017·48.52 MB·English

by  J. P. Holman

What Every Engineer Should
Know About Excel
What Every Engineer Should Know: A Series
Series Editor*
Phillip A. Laplante
Pennsylvania State University
1. What Every Engineer Should Know about Patents
William G. Konold, Bruce Tittel, Donald F. Frei, and David S. Stallard
2. What Every Engineer Should Know about Product Liability
James F. Thorpe and William H. Middendorf
3. What Every Engineer Should Know about Microcomputers: Hardware/
Software Design, A Step-by-Step Example
William S. Bennett and Carl F. Evert, Jr.
4. What Every Engineer Should Know about Economic Decision Analysis
Dean S. Shupe
5. What Every Engineer Should Know about Human Resources Management
Desmond D. Martin and Richard L. Shell
6. What Every Engineer Should Know about Manufacturing Cost Estimating
Eric M. Malstrom
7. What Every Engineer Should Know about Inventing
William H. Middendorf
8. What Every Engineer Should Know about Technology Transfer and Innovation
Louis N. Mogavero and Robert S. Shane
9. What Every Engineer Should Know about Project Management
Arnold M. Ruskin and W. Eugene Estes
10. What Every Engineer Should Know about Computer-Aided Design and
Computer-Aided Manufacturing: The CAD/CAM Revolution
John K. Krouse
11. What Every Engineer Should Know about Robots
Maurice I. Zeldman
12. What Every Engineer Should Know about Microcomputer Systems Design
and Debugging
Bill Wray and Bill Crawford
13. What Every Engineer Should Know about Engineering Information Resources
Margaret T. Schenk and James K. Webster
14. What Every Engineer Should Know about Microcomputer Program Design
Keith R. Wehmeyer
* Founding Series Editor: William H. Middendorf.
15. What Every Engineer Should Know about Computer Modeling and Simulation
Don M. Ingels
16. What Every Engineer Should Know about Engineering Workstations
Justin E. Harlow III
17. What Every Engineer Should Know about Practical CAD/CAM Applications
John Stark
18. What Every Engineer Should Know about Threaded Fasteners: Materials and
Design
Alexander Blake
19. What Every Engineer Should Know about Data Communications
Carl Stephen Clifton
20. What Every Engineer Should Know about Material and Component Failure,
Failure Analysis, and Litigation
Lawrence E. Murr
21. What Every Engineer Should Know about Corrosion
Philip Schweitzer
22. What Every Engineer Should Know about Lasers
D. C. Winburn
23. What Every Engineer Should Know about Finite Element Analysis
John R. Brauer
24. What Every Engineer Should Know about Patents: Second Edition
William G. Konold, Bruce Tittel, Donald F. Frei, and David S. Stallard
25. What Every Engineer Should Know about Electronic Communications Systems
L. R. McKay
26. What Every Engineer Should Know about Quality Control
Thomas Pyzdek
27. What Every Engineer Should Know about Microcomputers: Hardware/
Software Design, A Step-by-Step Example, Second Edition, Revised and
Expanded
William S. Bennett, Carl F. Evert, and Leslie C. Lander
28. What Every Engineer Should Know about Ceramics
Solomon Musikant
29. What Every Engineer Should Know about Developing Plastics Products
Bruce C. Wendle
30. What Every Engineer Should Know about Reliability and Risk Analysis
M. Modarres
31. What Every Engineer Should Know about Finite Element Analysis: Second
Edition, Revised and Expanded
John R. Brauer
32. What Every Engineer Should Know about Accounting and Finance
Jae K. Shim and Norman Henteleff
33. What Every Engineer Should Know about Project Management: Second
Edition, Revised and Expanded
Arnold M. Ruskin and W. Eugene Estes
34. What Every Engineer Should Know about Concurrent Engineering
Thomas A. Salomone
35. What Every Engineer Should Know about Ethics
Kenneth K. Humphreys
36. What Every Engineer Should Know about Risk Engineering and Management
John X. Wang and Marvin L. Roush
37. What Every Engineer Should Know about Decision Making Under
Uncertainty
John X. Wang
38. What Every Engineer Should Know about Computational Techniques of Finite
Element Analysis
Louis Komzsik
39. What Every Engineer Should Know about Excel
Jack P. Holman
40. What Every Engineer Should Know about Software Engineering
Phillip A. Laplante
41. What Every Engineer Should Know about Developing Real-Time Embedded
Products
Kim R. Fowler
42. What Every Engineer Should Know about Business Communication
John X. Wang
43. What Every Engineer Should Know about Career Management
Mike Ficco
44. What Every Engineer Should Know about Starting a High-Tech Business
Venture
Eric Koester
45. What Every Engineer Should Know about MATLAB® and Simulink®
Adrian B. Biran with contributions by Moshe Breiner
46. Green Entrepreneur Handbook: The Guide to Building and Growing a Green
and Clean Business
Eric Koester
47. Technical Writing: A Practical Guide for Engineers and Scientists
Phillip A. Laplante
48. What Every Engineer Should Know about Cyber Security and Digital
Forensics
Joanna F. DeFranco
49. What Every Engineer Should Know about Modeling and Simulation
Raymond Joseph Madachy
50. What Every Engineer Should Know about Excel, Second Edition
Jack P. Holman and Blake K. Holman

What Every Engineer Should
Know About Excel
Second Edition
Jack P. Holman and Blake K. Holman
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2018 by Taylor & Francis Group, LLC
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Printed on acid-free paper
International Standard Book Number-13: 978-1-138-30614-1 (Hardback)
International Standard Book Number-13: 978-1-138-03530-0 (Paperback)
This book contains information obtained from authentic and highly regarded sources. Reasonable efforts have been
made to publish reliable data and information, but the author and publisher cannot assume responsibility for the
validity of all materials or the consequences of their use. The authors and publishers have attempted to trace the copy-
right holders of all material reproduced in this publication and apologize to copyright holders if permission to publish
in this form has not been obtained. If any copyright material has not been acknowledged, please write and let us know
so we may rectify in any future reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or
utilized in any form by any electronic, mechanical, or other means, now known or hereafter invented, including pho-
tocopying, microfilming, and recording, or in any information storage or retrieval system, without written permission
from the publishers.
For permission to photocopy or use material electronically from this work, please access www.copyright.com (http://
www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA
01923, 978-750-8400. CCC is a not-for-profit organization that provides licenses and registration for a variety of users.
For organizations that have been granted a photocopy license by the CCC, a separate system of payment has been
arranged.
Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are used only for
identification and explanation without intent to infringe.
Library of Congress Cataloging-in-Publication Data
Names: Holman, J. P. (Jack Philip), author.
Title: What every engineer should know about Excel / J.P. Holman and Blake K.
Holman.
Description: Second edition. | Boca Raton : Taylor & Francis, CRC Press,
2018. | Series: What every engineer should know series | Includes
bibliographical references and index.
Identifiers: LCCN 2017035509 | ISBN 9781138035300 (pbk.) | ISBN 9781138306141
(hardback) | ISBN 9781315268583 (ebook)
Subjects: LCSH: Microsoft Excel (Computer file) | Engineering--Computer
programs.
Classification: LCC TA345 .H65 2018 | DDC 005.54--dc23
LC record available at https://lccn.loc.gov/2017035509
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
Contents
Preface to the First Edition .........................................................................................................xiii
Preface to the Second Edition ......................................................................................................xv
About the Authors .....................................................................................................................xvii
1. Introduction .............................................................................................................................1
1.1 Getting the Most from Microsoft Excel .....................................................................1
1.2 Conventions ...................................................................................................................2
1.3 Introduction to the Microsoft Office Ribbon Bar .....................................................3
1.4 Outline of Contents .......................................................................................................3
2. Miscellaneous Operations in Excel and Word .................................................................5
2.1 Introduction ...................................................................................................................5
2.2 Generating a Screenshot ..............................................................................................5
2.3 Custom Keyboard Setup for Symbols in Word or Excel .........................................6
2.4 Viewing or Printing Column and Row Headings and Gridlines in Excel ...........7
2.5 Miscellaneous Useful Tips and Shortcuts .................................................................7
2.6 Moving Objects in Small Increments (Nudging) ...................................................10
2.7 Formatting Objects in Word, Including Wrapping ................................................10
2.8 Formatting Objects in Excel ......................................................................................10
2.9 Copying Formulas by Dragging the Fill Handle ...................................................12
2.10 Copying Cell Formulas: Effect of Relative and Absolute Addresses ..................13
2.11 Shortcut for Changing the Status of Cell Addresses .............................................15
2.12 Switching and Copying Columns or Rows, and Changing Rows to
Columns or Columns to Rows ..................................................................................16
2.13 Built-In Functions in Excel .........................................................................................17
2.14 Creating Single-Variable Tables ................................................................................17
2.15 Creating Two-Variable Tables ....................................................................................20
Problems .......................................................................................................................24
3. Charts and Graphs ...............................................................................................................27
3.1 Introduction .................................................................................................................27
3.2 Moving Dialog Windows ...........................................................................................27
3.3 Excel Choices of x–y Scatter Charts ..........................................................................28
3.4 Selecting and Adding Data for x–y Scatter Charts ................................................29
3.5 Changing/Replacing Data for Charts ......................................................................30
3.6 Adding Data to Charts ...............................................................................................31
3.7 Adding Trend Lines and Correlation Equations to Scatter Charts .....................32
3.8 Equation for R2 ............................................................................................................33
3.9 Correlation of Experimental Data with Power Relation ........................................33
3.10 Use of Logarithmic Scales ..........................................................................................36
3.11 Correlation with Exponential Functions .................................................................37
3.12 Use of Different Scatter Graphs for the Same Data ................................................38
3.12.1 Observations ...................................................................................................39
3.13 Plot of a Function of Two Variables with Different Chart Types .........................40
ix