Excel 2010 Made Simple

Cover......Page 1
Contents at a Glance......Page 3
Contents......Page 356
About the Author......Page 362
About the Technical Reviewer......Page 363
Acknowledgments......Page 364
The Excel Worksheet: What You’re Looking At......Page 4
Key Tips: Accessing Buttons with the Keyboard......Page 8
Contextual Tabs......Page 9
A Visit Backstage......Page 10
Customizing the Quick Access Toolbar......Page 11
Where to Learn More......Page 15
Excel Keyboard Equivalents......Page 21
The Advantages of Learning More......Page 29
Spreadsheets Defined......Page 30
Summary......Page 32
Getting Around a Worksheet......Page 33
Selecting Multiple Cells......Page 36
Selecting Cells Down and Across the Worksheet......Page 37
Selecting All the Cells......Page 38
Still One More Selection Technique—The Name Box......Page 39
Entering Text and Data......Page 40
Aligning Your Data—Where It Appears in the Cell......Page 41
Altering a column manually......Page 43
Using the Auto-fit Feature......Page 44
Entering Numerical Data—How it’s Different......Page 46
Entering Data into a Selected Range......Page 47
Auto Filling a Numeric Sequence......Page 50
Using the Auto Fill Option Button......Page 52
Customizing Auto Fill Lists......Page 55
Data Validation: Bringing Quality Control to the Worksheet......Page 56
Making a List—Personalizing a Drop-Down Menu......Page 59
Explaining Data Validation Errors with Error Alerts......Page 61
Adding Data Entry Instructions with Input Messages......Page 62
Summary......Page 63
Changing Your Data......Page 64
Undoing an Edit......Page 65
Copying and Moving: Duplicating and Relocating Your Data......Page 67
Copying Data......Page 68
The Clipboard: The Storage Area for Copied and Cut Data......Page 70
Summary......Page 72
Automatic Calculations with Functions......Page 73
Adding a Column of Numbers......Page 74
Selecting the Range You Need......Page 76
Viewing and Editing Your Formula: Back to the Formula Bar......Page 77
Calculating an Average......Page 79
Displaying Values Based on a Certain Condition......Page 80
Locating Functions in the Function Library......Page 82
Customizing the Worksheet with Formulas......Page 84
Writing a Basic Formula......Page 85
Working Out the Order of Operations in a Formula......Page 86
Copying Formulas: More Than Just Duplication......Page 87
Naming a Range in the Name Box......Page 95
Naming Ranges from the Data in Your Worksheet......Page 99
Naming A Range Containing One Cell: Why Bother?......Page 100
The Name Manager: Where They’re All Ar-ranged......Page 101
Summary......Page 102
What Formatting Does (and Doesn’t Do)......Page 103
Changing the Font......Page 104
Changing the Font Size......Page 106
Using Bold, Italics, and Underline......Page 107
Determining a Cell’s Formatting......Page 108
Adding a Border......Page 109
Adding Color to Your Cells......Page 111
Adding Extra Formatting......Page 113
Changing Horizontal Alignment......Page 114
Changing Vertical Alignment......Page 115
Changing Data Orientation......Page 116
Wrapping Text......Page 119
Adding a Title with Merge and Center......Page 120
Inserting, Deleting, and Hiding Columns and Rows......Page 121
Inserting a Column or Row......Page 122
Inserting Multiple Columns or Rows......Page 123
Deleting Columns and Rows......Page 124
Hiding Rows and Columns......Page 125
Unhiding Columns and Rows......Page 127
Inserting and Deleting Cells......Page 128
Turning Values into Currency......Page 129
Punctuating Values......Page 132
Formatting Decimal Points......Page 133
Working with Dates: Dates Are Numbers Too......Page 134
The Special Formats Option......Page 137
The Custom Option......Page 138
Copying Formats (Not Data) with the Format Painter......Page 140
Applying Ready-Made Formats with Styles......Page 141
Customizing Your Own Style......Page 142
Applying Styles Quickly: Another Way to Access Formatting Options......Page 144
Conditional Formatting......Page 145
An Alternative Approach to the Same Result......Page 149
Some Additional Conditional Formatting Options......Page 150
Using Data Bars: A Different Kind of Conditional Format......Page 152
Summary......Page 153
Charting Your Data......Page 154
Defining Chart Elements......Page 155
Choosing a Chart Type......Page 156
Creating a Column Chart......Page 158
Moving and Resizing the Chart......Page 159
Changing the Chart by Changing Its Data......Page 161
Changing the Chart Type......Page 162
Changing the Default Chart......Page 166
Switching Rows with Columns: A Different Kind of Change......Page 167
Formatting Charts......Page 168
Formatting with the Mini-Toolbar......Page 169
Formatting with the Current Selection Button Group......Page 170
Exploring the Format Dialog Box......Page 171
Reformatting the Vertical Axis......Page 174
Color-Coordinating Your Data Using Chart Styles......Page 176
Formatting a Chart Object Using Shape Styles......Page 177
Formatting Text Elements with WordArt......Page 178
Adding Extra Chart Elements with Chart Layouts......Page 179
Adding Extra Chart Elements with the Layout Tab......Page 180
Introducing Sparklines: Mini-Charts Placed in Cells......Page 188
Modifying Sparklines......Page 190
Representing Binary Values with Sparklines......Page 191
Summary......Page 192
Sorting and Filtering Your Data: Excel’s Database Features......Page 193
Sorting Data: Instilling Order in Your Data......Page 194
Sorting by Two Fields: The Hows and Whys......Page 197
Sorting by Cell Format......Page 199
Finding What You Want with Filters......Page 200
Text and Number Filters: Filters Within the Filter......Page 203
Filtering Multiple Fields......Page 206
Tables: Adding User-Friendliness to Your Database......Page 207
Finding Duplicate Records in the Table (and Removing Them)......Page 214
Converting a Table to a Range......Page 215
Summary......Page 216
Looking at Some PivotTables......Page 217
Creating a PivotTable......Page 222
Choosing Which Data to Work On......Page 225
Getting the Fields Where You Want Them......Page 226
Filtering Items Using the Report Filter Area......Page 227
Creating a Report Worksheet for Each Item in a Filter......Page 229
Counting Records: A Way to Break Out Text Data......Page 230
Grouping Related Items Ussing Two Fields......Page 232
Changing the Calculation......Page 235
Grouping PivotTable Data: Organizing Your Time(s)......Page 237
Refreshing the PivotTable: Changing the Data......Page 238
Adding New Records to a PivotTable......Page 239
Viewing Which Records Are Filtered: Using the Slicer......Page 240
How the Slicer Works......Page 241
Restyling the Slicer......Page 244
Formatting the PivotTable......Page 245
Styling Your Report......Page 247
Changing PivotTable Headers......Page 248
Layout Options......Page 249
Creating Charts from PivotTables Using PivotCharts......Page 251
Filtering Data in the Chart with Field Buttons......Page 254
Creating a PivotTable and PivotChart Together......Page 256
Summary......Page 257
Adding Worksheets to Your Workbook......Page 258
Clicking Through the Worksheets......Page 259
Adding and Moving New Worksheets......Page 260
Deleting Sheets......Page 262
Renaming and Recoloring the Worksheet Tabs......Page 263
Hiding Sheets......Page 264
Grouping Sheets: Changing Multiple Sheets at the Same Time......Page 265
Ungrouping the Sheets......Page 266
Referring to Cells in Other Worksheets: Using Them in Formulas......Page 267
Using Ranges on Other Sheets in Formulas......Page 268
Using the View Context Tab to Show and Hide Basic Screen Elements......Page 270
Showing Formulas in Cells......Page 271
Hiding the Ribbon......Page 272
Keeping Important Data in View with the Freeze Panes Option......Page 273
Freezing Rows and Columns at the Same Time......Page 275
Protecting the Worksheet and the Workbook......Page 276
Protecting a Worksheet......Page 277
Using a Password: Some Extra Protection......Page 278
Protecting Some, but Not All, of a Worksheet......Page 279
Hiding Formulas......Page 281
Protecting a Workbook......Page 283
Summary......Page 285
Printing the Entire Worksheet......Page 286
Printing a Selection......Page 287
Surveying Printing Options: The Print Backstage......Page 291
Setting the Print Area......Page 297
Working with Page Breaks......Page 299
Previewing the Page Break: Getting a Bird’s-Eye View of the Printout......Page 302
Printing Titles......Page 305
Adding Headers and Footers in the Page Layout View......Page 309
Adding Headers and Footers Using the Page Setup Dialog Box......Page 311
Adding Custom Headers and Footers......Page 313
Printing the Gridlines and Headings......Page 316
Summary......Page 318
The Two Kinds of Macros......Page 319
Composing a Macro......Page 320
About Saving a Workbook with a Macro......Page 322
Playing Back the Macro......Page 323
Relative References in a Macro......Page 324
Saving a Macro to the Personal Macro Workbook......Page 326
Editing a Macro by Tweaking It in VBA......Page 328
Exposing the VBA Worksheet......Page 329
Activating a Macro with a Keyboard Shortcut......Page 331
A Note on Macro Security......Page 333
Summary......Page 334
A......Page 335
C......Page 336
D......Page 339
F......Page 340
G......Page 342
K......Page 343
M......Page 344
P......Page 345
Q......Page 347
S......Page 348
V......Page 350
W......Page 351
Y, Z......Page 353