John Walkenbach's Favorite Excel 2010 Tips and Tricks

In this Book

• Introduction
• Understanding Excel Versions
• Maximizing Ribbon Efficiency
• Understanding Protected View
• Selecting Cells Efficiently
• Making “Special” Range Selections
• Undoing, Redoing, and Repeating
• Discovering Some Useful Shortcut Keys
• Navigating Sheets in a Workbook
• Resetting the Used Area of a Worksheet
• Understanding Workbooks versus Windows
• Customizing the Quick Access Toolbar
• Customizing the Ribbon
• Accessing the Ribbon with Your Keyboard
• Recovering Your Work
• Customizing the Default Workbook
• Using Document Themes
• Hiding User Interface Elements
• Hiding Columns or Rows
• Hiding Cell Contents
• Taking Pictures of Ranges
• Performing Inexact Searches
• Replacing Formatting
• Changing the Excel Color Scheme
• Limiting the Usable Area in a Worksheet
• Using an Alternative to Cell Comments
• Understanding the Excel Help System
• Making a Worksheet “Very Hidden”
• Working with the Backstage View
• Understanding the Types of Data
• Moving the Cell Pointer after Entering Data
• Selecting a Range of Input Cells before Entering Data
• Using AutoComplete to Automate Data Entry
• Removing Duplicate Rows
• Keeping Titles in View
• Automatically Filling a Range with a Series
• Working with Fractions
• Resizing the Formula Bar
• Proofing Your Data with Audio
• Controlling Automatic Hyperlinks
• Entering Credit Card Numbers
• Using the Excel Built-In Data Entry Form
• Customizing and Sharing AutoCorrect Entries
• Restricting Cursor Movement to Input Cells
• Controlling the Office Clipboard
• Creating a Drop-Down List in a Cell
• Using the Mini Toolbar
• Indenting Cell Contents
• Quick Number Formatting
• Creating Custom Number Formats
• Using Custom Number Formats to Scale Values
• Using Custom Date and Time Formatting
• Examining Some Useful Custom Number Formats
• Updating Old Fonts
• Understanding Conditional Formatting Visualization
• Showing Text and a Value in a Cell
• Merging Cells
• Formatting Individual Characters in a Cell
• Displaying Times That Exceed 24 Hours
• Fixing Non-Numeric Numbers
• Adding a Frame to a Range
• Dealing with Gridlines, Borders, and Underlines
• Inserting a Watermark
• Adding a Background Image to a Worksheet
• Wrapping Text in a Cell
• Seeing All Characters in a Font
• Entering Special Characters
• Using Named Styles
• Using Formula AutoComplete
• Knowing When to Use Absolute References
• Knowing When to Use Mixed References
• Changing the Type of a Cell Reference
• Converting a Vertical Range to a Table
• AutoSum Tricks
• Using the Status Bar Selection Statistics Feature
• Converting Formulas to Values
• Transforming Data without Using Formulas
• Transforming Data by Using Temporary Formulas
• Deleting Values While Keeping Formulas
• Summing across Sheets
• Dealing with Function Arguments
• Annotating a Formula without Using a Comment
• Making an Exact Copy of a Range of Formulas
• Monitoring Formula Cells from Any Location
• Displaying and Printing Formulas
• Avoiding Error Displays in Formulas
• Using Goal Seeking
• Understanding the Secret about Names
• Using Named Constants
• Using Functions in Names
• Creating a List of Names
• Using Dynamic Names
• Creating Worksheet-Level Names
• Working with Pre-1900 Dates
• Working with Negative Time Values
• Calculating Holidays
• Calculating a Weighted Average
• Calculating a Person's Age
• Ranking Values
• Converting Inches to Feet and Inches
• Using the DATEDIF Function
• Counting Characters in a Cell
• Numbering Weeks
• Using a Pivot Table Instead of Formulas
• Expressing a Number as an Ordinal
• Extracting Words from a String
• Parsing Names
• Removing Titles from Names
• Generating a Series of Dates
• Determining Specific Dates
• Displaying a Calendar in a Range
• Various Methods of Rounding Numbers
• Rounding Time Values
• Using the New AGGREGATE Function
• Returning the Last Nonblank Cell in a Column or Row
• Using the COUNTIF Function
• Counting Cells That Meet Multiple Criteria
• Counting Nonduplicated Entries in a Range
• Calculating Single-Criterion Conditional Sums
• Calculating Multiple-Criterion Conditional Sums
• Looking up an Exact Value
• Performing a Two-Way Lookup
• Performing a Two-Column Lookup
• Performing a Lookup by Using an Array
• Using the INDIRECT Function
• Creating Megaformulas
• Converting between Measurement Systems
• Converting Temperatures
• Solving Simultaneous Equations
• Solving Recursive Equations
• Generating Random Numbers
• Calculating Roots
• Calculating a Remainder
• Creating a Text Chart Directly in a Range
• Selecting Elements in a Chart
• Creating a Self-Expanding Chart
• Creating Combination Charts
• Creating a Gantt Chart
• Creating a Gauge Chart
• Using Pictures in Charts
• Plotting Mathematical Functions
• Using High-Low Lines in a Chart
• Linking Chart Text to Cells
• Creating a Chart Template
• Saving a Chart as a Graphics File
• Saving a Range as a Graphic Image
• Making Charts the Same Size
• Resetting All Chart Formatting
• Freezing a Chart
• Creating Picture Effects with a Chart
• Creating Sparkline Graphics
• Selecting Objects on a Worksheet
• Making a Greeting Card
• Enhancing Text Formatting in Shapes
• Using Images as Line Chart Markers
• Changing the Shape of a Cell Comment
• Adding an Image to a Cell Comment
• Enhancing Images
• Using the Table Feature
• Working with Tables
• Using Formulas with a Table
• Numbering Rows in a Table
• Using Custom Views with Filtering
• Putting Advanced Filter Results on a Different Sheet
• Comparing Two Ranges by Using Conditional Formatting
• Randomizing a List
• Filling the Gaps in a Report
• Creating a List from a Summary Table
• Finding Duplicates by Using Conditional Formatting
• Creating a Quick Frequency Tabulation
• Controlling References to Cells within a Pivot Table
• Grouping Items by Date in a Pivot Table
• Unlinking a Pivot Table from its Source
• Using Pivot Table Slicers
• Understanding the New Excel File Formats
• Importing a Text File into a Worksheet Range
• Getting Data from a Web Page
• Displaying a Workbook's Full Path
• Using Document Properties
• Inspecting a Workbook
• Finding the Missing No to All Button When Closing Files
• Getting a List of Filenames
• Using Workspace Files
• Controlling What Gets Printed
• Displaying Repeated Rows or Columns on a Printout
• Printing Noncontiguous Ranges on a Single Page
• Preventing Objects from Printing
• Page-Numbering Tips
• Adding and Removing Page Breaks
• Saving to a PDF File
• Making Your Printout Fit on One Page
• Printing the Contents of a Cell in a Header or Footer
• Copying Page Setup Settings across Sheets
• Printing Cell Comments
• Printing a Giant Banner
• Using the Excel Error-Checking Features
• Identifying Formula Cells
• Dealing with Floating-Point Number Problems
• Removing Excess Spaces
• Viewing Names Graphically
• Locating Phantom Links
• Understanding Displayed versus Actual Values
• Tracing Cell Relationships
• Learning about Macros and VBA
• Recording a Macro
• Executing Macros
• Understanding Functions versus Subs
• Creating Simple Worksheet Functions
• Describing Function Arguments
• Making Excel Talk
• Understanding Custom Function Limitations
• Executing a Ribbon Command with a Macro
• Understanding Security Issues Related to Macros
• Using a Personal Macro Workbook