Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets

ISBN: 0596528345
EAN: 2147483647

Year: 2003
Pages: 136

Authors: David Hawley, Raina Hawley

BUY ON AMAZON

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