Excel 2003 For Dummies Quick Reference

by Walkenbach, John; Banfield, Colin

• ISBN13: 9780764539879
• ISBN10: 0764539876
• Edition: 1st
• Format: Paperback
• Copyright: 2003-10-17
• Publisher: For Dummies

Summary

If you're using Excel 2003 and you'd rather be working on your projects instead of plodding through everything you could ever need to know (and may never need to know) about Excel, this is the reference for you. In a compact, info-packed, spiral-bound book that puts the facts you need at your fingertips, it covers the basics most Excel users need to know, including: Opening, saving protecting, and recovering workbook files and using workbook templates Adding, copying, and deleting worksheets, e-mailing worksheet data, and publishing worksheet data to the Web Entering and editing data and text and working with cells and ranges Creating formulas and functions Creating and using names Auditing, formatting, and printing your work Charting your data Analyzing data with pivot tables Complete with concise, step-by-step explanations (most of which take less than one page) and lots of screen shots and tables, this no nonsense guide gets you working instead of searching or reading, A detailed index makes it easy to find what you need to know fast. Excel 2003 For Dummies is a practical, at-a-glance reference for any Excel user.

Author Biography

Colin Banfield has worked in the telecommunications field for more than 20 years. He has used spreadsheet programs for over 18 years, including every version of Microsoft Excel. Colin's largest Excel project to date is a comprehensive tool designed for central office equipment configuration, pricing, and manufacturing, which is used by sales, engineering and manufacturing personnel. In his spare time, Colin enjoys photography, astronomy, chess and expanding his musical repertoire.

John Walkenbach is the leading authority on spreadsheet software. He is the author of more than two dozen spreadsheet books and has written more than 300 articles and reviews for a variety of publications, including PC World, InfoWorld, Windows magazine, and PC/Computing. He is also the principal of JWalk and Associates Inc., a consulting firm that specializes in spreadsheet application development.

Table of Contents

The Big Picture: Microsoft Office Excel 2003	p. 1
What You See: The Excel Window	p. 2
What You See: Dialog Boxes	p. 4
Toolbar Table	p. 6
The Basics: Starting Excel	p. 8
The Basics: Getting Acquainted with Excel	p. 8
Navigating with the keyboard	p. 8
Navigating with the mouse	p. 9
Understanding the Excel menu bar	p. 9
The Basics: Getting Help	p. 11
The Basics: Creating an Empty Workbook File	p. 13
The Basics: Saving a Workbook File	p. 13
The Basics: Opening a Workbook File	p. 14
The Basics: Closing a Workbook File and Exiting Excel	p. 15
What You Can Do: Create and Analyze Data	p. 16
What You Can Do: Show Data Graphically	p. 17
What You Can Do: Import Data for Analysis	p. 18
Using Workbook Files	p. 1
Changing the Default File Location	p. 2
Creating Multiple Windows (Views) for a Workbook	p. 2
Deleting a Workbook File	p. 3
Opening Nonstandard Files	p. 4
Opening a non-Excel or older Excel file	p. 4
Opening a workspace file	p. 4
Protecting a Workbook File	p. 4
Recovering Workbook Files after a System Crash	p. 5
Saving Files	p. 7
Saving a workbook under a different name	p. 7
Saving a workbook in a different or older file format	p. 7
Saving a workspace file	p. 8
Switching among Open Workbooks	p. 9
Working with Workbook Templates	p. 9
Creating a workbook template	p. 9
Creating a workbook from a template	p. 10
Creating a default workbook template	p. 11
Working with Worksheets	p. 13
Activating Worksheets	p. 14
Adding a New Worksheet	p. 14
Arranging Windows Automatically	p. 14
Changing a Worksheet's Name	p. 15
Coloring a Worksheet Tab	p. 15
Copying a Worksheet	p. 16
Deleting a Worksheet	p. 17
E-Mailing Worksheet Data	p. 17
Freezing Row or Column Titles	p. 18
Hiding and Unhiding a Worksheet	p. 19
Moving a Worksheet	p. 19
Protecting a Worksheet	p. 20
Publishing Your Worksheet Data to the Web	p. 21
Publishing your worksheet data to a Web page	p. 22
Republishing your worksheet data to the Web	p. 23
Splitting Panes	p. 24
Using Full-Screen View	p. 25
Zooming Worksheets	p. 25
Entering and Editing Worksheet Data	p. 27
Copying Cells and Ranges	p. 28
Copying a cell to another cell or a range	p. 28
Copying a range to another range	p. 29
Copying data to another worksheet or workbook	p. 30
Copying multiple cells or ranges to another range	p. 31
Deleting Entire Rows and Columns	p. 32
Editing a Cell's Contents	p. 33
Entering Data into a Range	p. 35
Entering data into a specific range	p. 35
Entering the same data into a range of cells	p. 35
Entering Dates and Times	p. 36
Entering specific dates and times	p. 36
Entering the current date or time	p. 37
Entering Formulas	p. 37
Entering formulas manually	p. 38
Entering formulas by pointing	p. 38
Entering Text	p. 39
Entering text into cells	p. 39
Completing text entries by using AutoComplete	p. 40
Entering Values	p. 40
Entering values into cells	p. 41
Entering fractions	p. 41
Erasing Data in Cells and Ranges	p. 41
Filling a Series	p. 42
Entering a series of incremental values or dates	p. 43
Entering a series of text	p. 43
Inserting Decimal Points Automatically	p. 44
Inserting Entire Rows and Columns	p. 44
Moving Cells and Ranges	p. 45
Moving data to a new location in the same worksheet	p. 45
Moving data to a different worksheet or workbook	p. 46
Replacing the Contents of a Cell	p. 47
Searching and Replacing Data	p. 47
Searching for data	p. 48
Searching for and replacing data	p. 49
Selecting Cells and Ranges	p. 50
Selecting a cell	p. 50
Selecting a range	p. 50
Selecting noncontiguous ranges	p. 51
Selecting entire rows and columns	p. 51
Selecting a multisheet (3-D) range	p. 52
Transposing a Range	p. 52
Undoing Changes and Mistakes	p. 53
Validating Data Entry	p. 54
Using Formulas and Functions	p. 57
Absolute, Relative, and Mixed References	p. 58
Add-In Functions	p. 58
Basic Formula Essentials	p. 59
Changing When Formulas Are Calculated	p. 60
Converting Formulas to Values	p. 61
Editing Functions in Formulas	p. 61
Entering Functions in Formulas	p. 62
Entering functions manually	p. 62
Using the Extended AutoSum tool	p. 63
Using the Insert Function dialog box	p. 64
Modifying a Range Reference Used in a Function	p. 65
Referencing Cells in Other Worksheets	p. 66
Creating and Using Names	p. 67
Changing Names	p. 69
Creating Names	p. 69
Creating names by using the Define Name dialog box	p. 69
Creating names by using the Name Box	p. 70
Creating names automatically	p. 70
Creating multisheet names	p. 71
Creating a Table of Names	p. 72
Deleting Names	p. 72
Names in Formulas	p. 73
Applying names to existing cell references	p. 73
Pasting names into a formula	p. 74
Using row and column headers	p. 74
Using sheet-level names	p. 75
Naming Constants	p. 75
Redefining Name References	p. 76
Auditing Your Work	p. 77
Cell Comments	p. 79
Adding a cell comment	p. 79
Editing a cell comment	p. 79
Changing the looks of a cell comment	p. 79
Viewing cell comments	p. 80
Checking for Specific Worksheet Errors	p. 80
Checking for errors in the background	p. 81
Checking for errors manually	p. 81
Displaying Formulas in a Worksheet	p. 82
Evaluating Parts of a Formula	p. 83
Formula Error Values	p. 84
Handling Circular References	p. 85
Locating Errors by Selecting Special Cells	p. 86
Proofreading Your Worksheet Data	p. 87
Spell Checking	p. 88
Tracing Cell Relationships	p. 88
Tracing precedents and dependents	p. 89
Tracing formula error values	p. 90
Using the Watch Window	p. 90
Displaying and adding cells to the Watch Window	p. 91
Removing cells from the Watch Window	p. 92
Hiding the Watch Window	p. 92
Formatting Your Data	p. 93
Adding Borders to a Cell or Range	p. 94
Aligning Cell Contents	p. 96
Setting horizontal and vertical alignment	p. 96
Indenting the contents of a cell	p. 96
Justifying (refitting) text across cells	p. 97
Wrapping text within a cell	p. 98
Applying Background Colors and Patterns	p. 99
Applying a Background Graphic	p. 99
Changing the Appearance of Text in Cells	p. 100
Changing fonts and text sizes	p. 100
Applying colors to text	p. 100
Changing text attributes	p. 101
Changing Text Orientation (Direction)	p. 101
Copying Formats	p. 102
Formatting Based on a Cell's Contents	p. 103
Formatting Numbers	p. 104
Using Excel's built-in number formats	p. 104
Creating custom number formats	p. 105
Formatting a Table Automatically	p. 106
Hiding Cell Contents	p. 106
Hiding and Unhiding Columns and Rows	p. 107
Hiding columns and rows	p. 107
Unhiding columns and rows	p. 107
Merging Cells	p. 108
Modifying Cell Size	p. 109
Changing column width	p. 109
Changing row height	p. 110
Using Named Styles	p. 111
Creating named styles	p. 111
Applying named styles	p. 112
Printing Your Work	p. 113
Adjusting Margins	p. 114
Centering Printed Output	p. 115
Changing Default Print Settings by Using a Template	p. 115
Changing the Header or Footer	p. 116
Selecting a predefined header or footer	p. 116
Creating a custom header or footer	p. 116
Controlling Page Settings	p. 118
Changing page orientation	p. 118
Selecting paper size	p. 119
Scaling your printed output	p. 119
Specifying the beginning page number	p. 119
Dealing with Page Breaks	p. 120
Inserting manual page breaks	p. 120
Removing manual page breaks	p. 121
Previewing and adjusting page breaks	p. 121
Previewing Your Work	p. 122
Printing Cell Comments	p. 122
Printing Charts	p. 123
Printing Colors in Black and White	p. 124
Printing in Draft Quality	p. 124
Printing or Substituting Error Values	p. 124
Printing or Suppressing Gridlines	p. 125
Printing Row and Column Headings	p. 125
Selecting Global Print Options	p. 125
Printing noncontiguous ranges	p. 125
Printing selected pages	p. 126
Selecting a printer	p. 126
Setting the print area	p. 127
Specifying the data range to print	p. 127
Setting Print Titles	p. 128
Specifying Page Order	p. 128
Charting Your Data	p. 129
Adding Elements to a Chart	p. 130
Adding a new data series to a chart	p. 130
Adding a trendline to a data series	p. 131
Adding Fill Effects to a Chart	p. 132
Changing a Chart's Data Series	p. 132
Changing a Chart's Location	p. 133
Changing Chart Elements	p. 133
Changing a chart's scale	p. 133
Changing a chart's gridlines	p. 135
Changing data markers	p. 136
Changing the Chart Type	p. 137
Creating a Chart by Using the Chart Wizard	p. 137
Chart Wizard--Dialog box 1 of 4	p. 138
Chart Wizard--Dialog box 2 of 4	p. 139
Chart Wizard--Dialog box 3 of 4	p. 139
Chart Wizard--Dialog box 4 of 4	p. 140
Creating a Custom Chart Type	p. 140
Creating a Default Chart on a Chart Sheet	p. 141
Deleting a Chart Element or Data Series	p. 141
Displaying a Data Table in a Chart	p. 141
Displaying Data Labels in a Chart	p. 142
Formatting a Chart Element	p. 143
Handling Missing Data in a Chart	p. 144
Inserting and Modifying Chart Legends	p. 145
Adding a legend to a chart	p. 145
Adding titles to a chart legend	p. 145
Formatting a chart legend or legend entry	p. 146
Moving a Chart Element	p. 146
Rotating 3-D Charts	p. 147
Selecting a Chart Element	p. 147
Linking and Consolidating Worksheets	p. 149
Consolidating Worksheets	p. 150
Consolidating by position or by matching labels	p. 150
Consolidating by using formulas	p. 151
Linking Workbooks	p. 152
Referencing cells in other workbooks	p. 152
Changing the source of links	p. 153
Severing (breaking) links	p. 153
Updating links	p. 154
Working with Lists and External Data	p. 155
Accessing Advanced Filtering	p. 157
Setting up a criteria range for advanced filtering	p. 157
Performing advanced filtering	p. 158
Applying Database Functions with Lists	p. 159
Calculating Subtotals	p. 160
Filtering a List	p. 160
Filtering by using an Excel AutoFilter	p. 160
Filtering by using a custom AutoFilter	p. 162
Importing Data from a Text File	p. 163
Importing Data from the Web	p. 164
By using an existing Web query file	p. 164
By creating a new Web query	p. 165
Refreshing Imported Data	p. 167
Sorting a List	p. 167
Sorting numerically or alphabetically	p. 167
Using a custom sort order	p. 169
Goal Seeking and What-If Analysis	p. 171
Creating Data Input Tables	p. 172
Creating a one-input table	p. 172
Creating a two-input table	p. 173
Goal Seeking	p. 175
Using Scenario Manager	p. 176
Creating a named scenario	p. 176
Creating a scenario summary report	p. 178
Displaying a named scenario	p. 178
Analyzing Data with PivotTables	p. 179
Adding and Removing Fields in a PivotTable Report	p. 180
Inserting a new field	p. 180
Removing a field	p. 180
Creating a PivotTable Report	p. 180
Formatting a PivotTable Report	p. 183
Grouping PivotTable Items	p. 184
Modifying a PivotTable's Structure	p. 185
Refreshing a PivotTable Report	p. 185
Glossary: Tech Talk	p. 187
Index	p. 195
Table of Contents provided by Ingram. All Rights Reserved.