Excel® 2007: Top 100 Simplified® Tips & Tricks

Excel® 2007: Top 100 Simplified® Tips & Tricks

by Denise Etheridge
Released July 2007
Publisher(s): Wiley
ISBN: 9780470126745

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

You already know Excel 2007. Now you'd like to go beyond with shortcuts, tricks, and tips that let you work smarter and faster. And because you learn more easily when someone shows you how, this is the book for you. Inside, you'll find clear, illustrated instructions for 100 tasks that reveal cool secrets, teach timesaving tricks, and explain great tips guaranteedto make you more productive with Excel 2007.

* Minimal text and maximum illustrations
*

Task-oriented, step-by-step approach
*

Navigational aids connect instructions to illustrations
*

Self-contained, two-page lessons
*

Uniform layout makes it easy to read less, learn more

How easy is it?


Look for these symbols marking the difficulty of each task.

* Demonstrates a new spin on a common task
*

Introduces a new skill or a new task
*

Combines multiple skills requiring in-depth knowledge
*

Requires extensive skill and may involve other technologies

Table of contents

  1. Copyright
  2. Praise for Visual Books
  3. Credits
  4. About the Author
  5. How To Use This Book
    1. Who is this book for?
    2. Conventions Used In This Book
  6. 1. Boost Your Efficiency
    1. #1. Validate with a Validation List
      1. Paste Your Validation List
    2. #2. Validate with Data Entry Rules
    3. #3. Extend a Series with AutoFill
    4. #4. Insert Symbols or Special Characters
      1. Add a Symbol
      2. Add a Special Character
    5. #5. Hide Rows by Grouping and Outlining
      1. Add a Group
      2. Remove a Group
    6. #6. Find and Replace Formats
    7. #7. Add Comments to Your Worksheet
      1. Add a Comment
      2. Display All Comments
    8. #8. Let Excel Read Back Your Data
      1. Read Cells
      2. Speak Cells on Enter
    9. #9. Create Your Own Sort or AutoFill
  7. 2. Work with Formulas and Functions
    1. #10. Enter Formulas Using a Variety of Methods
      1. Calculate with an Operator
      2. Calculate by Using a Function and Cell Addresses
      3. Calculate by Using Autosum
    2. #11. Name Cells and Ranges
      1. Name a Range of Cells
      2. Create Named Ranges from a Selection
    3. #12. Define a Constant
      1. Define a Constant
      2. Display a Constant
    4. #13. Create Formulas That Include Names
      1. Use a Constant or Range Name in a Formula
      2. Use a Constant or Range Name in a Formula
    5. #14. Calculate with the Function Wizard
    6. #15. Figure Out Loan Terms
    7. #16. Determine the Internal Rate of Return
    8. #17. Determine the Nth Largest Value
    9. #18. Create a Conditional Formula
    10. #19. Calculate a Conditional Sum
    11. #20. Add a Calculator
      1. Add the Calculator
      2. Use the Scientific Mode
    12. #21. Find Products and Square Roots
      1. Calculate a Product
      2. Calculate a Square Root
    13. #22. Perform Time Calculations
      1. Find the Difference Between Two Times
      2. Convert a Serial Value to a Time
    14. #23. Perform Date Calculations
      1. Find Number of Days Between Two Dates
  8. 3. Copy, Format, and More
    1. #24. Check Your Formulas for Errors
    2. #25. Trace Precedents and Dependents
      1. Trace Precedents
      2. Trace Dependents
    3. #26. Change Text to Numbers
    4. #27. Convert a Row to a Column
    5. #28. Copy with the Office Clipboard
    6. #29. Adjust Column Widths with Paste Special
    7. #30. Specify How to Paste with Paste Special
    8. #31. Create Your Own Style
      1. Group Formats as a Style
      2. Apply a Format Style
    9. #32. Copy Styles to Another Workbook
    10. #33. Conditionally Format Your Worksheet
      1. Conditional Format
      2. Data Bars
      3. Modify Rules
    11. #34. Track Changes While Editing
    12. #35. Consolidate Worksheets
  9. 4. Manipulate Records
    1. #36. Enter Data with a Form
    2. #37. Filter Duplicate Records
    3. #38. Perform Simple Sorts and Filters
      1. Sort a List
      2. Filter a List
    4. #39. Perform Complex Sorts
    5. #40. Sort by Cell Color, Font Color, or Icon
    6. #41. Perform Complex Filters
    7. #42. Filter by Multiple Criteria
    8. #43. Subtotal Sorted Data
    9. #44. Chart Filtered Data Easily
    10. #45. Count Filtered Records
    11. #46. Look Up Information in Your Worksheet
    12. #47. Define Data as a Table
    13. #48. Modify a Table Style
  10. 5. Explore the Patterns in Your Data
    1. #49. Create a PivotTable
    2. #50. Modify PivotTable Data and Layout
      1. Refresh Data
      2. Change Layout
    3. #51. Compute PivotTable Sub and Grand Totals
    4. #52. Create a PivotTable Calculated Field
    5. #53. Hide Rows or Columns in a PivotTable
    6. #54. Sort a PivotTable
      1. Sort Field Labels
      2. Sort Data Fields
    7. #55. Create a PivotChart
    8. #56. Describe Data with Statistics
    9. #57. Find the Correlation between Variables
    10. #58. Explore Outcomes with What-If Analysis
    11. #59. Optimize a Result with Goal Seek
  11. 6. Create Charts
    1. #60. Create a Chart That Has Visual Appeal
    2. #61. Add Chart Details
      1. Change Chart Location
      2. Change Rotation and Perspective
      3. Change the Wall and Floor
      4. Adjust the Axis
      5. Change the Gridlines
    3. #62. Change the Chart Type
    4. #63. Add a Trendline
    5. #64. Add and Remove Chart Data
      1. Change the Data Area
      2. Add a Legend Item
    6. #65. Add Error Bars
    7. #66. Create a Histogram
    8. #67. Create a Combination Chart
      1. Create a Secondary Axis
      2. Change Chart Type
  12. 7. Present Worksheets
    1. #68. Format Numbers, Dates, and Times
      1. Comma Style Format
      2. Accounting Number Format
      3. Currency Format
      4. Percents
      5. Dates
      6. Times
      7. Fractions
    2. #69. Apply Formats to Cells
      1. Create a Fill
      2. Change the Text Color
      3. Add a Border
      4. Merge and Center
      5. Wrap Text
      6. Orientation
    3. #70. Fill with a Gradient
    4. #71. Format Quickly with Format Painter
    5. #72. Insert Shapes into Your Worksheet
    6. #73. Insert Text Boxes into Your Worksheet
    7. #74. Insert Photographs into Your Worksheet
    8. #75. Arrange the Graphics in Your Worksheet
      1. Rotate and Align Graphics
    9. #76. Insert a Background Image
    10. #77. Take a Picture of Your Worksheet
  13. 8. Protect, Save, and Print
    1. #78. Protect Your Worksheet
    2. #79. Save a Workbook as a Template
    3. #80. Choose a Format When Saving a Workbook
    4. #81. Print Multiple Areas of a Workbook
    5. #82. Print Multiple Worksheets from a Workbook
  14. 9. Extend Excel
    1. #83. Paste Link into Word or PowerPoint
    2. #84. Embed a Worksheet
    3. #85. Create a Link from an Excel Workbook
    4. #86. Query a Web Site
    5. #87. Copy a Word Table into Excel
    6. #88. Import a Text File into Excel
    7. #89. Import an Access Database into Excel
    8. #90. Query an Access Database
    9. #91. Reuse a Saved Query
    10. #92. Import an Excel Worksheet into Access
    11. #93. Using Excel with Mail Merge
  15. 10. Customize Excel
    1. #94. Add Features by Installing Add-Ins
    2. #95. Customize the Quick Access Toolbar
      1. Add a Feature to the Quick Access Toolbar
      2. Move the Quick Access Toolbar Below the Ribbon
    3. #96. Work with Multiple Windows
    4. #97. Save Time by Creating a Custom View
    5. #98. Create a Custom Number Format
    6. #99. Automate Your Worksheet with Macros
    7. #100. Add a Button to Run a Macro

Product information

  • Title: Excel® 2007: Top 100 Simplified® Tips & Tricks
  • Author(s): Denise Etheridge
  • Release date: July 2007
  • Publisher(s): Wiley
  • ISBN: 9780470126745