Excel 2010 Made Simple (eBook)

Abbott Katz, author of Beginning Microsoft Excel 2010, brings extensive Microsoft Office instructional experience to the task, having trained users of the suite s components in varied academic and corporate settings. He has a doctorate in sociology and wide-ranging writing experience as well. Abbott has composed, and continue to compose, numerous complex spreadsheets, twinning technical fluency with the imagination to realize Excel s potential in a diverse array of settings.

Cover 2
Title Page 2
Copyright Page 3
Contents at a Glance 4
Table of Contents 5
About the Author 11
About the Technical Reviewer 12
Acknowledgments 13
Quick Start Guide 14
The Excel Worksheet: What You’re Looking At 14
Key Tips: Accessing Buttons with the Keyboard 18
Contextual Tabs 19
A Visit Backstage 20
Customizing the Quick Access Toolbar 21
Where to Learn More 25
Excel Keyboard Equivalents 31
Chapter 1 Introducing Excel 2010 39
The Advantages of Learning More 39
Spreadsheets Defined 40
Excel Can Be Fun 42
Summary 42
Chapter 2 Getting Around the Worksheet and Data Entry 43
The Journey Starts Here 43
Looking Around 43
Getting Around a Worksheet 43
Selecting Multiple Cells 46
Selecting Cells Down and Across the Worksheet 47
Selecting Cells with the Keyboard 48
Selecting All the Cells 48
Still One More Selection Technique—The Name Box 49
Entering Text and Data 50
Aligning Your Data—Where It Appears in the Cell 51
Widening and Narrowing Columns 53
Altering a column manually 53
Using the Auto-fit Feature 54
Entering Numerical Data—How it’s Different 56
Entering Data into a Selected Range 57
Using Auto Fill to Speed Up Data Entry 60
Copying a Value with Auto Fill 60
Auto Filling a Numeric Sequence 60
Using Auto Fill with Text 62
Using the Auto Fill Option Button 62
Customizing Auto Fill Lists 65
Data Validation: Bringing Quality Control to the Worksheet 66
Making a List—Personalizing a Drop-Down Menu 69
Explaining Data Validation Errors with Error Alerts 71
Adding Data Entry Instructions with Input Messages 72
Summary 73
Chapter 3 Editing Data 74
Changing Your Data 74
Undoing an Edit 75
Undoing What You’ve Just Undone with the Redo Button 77
Deleting Cell Contents 77
Copying and Moving: Duplicating and Relocating Your Data 77
Copying Data 78
Moving Data 80
The Clipboard: The Storage Area for Copied and Cut Data 80
Summary 82
Chapter 4 Number Crunching 101: Functions, Formulas, and Ranges 83
Automatic Calculations with Functions 83
Adding a Column of Numbers 84
Selecting the Range You Need 86
Viewing and Editing Your Formula: Back to the Formula Bar 87
Calculating an Average 89
Displaying Values Based on a Certain Condition 90
Revisiting Function Structure 92
Locating Functions in the Function Library 92
Customizing the Worksheet with Formulas 94
Writing a Basic Formula 95
Working Out the Order of Operations in a Formula 96
Copying Formulas: More Than Just Duplication 97
The Classic Copy-and-Paste Method 98
Moving Formulas: An Important Difference 99
Keeping a Cell Reference Constant with Absolute Addressing 100
Absolute Addressing: When We Don’t Want It to Work 101
Introducing an Absolute Cell Address 102
Copying a Formula’s Result Only 103
Clarifying Cell References by Naming a Range 105
Naming a Range in the Name Box 105
Naming Ranges from the Data in Your Worksheet 109
Naming A Range Containing One Cell: Why Bother? 110
The Name Manager: Where They’re All Ar-ranged 111
Summary 112
Chapter 5 For Appearance’s Sake: Formatting Your Data 113
What Formatting Does (and Doesn’t Do) 113
Basic Formatting 114
Changing the Font 114
Changing the Font Size 116
Using Bold, Italics, and Underline 117
Determining a Cell’s Formatting 118
Adding a Border 119
Adding Color to Your Cells 121
Adding Extra Formatting 123
Aligning (and Realigning) Your Data 124
Changing Horizontal Alignment 124
Changing Vertical Alignment 125
Changing Data Orientation 126
Indenting Data 129
Wrapping Text 129
Adding a Title with Merge and Center 130
Inserting, Deleting, and Hiding Columns and Rows 131
Inserting a Column or Row 132
Inserting Multiple Columns or Rows 133
What Inserting Does to Formulas 134
Deleting Columns and Rows 134
Hiding Rows and Columns 135
Unhiding Columns and Rows 137
Inserting and Deleting Cells 138
Formatting Values: Making the Numbers Look Good 139
Turning Values into Currency 139
Working with Percentages 142
Punctuating Values 142
Formatting Decimal Points 143
Working with Dates: Dates Are Numbers Too 144
Customizing Number Formats 147
The Special Formats Option 147
The Custom Option 148
Copying Formats (Not Data) with the Format Painter 150
Applying Ready-Made Formats with Styles 151
Customizing Your Own Style 152
Applying Styles Quickly: Another Way to Access Formatting Options 154
Conditional Formatting 155
Looking for Scores Equal to or Greater Than 90 159
An Alternative Approach to the Same Result 159
Some Additional Conditional Formatting Options 160
Turning Off Conditional Formatting 162
Using Data Bars: A Different Kind of Conditional Format 162
Summary 163
Chapter 6 Charting Your Data 164
Defining Chart Elements 165
Choosing a Chart Type 166
Creating a Column Chart 168
Creating a Chart in 2 Seconds 169
Changing a Chart 169
Moving and Resizing the Chart 169
Changing the Chart by Changing Its Data 171
Changing the Chart Type 172
Where’s the Pie Chart? 176
Changing the Default Chart 176
Switching Rows with Columns: A Different Kind of Change 177
Formatting Charts 178
Formatting with the Mini-Toolbar 179
Formatting with the Context Menu 180
Formatting with the Current Selection Button Group 180
Exploring the Format Dialog Box 181
Reformatting the Vertical Axis 184
Color-Coordinating Your Data Using Chart Styles 186
Formatting a Chart Object Using Shape Styles 187
Formatting Text Elements with WordArt 188
Adding Extra Chart Elements with Chart Layouts 189
Adding Extra Chart Elements with the Layout Tab 190
Chart Title 190
Axis Titles 192
Legend 193
Data Labels 193
Data Table 194
Axes 194
Plot Area 197
Introducing Sparklines: Mini-Charts Placed in Cells 198
Modifying Sparklines 200
Representing Binary Values with Sparklines 201
Summary 202
Chapter 7 Sorting and Filtering Your Data: Excel’s Database Features 203
Sorting Data: Instilling Order in Your Data 204
Sorting by Two Fields: The Hows and Whys 207
Sorting by Cell Format 209
Finding What You Want with Filters 210
Clearing a Filter 213
Text and Number Filters: Filters Within the Filter 213
Filtering Multiple Fields 216
Tables: Adding User-Friendliness to Your Database 217
Finding Duplicate Records in the Table (and Removing Them) 224
Converting a Table to a Range 225
Summary 226
Chapter 8 PivotTables: Data Aggregation Without the Aggravation 227
Looking at Some PivotTables 227
Creating a PivotTable 232
Choosing Which Data to Work On 235
Getting the Fields Where You Want Them 236
Pivoting the Data Sideways Using the Column Labels Area 237
Filtering Items Using the Report Filter Area 237
Creating a Report Worksheet for Each Item in a Filter 239
Counting Records: A Way to Break Out Text Data 240
Grouping Related Items Using Two Fields 242
Using the Row and Column Value Areas to Group Items 245
Changing the Calculation 245
Grouping PivotTable Data: Organizing Your Time(s) 247
Refreshing the PivotTable: Changing the Data 248
Adding New Records to a PivotTable 249
Viewing Which Records Are Filtered: Using the Slicer 250
How the Slicer Works 251
Restyling the Slicer 254
Formatting the PivotTable 255
Styling Your Report 257
Changing PivotTable Headers 258
Layout Options 259
Creating Charts from PivotTables Using PivotCharts 261
Filtering Data in the Chart with Field Buttons 264
Creating a PivotTable and PivotChart Together 266
Summary 267
Chapter 9 Managing Your Workbook 268
Adding Worksheets to Your Workbook 268
Clicking Through the Worksheets 269
Adding and Moving New Worksheets 270
Deleting Sheets 272
Copying a Sheet 273
Renaming and Recoloring the Worksheet Tabs 273
Hiding Sheets 274
Grouping Sheets: Changing Multiple Sheets at the Same Time 275
How to Group Sheets 276
Ungrouping the Sheets 276
Referring to Cells in Other Worksheets: Using Them in Formulas 277
Using Ranges on Other Sheets in Formulas 278
Using the View Context Tab to Show and Hide Basic Screen Elements 280
Showing Formulas in Cells 281
Hiding the Ribbon 282
Keeping Important Data in View with the Freeze Panes Option 283
Freezing Rows and Columns at the Same Time 285
Protecting the Worksheet and the Workbook 286
Protecting a Worksheet 287
Using a Password: Some Extra Protection 288
Unprotecting a Worksheet 289
Protecting Some, but Not All, of a Worksheet 289
Hiding Formulas 291
Protecting a Workbook 293
Unprotecting a Workbook 295
Summary 295
Chapter 10 Printing Your Worksheets: Hard Copies Made Easy 296
Deciding What You Want to Print 296
Printing the Entire Worksheet 296
Printing a Selection 297
Surveying Printing Options: The Print Backstage 301
Setting the Print Area 307
Customizing Your Printing 309
Working with Page Breaks 309
Previewing the Page Break: Getting a Bird’s-Eye View of the Printout 312
Printing Titles 315
Adding Headers and Footers 319
Adding Headers and Footers in the Page Layout View 319
Adding Headers and Footers Using the Page Setup Dialog Box 321
Adding Custom Headers and Footers 323
Printing the Gridlines and Headings 326
Summary 328
Chapter 11 Automating Your Work with Macros 329
The Two Kinds of Macros 329
Composing a Macro 330
About Saving a Workbook with a Macro 332
Playing Back the Macro 333
What We’ve Done 334
Relative References in a Macro 334
Saving a Macro to the Personal Macro Workbook 336
Deleting a Macro 338
Editing a Macro by Tweaking It in VBA 338
VBA: Written Behind the Scenes 339
Exposing the VBA Worksheet 339
Activating a Macro with a Keyboard Shortcut 341
A Note on Macro Security 343
Summary 344
Index 345