101 Excel 2013 Tips, Tricks and Timesavers
John Wiley & Sons Inc (Verlag)
978-1-118-64218-4 (ISBN)
Get the most out of Excel 2013 with this exceptional advice from Mr. Spreadsheet himself!
Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, mixing nesting limits, and more, 101 Excel 2013 Tips, Tricks, & Timesavers will save you time and help you avoid common spreadsheet stumbling blocks.
Reveals ways to maximize the power of Excel to create robust applications
Draws on John Walkenbach's years of experience using Excel and writing more than 50 books
Shares tips and tricks for dealing with function arguments, creating add-ins, using UserForms, working with dynamic chart data, and changing data entry orientation
Provides shortcuts and helpful techniques for sorting more than three columns, entering fake data for testing purposes, and setting up powerful pivot tables
101 Excel 2013 Tips, Tricks, & Timesavers is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel!
John Walkenbach, arguably the foremost authority on Excel, has written 50+ books, including the bestselling Excel Bible, Excel Formulas, and Excel Power Programming with VBA, as well as more than 300 articles for publications such as PC World, InfoWorld, and Windows. He created the award-winning Power Utility Pak, and provides Excel information and insight at www.spreadsheetpage.com.
Introduction 1
What You Should Know 1
What You Should Have 1
Conventions in This Book 2
Formula listings 2
Key names 2
The Ribbon 2
Functions, procedures, and named ranges 3
Mouse conventions 3
What the icons mean 3
How This Book Is Organized 4
How to Use This Book 4
About the Power Utility Pak Offer 4
Part I: Workbooks and Files
Tip 1: Changing the Look of Excel 7
Cosmetic changes 7
Hiding the Ribbon 8
Using options on the View tab 8
Hiding other elements 9
Hiding the status bar 9
Tip 2: Customizing the Quick Access Toolbar 10
About the Quick Access toolbar 10
Adding new commands to the Quick Access toolbar 10
Performing other Quick Access toolbar actions 13
Tip 3: Customizing the Ribbon 14
How to customize the Ribbon 14
Tip 4: Understanding Protected View 17
What causes Protected View? 17
Printing and copying 18
Forcing a file to open in Normal view 18
Tip 5: Understanding AutoRecover 20
Recovering versions of the current workbook 20
Recovering unsaved work 20
Tip 6: Using a Workbook in a Browser 22
Tip 7: Saving to a Read-Only Format 24
Send a printed copy 24
Send an electronic copy in the form of a PDF file. 24
Send an MHTML file 25
Tip 8: Generating a List of Filenames 27
Tip 9: Generating a List of Sheet Names 29
Tip 10: Using Document Themes 32
Applying a theme 34
Customizing a theme 35
Tip 11: Understanding Excel Compatibility Issues 37
The Excel 2013 file formats 37
The Office Compatibility Pack 37
Checking compatibility 38
Tip 12: Where to Change Printer Settings 39
Part II: Formatting
Tip 13: Working with Merged Cells 43
Other merge actions 44
Potential problems with merged cells 44
Locating all merged cells 45
Unmerging all merged cells 46
Alternatives to merged cells 47
Tip 14: Indenting Cell Contents 48
Tip 15: Using Named Styles 50
Using the Style gallery 50
Modifying an existing style 51
Creating new styles 52
Merging styles from other workbooks 53
Tip 16: Creating Custom Number Formats 54
Parts of a number format string 55
Custom number format codes 55
Tip 17: Using Custom Number Formats to Scale Values 58
Tip 18: Creating a Bulleted List 60
Using a bullet character. 60
Using SmartArt 61
Tip 19: Shading Alternate Rows Using Conditional Formatting 62
Displaying alternate row shading 62
Creating checkerboard shading 63
Shading groups of rows 64
Tip 20: Formatting Individual Characters in a Cell 65
Tip 21: Using the Format Painter 66
Painting basics 66
Format Painter variations 67
Tip 22: Inserting a Watermark 68
Tip 23: Showing Text and a Value in a Cell 70
Using concatenation 70
Using the TEXT function 71
Using a custom number format 71
Tip 24: Avoiding Font Substitution for Small Point Sizes 72
Tip 25: Updating Old Fonts 75
Part III: Formulas
Tip 26: Resizing the Formula Bar 81
Tip 27: Monitoring Formula Cells from Any Location 83
About the Watch Window 83
Customizing the Watch Window 84
Navigating with the Watch Window 84
Tip 28: Learning Some AutoSum Tricks 85
Tip 29: Knowing When to Use Absolute and Mixed References 87
Using absolute references 87
Using mixed references 88
Tip 30: Avoiding Error Displays in Formulas 90
Using the IFERROR function 90
Using the ISERROR function 91
Tip 31: Creating Worksheet-Level Names 92
Tip 32: Using Named Constants 94
Tip 33: Sending Personalized E-Mail from Excel 96
About the HYPERLINK function 96
A practical example using HYPERLINK 97
Tip 34: Looking Up an Exact Value 99
Tip 35: Performing a Two-Way Lookup 101
Using a formula 101
Using implicit intersection 102
Tip 36: Performing a Two-Column Lookup 103
Tip 37: Calculating Holidays 105
New Year’s Day 105
Martin Luther King Jr. Day 105
Presidents’ Day 106
Easter 106
Memorial Day 106
Independence Day 106
Labor Day 107
Columbus Day 107
Veterans Day 107
Thanksgiving Day 107
Christmas Day 107
Tip 38: Calculating a Person’s Age 108
Method 1 108
Method 2 108
Method 3 108
Tip 39: Working with Pre-1900 Dates 110
Use three columns 110
Use custom functions 111
Use a different product 113
Tip 40: Displaying a Live Calendar in a Range 114
Tip 41: Returning the Last Nonblank Cell in a Column or Row 116
Cell counting method 116
Array formula method 117
Standard formula method 117
Tip 42: Various Methods of Rounding Numbers 118
Rounding to the nearest multiple 118
Rounding currency values 119
Using the INT and TRUNC functions 119
Rounding to n significant digits 120
Tip 43: Converting Between Measurement Systems 121
Tip 44: Counting Nonduplicated Entries in a Range 123
Tip 45: Using the AGGREGATE Function 125
Tip 46: Making an Exact Copy of a Range of Formulas 128
Tip 47: Using the Background Error-Checking Features 130
Tip 48: Using the Inquire Add-In 132
Workbook analysis 132
Diagram tools 133
Compare files 133
Other options 134
Tip 49: Hiding and Locking Your Formulas 135
Hiding and locking formula cells 135
Unlocking nonformula cells 136
Protecting the worksheet 136
Tip 50: Using the INDIRECT Function 138
Specifying rows indirectly 138
Specifying worksheet names indirectly 139
Making a cell reference unchangeable 140
Tip 51: Formula Editing in Dialog Boxes 141
Tip 52: Converting a Vertical Range to a Table 142
Part IV: Working with Data
Tip 53: Selecting Cells Efficiently 147
Selecting a range by using the Shift and arrow keys 147
Selecting the current region 148
Selecting a range by Shift+clicking 148
Selecting noncontiguous ranges 148
Selecting entire rows 149
Selecting entire columns 149
Selecting multisheet ranges 149
Tip 54: Automatically Filling a Range with a Series 151
Tip 55: Fixing Trailing Minus Signs 154
Tip 56: Restricting Cursor Movement to Input Cells 155
Tip 57: Transforming Data with and Without Using Formulas 157
Transforming data without formulas 157
Transforming data by using temporary formulas 158
Tip 58: Creating a Drop-Down List in a Cell 160
Tip 59: Comparing Two Ranges by Using Conditional Formatting 162
Tip 60: Finding Duplicates by Using Conditional Formatting 165
Tip 61: Working with Credit Card Numbers 168
Entering credit card numbers manually 168
Importing credit card numbers 169
Tip 62: Identifying Excess Spaces 170
Tip 63: Transposing a Range 173
Using Paste Special 173
Using the TRANSPOSE function 174
Tip 64: Using Flash Fill to Extract Data 176
Changing the case of text 176
Extracting last names 177
Extracting first names. 177
Extracting middle names 178
Extracting domain names from URLs 178
Potential problems 178
Tip 65: Using Flash Fill to Combine Data 179
Tip 66: Inserting Stock Information 181
Hiding irrelevant rows and columns 182
Behind the scenes 182
Tip 67: Getting Data from a Web Page 184
Pasting static information 184
Pasting refreshable information 185
Opening the web page directly 187
Tip 68: Importing a Text File into a Worksheet Range 188
Tip 69: Using the Quick Analysis Feature 190
Tip 70: Filling the Gaps in a Report 192
Tip 71: Performing Inexact Searches 194
Tip 72: Proofing Your Data with Audio 196
Adding speech commands to the Ribbon 196
Using the speech commands 196
Tip 73: Getting Data from a PDF File 198
Using copy and paste 198
Using Word 2013 as an intermediary 200
Part V: Tables and Pivot Tables
Tip 74: Understanding Tables 205
Understanding what a table is 205
Range versus table 206
Limitations of using a table 207
Tip 75: Using Formulas with a Table 208
Working with the Total row 208
Using formulas within a table 209
Referencing data in a table 211
Tip 76: Numbering Table Rows Automatically 212
Tip 77: Identifying Data Appropriate for a Pivot Table 214
Tip 78: Using a Pivot Table Instead of Formulas 218
Inserting subtotals 218
Using formulas 220
Using Excel’s PivotTable feature 220
Tip 79: Controlling References to Cells Within a Pivot Table 222
Tip 80: Creating a Quick Frequency Tabulation 224
Tip 81: Grouping Items by Date in a Pivot Table 227
Tip 82: Creating Pivot Tables with Multiple Groupings 230
Tip 83: Using Pivot Table Slicers and Timelines 232
Using slicers 232
Using a timeline 234
Part VI: Charts and Graphics
Tip 84: Understanding Recommended Charts 239
Tip 85: Customizing Charts 241
Adding or removing chart elements 241
Modifying a chart style or colors 241
Filtering chart data 242
Tip 86: Making Charts the Same Size 243
Tip 87: Creating a Chart Template 245
Creating a template 245
Using a template 246
Tip 88: Creating a Combination Chart 247
Inserting a preconfigured combination chart 247
Customizing a combination chart 248
Tip 89: Handling Missing Data in a Chart 250
Tip 90: Using High-Low Lines in a Chart 252
Tip 91: Using Multi-Level Category Labels 253
Tip 92: Linking Chart Text to Cells 255
Tip 93: Freezing a Chart 257
Converting a chart into a picture 257
Converting range references into arrays 258
Tip 94: Creating a Chart Directly in a Range 260
Using conditional formatting data bars 260
Using formulas to display repeating characters 261
Tip 95: Creating Minimalistic Charts 264
Simple column charts 264
Simple pie charts 264
Simple line charts 265
A gauge chart 266
Tip 96: Applying Chart Data Labels from a Range 268
Tip 97: Grouping Charts and Other Objects 270
Grouping charts 270
Grouping other objects 271
Tip 98: Taking Pictures of Ranges 273
Creating a static image of a range 273
Creating a live image of a range 274
Saving a range as a graphic image 275
Tip 99: Changing the Look of Cell Comments 276
Setting up your Quick Access toolbar 276
Formatting a comment 276
Changing the shape of a comment 277
Adding an image to a cell comment 278
Tip 100: Enhancing Images 279
Tip 101: Saving Shapes, Charts, and Ranges as Images 281
Index 283
| Erscheint lt. Verlag | 16.7.2013 |
|---|---|
| Verlagsort | New York |
| Sprache | englisch |
| Maße | 185 x 231 mm |
| Gewicht | 454 g |
| Themenwelt | Informatik ► Office Programme ► Excel |
| Informatik ► Office Programme ► Outlook | |
| ISBN-10 | 1-118-64218-X / 111864218X |
| ISBN-13 | 978-1-118-64218-4 / 9781118642184 |
| Zustand | Neuware |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
