Download Excel Workbook for Dummies (ISBN - 0471798452) PDF

TitleExcel Workbook for Dummies (ISBN - 0471798452)
TagsFor Dummies
LanguageEnglish
File Size7.3 MB
Total Pages370
Table of Contents
                            Excel Workbook For Dummies
	About the Author
	Dedication
	Author’s Acknowledgments
	Contents at a Glance
	Table of Contents
	Introduction
		About This Book
		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
	Part I: Creating Spreadsheets
		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
		Chapter 2: Formatting the Spreadsheet
			Resizing Columns and Rows
			Cell Formatting Techniques
			Hiding Columns and Rows
		Chapter 3: Printing Spreadsheet Reports
			Previewing the Printed Report
			Adjusting Page Breaks
			Adding Custom Headers and Footers
			Adding Print Titles to a Report
			Modifying the Print Setting for a Report
			Printing All or Part of the Workbook
		Chapter 4: Modifying the Spreadsheet
			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 Notes in the Spreadsheet
			Using Find and Replace and Spell-Checking
			Group Editing
	Part II: Using Formulas and Functions
		Chapter 5: Building Formulas
			Building Formulas
			Using External Reference Links
			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
		Chapter 14: Charting Spreadsheet Data
			Understanding Excel Charts
			Creating Charts
			Formatting Charts
			Editing Charts
		Chapter 15: Adding Graphics to the Spreadsheet
			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
		Chapter 17: Protecting the Spreadsheet
			Password-Protecting the Workbook
			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
		Chapter 21: Adding Hyperlinks to Spreadsheets
			Creating Hyperlinks
			Editing Hyperlinks
			Assigning Links to Toolbars and Menus
	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
			Freeze Column and Row Headings
			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
	Appendix A: About the CD
		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
graphics in your spreadsheets.

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
URL address

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

user-defined function, creating a, 300–305.
See also custom function

• 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. See also VLOOKUP function
(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
VLOOKUP function (vertical lookup). See also

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

adding hyperlinks to a, 277–278
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
Windows taskbar

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

windows (vertical), 314
WordArt, 198–200
WordArt Gallery dialog box, 198
workbook. See also workbook file

adding hyperlinks to other sheets in a, 274–275
adding summary information to a, 49–51
adding worksheets to a, 310
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
password-protecting the, 221–223
printing entire, 45–46
protecting entire, 228–230
working with two, 314–315

workbook file. See also workbook
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
worksheet. See also spreadsheet

adding a, 14
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

Similer Documents