Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel's advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks -- clever tools, tips and techniques -- that will leapfrog your work beyond the ordinary.

Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known "backdoor" tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to:

• Reduce workbook and worksheet frustration -- manage how users interact with worksheets, find and highlight information, and deal with debris and corruption.
• Analyze and manage data -- extend and automate these features, moving beyond the limited tasks they were designed to perform.
• Hack names -- learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.
• Get the most out of PivotTables -- avoid the problems that make them frustrating and learn how to extend them.
• Create customized charts -- tweak and combine Excel's built-in charting capabilities.
• Hack formulas and functions -- subjects range from moving formulas around to dealing with datatype issues to improving recalculation time.
• Make the most of macros -- including ways to manage them and use them to extend other features.
• Use the enhanced capabilities of Microsoft Office 2007 to combine Excel with Word, Access, and Outlook.

David Hawley of Perth, Australia is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel. The company's web site at www.ozgrid.com provides online and email Excel training, a directory of useful Excel add-ins, a user forum, and lots of tips and tricks.

Raina Hawley of Perth, Australia is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel. The company's web site at www.ozgrid.com provides online and email Excel training, a directory of useful Excel add-ins, a user forum, and lots of tips and tricks. Raina is also a lecturing team member at West Australian Institute of Management (WAIM) with specific expertise in Word, Excel, Powerpoint, Internet and email.

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, Macintosh, and Earlier Excel Versions;
Conventions Used in This Book;
Using Code Examples;
Safari® Enabled;
How to Contact Us;
Chapter 1: Reducing Workbook and Worksheet Frustration;
1.1 Hacks 1-16;
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 Find Data That Appears Two or More Times Using Conditional Formatting;
12 Tie Custom Toolbars to a Particular Workbook;
13 Outsmart Excel's Relative Reference Handler;
14 Remove Phantom Workbook Links;
15 Reduce Workbook Bloat;
16 Extract Data from a Corrupt Workbook;
Chapter 2: Hacking Excel's Built-in Features;
2.1 Hacks 17-43;
17 Validate Data Based on a List on Another Worksheet;
18 Control Conditional Formatting with Checkboxes;
19 Identify Formulas with Conditional Formatting;
20 Count or Sum Cells That Meet Conditional Formatting Criteria;
21 Highlight Every Other Row or Column;
22 Create 3-D Effects in Tables or Cells;
23 Turn Conditional Formatting and Data Validation On and Off with a Checkbox;
24 Support Multiple Lists in a ComboBox;
25 Create Validation Lists That Change Based on a Selection from Another List;
26 Use Replace... to Remove Unwanted Characters;
27 Convert Text Numbers to Real Numbers;
28 Extract the Numeric Portion of a Cell Entry;
29 Customize Cell Comments;
30 Sort by More Than Three Columns;
31 Random Sorting;
32 Manipulate Data with the Advanced Filter;
33 Create Custom Number Formats;
34 Add More Levels of Undo to Excel for Windows;
35 Create Custom Lists;
36 Boldface Excel Subtotals;
37 Convert Excel Formulas and Functions to Values;
38 Automatically Add Data to a Validation List;
39 Hack Excel's Date and Time Features;
40 Enable Grouping and Outlining on a Protected Worksheet;
41 Prevent Blanks/Missing Fields in a Table;
42 Provide Decreasing Data Validation Lists;
43 Add a Custom List to the Fill Handle;
Chapter 3: Naming Hacks;
3.1 Hacks 44-49;
44 Address Data by Name;
45 Use the Same Name for Ranges on Different Worksheets;
46 Create Custom Functions Using Names;
47 Create Ranges That Expand and Contract;
48 Nest Dynamic Ranges for Maximum Flexibility;
49 Identify Named Ranges on a Worksheet;
Chapter 4: Hacking PivotTables;
4.1 Hacks 50-54;
50 PivotTables: A Hack in Themselves;
51 Share PivotTables but Not Their Data;
52 Automate PivotTable Creation;
53 Move PivotTable Grand Totals;
54 Efficiently Pivot Another Workbook's Data;
Chapter 5: Charting Hacks;
5.1 Hacks 55-72;
55 Explode a Single Slice from a Pie Chart;
56 Create Two Sets of Slices in One Pie Chart;
57 Create Charts That Adjust to Data;
58 Interact with Your Charts Using Custom Controls;
59 Four Quick Ways to Update Your Charts;
60 Hack Together a Simple Thermometer Chart;
61 Create a Column Chart with Variable Widths and Heights;
62 Create a Speedometer Chart;
63 Link Chart Text Elements to a Cell;
64 Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted;
65 Add a Directional Arrow to the End of a Line Series;
66 Place an Arrow on the End of a Horizontal (X) Axis;
67 Correct Narrow Columns When Using Dates;
68 Position Axis Labels;
69 Tornado Chart;
70 Gauge Chart;
71 Conditional Highlighting Axis Labels;
72 Create Totals on a Stacked Column Chart;
Chapter 6: Hacking Formulas and Functions;
6.1 Hacks 73-105;
73 Add Descriptive Text to Your Formulas;
74 Move Relative Formulas Without Changing References;
75 Compare Two Excel Ranges;
76 Fill All Blank Cells in a List;
77 Make Your Formulas Increment by Rows When You Copy Across Columns;
78 Convert Dates to Excel Formatted Dates;
79 Sum or Count Cells While Avoiding Error Values;
80 Reduce the Impact of Volatile Functions on Recalculation;
81 Count Only One Instance of Each Entry in a List;
82 Sum Every Second, Third, or Nth Row or Cell;
83 Find the Nth Occurrence of a Value;
84 Make the Excel Subtotal Function Dynamic;
85 Add Date Extensions;
86 Convert Numbers with the Negative Sign on the Right to Excel Numbers;
87 Display Negative Time Values;
88 Use the VLOOKUP Function Across Multiple Tables;
89 Show Total Time As Days, Hours, and Minutes;
90 Determine the Number of Specified Days in Any Month;
91 Construct Mega-Formulas;
92 Hack Mega-Formulas that Reference Other Workbooks;
93 Hack One of Excel's Database Functions to Take the Place of Many Functions;
94 Extract Specified Words from a Text String;
95 Count Words in a Cell or Range of Cells;
96 Return a Worksheet Name to a Cell;
97 Sum Cells with Multiple Criteria;
98 Count Cells with Multiple Criteria;
99 Calculate a Sliding Tax Scale;
100 Add/Subtract Months from a Date;
101 Find the Last Day of Any Given Month;
102 Calculate a Person's Age;
103 Return the Weekday of a Date;
104 Evaluate a Text Equation;
105 Lookup from Within a Cell;
Chapter 7: Macro Hacks;
7.1 Hacks 106-134;
106 Speed Up Code While Halting Screen Flicker;
107 Run a Macro at a Set Time;
108 Use CodeNames to Reference Sheets in Excel Workbooks;
109 Connect Buttons to Macros Easily;
110 Create a Workbook Splash Screen;
111 Display a "Please Wait" Message;
112 Have a Cell Ticked or Unticked upon Selection;
113 Count or Sum Cells That Have a Specified Fill Color;
114 Add the Microsoft Excel Calendar Control to Any Excel Workbook;
115 Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop;
116 Retrieve a Workbook's Name and Path;
117 Get Around Excel's Three-Criteria Limit for Conditional Formatting;
118 Run Procedures on Protected Worksheets;
119 Distribute Macros;
120 Delete Rows Based on a Condition;
121 Track and Report Changes in Excel;
122 Automatically Add Date/Time to a Cell upon Entry;
123 Create a List of Workbook Hyperlinks;
124 Advanced Find;
125 Find a Number Between Two Numbers;
126 Convert Formula References from Relative to Absolute;
127 Name a Workbook with the Text in a Cell;
128 Hide and Restore Toolbars in Excel;
129 Sort Worksheets;
130 Password-Protect a Worksheet from Viewing;
131 Change Text to Upper- or Proper Case;
132 Force Text to Upper- or Proper Case;
133 Prevent Case Sensitivity in VBA Code;
134 Display AutoFilter Criteria;
Chapter 8: Cross-Application Hacks;
8.1 Hacks 135-138;
135 Import Data from Access 2007 into Excel 2007;
136 Retrieve Data from Closed Workbooks;
137 Automate Word from Excel;
138 Automate Outlook from Excel;
Colophon;