Excel Hacks by Hawley, David -- Read -- Imperial Library of Trantor

Index

Excel Hacks

SPECIAL OFFER: Upgrade this ebook with O’Reilly 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

1. Reducing Workbook and Worksheet Frustration

Hacks #1-15

The 80/20 Rule Structural Tips Formatting Tips Formula Tips

Hack #1. Create a Personal View of Your Workbooks Hack #2. Enter Data into Multiple Worksheets Simultaneously

Grouping Worksheets Manually Grouping Worksheets Automatically

Hack #3. Prevent Users from Performing Certain Actions

Preventing Save As... in a Workbook Preventing Users from Printing a Workbook Preventing Users from Inserting More Worksheets

Hack #4. Prevent Seemingly Unnecessary Prompts

Enabling Macros When You Don't Have Any Prompting to Save Nonexistent Changes 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

Creating Your Own Template Tab Using a Custom 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

Eliminating Superfluous Formatting Honing Data Sources Cleaning Corrupted Workbooks

Hack #15. Extract Data from a Corrupt Workbook

If You Can Open Your Workbook If You Cannot Open Your File

2. Hacking Excel's Built-in Features

Hacks #16-38 Hack #16. Validate Data Based on a List on Another Worksheet

Method 1: Named Ranges Method 2: the INDIRECT Function The Pros and Cons of Both Methods

Hack #17. Control Conditional Formatting with Checkboxes

Setting Up Checkboxes for Conditional Formatting Toggling Number Highlighting On and Off

Hack #18. Identify Formulas with Conditional Formatting Hack #19. Count or Sum Cells That Meet Conditional Formatting Criteria

An Alternate Path

Hack #20. Highlight Every Other Row or Column Hack #21. Create 3D Effects in Tables or Cells

Using a 3D Effect on a Table of Data

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

Method 1: Named Ranges Method 2: the INDIRECT Function The Pros and Cons of Each Method

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

Hacking the Hack

Hack #36. Convert Excel Formulas and Functions to Values

Using Paste Special Using Copy Here as Values Only Using a Macro

Hack #37. Automatically Add Data to a Validation List Hack #38. Hack Excel's Date and Time Features

Adding Beyond 24 Hours Time and Date Calculations Real Dates and Times A Date Bug?

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

Method 1 Method 2

4. Hacking PivotTables

Hacks #46-49 Hack #45. PivotTables: A Hack in Themselves

Why Are They Called PivotTables? What Are PivotTables Good For? Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability? PivotCharts Extend PivotTables Creating Tables and Lists for Use in PivotTables The PivotTable and PivotChart Wizard

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

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

Plotting the Last x Number of Readings

Hack #53. Interact with Your Charts Using Custom Controls

Using a Dynamic Named Range Linked to a Scrollbar Using a Dynamic Named Range Linked to a Drop-Down List

Hack #54. Three Quick Ways to Update Your Charts

Using Drag-and-Drop Using the Formula Bar Dragging the Bounding Area

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

Hiding Rows or Columns

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

Method 1: Using True or False Method 2: Using Conditional Formatting

Hack #63. Fill All Blank Cells in a List

Method 1: Filling Blanks Via a Formula Method 2: Filling Blanks Via a Macro

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

Method 1: Changing Excel's Default Date System Method 2: Using the TEXT Function Method 3: Using a Custom Format

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

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 "Please Wait" Message 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

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

Running the Hack Hacking the Hack

Making the Query Dynamic Using Different Data Graphing Results

Hack #99. Access SOAP Web Services from Excel Hack #100. Create Excel Spreadsheets Using Other Environments

9. Glossary About the Authors Colophon SPECIAL OFFER: Upgrade this ebook with O’Reilly

← Prev
Back
Next →

← Prev
Back
Next →