Table of Contents

     
   
  Table of Contents
  Reviews
  Reader Reviews
  Errata
  Academic
          
Excel Hacks
By David  Hawley, Raina  Hawley
 
Publisher : O'Reilly
Pub Date : March 2004
ISBN : 0-596-00625-X
Pages : 304
   
Copyright
      Dedication
      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 and Macintosh Users
        Conventions Used in This Book
        Using Code Examples
        How to Contact Us
        Chapter 1.   Reducing Workbook and Worksheet Frustration
        Hacks #1-15
        Section 1.   Create a Personal View of Your Workbooks
        Section 2.   Enter Data into Multiple Worksheets Simultaneously
        Section 3.   Prevent Users from Performing Certain Actions
        Section 4.   Prevent Seemingly Unnecessary Prompts
        Section 5.   Hide Worksheets So That They Cannot Be Unhidden
        Section 6.   Customize the Templates Dialog and Default Workbook
        Section 7.   Create an Index of Sheets in Your Workbook
        Section 8.   Limit the Scrolling Range of Your Worksheet
        Section 9.   Lock and Protect Cells Containing Formulas
        Section 10.   Find Duplicate Data using Conditional Formatting
        Section 11.   Tie Custom Toolbars to a Particular Workbook
        Section 12.   Outsmart Excel's Relative Reference Handler
        Section 13.   Remove Phantom Workbook Links
        Section 14.   Reduce Workbook Bloat
        Section 15.   Extract Data from a Corrupt Workbook
        Chapter 2.   Hacking Excel's Built-in Features
        Hacks #16-38
        Section 16.   Validate Data Based on a List on Another Worksheet
        Section 17.   Control Conditional Formatting with Checkboxes
        Section 18.   Identify Formulas with Conditional Formatting
        Section 19.   Count or Sum Cells That Meet Conditional Formatting Criteria
        Section 20.   Highlight Every Other Row or Column
        Section 21.   Create 3D Effects in Tables or Cells
        Section 22.   Turn Conditional Formatting and Data Validation On and Off with a Checkbox
        Section 23.   Support Multiple Lists in a ComboBox
        Section 24.   Create Validation Lists That Change Based on a Selection from Another List
        Section 25.   Force Data Validation to Reference a List on Another Worksheet
        Section 26.   Use Replace... to Remove Unwanted Characters
        Section 27.   Convert Text Numbers to Real Numbers
        Section 28.   Customize Cell Comments
        Section 29.   Sort by More Than Three Columns
        Section 30.   Random Sorting
        Section 31.   Manipulate Data with the Advanced Filter
        Section 32.   Create Custom Number Formats
        Section 33.   Add More Levels of Undo to Excel for Windows
        Section 34.   Create Custom Lists
        Section 35.   Boldface Excel Subtotals
        Section 36.   Convert Excel Formulas and Functions to Values
        Section 37.   Automatically Add Data to a Validation List
        Section 38.   Hack Excel's Date and Time Features
        Chapter 3.   Naming Hacks
        Hacks #39-44
        Section 39.   Address Data by Name
        Section 40.   Use the Same Name for Ranges on Different Worksheets
        Section 41.   Create Custom Functions Using Names
        Section 42.   Create Ranges That Expand and Contract
        Section 43.   Nest Dynamic Ranges for Maximum Flexibility
        Section 44.   Identify Named Ranges on a Worksheet
        Chapter 4.   Hacking PivotTables
        Hacks #46-49
        Section 45.   PivotTables: A Hack in Themselves
        Section 46.   Share PivotTables but Not Their Data
        Section 47.   Automate PivotTable Creation
        Section 48.   Move PivotTable Grand Totals
        Section 49.   Efficiently Pivot Another Workbook's Data
        Chapter 5.   Charting Hacks
        Hacks #50-59
        Section 50.   Explode a Single Slice from a Pie Chart
        Section 51.   Create Two Sets of Slices in One Pie Chart
        Section 52.   Create Charts That Adjust to Data
        Section 53.   Interact with Your Charts Using Custom Controls
        Section 54.   Three Quick Ways to Update Your Charts
        Section 55.   Hack Together a Simple Thermometer Chart
        Section 56.   Create a Column Chart with Variable Widths and Heights
        Section 57.   Create a Speedometer Chart
        Section 58.   Link Chart Text Elements to a Cell
        Section 59.   Hack Chart Data So That Blank Cells Are Not Plotted
        Chapter 6.   Hacking Formulas and Functions
        Hacks #60-80
        Section 60.   Add Descriptive Text to Your Formulas
        Section 61.   Move Relative Formulas Without Changing References
        Section 62.   Compare Two Excel Ranges
        Section 63.   Fill All Blank Cells in a List
        Section 64.   Make Your Formulas Increment by Rows When You Copy Across Columns
        Section 65.   Convert Dates to Excel Formatted Dates
        Section 66.   Sum or Counting Cells While Avoiding Error Values
        Section 67.   Reduce the Impact of Volatile Functions on Recalculation
        Section 68.   Count Only One Instance of Each Entry in a List
        Section 69.   Sum Every Second, Third, or nth Row or Cell
        Section 70.   Find the nth Occurrence of a Value
        Section 71.   Make the Excel Subtotal Function Dynamic
        Section 72.   Add Date Extensions
        Section 73.   Convert Numbers with the Negative Sign on the Right to Excel Numbers
        Section 74.   Display Negative Time Values
        Section 75.   Use the VLOOKUP Function Across Multiple Tables
        Section 76.   Show Total Time as Days, Hours, and Minutes
        Section 77.   Determine the Number of Specified Days in Any Month
        Section 78.   Construct Mega-Formulas
        Section 79.   Hack Mega-Formulas that Reference Other Workbooks
        Section 80.   Hack One of Excel's Database Functions to Take the Place of Many Functions
        Chapter 7.   Macro Hacks
        Hacks #81-94
        Section 81.   Speed Up Code While Halting Screen Flicker
        Section 82.   Run a Macro at a Set Time
        Section 83.   Use CodeName to Reference Sheets in Excel Workbooks
        Section 84.   Connect Buttons to Macros Easily
        Section 85.   Create a Workbook Splash Screen
        Section 86.   Display a "Please Wait" Message
        Section 87.   Have a Cell Ticked or Unticked upon Selection
        Section 88.   Count or Sum Cells That Have a Specified Fill Color
        Section 89.   Add the Microsoft Excel Calendar Control to Any Excel Workbook
        Section 90.   Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
        Section 91.   Retrieve a Workbook's Name and Path
        Section 92.   Get Around Excel's Three-Criteria Limit for Conditional Formatting
        Section 93.   Run Procedures on Protected Worksheets
        Section 94.   Distribute Macros
        Chapter 8.   Connecting Excel to the World
        Hacks #95-100
        Section 95.   Load an XML Document into Excel
        Section 96.   Save to SpreadsheetML and Extracting Data
        Section 97.   Create Spreadsheets using SpreadsheetML
        Section 98.   Import Data Directly into Excel
        Section 99.   Access SOAP Web Services from Excel
        Section 100.   Create Excel Spreadsheets Using Other Environments
        Glossary
      Colophon


Excel Hacks
Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets
ISBN: 0596528345
EAN: 2147483647
Year: 2003
Pages: 136

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net