101 Excel 2013 Tips, Tricks and Timesavers

About the Author......Page 5
Table of Contents......Page 7
What You Should Have......Page 15
The Ribbon......Page 16
What the icons mean......Page 17
About the Power Utility Pak Offer......Page 18
Part I: Workbooks and Files......Page 19
Tips and Where to Find Them......Page 20
Cosmetic changes......Page 21
Using options on the View tab......Page 22
Hiding the status bar......Page 23
Adding new commands to the Quick Access toolbar......Page 24
Performing other Quick Access toolbar actions......Page 27
How to customize the Ribbon......Page 28
What causes Protected View?......Page 31
Forcing a file to open in Normal view......Page 32
Recovering unsaved work......Page 34
Tip 6: Using a Workbook in a Browser......Page 36
Send an electronic copy in the form of a PDF file......Page 38
Send an MHTML file......Page 39
Tip 8: Generating a List of Filenames......Page 41
Tip 9: Generating a List of Sheet Names......Page 43
Tip 10: Using Document Themes......Page 46
Applying a theme......Page 48
Customizing a theme......Page 49
The Office Compatibility Pack......Page 51
Checking compatibility......Page 52
Tip 12: Where to Change Printer Settings......Page 53
Part II: Formatting......Page 55
Tips and Where to Find Them......Page 56
Tip 13: Working with Merged Cells......Page 57
Potential problems with merged cells......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
Using the Style gallery......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
Custom number format codes......Page 69
Tip 17: Using Custom Number Formats to Scale Values......Page 72
Using a bullet character......Page 74
Using SmartArt......Page 75
Displaying alternate row shading......Page 76
Creating checkerboard shading......Page 77
Shading groups of rows......Page 78
Tip 20: Formatting Individual Characters in a Cell......Page 79
Painting basics......Page 80
Format Painter variations......Page 81
Tip 22: Inserting a Watermark......Page 82
Using concatenation......Page 84
Using a custom number format......Page 85
Tip 24: Avoiding Font Substitution for Small Point Sizes......Page 86
Tip 25: Updating Old Fonts......Page 89
Part III: Formulas......Page 93
Tips and Where to Find Them......Page 94
Tip 26: Resizing the Formula Bar......Page 95
About the Watch Window......Page 97
Navigating with the Watch Window......Page 98
Tip 28: Learning Some AutoSum Tricks......Page 99
Using absolute references......Page 101
Using mixed references......Page 102
Using the IFERROR function......Page 104
Using the ISERROR function......Page 105
Tip 31: Creating Worksheet-Level Names......Page 106
Tip 32: Using Named Constants......Page 108
About the HYPERLINK function......Page 110
A practical example using HYPERLINK......Page 111
Tip 34: Looking Up an Exact Value......Page 113
Using a formula......Page 115
Using implicit intersection......Page 116
Tip 36: Performing a Two-Column Lookup......Page 117
Martin Luther King Jr. Day......Page 119
Independence Day......Page 120
Christmas Day......Page 121
Method 3......Page 122
Use three columns......Page 124
Use custom functions......Page 125
Use a different product......Page 127
Tip 40: Displaying a Live Calendar in a Range......Page 128
Cell counting method......Page 130
Standard formula method......Page 131
Rounding to the nearest multiple......Page 132
Using the INT and TRUNC functions......Page 133
Rounding to n significant digits......Page 134
Tip 43: Converting Between Measurement Systems......Page 135
Tip 44: Counting Nonduplicated Entries in a Range......Page 137
Tip 45: Using the AGGREGATE Function......Page 139
Tip 46: Making an Exact Copy of a Range of Formulas......Page 142
Tip 47: Using the Background Error-Checking Features......Page 144
Workbook analysis......Page 146
Compare files......Page 147
Other options......Page 148
Hiding and locking formula cells......Page 149
Protecting the worksheet......Page 150
Specifying rows indirectly......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
Part IV: Working with Data......Page 159
Tips and Where to Find Them......Page 160
Selecting a range by using the Shift and arrow keys......Page 161
Selecting noncontiguous ranges......Page 162
Selecting multisheet ranges......Page 163
Tip 54: Automatically Filling a Range with a Series......Page 165
Tip 55: Fixing Trailing Minus Signs......Page 168
Tip 56: Restricting Cursor Movement to Input Cells......Page 169
Transforming data without formulas......Page 171
Transforming data by using temporary formulas......Page 172
Tip 58: Creating a Drop-Down List in a Cell......Page 174
Tip 59: Comparing Two Ranges by Using Conditional Formatting......Page 176
Tip 60: Finding Duplicates by Using Conditional Formatting......Page 179
Entering credit card numbers manually......Page 182
Importing credit card numbers......Page 183
Tip 62: Identifying Excess Spaces......Page 184
Using Paste Special......Page 187
Using the TRANSPOSE function......Page 188
Changing the case of text......Page 190
Extracting first names......Page 191
Potential problems......Page 192
Tip 65: Using Flash Fill to Combine Data......Page 193
Tip 66: Inserting Stock Information......Page 195
Behind the scenes......Page 196
Pasting static information......Page 198
Pasting refreshable information......Page 199
Opening the web page directly......Page 201
Tip 68: Importing a Text File into a Worksheet Range......Page 202
Tip 69: Using the Quick Analysis Feature......Page 204
Tip 70: Filling the Gaps in a Report......Page 206
Tip 71: Performing Inexact Searches......Page 208
Using the speech commands......Page 210
Using copy and paste......Page 212
Using Word 2013 as an intermediary......Page 214
Part V: Tables and Pivot Tables......Page 217
Tips and Where to Find Them......Page 218
Understanding what a table is......Page 219
Range versus table......Page 220
Limitations of using a table......Page 221
Working with the Total row......Page 222
Using formulas within a table......Page 223
Referencing data in a table......Page 225
Tip 76: Numbering Table Rows Automatically......Page 226
Tip 77: Identifying Data Appropriate for a Pivot Table......Page 228
Inserting subtotals......Page 232
Using Excel’s PivotTable feature......Page 234
Tip 79: Controlling References to Cells Within a Pivot Table......Page 236
Tip 80: Creating a Quick Frequency Tabulation......Page 238
Tip 81: Grouping Items by Date in a Pivot Table......Page 241
Tip 82: Creating Pivot Tables with Multiple Groupings......Page 244
Using slicers......Page 246
Using a timeline......Page 248
Part VI: Charts and Graphics......Page 251
Tips and Where to Find Them......Page 252
Tip 84: Understanding Recommended Charts......Page 253
Modifying a chart style or colors......Page 255
Filtering chart data......Page 256
Tip 86: Making Charts the Same Size......Page 257
Creating a template......Page 259
Using a template......Page 260
Inserting a preconfigured combination chart......Page 261
Customizing a combination chart......Page 262
Tip 89: Handling Missing Data in a Chart......Page 264
Tip 90: Using High-Low Lines in a Chart......Page 266
Tip 91: Using Multi-Level Category Labels......Page 267
Tip 92: Linking Chart Text to Cells......Page 269
Converting a chart into a picture......Page 271
Converting range references into arrays......Page 272
Using conditional formatting data bars......Page 274
Using formulas to display repeating characters......Page 275
Simple pie charts......Page 278
Simple line charts......Page 279
A gauge chart......Page 280
Tip 96: Applying Chart Data Labels from a Range......Page 282
Grouping charts......Page 284
Grouping other objects......Page 285
Creating a static image of a range......Page 287
Creating a live image of a range......Page 288
Saving a range as a graphic image......Page 289
Formatting a comment......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 101: Saving Shapes, Charts, and Ranges as Images......Page 295
Index......Page 297