101 Excel 2013 Tips, Tricks and Timesavers

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!

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

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.