Excel Hacks, Second Edition

Print $29.99

Print+Ebook $32.99

Ebook $23.99

(Mobi, PDF, ePub)

Safari Books Online

What is this?

Print £22.99

What is this?

Excel Hacks, Second Edition

Tips & Tools for Streamlining Your Spreadsheets

By: David Hawley, Raina Hawley
Publisher:: O'Reilly Media
Released:: June 2007
Pages:: 416

Product Editions

Description

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.

Full Description

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.

You can either browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. However you use it, Excel Hacks will help you increase productivity and give you hours of "hacking" enjoyment along the way.

Table of Contents

Chapter 1 Reducing Workbook and Worksheet Frustration
1. Hacks 1–16
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. Find Data That Appears Two or More Times Using Conditional Formatting
13. Tie Custom Toolbars to a Particular Workbook
14. Outsmart Excel's Relative Reference Handler
15. Remove Phantom Workbook Links
16. Reduce Workbook Bloat
17. Extract Data from a Corrupt Workbook
Chapter 2 Hacking Excel's Built-in Features
1. Hacks 17–43
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 3-D 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. Use Replace… to Remove Unwanted Characters
12. Convert Text Numbers to Real Numbers
13. Extract the Numeric Portion of a Cell Entry
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
25. Enable Grouping and Outlining on a Protected Worksheet
26. Prevent Blanks/Missing Fields in a Table
27. Provide Decreasing Data Validation Lists
28. Add a Custom List to the Fill Handle
Chapter 3 Naming Hacks
1. Hacks 44–49
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 50–54
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 55–72
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. Four 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 Empty or FALSE Formula Cells Are Not Plotted
12. Add a Directional Arrow to the End of a Line Series
13. Place an Arrow on the End of a Horizontal (X) Axis
14. Correct Narrow Columns When Using Dates
15. Position Axis Labels
16. Tornado Chart
17. Gauge Chart
18. Conditional Highlighting Axis Labels
19. Create Totals on a Stacked Column Chart
Chapter 6 Hacking Formulas and Functions
1. Hacks 73–105
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 Count 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
23. Extract Specified Words from a Text String
24. Count Words in a Cell or Range of Cells
25. Return a Worksheet Name to a Cell
26. Sum Cells with Multiple Criteria
27. Count Cells with Multiple Criteria
28. Calculate a Sliding Tax Scale
29. Add/Subtract Months from a Date
30. Find the Last Day of Any Given Month
31. Calculate a Person's Age
32. Return the Weekday of a Date
33. Evaluate a Text Equation
34. Lookup from Within a Cell
Chapter 7 Macro Hacks
1. Hacks 106–134
2. Speed Up Code While Halting Screen Flicker
3. Run a Macro at a Set Time
4. Use CodeNames 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
16. Delete Rows Based on a Condition
17. Track and Report Changes in Excel
18. Automatically Add Date/Time to a Cell upon Entry
19. Create a List of Workbook Hyperlinks
20. Advanced Find
21. Find a Number Between Two Numbers
22. Convert Formula References from Relative to Absolute
23. Name a Workbook with the Text in a Cell
24. Hide and Restore Toolbars in Excel
25. Sort Worksheets
26. Password-Protect a Worksheet from Viewing
27. Change Text to Upper- or Proper Case
28. Force Text to Upper- or Proper Case
29. Prevent Case Sensitivity in VBA Code
30. Display AutoFilter Criteria
Chapter 8 Cross-Application Hacks
1. Hacks 135–138
2. Import Data from Access 2007 into Excel 2007
3. Retrieve Data from Closed Workbooks
4. Automate Word from Excel
5. Automate Outlook from Excel

Colophon

Product Details

Title:

Excel Hacks, Second Edition

By:

David Hawley, Raina Hawley

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print Release:

June 2007

Ebook Release:

December 2008

Pages:

416

Print ISBN:

978-0-596-52834-8

| ISBN 10:

0-596-52834-5

Ebook ISBN:

978-0-596-15957-3

| ISBN 10:

0-596-15957-9

Customer Reviews

About the Authors

David Hawley

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.

View David Hawley's full profile page.
Raina Hawley

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.

View Raina Hawley's full profile page.

Colophon

The tool on the cover of Excel Hacks is a trowel. The trowel shown is the type that is generally used in everyday gardening tasks such as removing stones from dirt, planting, and removing weeds.

The cover image is from the Stockbyte Work Tools CD. The cover font is Adobe ITC Garamond. The text font is Linotype Birka; the heading font is Adobe Helvetica Neue Condensed; and the code font is LucasFont's TheSans Mono Condensed.

Service and support by Satisfaction

O'Reilly Books & Videos

Excel Hacks, Second Edition

Tips & Tools for Streamlining Your Spreadsheets

Chapter 1 Reducing Workbook and Worksheet Frustration

Hacks 1–16

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

Find Data That Appears Two or More Times 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 17–43

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 3-D 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

Use Replace… to Remove Unwanted Characters

Convert Text Numbers to Real Numbers

Extract the Numeric Portion of a Cell Entry

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

Enable Grouping and Outlining on a Protected Worksheet

Prevent Blanks/Missing Fields in a Table

Provide Decreasing Data Validation Lists

Add a Custom List to the Fill Handle

Chapter 3 Naming Hacks

Hacks 44–49

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 50–54

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 55–72

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

Four 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 Empty or FALSE Formula Cells Are Not Plotted

Add a Directional Arrow to the End of a Line Series

Place an Arrow on the End of a Horizontal (X) Axis

Correct Narrow Columns When Using Dates