101 Excel 2013 Tips, Tricks and Timesavers

101 Excel 2013 Tips, Tricks and Timesavers

by John Walkenbach
Released July 2013
Publisher(s): Wiley
ISBN: 9781118642184

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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!

Table of contents

  1. Cover
  2. Table of Contents
  3. Title Page
  4. Introduction
  5. Part I: Workbooks and Files
    1. Tip 1: Changing the Look of Excel
    2. Tip 2: Customizing the Quick Access Toolbar
    3. Tip 3: Customizing the Ribbon
    4. Tip 4: Understanding Protected View
    5. Tip 5: Understanding AutoRecover
    6. Tip 6: Using a Workbook in a Browser
    7. Tip 7: Saving to a Read-Only Format
    8. Tip 8: Generating a List of Filenames
    9. Tip 9: Generating a List of Sheet Names
    10. Tip 10: Using Document Themes
    11. Tip 11: Understanding Excel Compatibility Issues
    12. Tip 12: Where to Change Printer Settings
  6. Part II: Formatting
    1. Tip 13: Working with Merged Cells
    2. Tip 14: Indenting Cell Contents
    3. Tip 15: Using Named Styles
    4. Tip 16: Creating Custom Number Formats
    5. Tip 17: Using Custom Number Formats to Scale Values
    6. Tip 18: Creating a Bulleted List
    7. Tip 19: Shading Alternate Rows Using Conditional Formatting
    8. Tip 20: Formatting Individual Characters in a Cell
    9. Tip 21: Using the Format Painter
    10. Tip 22: Inserting a Watermark
    11. Tip 23: Showing Text and a Value in a Cell
    12. Tip 24: Avoiding Font Substitution for Small Point Sizes
    13. Tip 25: Updating Old Fonts
  7. Part III: Formulas
    1. Tip 26: Resizing the Formula Bar
    2. Tip 27: Monitoring Formula Cells from Any Location
    3. Tip 28: Learning Some AutoSum Tricks
    4. Tip 29: Knowing When to Use Absolute and Mixed References
    5. Tip 30: Avoiding Error Displays in Formulas
    6. Tip 31: Creating Worksheet-Level Names
    7. Tip 32: Using Named Constants
    8. Tip 33: Sending Personalized E-Mail from Excel
    9. Tip 34: Looking Up an Exact Value
    10. Tip 35: Performing a Two-Way Lookup
    11. Tip 36: Performing a Two-Column Lookup
    12. Tip 37: Calculating Holidays
    13. Tip 38: Calculating a Person's Age
    14. Tip 39: Working with Pre-1900 Dates
    15. Tip 40: Displaying a Live Calendar in a Range
    16. Tip 41: Returning the Last Nonblank Cell in a Column or Row
    17. Tip 42: Various Methods of Rounding Numbers
    18. Tip 43: Converting Between Measurement Systems
    19. Tip 44: Counting Nonduplicated Entries in a Range
    20. Tip 45: Using the AGGREGATE Function
    21. Tip 46: Making an Exact Copy of a Range of Formulas
    22. Tip 47: Using the Background Error-Checking Features
    23. Tip 48: Using the Inquire Add-In
    24. Tip 49: Hiding and Locking Your Formulas
    25. Tip 50: Using the INDIRECT Function
    26. Tip 51: Formula Editing in Dialog Boxes
    27. Tip 52: Converting a Vertical Range to a Table
  8. Part IV: Working with Data
    1. Tip 53: Selecting Cells Efficiently
    2. Tip 54: Automatically Filling a Range with a Series
    3. Tip 55: Fixing Trailing Minus Signs
    4. Tip 56: Restricting Cursor Movement to Input Cells
    5. Tip 57: Transforming Data with and Without Using Formulas
    6. Tip 58: Creating a Drop-Down List in a Cell
    7. Tip 59: Comparing Two Ranges by Using Conditional Formatting
    8. Tip 60: Finding Duplicates by Using Conditional Formatting
    9. Tip 61: Working with Credit Card Numbers
    10. Tip 62: Identifying Excess Spaces
    11. Tip 63: Transposing a Range
    12. Tip 64: Using Flash Fill to Extract Data
    13. Tip 65: Using Flash Fill to Combine Data
    14. Tip 66: Inserting Stock Information
    15. Tip 67: Getting Data from a Web Page
    16. Tip 68: Importing a Text File into a Worksheet Range
    17. Tip 69: Using the Quick Analysis Feature
    18. Tip 70: Filling the Gaps in a Report
    19. Tip 71: Performing Inexact Searches
    20. Tip 72: Proofing Your Data with Audio
    21. Tip 73: Getting Data from a PDF File
  9. Part V: Tables and Pivot Tables
    1. Tip 74: Understanding Tables
    2. Tip 75: Using Formulas with a Table
    3. Tip 76: Numbering Table Rows Automatically
    4. Tip 77: Identifying Data Appropriate for a Pivot Table
    5. Tip 78: Using a Pivot Table Instead of Formulas
    6. Tip 79: Controlling References to Cells Within a Pivot Table
    7. Tip 80: Creating a Quick Frequency Tabulation
    8. Tip 81: Grouping Items by Date in a Pivot Table
    9. Tip 82: Creating Pivot Tables with Multiple Groupings
    10. Tip 83: Using Pivot Table Slicers and Timelines
  10. Part VI: Charts and Graphics
    1. Tip 84: Understanding Recommended Charts
    2. Tip 85: Customizing Charts
    3. Tip 86: Making Charts the Same Size
    4. Tip 87: Creating a Chart Template
    5. Tip 88: Creating a Combination Chart
    6. Tip 89: Handling Missing Data in a Chart
    7. Tip 90: Using High-Low Lines in a Chart
    8. Tip 91: Using Multi-Level Category Labels
    9. Tip 92: Linking Chart Text to Cells
    10. Tip 93: Freezing a Chart
    11. Tip 94: Creating a Chart Directly in a Range
    12. Tip 95: Creating Minimalistic Charts
    13. Tip 96: Applying Chart Data Labels from a Range
    14. Tip 97: Grouping Charts and Other Objects
    15. Tip 98: Taking Pictures of Ranges
    16. Tip 99: Changing the Look of Cell Comments
    17. Tip 100: Enhancing Images
    18. Tip 101: Saving Shapes, Charts, and Ranges as Images

Product information

  • Title: 101 Excel 2013 Tips, Tricks and Timesavers
  • Author(s): John Walkenbach
  • Release date: July 2013
  • Publisher(s): Wiley
  • ISBN: 9781118642184