Excel Hacks Tips Tools for Streamlining Your Spreadsheets 2nd Edition by David Hawley, Raina Hawley 9780596515966 0596515960

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

Reducing Workbook and Worksheet Frustration

The 80/20 Rule

Structural Tips

Formatting Tips

Formula Tips

Create a Personal View of Your Workbooks

Enter Data into Multiple Worksheets Simultaneously

Grouping Worksheets Manually

Grouping Worksheets Automatically

Prevent Users from Performing Certain Actions

Preventing Save As… in a Workbook

Preventing Users from Printing a Workbook

Preventing Users from Inserting More Worksheets

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

Hide Worksheets So That They Cannot Be Unhidden

Customize the Templates Dialog and Default Workbook

Creating Your Own Template Tab

Using a Custom Default Workbook

Create an Index of Sheets in Your Workbook

Creating an Index Sheet by Hand

Auto-Generate an Index Using VBA

Link to the Index from a Context Menu

Limit the Scrolling Range of Your Worksheet

Hiding Rows and Columns

Specifying a Valid Range

Activating Only the Used Range

Lock and Protect Cells Containing Formulas

Locking Formula Cells

Data Validation

Auto-Toggle Worksheet Protection

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

Eliminating Superfluous Formatting

Clean Up Your Macros

Honing Data Sources

Cleaning Corrupted Workbooks

Extract Data from a Corrupt Workbook

If You Can Open Your Workbook

If You Cannot Open Your File

Hacking Excel’s Built-in Features

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

Control Conditional Formatting with Checkboxes

Setting Up Checkboxes for Conditional Formatting

Toggling Number Highlighting On and Off

Identify Formulas with Conditional Formatting

Count or Sum Cells That Meet Conditional Formatting Criteria

An Alternate Path

Highlight Every Other Row or Column

Highlighting Dynamically

Create 3-D Effects in Tables or Cells

Using a 3-D Effect on a Table of Data

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

Using Paste Special

Using the TEXT Functions

Extract the Numeric Portion of a Cell Entry

Customize Cell Comments

Adding a Picture

Extracting Comment Text

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

Hacking the Hack

Convert Excel Formulas and Functions to Values

Using Paste Special

Using Copy Here As Values Only

Using a Macro

Automatically Add Data to a Validation List

Hack Excel’s Date and Time Features

Adding Beyond 24 Hours

Time and Date Calculations

Real Dates and Times

A Date Bug?

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

Naming Hacks

Address Data by Name

Use the Same Name for Ranges on Different Worksheets

Using Relative References

Simplify the summing

Create Custom Functions Using Names

Using Names with Intersect

Create Ranges That Expand and Contract

Nest Dynamic Ranges for Maximum Flexibility

Identify Named Ranges on a Worksheet

Method 1

Method 2

Hacking PivotTables

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

PivotTable Creation

Share PivotTables but Not Their Data

Automate PivotTable Creation

Save Time with a Macro

Move PivotTable Grand Totals

Efficiently Pivot Another Workbook’s Data

Charting Hacks

Explode a Single Slice from a Pie Chart

Create Two Sets of Slices in One Pie Chart

Create Charts That Adjust to Data

Plotting the Last x Number of Readings

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

Four Quick Ways to Update Your Charts

Using Drag-and-Drop

Using the Formula Bar

Dragging the Bounding Area

Using Paste Special

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

Hiding Rows or Columns

Using #N/A to Plot Blank Cells

Add a Directional Arrow to the End of a Line Series

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

In Excel 2007

In Older Excel Versions

Correct Narrow Columns When Using Dates

Position Axis Labels

Changing Label Position

Reversing Label Order

Tornado Chart

Gauge Chart

Conditional Highlighting Axis Labels

Create Totals on a Stacked Column Chart

Hacking Formulas and Functions

Add Descriptive Text to Your Formulas

Move Relative Formulas Without Changing References

Compare Two Excel Ranges

Method 1: Using True or False

Method 2: Using Conditional Formatting

Fill All Blank Cells in a List

Method 1: Filling Blanks via a Formula

Method 2: Filling Blanks via a Macro

Make Your Formulas Increment by Rows When You Copy Across Columns

Convert Dates to Excel Formatted Dates

Sum or Count Cells While Avoiding Error Values

Reduce the Impact of Volatile Functions on Recalculation

Count Only One Instance of Each Entry in a List

Before Excel 2007

Excel 2007

Using a Pivot Table

Sum Every Second, Third, or Nth Row or Cell

Using an Array Formula

Using SUMPRODUCT

Using DSUM

Find the Nth Occurrence of a Value

Make the Excel Subtotal Function Dynamic

Add Date Extensions

Convert Numbers with the Negative Sign on the Right to Excel Numbers

Display Negative Time Values

Method 1: Changing Excel’s Default Date System

Method 2: Using the TEXT Function

Method 3: Using a Custom Format

Use the VLOOKUP Function Across Multiple Tables

Show Total Time As Days, Hours, and Minutes

Determine the Number of Specified Days in Any Month

Construct Mega-Formulas

Hack Mega-Formulas that Reference Other Workbooks

Hack One of Excel’s Database Functions to Take the Place of Many Functions

Using DCOUNT to Filter on Two Criteria

Making the Comparison Operators Interchangeable

Extract Specified Words from a Text String

Getting the Last Word

Getting the First Word

Get the Nth Word

Count Words in a Cell or Range of Cells

SUBSTITUTE

LEN

Putting It Together

Hacking the Hack

Return a Worksheet Name to a Cell

Create a List of Worksheet Names

Extract Worksheet Names Only

Use the List in Formulas

Make the Range Address Variable

Sum Cells with Multiple Criteria

SUMIF

DSUM

SUMPRODUCT

SUM and IF

Count Cells with Multiple Criteria

Array Formulas

SUMPRODUCT

Calculate a Sliding Tax Scale

Using IF/SUM

Using a VLOOKUP Formula

Using a Custom Function

Method 1

Method 2

Add/Subtract Months from a Date

EDATE

Without EDATE

Find the Last Day of Any Given Month

Using Formulas

Using EOMONTH

Using a Custom Function

Calculate a Person’s Age

Return the Weekday of a Date

Get the Weekday as a Number

Return the Weekday as Weekday Name

Return the Weekday as Weekday Text

Evaluate a Text Equation

Lookup from Within a Cell

CHOOSE and MATCH

Keeping It Clean and Global

Lookup Scale

Macro Hacks

Speed Up Code While Halting Screen Flicker

Run a Macro at a Set Time

Use CodeNames to Reference Sheets in Excel Workbooks

Connect Buttons to Macros Easily

Create a Workbook Splash Screen

Display a “Please Wait” Message

Have a Cell Ticked or Unticked upon Selection

Count or Sum Cells That Have a Specified Fill Color

Add the Microsoft Excel Calendar Control to Any Excel Workbook

Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop

Retrieve a Workbook’s Name and Path

Get Around Excel’s Three-Criteria Limit for Conditional Formatting

Run Procedures on Protected Worksheets

Distribute Macros

Add a Menu Item

Delete Rows Based on a Condition

With AutoFilter

Without AutoFilter

Track and Report Changes in Excel

Track Changes on a Particular Worksheet

Track Changes on All Worksheets in One Workbook

Automatically Add Date/Time to a Cell upon Entry

Create a List of Workbook Hyperlinks

The Code

Running the Hack

Advanced Find

The UserForm

The Code

Running the Hack

Shortcut key

Button

Toolbar

Find a Number Between Two Numbers

The Code

Running the Hack

Convert Formula References from Relative to Absolute

Less Complicated Formulas

The code

Running the hack

Mega or Array Formulas

The code

Running the hack

Name a Workbook with the Text in a Cell

The Code

Running the Hack

Hide and Restore Toolbars in Excel

Attaching Your Toolbar to the Workbook

Coding the Toolbar Show and Restore

Sort Worksheets

The Code

Running the Hack

Password-Protect a Worksheet from Viewing

The Code

Running the Hack

Change Text to Upper- or Proper Case

The Code

Running the Hack

Force Text to Upper- or Proper Case

The Code

Uppercase

Proper case

Running the Hack

Hacking the Hack

Prevent Case Sensitivity in VBA Code

Ucase Function

The code

Running the hack

Option Compare Text

The code

Running the hack

Display AutoFilter Criteria

Cross-Application Hacks

Import Data from Access 2007 into Excel 2007

The Code

Running the Hack

Retrieve Data from Closed Workbooks

Excel 2007 and Windows Vista

The code

Running the hack

Windows XP

The code

Running the hack

Automate Word from Excel

The Code

Running the Hack

Automate Outlook from Excel

The Code

Running the Hack

Index