ExcelTips 2008 Archive

The ExcelTips 2008 archive includes hundreds of valuable tips.
The following is the Table of Contents for the archive:

Introduction

• What’s In This E-Book?
• What about Viruses?
• Need More Tips?
• A Special Note of Thanks
• Sharing this Document

General Tips

• I Hate the Excel 2007 User Interface
• Canceling a Command
• Arranging Workbook Windows
• Starting in Safe Mode
• Excluding a Specific Add-In at Startup
• Disabling Excel's Help System
• Where Is that Name?
• Turning Off Names
• Refusing Online Help
• Turning Off Dynamic Menus
• Animated Menus
• Turning Off Error Checking
• A Shortcut for Switching Focus
• Activating the Formula Bar with the Keyboard
• Limiting What is Shown in the Formula Bar
• Enlarging the Formula Bar
• Turning Off ScreenTips
• Simultaneous Scrolling
• Removing Personal Information
• Clearing Large Clipboard Entries
• Changing Error Checking Rules
• Forcing Manual Calculation For a Workbook
• Making AutoComplete Work for an Entire Column
• Excel Proficiency Testing
• Selected Cells Not Shaded
• Problem with Missing Context Menu Option
• Changing the Shortcut Menu
• Always Open at 100% Zoom
• Countering Compressed Columns
• Changing Horizontal Orientation
• Tab Key Won't Move from Cell to Cell in Locked Worksheet
• Turning Headers On and Off
• Making Column Headers Vertically Wider
• Forcing Stubborn Recalculation
• Viewing Same Cells on Different Worksheets
• Backing Up Custom Dictionaries
• Disabling a Function Key
• Simplifying the Font List
• Shortcut for Viewing Formulas

Editing Tips

• Creating New Windows
• Canceling an Edit
• Quickly Selecting Cells
• Selecting Noncontiguous Ranges with the Keyboard
• Displaying a Hidden First Row
• Copying Between Instances of Excel
• Pasting Without Updating References
• Converting From Relative to Absolute
• Inserting and Copying Rows
• Moving and Selecting Rows
• Entering Numbers in Excel
• Entering Info into Multiple Cells
• Limiting Entries to Numeric Values
• Limiting Input to a Format
• Dealing with Long Formulas
• Transposing Your Data
• Dragging to Clear Cells
• Deleting Blank Columns
• Conditionally Deleting Rows
• Adding Pop-Up Documentation to a Cell
• Correcting a Capital Mistake
• Modifying Proper Capitalization
• Selecting Formulas
• Deleting Everything Except Formulas
• Getting Rid of Everything Except Numbers
• Ranges on Multiple Worksheets
• Editing the Same Cell in Multiple Sheets
• Understanding AutoComplete
• Quickly Filling a Column
• Fast AutoFill
• AutoFill Won't Work as Expected
• Turning Off AutoFill for a Workbook
• Finding Unused Names
• Adjusting Formulas when Pasting
• Unwanted Data Changes
• Turning Off Automatic Capitalization
• Ensuring Unique Values in a Column
• Putting Addresses on State-Specific Worksheets
• Deleting Old Data from a Worksheet
• Working In Feet and Inches
• Shortening ZIP Codes
• Formatting Canadian Postal Codes
• Dividing Values
• Automatically Protecting After Input
• Scroll Wheel Doesn't Work when Editing in Excel 2007
• Typing Check Marks into Excel
• Retaining Formatting After a Paste Multiply
• Inserting a Radical Symbol
• Easily Changing Links
• Updating Links in Copied Files
• Copying a Hyperlink to Lots of Worksheets
• Removing Spaces
• Contingent Validation Lists
• Referencing the Last Cell in a Column
• Counting Words
• Getting Help when Entering Functions
• Automatically Breaking Text
• Checking for an Entry in a Cell
• Concatenating Ranges of Cells
• Converting Text to Values
• Using Slashed Zeroes
• Combining Multiple Rows in a Column
• Errors when Copying References to External Cells
• Deleting All Names but a Few
• Turning Off Insert Options
• Turning Off Paste Options
• Symbols Convert to Numbers in Excel
• Using Subtotals and Totals
• Copying Subtotals
• Wildcards in 'Replace With' Text

Formatting Tips

• Number Formatting Shortcuts
• Unable to Format Cells
• Changing Font Size Using a Shortcut Key
• Notation for Thousands and Millions
• Altering the Displayed Format of Numbers to the Nearest 100
• Using an Exact Number of Digits
• Formatting Currency
• Filling a Cell
• Fill Color Doesn't Display
• Referencing External Cell Colors
• Setting Vertical Alignment
• Using Strikethrough Formatting
• Drawing Borders
• Adding Diagonal Borders
• Partially Blocking Social Security Numbers
• Adding Drop Shadows to Cells
• Unhiding a Single Column
• Converting From Numbers to Text
• Converting Forced Text to Numbers
• Highlighting Cells Containing Specific Text
• Setting Cell Width and Height Using the Keyboard
• Using Fractional Number Formats
• Selecting a Paper Size
• Hiding Rows Based on Two Values
• Adjusting Cell Margins for More White Space
• Using AutoFormat
• Making All Occurrences Bold

Conditional Formatting Tips

• Copying Conditional Formatting
• Protecting Conditional Formatting
• Sorting or Filtering by Conditional Format Results
• Applying Conditional Formatting to Multiple Worksheets
• Changing Font Face and Size Conditionally
• Shading Based on Odds and Evens

Header and Footer Tips

• Dynamic Headers and Footers
• Using a Different Footer on Secondary Pages
• Putting a Different Date in a Header
• Printing a Multi-Line Footer
• Using Color in Headers and Footers
• Adding Ampersands in Headers and Footers
• Leading Zeros in Page Numbers
• Header and Footer Background Color

Online Tips

• Turning Off Hyperlink Activation
• Tying a Hyperlink to a Specific Cell
• Drop-Down List of Hyperlinks
• Changing Portions of Many Hyperlinks
• Removing Hyperlinks without a Macro
• Removing Hyperlinks without a Macro
• Getting Rid of Many Hyperlinks
• Getting Rid of All Hyperlinks
• Automatic Text in an E-mail
• Linking to a Specific Page in a PDF File
• Setting Web Fonts
• Specifying Your Target Monitor

Worksheet Tips

• Viewing Two Worksheets at Once
• Referencing Worksheet Tabs
• Quickly Copying Worksheets
• Disabling Moving Between Worksheets
• Preventing Someone from Recreating a Protected Worksheet
• Changing the Color of Worksheet Gridlines
• Locking Worksheet Names
• Switching Headers in a Frozen Row
• Protecting Worksheets from Deletion
• Protecting a Worksheet's Format
• Identifying the Last Cell Changed in a Worksheet

Worksheet Function Tips

• Returning a Worksheet Name
• Getting the Name of the Parent Workbook
• Large Numbers in the MOD Function
• Returning a Weight and a Date
• Counting Displayed Cells
• Finding the Lowest Numbers
• Rounding to Powers of 10
• Checking for Text
• Rounding Numbers
• Using the MROUND Worksheet Function
• SUMIF Doesn't Recalc Automatically
• Cell and Name References in COUNTIF
• Using the INT Worksheet Function
• Using the TRUNC Worksheet Function
• Returning Blanks with VLOOKUP
• Returning Blanks or Asterisks From a Lookup

Formula Tips

• Evaluating Formulas
• Exact Formula Copies
• Ignoring Case in a Comparison
• Summing Based on Formatting in Adjacent Cells
• Finding the Nth Occurrence of a Character
• Determining Business Quarters from Dates
• Summing Only Positive Values
• Errors When Subtracting
• Finding the First Non-Digit in a Text Value
• Referencing the Last Six Items in a Formula
• Counting Cells According to Case
• Stopping a Formula from Updating References
• Calculating the Median Age of a Group of People
• Generating Double-Digit Random Numbers
• Excluding Values from Averaging
• Returning Least-Significant Digits
• Rounding To the Nearest Even Integer
• Pulling Formulas from a Worksheet
• Figuring Out the Low-Score Winner
• Determining Winners, by Category
• Replacing Dashes with Periods
• Incrementing Numeric Portions of Serial Numbers
• Counting Non-Blank Cells
• Counting Unique Values
• Counting Consecutive Negative Numbers
• Counting Wins and Losses
• Counting Only Money Winners
• Last Non-Zero Value in a Row
• Getting the Name of the Worksheet Into a Cell
• Determining If a Value is Out of Limits
• Searching for a Value Using a Function
• Calculating Future Workdays
• Reversing Names In Place
• Cell Address of a Maximum Value
• Finding Columns of a Certain Width
• Determining Combinations to Make a Total
• Condensing Sequential Values to a Single Row
• Summing Digits in a Value
• Generating Random Strings of Characters
• Dealing with Circular References

PivotTable Tips

• Text Truncated in PivotTable
• Suppressing Zero Values in PivotTables
• Bogging Down with Calculated Items

Sorting and Filtering Tips

• Sorting a Range of Cells
• Sorting Decimal Values
• Sorting by Fill Color
• Fixing Odd Sorting Behavior
• Determining Sorting Criteria
• Filtering to a Date Range in the Past
• Filtering for Comments
• Counting Filtered Rows
• Performing Calculations While Filtering

Comment Tips

• Editing Comments
• Adding a Comment to Multiple Cells
• Pasting a Comment into Your Worksheet
• Copying Comments to Cells
• Placing Formula Answers in a Comment
• Linking Comments to Multiple Cells
• Finding and Replacing Text in Comments

Date and Time Tips

• How Excel Stores Dates and Times
• Converting Time Notation to Decimal Notation
• Changing Excel’s Starting Date
• Inserting the Current Time with Seconds
• Entering Large Time Values
• Using Early Dates
• Rounding Time
• Dates with Periods
• Using Excel for Timing
• Working with Elapsed Time
• Displaying Negative Times
• Entering Dates Without Separators
• Entering or Importing Times without Colons
• Modifying Default Year for Dates
• Counting Dates in a Range
• Days Left in the Year
• Forcing Dates Forward
• Automatically Advancing by a Month
• Every Second Tuesday
• Is Daylight Savings Time in Effect?
• Converting European Dates to US Dates
• Converting UNIX Date/Time Stamps
• Converting UTC Times to Local Times

Graphics Tips

• Resize Graphics Outside of Excel
• Putting a Graphic in a Header or Footer
• Specifying an Order for Drawing Objects
• Inserting a Watermark Behind Merged Cells
• Setting Default Attributes for Lines and Arrows
• Using WordArt in Excel
• Pictures in AutoShapes
• Adding Text to an AutoShape
• Placing Textbox Text Into a Worksheet
• Taking a Picture
• Getting Rid of Fixed Objects
• Pixels in a Text String
• Hiding Graphics when Filtering

Charting Tips

• Formatting Axis Patterns
• Formatting the Border of a Legend
• Selecting Fonts for a Chart
• Using Graphics to Represent Data Series
• Using the Mouse to Adjust Your View of 3-D Graphs
• Changing Axis Tick Marks
• Reading Values from Graphs
• Reordering the Display of a Data Series
• Adding Text Boxes to Charts
• Moving Groups of Data Labels at One Time
• Labeling X-Y Scatter Plots

Printing Tips

• Setting Page Margins
• Selecting a Paper Source
• Scaling Your Printing
• Automatically Printing a Range
• Changing Orientations within a Single Printout
• Printing Workbooks in a Folder
• Preventing Printing
• Printing All or Nothing
• Printing Multiple Pages On a Piece of Paper
• Repeating Rows on a Printout Except On the Last Page
• Showing Filter Criteria on a Printout
• Printing a Number of Different Pages
• Printing an Entire Workbook by Default
• Printing Just the Visible Data
• Hiding Errors on Printouts
• Printing Only Non-Blank Worksheets
• Locking the Print Area
• Protecting Print Settings
• Conditional Printing
• Printing Based on Cell Contents

Workbook and File Tips

• Increasing the Number of MRU Files
• Saving All Open Workbooks
• Saving a Workbook with a Preview
• Correctly Saving Delimited Files
• Setting the AutoRecover Directory
• Date Last Edited
• Hanging When Opening a Workbook
• File Formats that Include Field Formats
• Fixing "Can't Find Files" Errors
• Loading Unwanted Files at Startup
• Accessing Old Excel Data
• Locked File Puzzle
• Duplicate Workbooks Opening
• Saving in Two Locations
• Short-Lived Book1
• Finding the Parent Folder
• Merging Many Workbooks
• Using Your Own File Extensions
• Getting Rid of "Copy of"
• Avoiding Scientific Notation on File Imports
• Opening a Workbook to a Specific Worksheet
• Automatically Hiding Personal.xls
• Losing Data in a Shared Workbook
• Problems with Default Workbook and Worksheet Templates
• Importing Huge Data Files
• CSV File Opens with Data in a Single Column
• Importing Many Files Into Excel
• Getting Input from a Text File

Tools Tips

• Setting Spell-Checking Options
• Spell-checking Uppercase Words
• Adjusting Spell Check for Internet Addresses
• Allowing for Words that Contain Numbers
• Watching Cell Values
• Importing Custom Lists
• AutoFilling from a Custom List
• Viewing Comments
• Using Data Validation
• Setting Data Validation Input Messages
• Changing AutoFilter Drop-Down Arrow Colors
• Using AutoCorrect
• Adding and Using a Combo Box
• Independent Radio Buttons
• Formatting Combo Box Text
• Comparing Workbooks

Macro Tips

• Understanding Variables in VBA Macros
• Understanding Subroutines
• Understanding Functions
• Exiting a For ... Next Loop Early
• Understanding the Select Case Structure
• Documenting Changes in VBA Code
• Stepping Through a Macro with a Worksheet Visible
• Selecting a Range of Cells Relative to the Current Cell
• Selecting a Cell in the Current Row
• Jumping to the Start of the Next Data Entry Row
• Comparing Strings
• Determining If a Number is Odd or Even
• Creating Worksheets with a Macro
• Removing All Macros
• Working While a Macro is Running
• Forcing a Macro to Run When a Worksheet is Recalculated
• Automating Copying Macros
• Macro Fails after AutoFilter
• Disabled Macros
• Self-Aware Macros
• Counting Colors of Cells
• Counting Shaded Cells
• Out of Memory Errors when Accessing the VBA Editor
• Determining How Many Windows are Open
• Saving Changes when Closing
• Upgrading a Personal.xls Workbook
• Searching for Leading Apostrophes
• Waiting for Update Completion
• Generating Unique Numbers for Worksheets
• Saving a Workbook in a Macro
• Putting an X in a Clicked Cell
• Hiding Excel in VBA
• Determining a Worksheet's Number
• Retrieving Drive Statistics
• Default Worksheet when Opening
• Selecting Cells of a Specific Color
• Determining Mouse Cursor Coordinates On a Graphic
• Limiting Scroll Area
• Counting Precedents and Dependents
• Preparing Data for Import into Access
• Counting Commas in a Selection
• Extracting Proper Words
• Automatically Loading Add-ins
• Sheets for Days
• Sheets for Months
• Using BIN2DEC In a Macro
• Selecting All Visible Worksheets in a Macro
• Getting User Input in a Dialog Box
• Mouse Click Event in VBA
• Converting Phone Numbers
• Retrieving Worksheet Names
• Getting a File Name
• Finding the Last-Used Cell in a Macro
• Using Macros in Protected Workbooks
• Item Not Available in Library
• Automatically Changing References to VBA Libraries

Order Your ExcelTips Archives Today!

Each download item shown below (right column) includes the size of the download file. If you have a slow connection to the Internet, you can save precious time by ordering your archives on CD-ROM (left column).

The ExcelTips Archives include PDF documents. To read these documents, you need to have either Adobe Acrobat or Adobe Reader on your system. Adobe Reader is free to download; click the link at the right to get the latest version.

Archive	Dates Covered	Tips	Book Pages	CD		Download
ExcelTips 2001	6 Jan 01 through 29 Dec 01	207 tips	218 pages	$29.99		$22.99		(1.47 MB)
ExcelTips 2002	5 Jan 02 through 28 Dec 02	204 tips	222 pages	$29.99		$22.99		(1.59 MB)
ExcelTips 2003	4 Jan 03 through 27 Dec 03	410 tips	471 pages	$29.99		$22.99		(3.95 MB)
ExcelTips 2004	6 Jan 04 through 25 Dec 04	414 tips	428 pages	$29.99		$22.99		(4.17 MB)
ExcelTips 2005	1 Jan 05 through 31 Dec 05	423 tips	518 pages	$29.99		$22.99		(4.7 MB)
ExcelTips 2006	7 Jan 06 through 30 Dec 06	416 tips	493 pages	$29.99		$22.99		(7.6 MB)
ExcelTips 2007	6 Jan 07 through 29 Dec 07	414 tips	504 pages	$29.99		$22.99		(10.5 MB)
ExcelTips 2008	5 Jan 08 through 27 Dec 08	415 tips	514 pages	$29.99		$22.99		(10.6 MB)

You can also order ExcelTips archives by calling our order line: 801-607-2035.