Microsoft Excel 2013

Part I: Workbooks and Files - Page 19
Tips and Where to Find Them - Page 20
Tip 1: Changing the Look of Excel - Page 21
Hiding the Ribbon - Page 22
Hiding other elements - Page 23
Tip 2: Customizing the Quick Access Toolbar - Page 24
Tip 2: Customizing the Quick Access Toolbar - Page 25
Tip 2: Customizing the Quick Access Toolbar - Page 26
Performing other Quick Access toolbar actions - Page 27
Tip 3: Customizing the Ribbon - Page 28
Tip 3: Customizing the Ribbon - Page 29
Tip 3: Customizing the Ribbon - Page 30
Tip 4: Understanding Protected View - Page 31
Printing and copying - Page 32
Printing and copying - Page 33
Tip 5: Understanding AutoRecover - Page 34
Tip 5: Understanding AutoRecover - Page 35
Tip 6: Using a Workbook in a Browser - Page 36
Tip 6: Using a Workbook in a Browser - Page 37
Tip 7: Saving to a Read-Only Format - Page 38
Send an MHTML file - Page 39
Send an MHTML file - Page 40
Tip 8: Generating a List of Filenames - Page 41
Tip 8: Generating a List of Filenames - Page 42
Tip 9: Generating a List of Sheet Names - Page 43
Tip 9: Generating a List of Sheet Names - Page 44
Tip 9: Generating a List of Sheet Names - Page 45
Tip 10: Using Document Themes - Page 46
Tip 10: Using Document Themes - Page 47
Applying a theme - Page 48
Customizing a theme - Page 49
Customizing a theme - Page 50
Tip 11: Understanding Excel Compatibility Issues - Page 51
Checking compatibility - Page 52
Tip 12: Where to Change Printer Settings - Page 53
Tip 12: Where to Change Printer Settings - Page 54
Part II: Formatting - Page 55
Tips and Where to Find Them - Page 56
Tip 13: Working with Merged Cells - Page 57
Other merge actions - Page 58
Locating all merged cells - Page 59
Unmerging all merged cells - Page 60
Alternatives to merged cells - Page 61
Tip 14: Indenting Cell Contents - Page 62
Tip 14: Indenting Cell Contents - Page 63
Tip 15: Using Named Styles - Page 64
Modifying an existing style - Page 65
Creating new styles - Page 66
Merging styles from other workbooks - Page 67
Tip 16: Creating Custom Number Formats - Page 68
Parts of a number format string - Page 69
Parts of a number format string - Page 70
Parts of a number format string - Page 71
Tip 17: Using Custom Number Formats to Scale Values - Page 72
Tip 17: Using Custom Number Formats to Scale Values - Page 73
Tip 18: Creating a Bulleted List - Page 74
Using SmartArt - Page 75
Tip 19: Shading Alternate Rows Using Conditional Formatting - Page 76
Creating checkerboard shading - Page 77
Shading groups of rows - Page 78
Tip 20: Formatting Individual Characters in a Cell - Page 79
Tip 21: Using the Format Painter - Page 80
Format Painter variations - Page 81
Tip 22: Inserting a Watermark - Page 82
Tip 22: Inserting a Watermark - Page 83
Tip 23: Showing Text and a Value in a Cell - Page 84
Using the TEXT function - Page 85
Tip 24: Avoiding Font Substitution for Small Point Sizes - Page 86
Tip 24: Avoiding Font Substitution for Small Point Sizes - Page 87
Tip 24: Avoiding Font Substitution for Small Point Sizes - Page 88
Tip 25: Updating Old Fonts - Page 89
Tip 25: Updating Old Fonts - Page 90
Tip 25: Updating Old Fonts - Page 91
Tip 25: Updating Old Fonts - Page 92
Part III: Formulas - Page 93
Tips and Where to Find Them - Page 94
Tip 26: Resizing the Formula Bar - Page 95
Tip 26: Resizing the Formula Bar - Page 96
Tip 27: Monitoring Formula Cells from Any Location - Page 97
Customizing the Watch Window - Page 98
Tip 28: Learning Some AutoSum Tricks - Page 99
Tip 28: Learning Some AutoSum Tricks - Page 100
Tip 29: Knowing When to Use Absolute and Mixed References - Page 101
Using mixed references - Page 102
Using mixed references - Page 103
Tip 30: Avoiding Error Displays in Formulas - Page 104
Using the ISERROR function - Page 105
Tip 31: Creating Worksheet-Level Names - Page 106
Tip 31: Creating Worksheet-Level Names - Page 107
Tip 32: Using Named Constants - Page 108
Tip 32: Using Named Constants - Page 109
Tip 33: Sending Personalized E-Mail from Excel - Page 110
A practical example using HYPERLINK - Page 111
A practical example using HYPERLINK - Page 112
Tip 34: Looking Up an Exact Value - Page 113
Tip 34: Looking Up an Exact Value - Page 114
Tip 35: Performing a Two-Way Lookup - Page 115
Using implicit intersection - Page 116
Tip 36: Performing a Two-Column Lookup - Page 117
Tip 36: Performing a Two-Column Lookup - Page 118
Tip 37: Calculating Holidays - Page 119
Presidents’ Day - Page 120
Labor Day - Page 121
Tip 38: Calculating a Person’s Age - Page 122
Tip 38: Calculating a Person’s Age - Page 123
Tip 39: Working with Pre-1900 Dates - Page 124
Use custom functions - Page 125
Use custom functions - Page 126
Use a different product - Page 127
Tip 40: Displaying a Live Calendar in a Range - Page 128
Tip 40: Displaying a Live Calendar in a Range - Page 129
Tip 41: Returning the Last Nonblank Cell in a Column or Row - Page 130
Array formula method - Page 131
Tip 42: Various Methods of Rounding Numbers - Page 132
Rounding currency values - Page 133
Rounding to n significant digits - Page 134
Tip 43: Converting Between Measurement Systems - Page 135
Tip 43: Converting Between Measurement Systems - Page 136
Tip 44: Counting Nonduplicated Entries in a Range - Page 137
Tip 44: Counting Nonduplicated Entries in a Range - Page 138
Tip 45: Using the AGGREGATE Function - Page 139
Tip 45: Using the AGGREGATE Function - Page 140
Tip 45: Using the AGGREGATE Function - Page 141
Tip 46: Making an Exact Copy of a Range of Formulas - Page 142
Tip 46: Making an Exact Copy of a Range of Formulas - Page 143
Tip 47: Using the Background Error-Checking Features - Page 144
Tip 47: Using the Background Error-Checking Features - Page 145
Tip 48: Using the Inquire Add-In - Page 146
Diagram tools - Page 147
Other options - Page 148
Tip 49: Hiding and Locking Your Formulas - Page 149
Unlocking nonformula cells - Page 150
Unlocking nonformula cells - Page 151
Tip 50: Using the INDIRECT Function - Page 152
Specifying worksheet names indirectly - Page 153
Making a cell reference unchangeable - Page 154
Tip 51: Formula Editing in Dialog Boxes - Page 155
Tip 52: Converting a Vertical Range to a Table - Page 156
Tip 52: Converting a Vertical Range to a Table - Page 157
Tip 52: Converting a Vertical Range to a Table - Page 158
Part IV: Working with Data - Page 159
Tips and Where to Find Them - Page 160
Tip 53: Selecting Cells Efficiently - Page 161
Selecting the current region - Page 162
Selecting entire rows - Page 163
Selecting entire rows - Page 164
Tip 54: Automatically Filling a Range with a Series - Page 165
Tip 54: Automatically Filling a Range with a Series - Page 166
Tip 54: Automatically Filling a Range with a Series - Page 167
Tip 55: Fixing Trailing Minus Signs - Page 168
Tip 56: Restricting Cursor Movement to Input Cells - Page 169
Tip 56: Restricting Cursor Movement to Input Cells - Page 170
Tip 57: Transforming Data with and Without Using Formulas - Page 171
Transforming data by using temporary formulas - Page 172
Transforming data by using temporary formulas - Page 173
Tip 58: Creating a Drop-Down List in a Cell - Page 174
Tip 58: Creating a Drop-Down List in a Cell - Page 175
Tip 59: Comparing Two Ranges by Using Conditional Formatting - Page 176
Tip 59: Comparing Two Ranges by Using Conditional Formatting - Page 177
Tip 59: Comparing Two Ranges by Using Conditional Formatting - Page 178
Tip 60: Finding Duplicates by Using Conditional Formatting - Page 179
Tip 60: Finding Duplicates by Using Conditional Formatting - Page 180
Tip 60: Finding Duplicates by Using Conditional Formatting - Page 181
Tip 61: Working with Credit Card Numbers - Page 182
Importing credit card numbers - Page 183
Tip 62: Identifying Excess Spaces - Page 184
Tip 62: Identifying Excess Spaces - Page 185
Tip 62: Identifying Excess Spaces - Page 186
Tip 63: Transposing a Range - Page 187
Using the TRANSPOSE function - Page 188
Using the TRANSPOSE function - Page 189
Tip 64: Using Flash Fill to Extract Data - Page 190
Extracting last names - Page 191
Extracting middle names - Page 192
Tip 65: Using Flash Fill to Combine Data - Page 193
Tip 65: Using Flash Fill to Combine Data - Page 194
Tip 66: Inserting Stock Information - Page 195
Hiding irrelevant rows and columns - Page 196
Hiding irrelevant rows and columns - Page 197
Tip 67: Getting Data from a Web Page - Page 198
Pasting refreshable information - Page 199
Pasting refreshable information - Page 200
Opening the web page directly - Page 201
Tip 68: Importing a Text File into a Worksheet Range - Page 202
Tip 68: Importing a Text File into a Worksheet Range - Page 203
Tip 69: Using the Quick Analysis Feature - Page 204
Tip 69: Using the Quick Analysis Feature - Page 205
Tip 70: Filling the Gaps in a Report - Page 206
Tip 70: Filling the Gaps in a Report - Page 207
Tip 71: Performing Inexact Searches - Page 208
Tip 71: Performing Inexact Searches - Page 209
Tip 72: Proofing Your Data with Audio - Page 210
Tip 72: Proofing Your Data with Audio - Page 211
Tip 73: Getting Data from a PDF File - Page 212
Tip 73: Getting Data from a PDF File - Page 213
Using Word 2013 as an intermediary - Page 214
Using Word 2013 as an intermediary - Page 215
Using Word 2013 as an intermediary - Page 216
Part V: Tables and Pivot Tables - Page 217
Tips and Where to Find Them - Page 218
Tip 74: Understanding Tables - Page 219
Range versus table - Page 220
Limitations of using a table - Page 221
Tip 75: Using Formulas with a Table - Page 222
Using formulas within a table - Page 223
Using formulas within a table - Page 224
Referencing data in a table - Page 225
Tip 76: Numbering Table Rows Automatically - Page 226
Tip 76: Numbering Table Rows Automatically - Page 227
Tip 77: Identifying Data Appropriate for a Pivot Table - Page 228
Tip 77: Identifying Data Appropriate for a Pivot Table - Page 229
Tip 77: Identifying Data Appropriate for a Pivot Table - Page 230
Tip 77: Identifying Data Appropriate for a Pivot Table - Page 231
Tip 78: Using a Pivot Table Instead of Formulas - Page 232
Tip 78: Using a Pivot Table Instead of Formulas - Page 233
Using formulas - Page 234
Using formulas - Page 235
Tip 79: Controlling References to Cells Within a Pivot Table - Page 236
Tip 79: Controlling References to Cells Within a Pivot Table - Page 237
Tip 80: Creating a Quick Frequency Tabulation - Page 238
Tip 80: Creating a Quick Frequency Tabulation - Page 239
Tip 80: Creating a Quick Frequency Tabulation - Page 240
Tip 81: Grouping Items by Date in a Pivot Table - Page 241
Tip 81: Grouping Items by Date in a Pivot Table - Page 242
Tip 81: Grouping Items by Date in a Pivot Table - Page 243
Tip 82: Creating Pivot Tables with Multiple Groupings - Page 244
Tip 82: Creating Pivot Tables with Multiple Groupings - Page 245
Tip 83: Using Pivot Table Slicers and Timelines - Page 246
Tip 83: Using Pivot Table Slicers and Timelines - Page 247
Using a timeline - Page 248
Using a timeline - Page 249
Using a timeline - Page 250
Part VI: Charts and Graphics - Page 251
Tips and Where to Find Them - Page 252
Tip 84: Understanding Recommended Charts - Page 253
Tip 84: Understanding Recommended Charts - Page 254
Tip 85: Customizing Charts - Page 255
Filtering chart data - Page 256
Tip 86: Making Charts the Same Size - Page 257
Tip 86: Making Charts the Same Size - Page 258
Tip 87: Creating a Chart Template - Page 259
Using a template - Page 260
Tip 88: Creating a Combination Chart - Page 261
Customizing a combination chart - Page 262
Customizing a combination chart - Page 263
Tip 89: Handling Missing Data in a Chart - Page 264
Tip 89: Handling Missing Data in a Chart - Page 265
Tip 90: Using High-Low Lines in a Chart - Page 266
Tip 91: Using Multi-Level Category Labels - Page 267
Tip 91: Using Multi-Level Category Labels - Page 268
Tip 92: Linking Chart Text to Cells - Page 269
Tip 92: Linking Chart Text to Cells - Page 270
Tip 93: Freezing a Chart - Page 271
Converting range references into arrays - Page 272
Converting range references into arrays - Page 273
Tip 94: Creating a Chart Directly in a Range - Page 274
Using formulas to display repeating characters - Page 275
Using formulas to display repeating characters - Page 276
Using formulas to display repeating characters - Page 277
Tip 95: Creating Minimalistic Charts - Page 278
Simple line charts - Page 279
A gauge chart - Page 280
A gauge chart - Page 281
Tip 96: Applying Chart Data Labels from a Range - Page 282
Tip 96: Applying Chart Data Labels from a Range - Page 283
Tip 97: Grouping Charts and Other Objects - Page 284
Grouping other objects - Page 285
Grouping other objects - Page 286
Tip 98: Taking Pictures of Ranges - Page 287
Creating a live image of a range - Page 288
Saving a range as a graphic image - Page 289
Tip 99: Changing the Look of Cell Comments - Page 290
Changing the shape of a comment - Page 291
Adding an image to a cell comment - Page 292
Tip 100: Enhancing Images - Page 293
Tip 100: Enhancing Images - Page 294
Tip 101: Saving Shapes, Charts, and Ranges as Images - Page 295
Tip 101: Saving Shapes, Charts, and Ranges as Images - Page 296