Excel for Chemists, with CD-ROM: A Comprehensive Guide

738 Pages·2011·42.922 MB·English

by  E. Joseph Billo

Excel® for Chemists
for Chemists
Excel®
A Comprehensive Guide
Third Edition
E. Joseph Billo
WILEY
A JOHN WILEY & SONS, INC., PUBLICATION
Copyright © 2011 by John Wiley & Sons, Inc. All rights reserved.
Published by John Wiley & Sons, Inc., Hoboken, New Jersey.
Published simultaneously in Canada.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under
Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the
Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center,
Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at
www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions
Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax (201) 748-
6008, or online at http://www.wiley.com/go/permission.
Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in
preparing this book, they make no representations or warranties with respect to the accuracy or completeness of
the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a
particular purpose. No warranty may be created or extended by sales representatives or written sales materials.
The advice and strategies contained herein may not be suitable for your situation. You should consult with a
professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other
commercial damages, including but not limited to special, incidental, consequential, or other damages.
For general information on our other products and services or for technical support, please contact our Customer
Care Department within the United States at [phone], outside the United States at [phone] or fax
[phone].
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be
available in electronic formats. For more information about Wiley products, visit our web site at
www.wiley.com.
Library of Congress Cataloging-in-Publication Data:
Billo, E. Joseph.
Excel for chemists : a comprehensive guide / E. Joseph Billo. — 3rd ed.
p. cm.
Includes index.
ISBN 978-0-470-38123-6 (pbk.)
1. Electronic spreadsheets. 2. Chemistry—Data processing. 3. Microsoft Excel (Computer file) I. Title.
QD39.3.S67B55 201I
542'.85—dc22 2011010945
Printed in the United States of America.
oBook: 978-1-118-09395-5
ePDF: 978-1-118-09393-1
ePub: 978-1-118-09394-8
10 9 8 7 6 5 4 3 21
Summary of Contents
Contents vii
Preface to the Third Edition xxv
Before You Begin xxvii
PART I THE BASICS
Chapter 1 Working with Excel 2007 or Excel 2010 3
Chapter 2 Working with Excel 2003 79
Chapter 3 Excel Formulas and Functions 137
Chapter 4 Excel 2007/2010 Charts 177
Chapter 5 Excel 2003 Charts 209
PART II ADVANCED SPREADSHEET TOPICS
Chapter 6 Advanced Worksheet Formulas 233
Chapter 7 Array Formulas 267
Chapter 8 Advanced Charting Techniques 289
Chapter 9 Using Excel's Database Features 327
Chapter 10 Importing Data into Excel 349
Chapter 11 Adding Controls to a Spreadsheet 365
Chapter 12 Other Language Versions of Excel 385
PART III SPREADSHEET MATHEMATICS
Chapter 13 Mathematical Methods for Spreadsheet Calculations 403
Chapter 14 Linear Regression and Curve Fitting 435
Chapter 15 Nonlinear Regression Using the Solver 463
PART IV EXCEL'S VISUAL BASIC FOR APPLICATIONS
Chapter 16 Visual Basic for Applications: An Introduction 491
Chapter 17 Programming with VBA 503
Chapter 18 Working with Arrays in VBA 543
PART V SOME APPLICATIONS OF VBA
Chapter 19 Command Macros 557
Chapter 20 Custom Functions 571
Chapter 21 Automatic Procedures 589
Chapter 22 Custom Menus 595
Chapter 23 Custom Toolbars and Toolbuttons 607
PART VI APPENDICES
Appendix A What's Where in Excel 2007/2010 629
Appendix B Selected Worksheet Functions by Category 633
Appendix C Alphabetical List of Selected Worksheet Functions 639
Appendix D Renamed Functions in Excel 2010 661
Appendix E Selected Visual Basic Keywords by Category 663
Appendix F Alphabetical List of Selected Visual Basic Keywords 667
Appendix G Selected Excel 4 Macro Functions 689
Appendix H Shortcut Keys by Keystroke 693
Appendix I Selected Shortcut Keys by Category 703
Appendix J ASCII Codes 707
Appendix K Contents of the CD-ROM 709
INDEX 719
v
Contents
Preface to the Third Edition xxv
Before You Begin xxvii
PART I THE BASICS
Chapter 2 Working with Excel 2007 or Excel 2010
What's New in Excel 2007 and Excel 2010 3
The Ribbon 3
New File Formats 4
A Much Larger Worksheet 4
Larger Limits for Some Features 5
New Worksheet Functions 5
The Downside 5
What's New in Excel 2010 5
The Excel 2007/2010 Document Window 6
Hiding, Moving or Resizing a Document Window 7
Working with Excel 2007/2010 7
The Office Button (Excel 2007) 7
The File Tab (Excel 2010) 8
The Excel Options Window 9
The Ribbon: An Overview 10
Shortcut Menus 12
Keyboard Access to the Ribbon 12
The Home Tab 13
Navigating Around the Workbook 13
Inserting or Deleting Worksheets 13
Changing the Name of a Worksheet 15
Rearranging the Order of Sheets in a Workbook 15
Selecting Multiple Worksheets: [Group] Mode 16
Changing the Color of Sheet Tabs 17
Using Move or Copy Sheet or Delete Sheet 17
Navigating Around the Worksheet 18
Selecting a Range of Cells 18
Selecting Non-Adjacent Ranges 19
Extending a Selection 19
Selecting a Block of Cells 19
Entering Data in a Worksheet 20
Entering Numbers 21
How Excel Stores and Displays Numbers 21
vn
viii Excel for Chemists
Entering Text 22
Entering Formulas 22
Editing Cell Entries 23
The Order in Which Excel Performs Operations in Formulas 24
Adding a Text Box 24
Entering an Equation in a Text Box 25
Entering a Cell Comment 26
Opening, Closing and Saving Documents 26
Opening or Creating Workbooks 27
The List of Recently Used Files 27
Using Close or Exit 27
Using Save or Save As 27
To Save a File for Use in Excel 2003 28
Editing a Worksheet 28
Inserting or Deleting Rows or Columns 28
Hiding Rows or Columns 30
Using Cut, Copy and Paste 30
Using Paste Special 31
Using Paste Special to Transpose Rows and Columns 33
Copying and Pasting a Picture of Cells 33
Using Clear 34
Copy, Cut or Paste Using Drag-and-Drop Editing 34
Duplicating Values or Formulas in a Range of Cells 35
Absolute, Relative and Mixed References 36
Relative References When Using Copy or Cut 37
Using AutoFill to Fill Down or Fill Right 37
Using AutoFill to Create a Series 37
The AutoFill Shortcut Menu 39
Formatting Worksheets 40
Using Column Width and Row Height 40
Formatting Cells 42
The Mini Toolbar 42
The Format Cells Dialog Box 43
Using Alignment 44
Using Font 46
The Alternate Character Set 46
Entering Subscripts and Superscripts 47
Using Border and Patterns 47
Using the Format Painter Toolbutton 48
Number Formatting 48
Using the Number Formatting Toolbuttons 48
Using Excel's Built-in Number Formats 49
Custom Number Formats 51
Contents ix
Custom Date Formats 53
Time Formats 53
Variable Number Formats 54
Conditional Number Formats 54
Formatting Numbers Using "Precision as Displayed" 54
Conditional Formatting (Part I) 55
Printing Documents 59
Using Page Setup 61
Using Print 62
Printing a Selected Range of Cells in a Worksheet 63
Using Set Print Area 64
Printing Row or Column Headings for a Multi-Page Worksheet 64
Protecting Data in Worksheets 64
Protecting a Workbook 65
Protecting a Workbook by Making It a Read-Only Workbook 65
Hiding a Worksheet 65
Protecting a Worksheet by Locking or Hiding Cell Contents 67
Controlling the Way Documents Are Displayed 69
Using New Window and Arrange 69
Different Views of the Same Worksheet 71
Using Split Screens 71
Using Freeze Panes 72
Using Zoom 73
Easing the Transition from Excel 2003 to Excel 2007/2010 73
Customize the Quick Access Toolbar 74
Use Shortcut Keys 74
Display Classic Menus 74
Excel 2007/2010 Workbook and Worksheet Specifications 77
Chapter 2 Working with Excel 2003
The Excel 2003 Document Window 79
Changing What Excel Displays 80
Moving or Resizing Documents 82
Navigating Around the Workbook 82
Changing the Name of a Worksheet 83
Changing the Color of Sheet Tabs 83
Rearranging the Order of Sheets in a Workbook 83
Selecting Multiple Worksheets: [Group] Mode 84
Navigating Around the Worksheet 85
Selecting a Range of Cells on the Worksheet 85
Selecting Non-Adjacent Ranges 86
Extending a Selection 86
Selecting a Block of Cells 86
x Excel for Chemists
Entering Data in a Worksheet 87
Entering Numbers 88
How Excel Stores and Displays Numbers 88
Entering Text 89
Entering Formulas 89
Editing Cell Entries 91
Adding a Text Box 91
;
Entering a Cell Comment 92
Using the Equation Editor 93
Excel's Menus: An Overview 94
Shortcut Menus 95
Menu Commands or Toolbuttons? 95
Opening, Closing and Saving Documents ...95
Opening or Creating Workbooks 95
Using Move or Copy Sheet... or Delete Sheet 96
Using Close or Exit 96
Types of Excel Document 97
Using Save or Save As 97
Using Save Workspace 97
Editing a Worksheet 98
Inserting or Deleting Rows or Columns 98
Using Cut, Copy and Paste 99
Copying and Pasting Multiple Items 99
Using Paste Special 100
Using Paste Special to Transpose Rows and Columns 101
Using Clear 101
Using the Insert Menu 101
Copy, Cut or Paste Using Drag-and-Drop Editing 102
Duplicating Values or Formulas in a Range of Cells 103
Absolute, Relative and Mixed References 104
Relative References When Using Copy and Cut 105
Using AutoFill to Fill Down or Fill Right 105
Creating a Series 105
Using AutoFill to Create a Series 106
The AutoFill Shortcut Menu 107
Formatting Worksheets 108
Using Column Width... and Row Height 108
Formatting Cells 108
Using Alignment 109
Using Font Ill
The Alternate Character Set 111
Entering Subscripts and Superscripts 112
Contents xi
Using Border and Patterns 112
Using the Format Painter Toolbutton 113
Number Formatting 113
Using Excel's Built-in Number Formats 113
Custom Number Formats 114
Date Formats 116
Time Formats : 117
Variable Number Formats 117
Conditional Number Formats 118
Using the Number Formatting Toolbuttons 118
Formatting Numbers Using "Precision as Displayed" 118
Using Conditional Formatting (Part I) 119
Printing Documents 120
Using Page Setup 120
Using Print Preview 123
Using Print 123
Printing a Selected Range of Cells in a Worksheet 124
Printing Row or Column Headings for a Multi-Page Worksheet 125
Protecting Data in Worksheets 125
Protecting a Workbook 125
Protecting a Workbook by Making it a Read-Only Workbook 126
Hiding a Worksheet 126
Protecting a Worksheet by Locking or Hiding Cell Contents 127
Controlling the Way Documents Are Displayed 130
Viewing Several Worksheets at the Same Time 130
Using New Window and Arrange 130
Different Views of the Same Worksheet 131
Using New Window 132
Using Split Screens 132
Using Freeze Panes 133
Opening Excel 2007/2010 Documents in Excel 2003 134
Excel 2003 Workbook and Worksheet Specifications 135
Chapter 3 Excel Formulas and Functions
The Elements of a Worksheet Formula 137
Constants 137
Operators 137
References: Absolute, Relative and Mixed 138
R1C1 Reference Style 139
Creating and Using External References 140
Creating an External Reference by Selecting 140
Creating an External Reference by Using Paste Link 140
The External Reference Contains the Complete Directory Path 141
xii Excel for Chemists
Updating References and Re-Establishing Links 141
Creating and Using 3-D References 142
Worksheet Functions: An Overview 143
Function Arguments 143
Nested Functions 144
New Functions Introduced in Excel 2007 144
Changes to Functions in Excel 2010 144
Using Insert Function 145
A Shortcut to a Function 147
Using Formula AutoComplete (Excel 2007/2010 Only) 147
Math and Trigonometric Functions 149
Trigonometric Functions 150
Functions for Working with Matrices 151
Statistical Functions 151
Logical Functions 152
The IF Function 153
Nested IF Functions 155
AND, OR and NOT 155
Using IFERROR (Excel 2007/2010 Only) 155
Using COUNTIF 156
Using SUMIF 156
Using AVERAGEIF (Excel 2007/2010 Only) 157
Using COUNTIFS, SUMIFS and AVERAGEIFS (Excel 2007/2010 Only).. 157
Using Conditional Formatting (Part II) 159
Date and Time Functions 161
How Excel Keeps Track of the Date and Time 161
Date and Time Arithmetic 162
Creating Date Series 162
Using Date and Time Functions 163
Examples of Date and Time Formulas 164
Text Functions 165
The LEN, LEFT, RIGHT and MID Functions 166
The UPPER, LOWER and PROPER Functions 166
The FIND, SEARCH, REPLACE, SUBSTITUTE and EXACT Functions 166
The FIXED and TEXT Functions 169
The VALUE Function 169
The CODE and CHAR Functions 169
Lookup and Reference Functions 170
Information Functions 171
Creating "Megaformulas" 172
Advantages and Disadvantages of Megaformulas 173
The Order in Which Excel Performs Operations in Formulas 174
Formula and Function Specifications 175