Book description
Build robust Excel 2010 apps quickly and efficiently
Known as "Mr. Spreadsheet," John Walkenbach's name is synonymous with excellence in computer books that explain the complexities of various topics. With this collection of favorite Excel tips and tricks, you get a unique look at ways to make Excel 2010 more efficient than you ever imagined.
Packed with easy-to-understand advice regarding all aspects of Excel, this book shares improved ways of speeding up application development with Excel and maximizing the power of Excel to create robust applications.
Addresses the extensive changes to the 2010 version of Excel and shares tricks and shortcuts for making your Excel experience as successful and efficient as possible
Reveals ways to deal with function arguments, create "impossible" charts, and tame the Ribbon bar
Discusses absolute vs. relative references, change data entry orientation, and sort more than three columns
Demonstrates ways to enter fake data for testing purposes
With John Walkenbach's Favorite Excel 2010 Tips and Tricks, you'll get a jump start on mastering the extensive changes to the 2010 version of Excel.
Table of contents
- Copyright
- About the Author
- Publisher's Acknowledgments
- INTRODUCTION
-
1. Basic Excel Usage
- 1.1. Understanding Excel Versions
- 1.2. Maximizing Ribbon Efficiency
- 1.3. Understanding Protected View
- 1.4. Selecting Cells Efficiently
- 1.5. Making "Special" Range Selections
- 1.6. Undoing, Redoing, and Repeating
- 1.7. Discovering Some Useful Shortcut Keys
- 1.8. Navigating Sheets in a Workbook
- 1.9. Resetting the Used Area of a Worksheet
- 1.10. Understanding Workbooks versus Windows
- 1.11. Customizing the Quick Access Toolbar
- 1.12. Customizing the Ribbon
- 1.13. Accessing the Ribbon with Your Keyboard
- 1.14. Recovering Your Work
- 1.15. Customizing the Default Workbook
- 1.16. Using Document Themes
- 1.17. Hiding User Interface Elements
- 1.18. Hiding Columns or Rows
- 1.19. Hiding Cell Contents
- 1.20. Taking Pictures of Ranges
- 1.21. Performing Inexact Searches
- 1.22. Replacing Formatting
- 1.23. Changing the Excel Color Scheme
- 1.24. Limiting the Usable Area in a Worksheet
- 1.25. Using an Alternative to Cell Comments
- 1.26. Understanding the Excel Help System
- 1.27. Making a Worksheet "Very Hidden"
- 1.28. Working with the Backstage View
-
2. Data Entry
- 2.1. Understanding the Types of Data
- 2.2. Moving the Cell Pointer after Entering Data
- 2.3. Selecting a Range of Input Cells before Entering Data
- 2.4. Using AutoComplete to Automate Data Entry
- 2.5. Removing Duplicate Rows
- 2.6. Keeping Titles in View
- 2.7. Automatically Filling a Range with a Series
- 2.8. Working with Fractions
- 2.9. Resizing the Formula Bar
- 2.10. Proofing Your Data with Audio
- 2.11. Controlling Automatic Hyperlinks
- 2.12. Entering Credit Card Numbers
- 2.13. Using the Excel Built-In Data Entry Form
- 2.14. Customizing and Sharing AutoCorrect Entries
- 2.15. Restricting Cursor Movement to Input Cells
- 2.16. Controlling the Office Clipboard
- 2.17. Creating a Drop-Down List in a Cell
-
3. Formatting
- 3.1. Using the Mini Toolbar
- 3.2. Indenting Cell Contents
- 3.3. Quick Number Formatting
- 3.4. Creating Custom Number Formats
- 3.5. Using Custom Number Formats to Scale Values
- 3.6. Using Custom Date and Time Formatting
- 3.7. Examining Some Useful Custom Number Formats
- 3.8. Updating Old Fonts
- 3.9. Understanding Conditional Formatting Visualization
- 3.10. Showing Text and a Value in a Cell
- 3.11. Merging Cells
- 3.12. Formatting Individual Characters in a Cell
- 3.13. Displaying Times That Exceed 24 Hours
- 3.14. Fixing Non-Numeric Numbers
- 3.15. Adding a Frame to a Range
- 3.16. Dealing with Gridlines, Borders, and Underlines
- 3.17. Inserting a Watermark
- 3.18. Adding a Background Image to a Worksheet
- 3.19. Wrapping Text in a Cell
- 3.20. Seeing All Characters in a Font
- 3.21. Entering Special Characters
- 3.22. Using Named Styles
-
4. Basic Formulas and Functions
- 4.1. Using Formula AutoComplete
- 4.2. Knowing When to Use Absolute References
- 4.3. Knowing When to Use Mixed References
- 4.4. Changing the Type of a Cell Reference
- 4.5. Converting a Vertical Range to a Table
- 4.6. AutoSum Tricks
- 4.7. Using the Status Bar Selection Statistics Feature
- 4.8. Converting Formulas to Values
- 4.9. Transforming Data without Using Formulas
- 4.10. Transforming Data by Using Temporary Formulas
- 4.11. Deleting Values While Keeping Formulas
- 4.12. Summing Across Sheets
- 4.13. Dealing with Function Arguments
- 4.14. Annotating a Formula without Using a Comment
- 4.15. Making an Exact Copy of a Range of Formulas
- 4.16. Monitoring Formula Cells from Any Location
- 4.17. Displaying and Printing Formulas
- 4.18. Avoiding Error Displays in Formulas
- 4.19. Using Goal Seeking
- 4.20. Understanding the Secret about Names
- 4.21. Using Named Constants
- 4.22. Using Functions in Names
- 4.23. Creating a List of Names
- 4.24. Using Dynamic Names
- 4.25. Creating Worksheet-Level Names
- 4.26. Working with Pre-1900 Dates
- 4.27. Working with Negative Time Values
-
5. Useful Formula Examples
- 5.1. Calculating Holidays
- 5.2. Calculating a Weighted Average
- 5.3. Calculating a Person's Age
- 5.4. Ranking Values
- 5.5. Converting Inches to Feet and Inches
- 5.6. Using the DATEDIF Function
- 5.7. Counting Characters in a Cell
- 5.8. Numbering Weeks
- 5.9. Using a Pivot Table Instead of Formulas
- 5.10. Expressing a Number as an Ordinal
- 5.11. Extracting Words from a String
- 5.12. Parsing Names
- 5.13. Removing Titles from Names
- 5.14. Generating a Series of Dates
-
5.15. Determining Specific Dates
- 5.15.1. Determining the day of the year
- 5.15.2. Determining the day of the week
- 5.15.3. Determining the date of the most recent Sunday
- 5.15.4. Determining the first day of the week after a date
- 5.15.5. Determining the nth occurrence of a day of the week in a month
- 5.15.6. Determining the last day of a month
- 5.15.7. Determining a date's quarter
- 5.16. Displaying a Calendar in a Range
- 5.17. Various Methods of Rounding Numbers
- 5.18. Rounding Time Values
- 5.19. Using the New AGGREGATE Function
- 5.20. Returning the Last Nonblank Cell in a Column or Row
- 5.21. Using the COUNTIF Function
- 5.22. Counting Cells That Meet Multiple Criteria
- 5.23. Counting Nonduplicated Entries in a Range
- 5.24. Calculating Single-Criterion Conditional Sums
- 5.25. Calculating Multiple-Criterion Conditional Sums
- 5.26. Looking Up an Exact Value
- 5.27. Performing a Two-Way Lookup
- 5.28. Performing a Two-Column Lookup
- 5.29. Performing a Lookup by Using an Array
- 5.30. Using the INDIRECT Function
- 5.31. Creating Megaformulas
- 6. Conversions and Mathematical Calculations
-
7. Charts and Graphics
- 7.1. Creating a Text Chart Directly in a Range
- 7.2. Selecting Elements in a Chart
- 7.3. Creating a Self-Expanding Chart
- 7.4. Creating Combination Charts
- 7.5. Creating a Gantt Chart
- 7.6. Creating a Gauge Chart
- 7.7. Using Pictures in Charts
- 7.8. Plotting Mathematical Functions
- 7.9. Using High-Low Lines in a Chart
- 7.10. Linking Chart Text to Cells
- 7.11. Creating a Chart Template
- 7.12. Saving a Chart as a Graphics File
- 7.13. Saving a Range as a Graphic Image
- 7.14. Making Charts the Same Size
- 7.15. Resetting All Chart Formatting
- 7.16. Freezing a Chart
- 7.17. Creating Picture Effects with a Chart
- 7.18. Creating Sparkline Graphics
- 7.19. Selecting Objects on a Worksheet
- 7.20. Making a Greeting Card
- 7.21. Enhancing Text Formatting in Shapes
- 7.22. Using Images as Line Chart Markers
- 7.23. Changing the Shape of a Cell Comment
- 7.24. Adding an Image to a Cell Comment
- 7.25. Enhancing Images
-
8. Data Analysis and Lists
- 8.1. Using the Table Feature
- 8.2. Working with Tables
- 8.3. Using Formulas with a Table
- 8.4. Numbering Rows in a Table
- 8.5. Using Custom Views with Filtering
- 8.6. Putting Advanced Filter Results on a Different Sheet
- 8.7. Comparing Two Ranges by Using Conditional Formatting
- 8.8. Randomizing a List
- 8.9. Filling the Gaps in a Report
- 8.10. Creating a List from a Summary Table
- 8.11. Finding Duplicates by Using Conditional Formatting
- 8.12. Creating a Quick Frequency Tabulation
- 8.13. Controlling References to Cells within a Pivot Table
- 8.14. Grouping Items by Date in a Pivot Table
- 8.15. Unlinking a Pivot Table from Its Source
- 8.16. Using Pivot Table Slicers
-
9. Working with Files
- 9.1. Understanding the New Excel File Formats
- 9.2. Importing a Text File into a Worksheet Range
- 9.3. Getting Data from a Web Page
- 9.4. Displaying a Workbook's Full Path
- 9.5. Using Document Properties
- 9.6. Inspecting a Workbook
- 9.7. Finding the Missing No to All Button When Closing Files
- 9.8. Getting a List of Filenames
- 9.9. Using Workspace Files
-
10. Printing
- 10.1. Controlling What Gets Printed
- 10.2. Displaying Repeated Rows or Columns on a Printout
- 10.3. Printing Noncontiguous Ranges on a Single Page
- 10.4. Preventing Objects from Printing
- 10.5. Page-Numbering Tips
- 10.6. Adding and Removing Page Breaks
- 10.7. Saving to a PDF File
- 10.8. Making Your Printout Fit on One Page
- 10.9. Printing the Contents of a Cell in a Header or Footer
- 10.10. Copying Page Setup Settings Across Sheets
- 10.11. Printing Cell Comments
- 10.12. Printing a Giant Banner
-
11. Spotting, Fixing, and Preventing Errors
- 11.1. Using the Excel Error-Checking Features
- 11.2. Identifying Formula Cells
- 11.3. Dealing with Floating-Point Number Problems
- 11.4. Removing Excess Spaces
- 11.5. Viewing Names Graphically
- 11.6. Locating Phantom Links
- 11.7. Understanding Displayed versus Actual Values
- 11.8. Tracing Cell Relationships
-
12. Basic VBA and Macros
- 12.1. Learning about Macros and VBA
- 12.2. Recording a Macro
- 12.3. Executing Macros
- 12.4. Understanding Functions Versus Subs
- 12.5. Creating Simple Worksheet Functions
- 12.6. Describing Function Arguments
- 12.7. Making Excel Talk
- 12.8. Understanding Custom Function Limitations
- 12.9. Executing a Ribbon Command with a Macro
- 12.10. Understanding Security Issues Related to Macros
- 12.11. Using a Personal Macro Workbook
Product information
- Title: John Walkenbach's Favorite Excel 2010 Tips and Tricks
- Author(s):
- Release date: July 2010
- Publisher(s): Wiley
- ISBN: 9780470475379
You might also like
book
Excel® 2013 Formulas and Functions
Master core Excel 2013 tools for building powerful, reliable spreadsheets! Excel expert Paul McFedries shows how …
book
Office 2013: The Missing Manual
Microsoft Office is the most widely used productivity software in the world, but most people just …
book
Microsoft® Office Excel® 2007: Data Analysis and Business Modeling, Second Edition
Master the techniques that business analysts at leading companies use to transform data into bottom-line results. …
book
Excel® 2013 On Demand
Excel 2013 On Demand is built from the ground up for today's beginning-to-intermediate-level Exceluser. Like every …