Beginning Microsoft Excel 2010 (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.

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
Introduction 14
CHAPTER 1 Introduction to Excel 15
Making the Acquaintance 15
Unlocking Your Inner Worksheet 15
The Pep Talk 18
Interacting with the Interface 19
Tab Talk 21
Something New, Something Old 23
Keeping Tabs…on a New One 24
Cool QAT 26
Understood in Context 27
Alt-ered Consciousness 28
CHAPTER 2 Getting Started and Getting Around the Worksheet 30
More Addresses Than the Phone Book—Cells, and How to Get There 30
Getting Around 32
Key Points 34
Ranges: A Select Tool 37
Data Entry: Getting Started 42
Entering Text: Trespassing Allowed 43
Numbers are (a little) Different 49
Relocating the Data: Copying and Moving 50
Having Your Fill 54
There’ll Be Some Changes Made—Editing Cells 59
CHAPTER 3 From Data Entry to Data Creation: Formula Basics and Beyond 63
Cell references explained 64
Ordering Up Your Results 66
Not Just Your Average Function 74
Making Range Values COUNT 75
MAX and MIN—Recording Highs and Lows 76
Absolute References: Absolutely Important 84
More of the Same 85
You Could Look It Up 91
If: Worth Knowing—No Ifs, Ands, or Buts 95
In Conclusion… 97
CHAPTER 4 Keeping Up Appearances—Formatting the Worksheet 98
The Font Button Group: A Closer Look 100
Borderline Command 104
Getting Oriented 115
Excel Has Got Your Number(s) 129
Dates—The Long and the Short of It 135
Time Is On Your Side—Yes It Is 136
A New Kind of Copy—and Paste 142
Style Setter 148
Formatting—With Conditions 150
Just a Bit More… 163
P. S. 165
IN CONCLUSION… 165
CHAPTER 5 The Stuff Of Legend—Charting in Excel 166
Starting Charting 166
Making a Chart of Our Own 169
Excluding Data 169
Evaluating the Chart 171
Resizing the Chart 172
Flipping the Data Series 174
Changing The Chart—It’s Your Call 175
The Column Chart 175
The Line Chart 177
The Pie Chart 179
The Bar Chart 181
The Area Chart 182
The Scatter Chart 182
The Design Tab—A Closer Look 183
Change Chart Type 183
Save As Template 184
Switch Row/Column 185
Select Data 186
Chart Layouts 187
Chart Styles 188
Move Chart 189
Changing the Chart Default—and the 2-Second Chart 189
The Layout Tab 190
Working with Chart Labels 195
Axes to Grind 198
The Format Tab—Getting Your Objects in Shape 204
Sparklines: Mini-Charts with Big Impact 205
You Win Some, You Lose Some 209
In Conclusion… 211
CHAPTER 6 Setting the Table: Database Features of Excel 2010 212
Sorting—Sort Of Easy 213
Using Header Rows 215
Sorting by More than One Field 216
The AutoFilter: Picking and Choosing Your Data 220
Playing—or Plying—the Numbers 224
The Advanced Filter—Setting Your Data Aside 226
Table Talk 229
Creating a Table 230
Using Table Styles 231
Adding a Total Row 232
Examining the other Table Style Options 234
At the Risk of Repeating Yourself: The Remove Duplicates Option 237
Data Validation: Improving Your Entrée to Data Entry 239
Using Data Validation 239
Adding Data Entry Rules 242
Adding an Input Message 245
Using the Error Alert Option 245
Adding a Validation Rule to Existing Data 247
In Conclusion… 248
CHAPTER 7 Working With Multiple Sheets 249
Adding To Sheets—Inserting Rows, Columns and Cells 250
Inserting a Column 250
Inserting a Row 251
Deleting Rows and Columns 251
Inserting and Deleting Cells 251
Hiding Rows and Columns—and Getting them Back 252
Multiple Worksheet Basics 253
Inserting a New Worksheet 254
Busting a (Sheet) Move 255
Hiding Worksheets 256
Grouping Worksheets 257
Far-Flung Formulas: Working with Multi-Sheet Cell References 257
Doing a Multi-sheet Calculation 258
Extending Your Reach: Referring to Cells in Different Workbooks 260
The Watch Window—Spying On Your Own Data 261
Protect Your Cells From Unwanted Intruders—Even Yourself 263
Protecting a Sheet 263
Locking Cells 264
Unlocking Cells 266
Protecting a Whole Workbook 268
Consolidating Your Data—Getting It All Together 269
In Conclusion… 274
CHAPTER 8 PivotTables and Pivot Charts 275
Starting Out with PivotTables 275
What’s in a Name? 277
Constructing a PivotTable: Let’s Go 278
Inserting the Table 279
Setting Up the PivotTable 281
Updating the Pivot Report 284
Changing the Calculation 284
Comparing the Data 285
Moving and Removing Fields 286
Adding Data Fields 287
Changing Field Settings 290
Pivoting the Table 292
Counting Totals 292
Using the Same Field Twice 294
Filters Again-PivotTable Style 295
Your Very Own Top 10 List 298
The Report Filter—Getting on Top of the PivotTable 299
The Slicer—Filter Deluxe 303
Grouping the Data 306
Formatting the Pivot Report—Values and All 310
Pivot Charts 312
In Conclusion… 317
CHAPTER 9 Getting It On Paper—Printing in Excel 2010 318
Hard Copy? Pretty Easy 318
The Print Area Option 320
Margin-al Utility 323
Printing As You See Fit 325
Headers and Footers—Getting to the Bottom (and the Top) of Your Printout 327
Title Search 332
There’s More Than One Way to View A Worksheet 336
Gimme a (Page) Break—Another View 339
Customize Your View, Too 343
In Conclusion… 345
CHAPTER 10 Taking it to the Cloud: Sharing and Collaborating on the Internet 346
Getting There 347
Permission Granted: Sharing Your Workbooks 354
Hey—You! Get Onto My Cloud! 357
In Conclusion… 358
APPENDIX A Working With Range Names 359
What’s in a Name? Plenty, if it’s a Range 359
Naming Many Ranges – at the Same Time 365
The Name Manager – Tracking Your Ranges 367
In Conclusion... 368
APPENDIX B Keyboard Shortcuts 369
Control Key Combinations 369
Function keys 371
Other shortcut keys 373
APPENDIX C Error Messages 377
Nobody’s Perfect 377
Index 383