Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying 101 Excel 2013 Tips, Tricks and Timesavers
  • Table Of Contents Toc
101 Excel 2013 Tips, Tricks and Timesavers

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach
close
close
101 Excel 2013 Tips, Tricks and Timesavers

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

Tip 64: Using Flash Fill to Extract Data

When you import data, it’s often necessary to clean up some of the text. For example, names may appear in uppercase that they should be in proper case. One approach is to use formulas to modify the text (see Tip 57). Another approach uses a feature introduced in Excel 2013: Flash Fill.

Flash Fill uses pattern recognition to extract data (and also concatenate data) from adjoining columns. Just enter a few examples in a column that’s adjacent to the data, and then choose Data⇒Data Tools⇒Flash Fill (or press Ctrl+E). Excel analyzes the examples you typed and attempts to fill in the remaining cells. If Excel didn’t recognize the pattern you had in mind, press Ctrl+Z, add another example or two, and try again.

Changing the case of text

Figure 64-1 shows a list of U.S. presidents in column A. Column B shows the result of using Flash Fill to convert the text to proper case.

Start by providing a few...

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
101 Excel 2013 Tips, Tricks and Timesavers
End of Section 8
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon