Part III: Formulas | 101 Excel 2013 Tips, Tricks and Timesavers

Book Overview & Buying
Table Of Contents

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach

101 Excel 2013 Tips, Tricks and Timesavers

By: John Walkenbach

Overview of this book

Excel is a popular program. Millions of people throughout the world use it on a regular basis. But it’s a safe bet that the vast majority of users have yet to discover some of the amazing things this product can do. 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! 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.

Free Chapter

Cover

Table of Contents

Title Page

Introduction

Part I: Workbooks and Files

Tip 1: Changing the Look of Excel

Tip 2: Customizing the Quick Access Toolbar

Tip 3: Customizing the Ribbon

Tip 4: Understanding Protected View

Tip 5: Understanding AutoRecover

Tip 6: Using a Workbook in a Browser

Tip 7: Saving to a Read-Only Format

Tip 8: Generating a List of Filenames

Tip 9: Generating a List of Sheet Names

Tip 10: Using Document Themes

Tip 11: Understanding Excel Compatibility Issues

Tip 12: Where to Change Printer Settings

Part II: Formatting

Tip 13: Working with Merged Cells

Tip 14: Indenting Cell Contents

Tip 15: Using Named Styles

Tip 16: Creating Custom Number Formats

Tip 17: Using Custom Number Formats to Scale Values

Tip 18: Creating a Bulleted List

Tip 19: Shading Alternate Rows Using Conditional Formatting

Tip 20: Formatting Individual Characters in a Cell

Tip 21: Using the Format Painter

Tip 22: Inserting a Watermark

Tip 23: Showing Text and a Value in a Cell

Tip 24: Avoiding Font Substitution for Small Point Sizes

Tip 25: Updating Old Fonts

Part III: Formulas

Tip 26: Resizing the Formula Bar

Tip 27: Monitoring Formula Cells from Any Location

Tip 28: Learning Some AutoSum Tricks

Tip 29: Knowing When to Use Absolute and Mixed References

Tip 30: Avoiding Error Displays in Formulas

Tip 31: Creating Worksheet-Level Names

Tip 32: Using Named Constants

Tip 33: Sending Personalized E-Mail from Excel

Tip 34: Looking Up an Exact Value

Tip 35: Performing a Two-Way Lookup

Tip 36: Performing a Two-Column Lookup

Tip 37: Calculating Holidays

Tip 38: Calculating a Person's Age

Tip 39: Working with Pre-1900 Dates

Tip 40: Displaying a Live Calendar in a Range

Tip 41: Returning the Last Nonblank Cell in a Column or Row

Tip 42: Various Methods of Rounding Numbers

Tip 43: Converting Between Measurement Systems

Tip 44: Counting Nonduplicated Entries in a Range

Tip 45: Using the AGGREGATE Function

Tip 46: Making an Exact Copy of a Range of Formulas

Tip 47: Using the Background Error-Checking Features

Tip 48: Using the Inquire Add-In

Tip 49: Hiding and Locking Your Formulas

Tip 50: Using the INDIRECT Function

Tip 51: Formula Editing in Dialog Boxes

Tip 52: Converting a Vertical Range to a Table

Part IV: Working with Data

Tip 53: Selecting Cells Efficiently

Tip 54: Automatically Filling a Range with a Series

Tip 55: Fixing Trailing Minus Signs

Tip 56: Restricting Cursor Movement to Input Cells

Tip 57: Transforming Data with and Without Using Formulas

Tip 58: Creating a Drop-Down List in a Cell

Tip 59: Comparing Two Ranges by Using Conditional Formatting

Tip 60: Finding Duplicates by Using Conditional Formatting

Tip 61: Working with Credit Card Numbers

Tip 62: Identifying Excess Spaces

Tip 63: Transposing a Range

Tip 64: Using Flash Fill to Extract Data

Tip 65: Using Flash Fill to Combine Data

Tip 66: Inserting Stock Information

Tip 67: Getting Data from a Web Page

Tip 68: Importing a Text File into a Worksheet Range

Tip 69: Using the Quick Analysis Feature

Tip 70: Filling the Gaps in a Report

Tip 71: Performing Inexact Searches

Tip 72: Proofing Your Data with Audio

Tip 73: Getting Data from a PDF File

Part V: Tables and Pivot Tables

Tip 74: Understanding Tables

Tip 75: Using Formulas with a Table

Tip 76: Numbering Table Rows Automatically

Tip 77: Identifying Data Appropriate for a Pivot Table

Tip 78: Using a Pivot Table Instead of Formulas

Tip 79: Controlling References to Cells Within a Pivot Table

Tip 80: Creating a Quick Frequency Tabulation

Tip 81: Grouping Items by Date in a Pivot Table

Tip 82: Creating Pivot Tables with Multiple Groupings

Tip 83: Using Pivot Table Slicers and Timelines

Part VI: Charts and Graphics

Tip 84: Understanding Recommended Charts

Tip 85: Customizing Charts

Tip 86: Making Charts the Same Size

Tip 87: Creating a Chart Template

Tip 88: Creating a Combination Chart

Tip 89: Handling Missing Data in a Chart

Tip 90: Using High-Low Lines in a Chart

Tip 91: Using Multi-Level Category Labels

Tip 92: Linking Chart Text to Cells

Tip 93: Freezing a Chart

Tip 94: Creating a Chart Directly in a Range

Tip 95: Creating Minimalistic Charts

Tip 96: Applying Chart Data Labels from a Range

Tip 97: Grouping Charts and Other Objects

Tip 98: Taking Pictures of Ranges

Tip 99: Changing the Look of Cell Comments

Tip 100: Enhancing Images

Tip 101: Saving Shapes, Charts, and Ranges as Images

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach

101 Excel 2013 Tips, Tricks and Timesavers

By: John Walkenbach

Overview of this book

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access