101 Excel 2013 Tips, Tricks and Timesavers

Informationen zum Autor 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. Klappentext 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 tables101 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! Zusammenfassung 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. Inhaltsverzeichnis 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 ...

List of contents

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
 
Tip 2: Customizing the Quick Access Toolbar 10
 
Tip 3: Customizing the Ribbon 14
 
Tip 4: Understanding Protected View 17
 
Tip 5: Understanding AutoRecover 20
 
Tip 6: Using a Workbook in a Browser 22
 
Tip 7: Saving to a Read-Only Format 24
 
Tip 8: Generating a List of Filenames 27
 
Tip 9: Generating a List of Sheet Names 29
 
Tip 10: Using Document Themes 32
 
Tip 11: Understanding Excel Compatibility Issues 37
 
Tip 12: Where to Change Printer Settings 39
 
Part II: Formatting
 
Tip 13: Working with Merged Cells 43
 
Tip 14: Indenting Cell Contents 48
 
Tip 15: Using Named Styles 50
 
Tip 16: Creating Custom Number Formats 54
 
Tip 17: Using Custom Number Formats to Scale Values 58
 
Tip 18: Creating a Bulleted List 60
 
Tip 19: Shading Alternate Rows Using Conditional Formatting 62
 
Tip 20: Formatting Individual Characters in a Cell 65
 
Tip 21: Using the Format Painter 66
 
Tip 22: Inserting a Watermark 68
 
Tip 23: Showing Text and a Value in a Cell 70
 
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
 
Tip 28: Learning Some AutoSum Tricks 85
 
Tip 29: Knowing When to Use Absolute and Mixed References 87
 
Tip 30: Avoiding Error Displays in Formulas 90
 
Tip 31: Creating Worksheet-Level Names 92
 
Tip 32: Using Named Constants 94
 
Tip 33: Sending Personalized E-Mail from Excel 96
 
Tip 34: Looking Up an Exact Value 99
 
Tip 35: Performing a Two-Way Lookup 101
 
Tip 36: Performing a Two-Column Lookup 103
 
Tip 37: Calculating Holidays 105
 
Tip 38: Calculating a Person's Age 108
 
Tip 39: Working with Pre-1900 Dates 110
 
Tip 40: Displaying a Live Calendar in a Range 114
 
Tip 41: Returning the Last Nonblank Cell in a Column or Row 116
 
Tip 42: Various Methods of Rounding Numbers 118
 
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
 
Tip 49: Hiding and Locking Your Formulas 135
 
Tip 50: Using the INDIRECT Function 138
 
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
 
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
 
Tip 58: Creating a Drop-Down List in a Cell 160
 
Tip 59: Comparing Two Ranges by Using Conditional For

Product details

• Authors: J Walkenbach, John Walkenbach, John (J-Walk and Associates Walkenbach, Walkenbach John
• Publisher: Wiley, John and Sons Ltd
• Languages: English
• Product format: Paperback / Softback
• Released: 16.07.2013
• EAN: 9781118642184
• ISBN: 978-1-118-64218-4
• No. of pages: 320
• Subjects: Natural sciences, medicine, IT, technology > IT, data processing > Application software; Excel 2013, Microsoft Excel, End-User Computing, Computer-Ratgeber