Microsoft Excel 2010 Visual Quick Tips

Chapter 1: Making Excel More Efficient - Page 14
Chapter 1: Making Excel More Efficient - Page 15
Customize the Quick Access Toolbar - Page 16
Customize the Quick Access Toolbar - Page 17
Customize the Ribbon - Page 18
Customize the Ribbon - Page 19
Customize the Ribbon - Page 20
Customize the Ribbon - Page 21
Export Ribbon Customizations to a File - Page 22
Export Ribbon Customizations to a File - Page 23
Configure Excel to Use the Mouse Wheel for Zooming - Page 24
Configure Excel to Use the Mouse Wheel for Zooming - Page 25
Move in a Different Direction When You Press Enter - Page 26
Move in a Different Direction When You Press Enter - Page 27
Automatically Insert a Decimal Point - Page 28
Automatically Insert a Decimal Point - Page 29
Configure When Excel Warns You About Long Operations - Page 30
Configure When Excel Warns You About Long Operations - Page 31
Pin Excel to the Windows 7 Taskbar - Page 32
Pin Excel to the Windows 7 Taskbar - Page 33
Make a Workbook Faster by Saving it as Binary - Page 34
Make a Workbook Faster by Saving it as Binary - Page 35
Open a New Window for a Workbook - Page 36
Open a New Window for a Workbook - Page 37
Allow Only Certain Values in a Cell - Page 38
Allow Only Certain Values in a Cell - Page 39
Apply Text or Formatting to Multiple Worksheets - Page 40
Apply Text or Formatting to Multiple Worksheets - Page 41
Quickly Display the Office Clipboard - Page 42
Quickly Display the Office Clipboard - Page 43
Use Dialog Box Controls to Input Data - Page 44
Use Dialog Box Controls to Input Data - Page 45
Use Dialog Box Controls to Input Data - Page 46
Use Dialog Box Controls to Input Data - Page 47
Check for Accessibility Problems - Page 48
Check for Accessibility Problems - Page 49
Chapter 2: Making It Easier to Manage Workbooks - Page 50
Chapter 2: Making It Easier to Manage Workbooks - Page 51
Increase the Number of Recent Documents - Page 52
Increase the Number of Recent Documents - Page 53
Open Workbooks Automatically at Startup - Page 54
Open Workbooks Automatically at Startup - Page 55
Create a Workspace of Workbooks - Page 56
Create a Workspace of Workbooks - Page 57
Specify a New Default File Location - Page 58
Specify a New Default File Location - Page 59
Set the Default Font and Font Size for New Workbooks - Page 60
Set the Default Font and Font Size for New Workbooks - Page 61
Set the Default Number of Worksheets for New Workbooks - Page 62
Set the Default Number of Worksheets for New Workbooks - Page 63
Repair a Corrupted Workbook File - Page 64
Repair a Corrupted Workbook File - Page 65
Convert a Workbook to a PDF File - Page 66
Convert a Workbook to a PDF File - Page 67
Create a Workbook Template - Page 68
Create a Workbook Template - Page 69
Create a New Workbook from an Existing File - Page 70
Create a New Workbook from an Existing File - Page 71
Compare Two Workbooks Side by Side - Page 72
Compare Two Workbooks Side by Side - Page 73
Check for Features Not Supported by Earlier Excel Versions - Page 74
Check for Features Not Supported by Earlier Excel Versions - Page 75
Chapter 3: Customizing Worksheet Presentation - Page 76
Chapter 3: Customizing Worksheet Presentation - Page 77
Create Custom Numeric Formats - Page 78
Create Custom Numeric Formats - Page 79
Create Custom Date and Time Formats - Page 80
Create Custom Date and Time Formats - Page 81
Set the Default Width for All Columns - Page 82
Set the Default Width for All Columns - Page 83
Hide Columns and Rows - Page 84
Hide Columns and Rows - Page 85
Create a Custom Fill List - Page 86
Create a Custom Fill List - Page 87
Maximize Work Space by Turning Off Window Elements - Page 88
Maximize Work Space by Turning Off Window Elements - Page 89
Create a Custom Cell Style - Page 90
Create a Custom Cell Style - Page 91
Build a Custom Table Style - Page 92
Build a Custom Table Style - Page 93
Create a Custom Color Scheme - Page 94
Create a Custom Color Scheme - Page 95
Create a Custom Font Scheme - Page 96
Create a Custom Font Scheme - Page 97
Save a Custom Workbook Theme - Page 98
Save a Custom Workbook Theme - Page 99
Build a Custom Header and Footer - Page 100
Build a Custom Header and Footer - Page 101
Customize the Excel Status Bar - Page 102
Minimize the Ribbon - Page 103
Chapter 4: Getting More Out of Formulas - Page 104
Chapter 4: Getting More Out of Formulas - Page 105
Paste a Formula’s Result - Page 106
Paste a Formula’s Result - Page 107
Show Formulas Instead of Results - Page 108
Show Formulas Instead of Results - Page 109
Use a Watch Window to Monitor a Cell Value - Page 110
Use a Watch Window to Monitor a Cell Value - Page 111
Use Absolute Cell References in a Formula - Page 112
Use Absolute Cell References in a Formula - Page 113
Create an Array Formula - Page 114
Create an Array Formula - Page 115
Apply Range Names to Formulas - Page 116
Apply Range Names to Formulas - Page 117
Create a Link to Another Worksheet - Page 118
Create a Link to Another Worksheet - Page 119
Combine Two Ranges Arithmetically - Page 120
Combine Two Ranges Arithmetically - Page 121
Troubleshoot a Formula by Stepping Through Each Part - Page 122
Troubleshoot a Formula by Stepping Through Each Part - Page 123
Skip Data Tables When Calculating Workbooks - Page 124
Skip Data Tables When Calculating Workbooks - Page 125
Turn On Iterative Calculations - Page 126
Turn On Iterative Calculations - Page 127
Display Text Rather than Error Values - Page 128
Display Text Rather than Error Values - Page 129
Check for Formula Errors in a Worksheet - Page 130
Check for Formula Errors in a Worksheet - Page 131
Audit a Formula to Locate Errors - Page 132
Audit a Formula to Locate Errors - Page 133
Chapter 5: Analyzing Excel Data - Page 134
Chapter 5: Analyzing Excel Data - Page 135
Highlight Cells That Meet Some Criteria - Page 136
Highlight Cells That Meet Some Criteria - Page 137
Highlight the Top or Bottom Values in a Range - Page 138
Highlight the Top or Bottom Values in a Range - Page 139
Analyze Cell Values with Data Bars - Page 140
Analyze Cell Values with Data Bars - Page 141
Analyze Cell Values with Color Scales - Page 142
Analyze Cell Values with Color Scales - Page 143
Analyze Cell Values with Icon Sets - Page 144
Analyze Cell Values with Icon Sets - Page 145
Create a Custom Conditional Formatting Rule - Page 146
Create a Custom Conditional Formatting Rule - Page 147
Calculate Multiple Solutions to a Formula - Page 148
Calculate Multiple Solutions to a Formula - Page 149
Plug Multiple Input Values into a Formula - Page 150
Plug Multiple Input Values into a Formula - Page 151
Plug Multiple Input Values into a Formula - Page 152
Plug Multiple Input Values into a Formula - Page 153
Calculate the Break-Even Point - Page 154
Calculate the Break-Even Point - Page 155
Consolidate Data from Multiple Worksheets - Page 156
Consolidate Data from Multiple Worksheets - Page 157
Consolidate Data from Multiple Worksheets - Page 158
Consolidate Data from Multiple Worksheets - Page 159
Filter Table Data - Page 160
Filter Table Data - Page 161
Remove Duplicate Values from a Table - Page 162
Remove Duplicate Values from a Table - Page 163
Create an Outline Automatically - Page 164
Create an Outline Automatically - Page 165
Enable the Analysis ToolPak Add-In - Page 166
Enable the Analysis ToolPak Add-In - Page 167
Chapter 6: Analyzing Data with PivotTables - Page 168
Chapter 6: Analyzing Data with PivotTables - Page 169
Build a PivotTable from an Excel Table - Page 170
Build a PivotTable from an Excel Table - Page 171
Create a PivotTable from External Data - Page 172
Create a PivotTable from External Data - Page 173
Create a PivotTable from External Data - Page 174
Create a PivotTable from External Data - Page 175
Refresh PivotTable Data - Page 176
Refresh PivotTable Data - Page 177
Add Multiple Fields to the Row or Column Area - Page 178
Add Multiple Fields to the Row or Column Area - Page 179
Add Multiple Fields to the Data Area - Page 180
Add Multiple Fields to the Data Area - Page 181
Move a Field to a Different Area - Page 182
Move a Field to a Different Area - Page 183
Apply a Report Filter - Page 184
Apply a Report Filter - Page 185
Apply a PivotTable Quick Style - Page 186
Apply a PivotTable Quick Style - Page 187
Change the PivotTable Summary Calculation - Page 188
Change the PivotTable Summary Calculation - Page 189
Customize the PivotTable Field List - Page 190
Create a PivotChart from a PivotTable - Page 191
Chapter 7: Importing Data into Excel - Page 192
Chapter 7: Importing Data into Excel - Page 193
Understanding External Data - Page 194
Understanding External Data - Page 195
Import Data from a Data Source - Page 196
Import Data from a Data Source - Page 197
Import Data from an Access Table - Page 198
Import Data from an Access Table - Page 199
Import Data from a Word Table - Page 200
Import Data from a Word Table - Page 201
Import Data from a Text File - Page 202
Import Data from a Text File - Page 203
Import Data from a Text File - Page 204
Import Data from a Text File - Page 205
Import Data from a Web Page - Page 206
Import Data from a Web Page - Page 207
Import Data from an XML File - Page 208
Import Data from an XML File - Page 209
Refresh Imported Data - Page 210
Refresh Imported Data - Page 211
Separate Cell Text into Columns - Page 212
Separate Cell Text into Columns - Page 213
Chapter 8: Querying Data Sources - Page 214
Chapter 8: Querying Data Sources - Page 215
Understanding Microsoft Query - Page 216
Understanding Microsoft Query - Page 217
Define a Data Source - Page 218
Define a Data Source - Page 219
Define a Data Source - Page 220
Define a Data Source - Page 221
Start Microsoft Query - Page 222
Tour the Microsoft Query Window - Page 223
Add a Table to the Query - Page 224
Add a Table to the Query - Page 225
Add Fields to the Query - Page 226
Add Fields to the Query - Page 227
Filter the Records with Query Criteria - Page 228
Filter the Records with Query Criteria - Page 229
Sort the Query Records - Page 230
Sort the Query Records - Page 231
Return the Query Results - Page 232
Return the Query Results - Page 233
Chapter 9: Protecting Excel Data - Page 234
Chapter 9: Protecting Excel Data - Page 235
Open a Read-Only Version of a Workbook - Page 236
Open a Read-Only Version of a Workbook - Page 237
Mark a Workbook as Final to Avoid Accidental Editing - Page 238
Mark a Workbook as Final to Avoid Accidental Editing - Page 239
Protect Workbooks by Shortening the AutoRecover Interval - Page 240
Protect Workbooks by Shortening the AutoRecover Interval - Page 241
Specify the Cells that Users Can Edit - Page 242
Specify the Cells that Users Can Edit - Page 243
Hide a Formula - Page 244
Hide a Formula - Page 245
Protect a Range with a Password - Page 246
Protect a Range with a Password - Page 247
Protect the Data in a Worksheet - Page 248
Protect the Data in a Worksheet - Page 249
Protect a Workbook’s Windows and Structure - Page 250
Protect a Workbook’s Windows and Structure - Page 251
Restore a Previous Version of a Workbook - Page 252
Restore a Previous Version of a Workbook - Page 253
Hide a Worksheet - Page 254
Hide a Worksheet - Page 255
Track Workbook Changes - Page 256
Track Workbook Changes - Page 257
Chapter 10: Maximizing Excel Security and Privacy - Page 258
Chapter 10: Maximizing Excel Security and Privacy - Page 259
Open a Workbook in Protected View - Page 260
Open a Workbook in Protected View - Page 261
Block Dangerous Excel File Types - Page 262
Block Dangerous Excel File Types - Page 263
Set the Macro Security Level - Page 264
Set the Macro Security Level - Page 265
Digitally Sign Your Excel Macros - Page 266
Digitally Sign Your Excel Macros - Page 267
Create a Trusted Location for Opening Files - Page 268
Create a Trusted Location for Opening Files - Page 269
Inspect a Workbook for Private Data - Page 270
Inspect a Workbook for Private Data - Page 271
Assign a Password to a Workbook - Page 272
Assign a Password to a Workbook - Page 273
Turn On Parental Control in Excel - Page 274
Turn On Parental Control in Excel - Page 275
Disable External Data Connections and Links - Page 276
Disable External Data Connections and Links - Page 277
Apply a Digital Signature to a Workbook - Page 278
Apply a Digital Signature to a Workbook - Page 279
Chapter 11: Learning VBA Basics - Page 280
Chapter 11: Learning VBA Basics - Page 281
Record a Macro - Page 282
Record a Macro - Page 283
Open the VBA Editor - Page 284
Open the VBA Editor - Page 285
Explore the Excel Object Model - Page 286
Explore the Excel Object Model - Page 287
Add a Macro to a Module - Page 288
Add a Macro to a Module - Page 289
Run a Macro - Page 290
Run a Macro - Page 291
Assign a Shortcut Key to a Macro - Page 292
Assign a Shortcut Key to a Macro - Page 293
Assign a Macro to the Quick Access Toolbar - Page 294
Assign a Macro to the Quick Access Toolbar - Page 295
Assign a Macro to the Ribbon - Page 296
Assign a Macro to the Ribbon - Page 297