Title Excel Workbook for Dummies (ISBN - 0471798452) For Dummies English 7.3 MB 370
```                            Excel Workbook For Dummies
Dedication
Author’s Acknowledgments
Contents at a Glance
Introduction
Conventions Used in This Book
Foolish Assumptions
How This Book Is Organized
Using the Practice Material on the CD-ROM
Icons Used in This Book
Where to Go from Here
Chapter 1: Entering the Spreadsheet Data
Launching Excel
Opening a New Workbook
Moving around the Workbook
Selecting Cell Ranges
Making Cell Entries
Saving the Spreadsheet in a Workbook File
Resizing Columns and Rows
Cell Formatting Techniques
Hiding Columns and Rows
Previewing the Printed Report
Adding Print Titles to a Report
Modifying the Print Setting for a Report
Printing All or Part of the Workbook
Finding and Opening the Workbook for Editing
Finding and Identifying the Region that Needs Editing
Selecting the Ranges to Edit
Editing Data Entries
Catching Errors with Text to Speech
Deleting and Inserting Data and Cells
Moving and Copying Data and Cells
Using Find and Replace and Spell-Checking
Group Editing
Part II: Using Formulas and Functions
Chapter 5: Building Formulas
Building Formulas
Controlling When Formulas Are Recalculated
Chapter 6: Copying and Correcting Formulas
Copying Formulas with Relative References
Copying Formulas with Absolute References
Copying Formulas with Mixed References
Using Range Names in Formulas
Building Array Formulas
Tracing and Eliminating Formula Errors
Dealing with Circular References
Chapter 7: Creating Date and Time Formulas
Constructing Date and Time Formulas
Working with the Date Functions
Working with the Time Functions
Chapter 8: Financial Formulas and Functions
Working with Financial Functions
Using the Basic Investment Functions
Figuring the Depreciation of an Asset
Chapter 9: Using Math Functions
Rounding Off Values
Finding Products, Powers, and Square Roots
Doing Fancier Sums
Chapter 10: Using Statistical Functions
Computing Averages
Finding the Highest and Lowest Values
Counting Cells
Using the Statistical Functions in the Analysis ToolPak Add-in
Chapter 11: Using the Lookup Functions
Returning Single Values from a Lookup Table
Using the Lookup Wizard
Chapter 12: Using the Logical Functions
Working with the Logical Functions
Constructing Decision-Making Formulas
Constructing Error-Trapping Formulas
Chapter 13: Text Formulas and Functions
Constructing Text Formulas
Using Text Functions
Part III: Working with Graphics
Understanding Excel Charts
Creating Charts
Formatting Charts
Editing Charts
Understanding Graphic Objects
Using the Drawing Toolbar
Part IV: Managing and Securing Data
Chapter 16: Building and Maintaining Data Lists
Creating a Data List
Sorting Lists
Subtotaling a List
Filtering a List
Querying External Database Tables
Protecting the Worksheet
Doing Data Entry in a Protected Worksheet
Protecting the Entire Workbook
Part V: Doing Data Analysis
Chapter 18: Performing What-If Analysis
Using Data Tables
Exploring Various Scenarios
Performing Goal Seeking
Creating Complex Models with Solver
Chapter 19: Generating Pivot Tables
Understanding Pivot Tables
Creating Pivot Tables
Modifying the Pivot Table
Creating Pivot Charts
Part VI: Excel and the Web
Chapter 20: Publishing Spreadsheets as Web Pages
Saving Worksheets as Web Pages
Doing a Web Query
Part VII: Macros and Visual Basic for Applications
Chapter 22: Using Macros
Creating Macros
Assigning Macros to Toolbars and Menus
Chapter 23: Using the Visual Basic Editor
Using the Visual Basic Editor
Creating User-Defined Functions
Part VIII: The Part of Tens
Chapter 24: Top Ten Tips for Using Excel like a Pro
Generate New Workbooks from Templates
Organize Spreadsheet Data on Different Worksheets
Create Data Series with AutoFill
Use Range Names
Prevent Data Entry Errors with Data Validation
Trap Error Values in Their Original Formulas
Save Memory by Using Array Formulas
Controlling the Display of Data in Tables through Outlines
Use Compare Side by Side to Work with Two Workbooks
Chapter 25: Ten (More or Less) Shortcut Keys for Entering Data
Chapter 26: Ten (More or Less) Shortcut Keys for Formatting the Worksheet
Chapter 27: Ten (More or Less) Shortcut Keys for Editing Data
Chapter 28: Ten (More or Less) Miscellaneous Shortcut Keys
System Requirements
Using the CD
What You’ll Find on the CD
Troubleshooting
Customer Care
Appendix B: Table of Exercises
Index
```
##### Document Text Contents
Page 1

by Greg Harvey, PhD

Excel®

Workbook
FOR

DUMmIES

01_798452 ffirs.qxp 3/14/06 10:33 AM Page i

File Attachment
C1.jpg

Page 2

01_798452 ffirs.qxp 3/14/06 10:33 AM Page iv

Page 185

Part III
Working with Graphics

19_798452 pt03.qxp 3/13/06 7:48 PM Page 169

Page 186

In this part . . .

Part III is living proof that there is indeed life beyondnumber-crunching in the Excel spreadsheet. Here,
you have an opportunity to tap into your more “artistic”
side as you get practice in creating and gussying up charts
of various types as well as experience in adding and using

19_798452 pt03.qxp 3/13/06 7:48 PM Page 170

Page 369

• U •
unattached text, 173
underline button, 26
Undo button, 57
undocking a floating toolbar, 65
union operator (,), 75
unmerge a super-cell, 31
UPPER function, 164–167

capturing for a Web query, 270
as a destination for a hyperlink, 274

user-defined function, creating a, 300–305.

• V •
#VALUE! error value, 73
values

alternate, 151–153
replacing in a cell, 53
rounding off, 125–126

VAR function, 253
VARP function, 253
VBA (Visual Basic for Applications), 293
VBE (Visual Basic Editor)

constructing a custom function in the, 301–302
editing a recorded macro in the, 296–297
overview, 293–295
Standard toolbar in the, 295

(vertical lookup)

building formulas that perform a, 145–146
performing a, 144–146

vertical scroll bar, 12–13
Visual Basic Editor. See VBE (Visual Basic Editor)
Visual Basic toolbar, 294

vertical lookup
arguments of the, 142
overview, 141–142

voice text entry, 16

• W •
WARNING icon, 6
Web data imported into Excel with a Web query,

271–272
Web page

creating an interactive, 263–264
creating with an interactive chart, 268–269

creating with an interactive data list, 266–267
creating with an interactive data table, 265–266
creating with interactive pivot tables, 267–268
creating a static, 262–263
exporting to Excel, 269–270
saving a spreadsheet as a static, 262–263
saving worksheets as a, 261

Web query
capturing URL address for a, 270
importing web data into Excel with a, 271–272
overview, 270

Web toolbar, 275
WEEKNUM function, 110
white-cross mouse pointer

editing with the, 57
moving the cell cursor with the, 12

Width display indicator, 43
Wiley Product Technical Support contact

information, 328
Windows 98 For Dummies (Rathbone), 325
Windows 2000 Professional For Dummies

(Rathbone), 325

launching Excel from the, 9
switching between books from the, 11

windows (vertical), 314
WordArt, 198–200
WordArt Gallery dialog box, 198

adding summary information to a, 49–51
assigning a password for opening and editing a,

222–223
blank, 10
Close Window button on the, 11
customizing templates in a, 309
generating new from templates, 309
making active, 11, 315
moving between sheets in a, 14
moving in the, 12–14
multi-sheet layout of a, 310
opening a new, 10–11
opening options of a, 53–54
printing entire, 45–46
protecting entire, 228–230
working with two, 314–315

filename extensions of the, 11
opening/closing the, 11
saving a spreadsheet in a, 20–21
searching for a, 51–53

353Index

43_798452 bindex.qxp 3/13/06 7:42 PM Page 353

Page 370

Excel Workbook For Dummies 354
workbook protection (enabling/disabling), 229
WORKDAY function, 111

alignment in a, 16
color-coding tabs on a, 310
custom view of a, 312
data entry in a protected, 227–228
default alignment of a, 16
editing multiples as a group, 69
hiding columns and rows in a, 35
manually modifying row height in a, 25
moving to a new area of a, 12–13
protecting a, 224–226
saving as Web pages overview, 261
scrolling through a, 12–13
selecting a, 14
using to organize spreadsheet data, 310

worksheet protection, 224–226
Wrap Cell control, 30

• X •
.xls, extension, 11
.xlt, extension, 11
.xml extension (Extensible Markup Language),

269
Xpos optional argument (InputBox), 297

• Y •
YEARFRAC function, 111
ypos optional argument (InputBox), 297

• Z •
zoom

default for Page Break Preview, 38
editing worksheets and, 54–55
increasing the, 38
in print preview window, 38
range of settings of, 54

43_798452 bindex.qxp 3/13/06 7:42 PM Page 354