ExcelTips 2005 Archive

The ExcelTips 2005 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

• Starting in Safe Mode
• A Shortcut for Switching Focus
• Language Conversion Resources for Menus
• Hanging When Opening a Workbook
• Date Last Edited
• Turning Off ScreenTips
• Removing Personal Information
• Adding Pop-up Documentation to a Cell
• Turning Off Error Checking
• Changing Error Checking Rules
• Changing the Color of Worksheet Gridlines
• Synchronized Workbook and Worksheet Names
• Simultaneous Scrolling
• Making AutoComplete Work for an Entire Column
• Countering Compressed Columns
• Clearing Large Clipboard Entries
• Locking Worksheet Names
• Viewing Two Worksheets at Once
• Forcing Manual Calculation for a Workbook
• Always Open at 100% Zoom
• Thoughts and Ideas on Significant Digits in Excel
• Rounding Religious Wars
• Rounding Religious Wars, Take Two
• Cannot Shut Down Excel
• Losing Data in a Shared Workbook
• Changing Horizontal Orientation
• Ignoring Other Applications
• Refusing Online Help
• Where Is that Name?
• Task Pane Doesn't Appear Properly
• Disabling the Windows Start Menu Key
• Subtotals Option Grayed Out
• Weird Actions for Arrow Keys and Enter
• Turning Headers On and Off
• Slowing Down Mouse Selection
• Forcing Stubborn Recalculation
• Arranging Workbook Windows
• Disabling a Function Key
• Simplifying the Font List
• Removing Page Break Preview Page Numbers
• Backing Up Custom Dictionaries
• Displaying Toolbars
• Changing Toolbar Location
• Customizing a Toolbar
• Creating a New Toolbar
• Deleting a Toolbar
• Resetting Toolbars to Their Default
• Creating Multi-Row Toolbars
• Saving Valuable Toolbar and Screen Space
• Where is Toolbar Customization Information Stored?
• Missing Toolbars
• Changing a Toolbar Button Image
• Editing a Toolbar Button Image
• Adding Your Own Menu Items
• Making Short Work of Menu Names
• Resetting Dynamic Menus
• Enlarging the Formula Bar
• Exporting from Excel to AutoCAD
• Snapshots of Excel Worksheets for PowerPoint
• Selecting Noncontiguous Ranges with the Keyboard
• Shortcut for Viewing Formulas

Editing Tips

• Creating Selections
• Selecting Formulas
• Entering Numbers in Excel
• Turning Off Paste Options
• Turning Off Insert Options
• Turning Off Automatic Capitalization
• Copying Formulas using a Pattern
• Copying Subtotals
• Copying Between Instances of Excel
• Inserting and Copying Rows
• Adjusting Formulas when Pasting
• Pasting Without Updating References
• Ensuring Unique Values in a Column
• Using an Input Mask
• Unwanted Data Changes
• Activating the Formula Bar with the Keyboard
• Creating New Windows
• Easily Changing Links
• Updating Links in Copied Files
• Uncovering and Removing Links
• Automatically Protecting After Input
• Fast AutoFill
• Working In Feet and Inches
• Shortening ZIP Codes
• Formatting Canadian Postal Codes
• Conditionally Deleting Rows
• Dividing Values
• Maintaining Accuracy of Significant Digits
• Retaining Formatting After a Paste Multiply
• Shortcut for Pasting Only Values
• Using the Same Range Name on Different Worksheets
• Converting From Relative to Absolute
• Dragging to Clear Cells
• Inserting a Radical Symbol
• Removing Spaces
• Deleting Blank Columns
• Dealing with Long Formulas
• Deleting Everything Except Formulas
• Automatically Breaking Text
• Concatenating Ranges of Cells
• Referencing the Last Cell in a Column
• Counting Words
• Displaying a Hidden First Row
• Converting Text to Values
• Checking for an Entry in a Cell
• Using Subtotals and Totals
• Transposing Your Data
• Entering Info into Multiple Cells
• Moving and Selecting Rows
• Quickly Filling a Column
• Combining Multiple Rows in a Column
• Deleting All Names but a Few
• Ranges on Multiple Worksheets
• Understanding AutoComplete
• Ensuring Rows and Columns are Empty
• Limiting Entries to Numeric Values
• Independent Radio Buttons

Formatting Tips

• Formatting Currency
• Unable to Format Cells
• Changing Cell Colors
• Drawing Borders
• Adding Diagonal Borders
• Partially Blocking Social Security Numbers
• Changing Font Size Using a Shortcut Key
• Highlighting Cells Containing Specific Text
• Altering the Displayed Format of Numbers to the Nearest 100
• Too Many Cell Formats
• Converting Forced Text to Numbers
• Converting From Numbers to Text
• Using an Exact Number of Digits
• Unhiding a Single Column
• Formatting Subtotal Rows
• Number Formatting Shortcuts
• Creating a Center Across Selection Button
• Superscripts in Custom Formats
• Creating Superscript and Subscript Buttons
• Creating 3-D Formatting for a Cell
• Using Fractional Number Formats
• Selecting a Paper Size
• Hiding Rows Based on Two Values
• Filling a Cell
• Adjusting Cell Margins for More White Space
• Stopping Fractions from Reducing
• Setting Vertical Alignment
• Setting Horizontal Alignment
• Changing Cell Patterns
• Coloring Cells with Formulas
• Decimal Tab Alignment
• Understanding Date and Time Formatting Codes
• Deleting Unwanted Styles
• Making All Occurrences Bold
• Formatting Combo Box Text
• Using AutoFormat
• Placing Limits on AutoFormat
• Undoing an AutoFormat
• Copying Conditional Formatting
• Changing Font Face and Size Conditionally
• Removing Conditional Formats, but Not the Effects
• Sorting or Filtering by Conditional Format Results
• Conditional Formats that Distinguish Blanks and Zeroes
• Conditional Formatting Based on Date Proximity
• Shading Based on Odds and Evens
• Conditional Formatting with Data Imported from Access

Header and Footer Tips

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

Online Tips

• Opening an HTML Page in Excel
• Setting Web Fonts
• Specifying Your Target Monitor
• Getting Rid of All Hyperlinks
• Removing All Hyperlinks without a Macro
• Removing Hyperlinks without a Macro
• Special Characters In Hyperlinks
• Putting More than One Hyperlink in a Cell
• Turning Off Hyperlink Activation

Worksheet Tips

• Quickly Inserting a New Worksheet
• Quickly Copying Worksheets
• Preventing Someone from Recreating a Protected Worksheet
• Disabling Moving Between Worksheets
• Protecting Worksheets from Deletion
• Selecting Combo Boxes in Locked Worksheets
• Referencing Worksheet Tabs
• Protecting Individual Worksheets, by User

Worksheet Function Tips

• Getting Help when Entering Functions
• Rounding Numbers
• Using the MROUND Worksheet Function
• Using the INT Worksheet Function
• Using the TRUNC Worksheet Function
• Returning Blanks or Asterisks From a Lookup
• Returning Blanks with VLOOKUP
• Returning a Worksheet Name
• Getting the Name of the Parent Workbook
• Counting Displayed Cells
• Rounding to Powers of 10
• Checking for Text
• SUMIF Doesn't Recalc Automatically
• Counting Records Matching Multiple Criteria

Formula Tips

• Exact Formula Copies
• Errors When Subtracting
• Counting Cells According to Case
• Excluding Values from Averaging
• Rounding To the Nearest Even Integer
• Figuring Out the Low-Score Winner
• Replacing Dashes with Periods
• Counting Non-Blank Cells
• Finding Differences Between Lists
• Determining Winners, by Category
• Counting Wins and Losses
• Returning a Blank Value
• Looking Up Names when Key Values are Identical
• Determining Combinations to Make a Total
• Ignoring Case in a Comparison
• Calculating Future Workdays
• Dealing with Circular References
• Counting Only Money Winners
• Summing Digits in a Value
• Counting Unique Values
• Deleting Duplicate Text Values
• Counting within Criteria

PivotTable Tips

• Text Truncated in PivotTable
• Changing the Default PivotTable Functions
• Suppressing Zero Values in PivotTables

Comment Tips

• Viewing Comments
• Editing Comments
• Managing Comments
• Changing the Comment Color
• Pasting a Comment into Your Worksheet
• Placing Formula Answers in a Comment
• Linking Comments to Multiple Cells
• Copying Comments to Cells

Sorting and Filtering Tips

• Sorting a Range of Cells
• Non-standard Sorting
• Fixing Odd Sorting Behavior
• Determining Sorting Criteria
• Performing Complex Sorts
• Creating a Sort Order
• More Than Two Conditions for Custom AutoFiltering
• Increasing the AutoFilter Drop-Down Limit
• Performing Calculations While Filtering
• Toggling AutoFilter
• Changing AutoFilter Drop-Down Arrow Colors

Date and Time Tips

• How Excel Stores Dates and Times
• Changing Excel's Starting Date
• Using Excel for Timing
• Working with Elapsed Time
• Entering Dates Without Separators
• Rounding Time
• Using Early Dates
• Entering Large Time Values
• Converting UNIX Date/Time Stamps
• Automatically Converting to GMT
• Modifying Default Year for Dates
• Forcing Dates Forward
• Days Left in the Year
• Counting Dates in a Range
• Automatically Advancing by a Month
• Every Second Tuesday
• Converting Time Notation to Decimal Notation
• Entering or Importing Times without Colons
• Working with Minutes

Graphics Tips

• Taking a Picture
• Inserting a Picture in Your Workbook
• Inserting from a Camera or Scanner
• Using WordArt in Excel
• Specifying a Font in WordArt
• Inserting a Watermark Behind Merged Cells
• Displaying the AutoShapes Menu
• Adding AutoShapes
• Placing Textbox Text Into a Worksheet
• Adding Text to an AutoShape
• Pictures in AutoShapes
• Adding Text Boxes to Charts
• Selecting Fonts for a Chart
• Reading Values from Graphs
• Easily Changing Chart Data Ranges
• Automatically Creating Charts for Individual Rows in a Data Table
• Controlling the Plotting of Empty Cells
• Exploded Pie Chart Sections
• Watermarks in Excel
• Exporting Black and White Charts
• Specifying Chart Sizes
• Sizing Text Boxes and Cells the Same
• Reordering the Display of a Data Series
• Positive and Negative Colors in a Chart
• Numeric Value and Percentage Value in a Graph Column
• Moving Groups of Data Labels at One Time
• Using the Keyboard to Select and Resize a Chart Object

Printing Tips

• Setting Up Your Printer
• Selecting a Paper Source
• Scaling Your Printing
• Setting Page Margins
• Printing Multiple Pages On a Piece of Paper
• Protecting Print Settings
• Printing All or Nothing
• Printing Workbooks in a Folder
• Printing an Entire Workbook by Default
• Printing a Number of Different Pages
• Using Multiple Print Settings
• Preventing Printing
• Printing Just the Visible Data
• Showing Filter Criteria on a Printout
• Conditional Printing
• Hiding Errors on Printouts
• Specifying a Paper Tray in a Macro
• Printing Comments
• Printing Multiple Selections
• Printing to a Disk File

File Tips

• Excluding a Specific Add-In at Startup
• Opening Two Workbooks with the Same Name
• Saving a Workbook with a Preview
• Sizing the Preview Pane
• Duplicate Workbooks Opening
• Setting the AutoRecover Directory
• Locked File Puzzle
• Jumping Around Folders
• Fixing "Can't Find Files" Errors
• Loading Unwanted Files at Startup
• Speeding Up Opening and Saving a Workbook
• Importing Huge Data Files
• Big File Memory Blues
• CSV File Opens with Data in a Single Column
• Don't Update Links to Other Programs
• Saving in Two Locations
• Short-Lived Book1
• Working with Lotus 1-2-3 Spreadsheets
• Getting Rid of "Copy of"
• Closing All Open Workbooks
• Getting Input from a Text File
• Importing Many Files Into Excel
• Finding the Parent Folder
• Using Your Own File Extensions
• Avoiding Scientific Notation on File Imports
• Can't Open Multiple Workbooks from the Desktop
• Seeing Full File Names in the Files Menu
• Converting Imported Information to Numeric Values
• Inconsistent Output for Empty Columns in a CSV File
• Opening Multiple Workbooks at Once
• Crashing when Searching for Files

Tools Tips

• Evaluating Formulas
• Setting Spell-checking Options
• Spell-checking Uppercase Words
• Adjusting Spell Check for Internet Addresses
• Allowing for Words that Contain Numbers
• Comparing Workbooks
• Watching Cell Values
• Speaking the Contents of Cells
• Adding and Using a Combo Box
• Importing Custom Lists
• AutoFilling from a Custom List
• Using AutoCorrect
• Using Data Validation
• Setting Data Validation Input Messages
• Specifying a Data Validation Error Message

Macro Tips

• Understanding Variables in VBA Macros
• Understanding Subroutines
• Understanding Functions
• Understanding the Select Case Structure
• Exiting a For ... Next Loop Early
• Comparing Strings
• Selecting a Cell in the Current Row
• Selecting a Range of Cells Relative to the Current Cell
• Selecting Cells of a Specific Color
• Selecting All Visible Worksheets in a Macro
• Deleting a Macro
• Removing All Macros
• Deleting Macros from within a Macro
• Working While a Macro is Running
• Stepping Through a Macro with a Worksheet Visible
• Disabled Macros
• Forcing a Macro to Run When a Worksheet is Recalculated
• Macros in Template Files
• Saving Changes when Closing
• Automating Copying Macros
• Generating a List of Macros
• Macro Slows Down on More Powerful Machine
• Generating Unique Numbers for Worksheets
• Retrieving Drive Statistics
• Determining How Many Windows are Open
• Saving a Workbook in a Macro
• Default Worksheet when Opening
• Counting Precedents and Dependents
• Automatically Loading Add-ins
• Sheets for Months
• Hiding Excel in VBA
• Naming Tabs for Weeks
• Creating Worksheets with a Macro
• Triggering a Macro for Drop-Down List Changes
• Recovering Macros from Corrupted Workbooks
• Jumping to the Start of the Next Data Entry Row
• Limiting Scroll Area
• Jumping to Alphabetic Worksheets
• Using Macros in Protected Workbooks
• Fixing Macro Button Behavior in Protected Worksheets
• Counting Shaded Cells
• Extracting Proper Words
• Sheets for Days
• Unhiding or Listing All Objects
• Preserving the Undo List
• Using BIN2DEC In a Macro
• Unlocking Charts
• Mouse Click Event in VBA
• Finding the Last-Used Cell in a Macro
• Getting User Input in a Dialog Box
• Using Message Boxes
• Determining If a Number is Odd or Even
• Converting Phone Numbers
• Merging Many Workbooks
• Retrieving Worksheet Names
• Getting a File Name
• Unprotecting Groups of Worksheets
• Running a Macro in a Number of Workbooks
• Understanding Add-Ins
• Creating Add-Ins
• Using Custom Add-Ins
• Word Documents from Excel Macros
• Preparing Data for Import into Access

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.