Excel Hacks: 100 Industrial-Strength Tips and Tools

Raina Hawley, David Hawley

  • 出版商: O'Reilly
  • 出版日期: 2004-03-01
  • 售價: $950
  • 貴賓價: 9.5$903
  • 語言: 英文
  • 頁數: 304
  • 裝訂: Paperback
  • ISBN: 059600625X
  • ISBN-13: 9780596006259
  • 相關分類: Excel





If you think that getting creative with Excel means the underhanded tweaking of numbers, think again. Excel Hacks shows even the most experienced users how to do things with Excel they might never have thought of doing--and lets them have a little fun while they're at it.

Microsoft Excel is not just the dominant spreadsheet in the world; it's also one of the most popular applications ever created. Its success lies not only in its power and flexibility, but also in its streamlined, familiar interface that casually conceals its considerable capabilities. You don't need to know everything that Excel can do in order to use it effectively, but if you're like the millions of Excel power users looking to improve productivity, then Excel Hacks will show you a wide variety of Excel tasks you can put to use, most of which are off the beaten path.

With this book, Excel power users can bring a hacker's creative approach to both common and uncommon Excel topics--"hackers" in this sense being those who like to tinker with technology to improve it. The "100 Industrial Strength Tips and Tools" in Excel Hacks include little known "backdoor" adjustments for everything from reducing workbook and worksheet frustration to hacking built-in features such as pivot tables, charts, formulas and functions, and even the macro language.

This resourceful roll-up-your-sleeves guide is for intermediate to advanced Excel users eager to explore new ways to make Excel do things--from data analysis to worksheet management to import/export--that you never thought possible. Excel Hacks will help you increase productivity with Excel 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. Tie Custom Toolbars to a Particular Workbook
      12. Outsmart Excel's Relative Reference Handler
      13. Remove Phantom Workbook Links
      14. Reduce Workbook Bloat
      15. Extract Data from a Corrupt Workbook

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

Chapter 3. Naming Hacks
      39. Address Data by Name
      40. Use the Same Name for Ranges on Different Worksheets
      41. Create Custom Functions Using Names
      42. Create Ranges That Expand and Contract
      43. Nest Dynamic Ranges for Maximum Flexibility
      44. Identify Named Ranges on a Worksheet

Chapter 4. Hacking PivotTables
      45. PivotTables: A Hack in Themselves
      46. Share PivotTables but Not Their Data
      47. Automate PivotTable Creation
      48. Move PivotTable Grand Totals
      49. Efficiently Pivot Another Workbook's Data

Chapter 5. Charting Hacks
      50. Explode a Single Slice from a Pie Chart
      51. Create Two Sets of Slices in One Pie Chart
      52. Create Charts That Adjust to Data
      53. Interact with Your Charts Using Custom Controls
      54. Three Quick Ways to Update Your Charts
      55. Hack Together a Simple Thermometer Chart
      56. Create a Column Chart with Variable Widths and Heights
      57. Create a Speedometer Chart
      58. Link Chart Text Elements to a Cell
      59. Hack Chart Data So That Blank Cells Are Not Plotted

Chapter 6. Hacking Formulas and Functions
      60. Add Descriptive Text to Your Formulas
      61. Move Relative Formulas Without Changing References
      62. Compare Two Excel Ranges
      63. Fill All Blank Cells in a List
      64. Make Your Formulas Increment by Rows When You Copy Across Columns
      65. Convert Dates to Excel Formatted Dates
      66. Sum or Counting Cells While Avoiding Error Values
      67. Reduce the Impact of Volatile Functions on Recalculation
      68. Count Only One Instance of Each Entry in a List
      69. Sum Every Second, Third, or nth Row or Cell
      70. Find the nth Occurrence of a Value
      71. Make the Excel Subtotal Function Dynamic
      72. Add Date Extensions
      73. Convert Numbers with the Negative Sign on the Right to Excel Numbers
      74. Display Negative Time Values
      75. Use the VLOOKUP Function Across Multiple Tables
      76. Show Total Time as Days, Hours, and Minutes
      77. Determine the Number of Specified Days in Any Month
      78. Construct Mega-Formulas
      79. Hack Mega-Formulas that Reference Other Workbooks
      80. Hack One of Excel's Database Functions to Take the Place of Many Functions

Chapter 7. Macro Hacks
      81. Speed Up Code While Halting Screen Flicker
      82. Run a Macro at a Set Time
      83. Use CodeName to Reference Sheets in Excel Workbooks
      84. Connect Buttons to Macros Easily
      85. Create a Workbook Splash Screen
      86. Display a "Please Wait" Message
      87. Have a Cell Ticked or Unticked upon Selection
      88. Count or Sum Cells That Have a Specified Fill Color
      89. Add the Microsoft Excel Calendar Control to Any Excel Workbook
      90. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
      91. Retrieve a Workbook's Name and Path
      92. Get Around Excel's Three-Criteria Limit for Conditional Formatting
      93. Run Procedures on Protected Worksheets
      94. Distribute Macros

Chapter 8. Connecting Excel to the World
      95. Load an XML Document into Excel
      96. Save to SpreadsheetML and Extracting Data
      97. Create Spreadsheets using SpreadsheetML
      98. Import Data Directly into Excel
      99. Access SOAP Web Services from Excel
      100. Create Excel Spreadsheets Using Other Environments