Books & Videos

Excel Hacks

100 Industrial Strength Tips and Tools

By David Hawley, Raina Hawley

Publisher: O'Reilly Media

Release Date: February 2009

Pages: 304

Read on Safari with a 10-day trial

Start your free trial now Buy on Amazon

Where’s the cart? Now you can get everything on Safari. To purchase books, visit Amazon or your favorite retailer. Questions? See our FAQ or contact customer service:

1-800-889-8969 / 707-827-7019
support@oreilly.com

View/Submit Errata

If you think that getting creative with Excel means the underhanded tweaking of numbers, think again. Excel Hacks shows even the most experienced users how to do things with Excel they might never have thought of doing--and lets them have a little fun while they're at it. Microsoft Excel is not just the dominant spreadsheet in the world; it's also one of the most popular applications ever created. Its success lies not only in its power and flexibility, but also in its streamlined, familiar interface that casually conceals its considerable capabilities. You don't need to know everything that Excel can do in order to use it effectively, but if you're like the millions of Excel power users looking to improve productivity, then Excel Hacks will show you a wide variety of Excel tasks you can put to use, most of which are off the beaten path. With this book, Excel power users can bring a hacker's creative approach to both common and uncommon Excel topics--"hackers" in this sense being those who like to tinker with technology to improve it. The "100 Industrial Strength Tips and Tools" in Excel Hacks include little known "backdoor" adjustments for everything from reducing workbook and worksheet frustration to hacking built-in features such as pivot tables, charts, formulas and functions, and even the macro language. This resourceful roll-up-your-sleeves guide is for intermediate to advanced Excel users eager to explore new ways to make Excel do things--from data analysis to worksheet management to import/export--that you never thought possible. Excel Hacks will help you increase productivity with Excel and give you hours of "hacking" enjoyment along the way.

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

Colophon

Books & Videos

Excel Hacks

100 Industrial Strength Tips and Tools

Read on Safari with a 10-day trial

Table of Contents

Chapter 1 Reducing Workbook and Worksheet Frustration

Hacks #1-15

Create a Personal View of Your Workbooks

Enter Data into Multiple Worksheets Simultaneously

Prevent Users from Performing Certain Actions

Prevent Seemingly Unnecessary Prompts

Hide Worksheets So That They Cannot Be Unhidden

Customize the Templates Dialog and Default Workbook

Create an Index of Sheets in Your Workbook

Limit the Scrolling Range of Your Worksheet

Lock and Protect Cells Containing Formulas

Find Duplicate Data using Conditional Formatting

Tie Custom Toolbars to a Particular Workbook

Outsmart Excel's Relative Reference Handler

Remove Phantom Workbook Links

Reduce Workbook Bloat

Extract Data from a Corrupt Workbook

Chapter 2 Hacking Excel's Built-in Features

Hacks #16-38

Validate Data Based on a List on Another Worksheet

Control Conditional Formatting with Checkboxes

Identify Formulas with Conditional Formatting

Count or Sum Cells That Meet Conditional Formatting Criteria

Highlight Every Other Row or Column

Create 3D Effects in Tables or Cells

Turn Conditional Formatting and Data Validation On and Off with a Checkbox

Support Multiple Lists in a ComboBox

Create Validation Lists That Change Based on a Selection from Another List

Force Data Validation to Reference a List on Another Worksheet

Use Replace... to Remove Unwanted Characters

Convert Text Numbers to Real Numbers

Customize Cell Comments

Sort by More Than Three Columns

Random Sorting

Manipulate Data with the Advanced Filter

Create Custom Number Formats

Add More Levels of Undo to Excel for Windows

Create Custom Lists

Boldface Excel Subtotals

Convert Excel Formulas and Functions to Values

Automatically Add Data to a Validation List

Hack Excel's Date and Time Features

Chapter 3 Naming Hacks

Hacks #39-44

Address Data by Name

Use the Same Name for Ranges on Different Worksheets

Create Custom Functions Using Names

Create Ranges That Expand and Contract

Nest Dynamic Ranges for Maximum Flexibility

Identify Named Ranges on a Worksheet

Chapter 4 Hacking PivotTables

Hacks #46-49

PivotTables: A Hack in Themselves

Share PivotTables but Not Their Data

Automate PivotTable Creation

Move PivotTable Grand Totals

Efficiently Pivot Another Workbook's Data

Chapter 5 Charting Hacks

Hacks #50-59

Explode a Single Slice from a Pie Chart

Create Two Sets of Slices in One Pie Chart

Create Charts That Adjust to Data

Interact with Your Charts Using Custom Controls

Three Quick Ways to Update Your Charts

Hack Together a Simple Thermometer Chart

Create a Column Chart with Variable Widths and Heights

Create a Speedometer Chart

Link Chart Text Elements to a Cell

Hack Chart Data So That Blank Cells Are Not Plotted

Chapter 6 Hacking Formulas and Functions

Hacks #60-80

Add Descriptive Text to Your Formulas

Move Relative Formulas Without Changing References

Compare Two Excel Ranges

Fill All Blank Cells in a List