Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets, 2/e

David Hawley, Raina Hawley

  • 出版商: O'Reilly
  • 出版日期: 2007-07-10
  • 售價: $1,090
  • 貴賓價: 9.5$1,036
  • 語言: 英文
  • 頁數: 412
  • 裝訂: Paperback
  • ISBN: 0596528345
  • ISBN-13: 9780596528348
  • 相關分類: Excel
  • 下單後立即進貨 (約1週~2週)




Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel's advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks -- clever tools, tips and techniques -- that will leapfrog your work beyond the ordinary.

Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known "backdoor" tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to:
  • Reduce workbook and worksheet frustration -- manage how users interact with worksheets, find and highlight information, and deal with debris and corruption.
  • Analyze and manage data -- extend and automate these features, moving beyond the limited tasks they were designed to perform.
  • Hack names -- learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.
  • Get the most out of PivotTables -- avoid the problems that make them frustrating and learn how to extend them.
  • Create customized charts -- tweak and combine Excel's built-in charting capabilities.
  • Hack formulas and functions -- subjects range from moving formulas around to dealing with datatype issues to improving recalculation time.
  • Make the most of macros -- including ways to manage them and use them to extend other features.
  • Connect Excel to the world -- use XML and take advantage of web sites and services from Google and Amazon to get information into and out of your spreadsheets.
You can either browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. However you use it, Excel Hacks will help you increase productivity and give you hours of "hacking" enjoyment along the way.

Table of Contents



Chapter 1. Reducing Workbook and Worksheet Frustration
      1. Create a Personal View of Your Workbooks
      2. Enter Data into Multiple Worksheets Simultaneously
      3. Prevent Users from Performing Certain Actions
      4. Prevent Seemingly Unnecessary Prompts
      5. Hide Worksheets So That They Cannot Be Unhidden
      6. Customize the Templates Dialog and Default Workbook
      7. Create an Index of Sheets in Your Workbook
      8. Limit the Scrolling Range of Your Worksheet
      9. Lock and Protect Cells Containing Formulas
      10. Find Duplicate Data Using Conditional Formatting
      11. Find Data That Appears Two or More Times Using Conditional Formatting
      12. Tie Custom Toolbars to a Particular Workbook
      13. Outsmart Excel's Relative Reference Handler
      14. Remove Phantom Workbook Links
      15. Reduce Workbook Bloat
      16. Extract Data from a Corrupt Workbook

Chapter 2. Hacking Excel's Built-in Features
      17. Validate Data Based on a List on Another Worksheet
      18. Control Conditional Formatting with Checkboxes
      19. Identify Formulas with Conditional Formatting
      20. Count or Sum Cells That Meet Conditional Formatting Criteria
      21. Highlight Every Other Row or Column
      22. Create 3-D Effects in Tables or Cells
      23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox
      24. Support Multiple Lists in a ComboBox
      25. Create Validation Lists That Change Based on a Selection from Another List
      26. Use Replace... to Remove Unwanted Characters
      27. Convert Text Numbers to Real Numbers
      28. Extract the Numeric Portion of a Cell Entry
      29. Customize Cell Comments
      30. Sort by More Than Three Columns
      31. Random Sorting
      32. Manipulate Data with the Advanced Filter
      33. Create Custom Number Formats
      34. Add More Levels of Undo to Excel for Windows
      35. Create Custom Lists
      36. Boldface Excel Subtotals
      37. Convert Excel Formulas and Functions to Values
      38. Automatically Add Data to a Validation List
      39. Hack Excel's Date and Time Features
      40. Enable Grouping and Outlining on a Protected Worksheet
      41. Prevent Blanks/Missing Fields in a Table
      42. Provide Decreasing Data Validation Lists
      43. Add a Custom List to the Fill Handle

Chapter 3. Naming Hacks
      44. Address Data by Name
      45. Use the Same Name for Ranges on Different Worksheets
      46. Create Custom Functions Using Names
      47. Create Ranges That Expand and Contract
      48. Nest Dynamic Ranges for Maximum Flexibility
      49. Identify Named Ranges on a Worksheet

Chapter 4. Hacking PivotTables
      50. PivotTables: A Hack in Themselves
      51. Share PivotTables but Not Their Data
      52. Automate PivotTable Creation
      53. Move PivotTable Grand Totals
      54. Efficiently Pivot Another Workbook's Data

Chapter 5. Charting Hacks
      55. Explode a Single Slice from a Pie Chart
      56. Create Two Sets of Slices in One Pie Chart
      57. Create Charts That Adjust to Data
      58. Interact with Your Charts Using Custom Controls
      59. Four Quick Ways to Update Your Charts
      60. Hack Together a Simple Thermometer Chart
      61. Create a Column Chart with Variable Widths and Heights
      62. Create a Speedometer Chart
      63. Link Chart Text Elements to a Cell
      64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted
      65. Add a Directional Arrow to the End of a Line Series
      66. Place an Arrow on the End of a Horizontal (X) Axis
      67. Correct Narrow Columns When Using Dates
      68. Position Axis Labels
      69. Tornado Chart
      70. Gauge Chart
      71. Conditional Highlighting Axis Labels
      72. Create Totals on a Stacked Column Chart

Chapter 6. Hacking Formulas and Functions
      73. Add Descriptive Text to Your Formulas
      74. Move Relative Formulas Without Changing References
      75. Compare Two Excel Ranges
      76. Fill All Blank Cells in a List
      77. Make Your Formulas Increment by Rows When You Copy Across Columns
      78. Convert Dates to Excel Formatted Dates
      79. Sum or Count Cells While Avoiding Error Values
      80. Reduce the Impact of Volatile Functions on Recalculation
      81. Count Only One Instance of Each Entry in a List
      82. Sum Every Second, Third, or Nth Row or Cell
      83. Find the Nth Occurrence of a Value
      84. Make the Excel Subtotal Function Dynamic
      85. Add Date Extensions
      86. Convert Numbers with the Negative Sign on the Right to Excel Numbers
      87. Display Negative Time Values
      88. Use the VLOOKUP Function Across Multiple Tables
      89. Show Total Time As Days, Hours, and Minutes
      90. Determine the Number of Specified Days in Any Month
      91. Construct Mega-Formulas
      92. Hack Mega-Formulas that Reference Other Workbooks
      93. Hack One of Excel's Database Functions to Take the Place of Many Functions
      94. Extract Specified Words from a Text String
      95. Count Words in a Cell or Range of Cells
      96. Return a Worksheet Name to a Cell
      97. Sum Cells with Multiple Criteria
      98. Count Cells with Multiple Criteria
      99. Calculate a Sliding Tax Scale
      100. Add/Subtract Months from a Date
      101. Find the Last Day of Any Given Month
      102. Calculate a Person's Age
      103. Return the Weekday of a Date
      104. Evaluate a Text Equation
      105. Lookup from Within a Cell

Chapter 7. Macro Hacks
      106. Speed Up Code While Halting Screen Flicker
      107. Run a Macro at a Set Time
      108. Use CodeNames to Reference Sheets in Excel Workbooks
      109. Connect Buttons to Macros Easily
      110. Create a Workbook Splash Screen
      111. Display a "Please Wait" Message
      112. Have a Cell Ticked or Unticked upon Selection
      113. Count or Sum Cells That Have a Specified Fill Color
      114. Add the Microsoft Excel Calendar Control to Any Excel Workbook
      115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
      116. Retrieve a Workbook's Name and Path
      117. Get Around Excel's Three-Criteria Limit for Conditional Formatting
      118. Run Procedures on Protected Worksheets
      119. Distribute Macros
      120. Delete Rows Based on a Condition
      121. Track and Report Changes in Excel
      122. Automatically Add Date/Time to a Cell upon Entry
      123. Create a List of Workbook Hyperlinks
      124. Advanced Find
      125. Find a Number Between Two Numbers
      126. Convert Formula References from Relative to Absolute
      127. Name a Workbook with the Text in a Cell
      128. Hide and Restore Toolbars in Excel
      129. Sort Worksheets
      130. Password-Protect a Worksheet from Viewing
      131. Change Text to Upper- or Proper Case
      132. Force Text to Upper- or Proper Case
      133. Prevent Case Sensitivity in VBA Code
      134. Display AutoFilter Criteria

Chapter 8. Cross-Application Hacks
      135. Import Data from Access 2007 into Excel 2007
      136. Retrieve Data from Closed Workbooks
      137. Automate Word from Excel
      138. Automate Outlook from Excel