Excel Hacks. Tips & Tools for Streamlining Your Spreadsheets. 2nd Edition

Spis treści książki

• Excel Hacks, 2nd Edition
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • Credits
    • About the Authors
    • Contributors
    • Acknowledgments
  • Preface
    • Why Excel Hacks?
    • Getting and Using the Hacks
    • How to Use This Book
    • How This Book Is Organized
    • Windows, Macintosh, and Earlier Excel Versions
    • Conventions Used in This Book
    • Using Code Examples
    • Safari Enabled
    • How to Contact Us
  • 1. Reducing Workbook and Worksheet Frustration
    • 1.1. Hacks 116
      • 1.1.1. The 80/20 Rule
      • 1.1.2. Structural Tips
      • 1.1.3. Formatting Tips
      • 1.1.4. Formula Tips
    • Hack #1. Create a Personal View of Your Workbooks
    • Hack #2. Enter Data into Multiple Worksheets Simultaneously
      • 1.3.1. Grouping Worksheets Manually
      • 1.3.2. Grouping Worksheets Automatically
    • Hack #3. Prevent Users from Performing Certain Actions
      • 1.4.1. Preventing Save As in a Workbook
      • 1.4.2. Preventing Users from Printing a Workbook
      • 1.4.3. Preventing Users from Inserting More Worksheets
    • Hack #4. Prevent Seemingly Unnecessary Prompts
      • 1.5.1. Enabling Macros When You Dont Have Any
      • 1.5.2. Prompting to Save Nonexistent Changes
      • 1.5.3. Stopping Excel's Warning Prompts for Recorded Macros
    • Hack #5. Hide Worksheets So That They Cannot Be Unhidden
    • Hack #6. Customize the Templates Dialog and Default Workbook
      • 1.7.1. Creating Your Own Template Tab
      • 1.7.2. Using a Custom Default Workbook
    • Hack #7. Create an Index of Sheets in Your Workbook
      • 1.8.1. Creating an Index Sheet by Hand
      • 1.8.2. Auto-Generate an Index Using VBA
      • 1.8.3. Link to the Index from a Context Menu
    • Hack #8. Limit the Scrolling Range of Your Worksheet
      • 1.9.1. Hiding Rows and Columns
      • 1.9.2. Specifying a Valid Range
      • 1.9.3. Activating Only the Used Range
    • Hack #9. Lock and Protect Cells Containing Formulas
      • 1.10.1. Locking Formula Cells
      • 1.10.2. Data Validation
      • 1.10.3. Auto-Toggle Worksheet Protection
    • Hack #10. Find Duplicate Data Using Conditional Formatting
    • Hack #11. Find Data That Appears Two or More Times Using Conditional Formatting
    • Hack #12. Tie Custom Toolbars to a Particular Workbook
    • Hack #13. Outsmart Excel's Relative Reference Handler
    • Hack #14. Remove Phantom Workbook Links
    • Hack #15. Reduce Workbook Bloat
      • 1.16.1. Eliminating Superfluous Formatting
      • 1.16.2. Clean Up Your Macros
      • 1.16.3. Honing Data Sources
      • 1.16.4. Cleaning Corrupted Workbooks
    • Hack #16. Extract Data from a Corrupt Workbook
      • 1.17.1. If You Can Open Your Workbook
      • 1.17.2. If You Cannot Open Your File
  • 2. Hacking Excel's Built-in Features
    • 2.1. Hacks 1743
    • Hack #17. Validate Data Based on a List on Another Worksheet
      • 2.2.1. Method 1: Named Ranges
      • 2.2.2. Method 2: the INDIRECT Function
      • 2.2.3. The Pros and Cons of Both Methods
    • Hack #18. Control Conditional Formatting with Checkboxes
      • 2.3.1. Setting Up Checkboxes for Conditional Formatting
      • 2.3.2. Toggling Number Highlighting On and Off
    • Hack #19. Identify Formulas with Conditional Formatting
    • Hack #20. Count or Sum Cells That Meet Conditional Formatting Criteria
      • 2.5.1. An Alternate Path
    • Hack #21. Highlight Every Other Row or Column
      • 2.6.1. Highlighting Dynamically
    • Hack #22. Create 3-D Effects in Tables or Cells
      • 2.7.1. Using a 3-D Effect on a Table of Data
    • Hack #23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox
    • Hack #24. Support Multiple Lists in a ComboBox
    • Hack #25. Create Validation Lists That Change Based on a Selection from Another List
    • Hack #26. Use Replace to Remove Unwanted Characters
    • Hack #27. Convert Text Numbers to Real Numbers
      • 2.12.1. Using Paste Special
      • 2.12.2. Using the TEXT Functions
    • Hack #28. Extract the Numeric Portion of a Cell Entry
    • Hack #29. Customize Cell Comments
      • 2.14.1. Adding a Picture
      • 2.14.2. Extracting Comment Text
    • Hack #30. Sort by More Than Three Columns
    • Hack #31. Random Sorting
    • Hack #32. Manipulate Data with the Advanced Filter
    • Hack #33. Create Custom Number Formats
    • Hack #34. Add More Levels of Undo to Excel for Windows
    • Hack #35. Create Custom Lists
    • Hack #36. Boldface Excel Subtotals
      • 2.21.1. Hacking the Hack
    • Hack #37. Convert Excel Formulas and Functions to Values
      • 2.22.1. Using Paste Special
      • 2.22.2. Using Copy Here As Values Only
      • 2.22.3. Using a Macro
    • Hack #38. Automatically Add Data to a Validation List
    • Hack #39. Hack Excel's Date and Time Features
      • 2.24.1. Adding Beyond 24 Hours
      • 2.24.2. Time and Date Calculations
      • 2.24.3. Real Dates and Times
      • 2.24.4. A Date Bug?
    • Hack #40. Enable Grouping and Outlining on a Protected Worksheet
    • Hack #41. Prevent Blanks/Missing Fields in a Table
    • Hack #42. Provide Decreasing Data Validation Lists
    • Hack #43. Add a Custom List to the Fill Handle
  • 3. Naming Hacks
    • 3.1. Hacks 4449
    • Hack #44. Address Data by Name
    • Hack #45. Use the Same Name for Ranges on Different Worksheets
      • 3.3.1. Using Relative References
    • Hack #46. Create Custom Functions Using Names
      • 3.4.1. Using Names with Intersect
    • Hack #47. Create Ranges That Expand and Contract
    • Hack #48. Nest Dynamic Ranges for Maximum Flexibility
    • Hack #49. Identify Named Ranges on a Worksheet
      • 3.7.1. Method 1
      • 3.7.2. Method 2
  • 4. Hacking PivotTables
    • 4.1. Hacks 5054
    • Hack #50. PivotTables: A Hack in Themselves
      • 4.2.1. Why Are They Called PivotTables?
      • 4.2.2. What Are PivotTables Good For?
      • 4.2.3. Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?
      • 4.2.4. PivotCharts Extend PivotTables
      • 4.2.5. Creating Tables and Lists for Use in PivotTables
      • 4.2.6. PivotTable Creation
    • Hack #51. Share PivotTables but Not Their Data
    • Hack #52. Automate PivotTable Creation
      • 4.4.1. Save Time with a Macro
    • Hack #53. Move PivotTable Grand Totals
    • Hack #54. Efficiently Pivot Another Workbook's Data
  • 5. Charting Hacks
    • 5.1. Hacks 5572
    • Hack #55. Explode a Single Slice from a Pie Chart
    • Hack #56. Create Two Sets of Slices in One Pie Chart
    • Hack #57. Create Charts That Adjust to Data
      • 5.4.1. Plotting the Last x Number of Readings
    • Hack #58. Interact with Your Charts Using Custom Controls
      • 5.5.1. Using a Dynamic Named Range Linked to a Scrollbar
      • 5.5.2. Using a Dynamic Named Range Linked to a Drop-Down List
    • Hack #59. Four Quick Ways to Update Your Charts
      • 5.6.1. Using Drag-and-Drop
      • 5.6.2. Using the Formula Bar
      • 5.6.3. Dragging the Bounding Area
      • 5.6.4. Using Paste Special
    • Hack #60. Hack Together a Simple Thermometer Chart
    • Hack #61. Create a Column Chart with Variable Widths and Heights
    • Hack #62. Create a Speedometer Chart
    • Hack #63. Link Chart Text Elements to a Cell
    • Hack #64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted
      • 5.11.1. Hiding Rows or Columns
      • 5.11.2. Using #N/A to Plot Blank Cells
    • Hack #65. Add a Directional Arrow to the End of a Line Series
    • Hack #66. Place an Arrow on the End of a Horizontal (X) Axis
      • 5.13.1. In Excel 2007
      • 5.13.2. In Older Excel Versions
    • Hack #67. Correct Narrow Columns When Using Dates
    • Hack #68. Position Axis Labels
      • 5.15.1. Changing Label Position
      • 5.15.2. Reversing Label Order
    • Hack #69. Tornado Chart
    • Hack #70. Gauge Chart
    • Hack #71. Conditional Highlighting Axis Labels
    • Hack #72. Create Totals on a Stacked Column Chart
  • 6. Hacking Formulas and Functions
    • 6.1. Hacks 73105
    • Hack #73. Add Descriptive Text to Your Formulas
    • Hack #74. Move Relative Formulas Without Changing References
    • Hack #75. Compare Two Excel Ranges
      • 6.4.1. Method 1: Using True or False
      • 6.4.2. Method 2: Using Conditional Formatting
    • Hack #76. Fill All Blank Cells in a List
      • 6.5.1. Method 1: Filling Blanks via a Formula
      • 6.5.2. Method 2: Filling Blanks via a Macro
    • Hack #77. Make Your Formulas Increment by Rows When You Copy Across Columns
    • Hack #78. Convert Dates to Excel Formatted Dates
    • Hack #79. Sum or Count Cells While Avoiding Error Values
    • Hack #80. Reduce the Impact of Volatile Functions on Recalculation
    • Hack #81. Count Only One Instance of Each Entry in a List
      • 6.10.1. Before Excel 2007
      • 6.10.2. Excel 2007
      • 6.10.3. Using a Pivot Table
    • Hack #82. Sum Every Second, Third, or Nth Row or Cell
      • 6.11.1. Using an Array Formula
      • 6.11.2. Using SUMPRODUCT
      • 6.11.3. Using DSUM
    • Hack #83. Find the Nth Occurrence of a Value
    • Hack #84. Make the Excel Subtotal Function Dynamic
    • Hack #85. Add Date Extensions
    • Hack #86. Convert Numbers with the Negative Sign on the Right to Excel Numbers
    • Hack #87. Display Negative Time Values
      • 6.16.1. Method 1: Changing Excel's Default Date System
      • 6.16.2. Method 2: Using the TEXT Function
      • 6.16.3. Method 3: Using a Custom Format
    • Hack #88. Use the VLOOKUP Function Across Multiple Tables
    • Hack #89. Show Total Time As Days, Hours, and Minutes
    • Hack #90. Determine the Number of Specified Days in Any Month
    • Hack #91. Construct Mega-Formulas
    • Hack #92. Hack Mega-Formulas that Reference Other Workbooks
    • Hack #93. Hack One of Excel's Database Functions to Take the Place of Many Functions
      • 6.22.1. Using DCOUNT to Filter on Two Criteria
      • 6.22.2. Making the Comparison Operators Interchangeable
    • Hack #94. Extract Specified Words from a Text String
      • 6.23.1. Getting the Last Word
      • 6.23.2. Getting the First Word
      • 6.23.3. Get the Nth Word
    • Hack #95. Count Words in a Cell or Range of Cells
      • 6.24.1. SUBSTITUTE
      • 6.24.2. LEN
      • 6.24.3. Putting It Together
      • 6.24.4. Hacking the Hack
    • Hack #96. Return a Worksheet Name to a Cell
      • 6.25.1. Create a List of Worksheet Names
      • 6.25.2. Extract Worksheet Names Only
      • 6.25.3. Use the List in Formulas
      • 6.25.4. Make the Range Address Variable
    • Hack #97. Sum Cells with Multiple Criteria
      • 6.26.1. SUMIF
      • 6.26.2. DSUM
      • 6.26.3. SUMPRODUCT
      • 6.26.4. SUM and IF
    • Hack #98. Count Cells with Multiple Criteria
      • 6.27.1. Array Formulas
      • 6.27.2. SUMPRODUCT
    • Hack #99. Calculate a Sliding Tax Scale
      • 6.28.1. Using IF/SUM
      • 6.28.2. Using a VLOOKUP Formula
      • 6.28.3. Using a Custom Function
    • Hack #100. Add/Subtract Months from a Date
      • 6.29.1. EDATE
      • 6.29.2. Without EDATE
    • Hack #101. Find the Last Day of Any Given Month
      • 6.30.1. Using Formulas
      • 6.30.2. Using EOMONTH
      • 6.30.3. Using a Custom Function
    • Hack #102. Calculate a Person's Age
    • Hack #103. Return the Weekday of a Date
      • 6.32.1. Get the Weekday as a Number
      • 6.32.2. Return the Weekday as Weekday Name
      • 6.32.3. Return the Weekday as Weekday Text
    • Hack #104. Evaluate a Text Equation
    • Hack #105. Lookup from Within a Cell
      • 6.34.1. CHOOSE and MATCH
      • 6.34.2. Keeping It Clean and Global
      • 6.34.3. Lookup Scale
  • 7. Macro Hacks
    • 7.1. Hacks 106134
    • Hack #106. Speed Up Code While Halting Screen Flicker
    • Hack #107. Run a Macro at a Set Time
    • Hack #108. Use CodeNames to Reference Sheets in Excel Workbooks
    • Hack #109. Connect Buttons to Macros Easily
    • Hack #110. Create a Workbook Splash Screen
    • Hack #111. Display a "Please Wait" Message
    • Hack #112. Have a Cell Ticked or Unticked upon Selection
    • Hack #113. Count or Sum Cells That Have a Specified Fill Color
    • Hack #114. Add the Microsoft Excel Calendar Control to Any Excel Workbook
    • Hack #115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
    • Hack #116. Retrieve a Workbook's Name and Path
    • Hack #117. Get Around Excel's Three-Criteria Limit for Conditional Formatting
    • Hack #118. Run Procedures on Protected Worksheets
    • Hack #119. Distribute Macros
      • 7.15.1. Add a Menu Item
    • Hack #120. Delete Rows Based on a Condition
      • 7.16.1. With AutoFilter
      • 7.16.2. Without AutoFilter
    • Hack #121. Track and Report Changes in Excel
      • 7.17.1. Track Changes on a Particular Worksheet
      • 7.17.2. Track Changes on All Worksheets in One Workbook
    • Hack #122. Automatically Add Date/Time to a Cell upon Entry
    • Hack #123. Create a List of Workbook Hyperlinks
      • 7.19.1. The Code
      • 7.19.2. Running the Hack
    • Hack #124. Advanced Find
      • 7.20.1. The UserForm
      • 7.20.2. The Code
      • 7.20.3. Running the Hack
    • Hack #125. Find a Number Between Two Numbers
      • 7.21.1. The Code
      • 7.21.2. Running the Hack
    • Hack #126. Convert Formula References from Relative to Absolute
      • 7.22.1. Less Complicated Formulas
      • 7.22.2. Mega or Array Formulas
    • Hack #127. Name a Workbook with the Text in a Cell
      • 7.23.1. The Code
      • 7.23.2. Running the Hack
    • Hack #128. Hide and Restore Toolbars in Excel
      • 7.24.1. Attaching Your Toolbar to the Workbook
      • 7.24.2. Coding the Toolbar Show and Restore
    • Hack #129. Sort Worksheets
      • 7.25.1. The Code
      • 7.25.2. Running the Hack
    • Hack #130. Password-Protect a Worksheet from Viewing
      • 7.26.1. The Code
      • 7.26.2. Running the Hack
    • Hack #131. Change Text to Upper- or Proper Case
      • 7.27.1. The Code
      • 7.27.2. Running the Hack
    • Hack #132. Force Text to Upper- or Proper Case
      • 7.28.1. The Code
      • 7.28.2. Running the Hack
      • 7.28.3. Hacking the Hack
    • Hack #133. Prevent Case Sensitivity in VBA Code
      • 7.29.1. Ucase Function
      • 7.29.2. Option Compare Text
    • Hack #134. Display AutoFilter Criteria
  • 8. Cross-Application Hacks
    • 8.1. Hacks 135138
    • Hack #135. Import Data from Access 2007 into Excel 2007
      • 8.2.1. The Code
      • 8.2.2. Running the Hack
    • Hack #136. Retrieve Data from Closed Workbooks
      • 8.3.1. Excel 2007 and Windows Vista
      • 8.3.2. Windows XP
    • Hack #137. Automate Word from Excel
      • 8.4.1. The Code
      • 8.4.2. Running the Hack
    • Hack #138. Automate Outlook from Excel
      • 8.5.1. The Code
      • 8.5.2. Running the Hack
  • Index
  • About the Authors
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje