ExcelTips 2004 Archive

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

• History of Excel
• Determining Your Version of Excel
• Determining Your Serial Number
• Getting Context-Sensitive Help
• Understanding R1C1 References
• Viewing Your Work Full-Screen
• Displaying Row and Column Labels
• Nifty Zooming
• Getting Rid of the Office Assistant
• Weird Mouse Shortcut
• Controlling the Behavior of the Mouse Wheel
• Understanding Manual Calculation
• Specifying the Behavior of the Enter Key
• Canceling a Command
• Controlling How Excel Interprets Percentages
• Turning Off Names
• Turning Off Dynamic Menus
• Turning on Placeholders
• Turning Off AutoFill
• Controlling Display of Page Breaks
• Changing Coordinate Colors
• Cycling Through Colors
• Resizing a Drop-Down List
• Changing the Shortcut Menu
• Getting Audible Feedback
• Rounding in Results
• Maintaining the Active Cell
• Library Not Registered Error
• Controlling the Display of Toolbars
• Quickly Removing a Toolbar Button
• Disappearing Toolbar Buttons for Macros
• Backing Up Your Customized Toolbars
• Setting the Default Font Size for Comment Balloons
• A Ruler in Excel
• Roman Numerals for Page Numbers
• Slow Excel Response Times
• Taskbar Setting isn't Sticky
• Appearance of Excel on the Taskbar
• Random Resetting of the Standard Toolbar
• Embedding Your Phone Number in a Workbook
• Saving All Open Workbooks
• Animated Menus

Editing Tips

• Annoying Excel Cuts and Copies
• Ctrl+V Pasting is Flakey
• Quickly Selecting Cells
• Quickly Entering Data
• Controlling Where You Edit Cell Contents
• Canceling an Edit
• Starting Out Formulas
• Noting When a Workbook was Changed
• Cleaning Up Lists
• Limiting Choices in a Cell
• Single-Use Drop-Down List
• Relative Worksheet References when Copying
• Inserting Rows
• Inserting and Deleting Rows in a Protected Worksheet
• Deleting Every X Rows
• Highlighting the Rows of Selected Cells
• How Many Rows and Columns Have I Selected?
• Getting Rid of Non-Printing Characters Intelligently
• Quick AutoFill Variations
• Copying Values
• Copying Cells to Fill a Range
• Copying a Cell without Formatting
• Quickly Updating Values
• Limiting Entry of Prior Dates
• Unique Name Entry, Take Two
• Pasting a Hyperlink
• Zooming In On Your Worksheet
• Finding Wayward Links
• Shortcut for Selecting a Data Range
• Merging Cells to a Single Sum
• Easily Entering Dispersed Data
• Forcing Input to Uppercase
• Sequentially Inputting Information
• Turning Off AutoFill for a Workbook
• Editing the Same Cell in Multiple Sheets
• Contingent Validation Lists
• Displaying Letter Grades
• Inserting Dashes between Letters and Numbers
• Segregating Numbers According To Their Sign
• Typing Check Marks into Excel
• Automatically Capitalizing Day Names
• Correcting a Capital Mistake
• Limiting Input to a Format
• Moving Part of a Footer Down a Line
• Putting Headers and Footers On Multiple Worksheets
• Full Path Names in Headers or Footers
• Putting Cell Contents in Footers

Formatting Tips

• Displaying a Hidden First Column
• Removing Borders
• Changing the Color of a Cell Border
• Indenting Cell Contents
• Understanding Cell Indenting
• Understanding Underlines
• Easy Value Hiding
• Cannot Format Cells
• Getting Rid of Negative Zero Amounts
• Specifying Superscript Text
• Using Strikethrough Formatting
• Professional Looking Fractions
• When is Currency Not Currency?
• Adjusting Row Height for Your Text
• Adjusting Row Height when Wrapping Text
• Automatic Row Height For Merged Cells with Text Wrap
• More than Three Conditional Formats
• Conditional Formatting for Errant Phone Numbers
• Conditionally Highlighting Cells Containing Formulas
• Protecting Conditional Formatting
• Saving Custom Formats
• Formatting a PivotTable
• Displaying Latitude and Longitude
• Adding Ordinal Notation to Dates
• Changing Fonts in Multiple Workbooks
• Controlling Display of Page Breaks
• Changing the Font Size in Combo Boxes
• Preventing Changes to Formatting and Page Size
• Copying Formats to a New Worksheet
• Double-Clicking to Widen Columns Won't Work
• Handling Leading Zeros in CSV Files
• Automatic Lines for Dividing Lists
• Referencing External Cell Colors
• Problems with Custom Views
• Fill Color Doesn't Display
• Embedding Fonts in Excel
• Formatting Text in Comment Boxes

Online Tips

• Suppressing the Reviewing Toolbar on E-mailed Workbooks
• Using Drag-and-Drop to Create a Hyperlink
• Deleting a Hyperlink
• Generating a Web Page
• Adding Excel Information to a Web Page
• Hyperlinks to Charts
• Using Web Discussions
• Connecting To a Discussion Server
• Starting a Discussion Thread
• Using the Discussion Pane
• Refreshing Web Discussions
• Filtering Web Discussions
• Printing Web Discussions
• Dynamic Hyperlinks in Excel

Worksheet Tips

• Moving and Selecting Sheets With the Keyboard
• Speeding Up Large Worksheets
• Workbook, Once Created, is Too Big for Memory
• Viewing More Than Two Places in a Worksheet
• Counting the Times a Worksheet is Used
• Using a Protected Worksheet
• Sorting Data on Protected Worksheets
• Colors and Fonts for Worksheet Tabs
• Multiple Rows of Worksheet Tabs
• Condensing Multiple Worksheets Into One

Worksheet Function Tips

• Using the COLUMN Function
• Determining the Least Common Multiple
• Converting to Hexadecimal
• Converting to Octal
• Determining a Value of a Cell
• Rounding to Even and Odd Values
• Using the IF Worksheet Function
• Returning the MODE of a Range
• Determining Columns in a Range
• Using the CONCATENATE Worksheet Function
• Converting Codes to Characters
• Cleaning Text
• Using the ABS Function
• Using the XIRR Function
• Using GEOMEAN With a Large List
• The EDATE Function
• Counting Colors of Cells
• Using the WEEKNUM Function
• Returning the Left-most Characters
• Calculating Fractions of Years
• Using the FORECAST Function
• Phantom Counts
• Understanding the VLOOKUP Function
• Returning a Weight and a Date

Formula Tips

• Combinations for Members in Meetings
• Maintaining Text Formatting in a Lookup
• Character Replacement in Simple Formulas
• Deriving High and Low Non-Zero Values
• Counting Unique Values
• Calculating the Interval Between Occurrences
• Counting Cells with Specific Characters
• Calculating Averages by Date
• Saving Common Formulas
• Filling References to Another Workbook
• Deriving a Secant and Cosecant
• Retrieving the Last Value in a Column
• Totaling Across Worksheets
• Converting Mainframe Date Formats
• Numbers in Base 12
• Counting Employees in Classes
• Relative References within Named Ranges
• Finding the Directory Name
• Combining Numbers and Text in a Cell
• Using a Numeric Portion of a Cell in a Formula
• Pulling Formulas from a Worksheet
• Rounding to the Nearest Half Dollar
• Combining Cell Contents

Sorting and Filtering Tips

• Sorting Letters and Numbers
• Sorting Dates by Month
• Sorting Data Containing Merged Cells
• Sorting by Fill Color
• Sorting by Colors
• Recognizing a Header Row when Sorting
• Too Many Formats when Sorting
• Incomplete and Corrupt Sorting
• Extracting Targeted Records from a List
• Filtering to a Date Range in the Past

Date and Time Tips

• Entering the Current Time
• Inserting Tomorrow's Date
• Defeating Automatic Date Parsing
• Monthly Close-Out Dates
• Alerts About Approaching Due Dates
• The Last Business Day
• Calculating the Day of the Year
• Calculating Months of Tenure
• Calculating Week-Ending Dates
• AutoFilling with Weekdays
• Pulling All Fridays
• Leap Years and Fiscal Periods
• Converting an Unsupported Date Format
• Parsing Non-Standard Date Formats
• Checking for Time Input

Graphics Tips

• Editing Graphics Objects
• Nudging a Graphic
• Symmetric Resizing of Graphics
• Resize Graphics Outside of Excel
• Moving and Copying Graphics Objects
• Duplicating Drawing Objects
• Inserting Multiple Drawing Objects
• Flipping a Drawing Object
• Adding Drop Shadows
• Changing Line Color in a Drawing Object
• Positioning Graphics Evenly
• Grouping and Ungrouping Objects
• Hiding Graphics
• Deleting All Graphics
• Drawing Simple Objects
• Drawing Lines
• Styles for Lines, Dashes, and Arrows
• Automatically Updating Charts for Additional Data
• Hiding Objects
• Creating an AutoShape
• Creating an Organization Chart
• Using Text Boxes
• Adding a Drop Shadow to a Text Box
• Sorting with Graphics
• Moving a Chart's Legend
• Turning the Legend On and Off
• Quickly Duplicating Drawing Objects
• Negatives in Pie Charts
• Locking Callouts to a Graph Location
• Deleting a Chart
• Changing the Pattern Used in a Data Series
• Creating a Log/Log Chart
• Changing Chart Type
• Changing How Arrows Look
• Modifying Axis Scale Labels
• Controlling Chart Gridlines
• Specifying an Order for Drawing Objects
• Smoothing Out Data Series
• Unselecting a Chart Item
• Using Dynamic Chart Titles
• Formatting Axis Patterns
• Formatting the Border of a Legend
• Identifying Scatter Plot Points
• Using Graphics to Represent Data Series
• Creating Charts in VBA
• Changing the Axis Scale
• Changing Axis Tick Marks
• Two-Level Axis Labels
• Adjusting Your View of 3-D Graphs
• Using the Mouse to Adjust Your View of 3-D Graphs
• Exporting a Graphics Group

Printing Tips

• Setting Print Quality
• Printing a Short Selection
• Repeating Rows at the Bottom of a Page
• Clearing the Print Area
• Adding the Set Print Area Tool
• Printing a Draft of a Worksheet
• Printing a Draft Watermark
• Specifying an Order for Page Printing
• Printing a Range of Pages
• Printing Workbook Properties
• Using Less Paper on Printouts
• Printing without Opening
• Adjusting Comment Printouts
• Printing a Chart Across Multiple Pages
• Out of Kilter Borders
• Printing Columns and Rows
• Fitting Your Printout on a Page
• Resetting Page Setup
• Automatically Printing a Range
• Disabling Printing
• Printing a List of Named Ranges

File Tips

• Saving a Workbook Using Passwords
• Cannot Double-Click to Open a Workbook
• Changing Link References
• Setting a Default File Format
• Converting from SuperCalc
• Odd Behavior when Opening a Shared File with a Shortcut
• Problems Saving in Older File Format
• Error Opening Second Workbook
• Error Opening Second Workbook, Take Two
• Reducing File Size
• Saving Worksheets in Lotus 1-2-3 Format
• Determining If a File Exists
• Determining the Length of a Text File
• Saving Information in a Text File
• Appending to a Non-Document Text File
• Correctly Saving Delimited Files
• Creating a CSV File
• Comma-Delimited Differences for PC and Mac
• Delays in Saving Workbooks
• Creating a Workbook Clone
• Grabbing the MRU List
• Trouble Opening Network Workbooks
• Who Has the File Open?
• Other "Phantom" Users
• Protecting Excel Files from Word
• Disabling Shift Key Use when Opening a Workbook
• Accessing Old Excel Data
• Jumping to the Real Last Cell

Tools Tips

• Searching Comments
• Searching for Wildcards
• Searching a Workbook by Default
• Searching a Workbook by Default, Take Two
• Adding Buttons to Your Worksheet
• Turning Off Track Changes without Unsharing
• Understanding Outlining
• Hiding Outline Symbols
• Embedding an Excel Chart in a Word Document
• AutoFill with Random Numbers
• Creating Dependent Drop-Lists
• Using Data Forms
• Counting with PivotTables
• Refreshing PivotTable Data
• Bogging Down with Calculated Items
• Editing Custom Dictionaries

Macro Tips

• Debugging a Macro
• Declaring Variables
• Inserting Worksheet Values with a Macro
• Creating a String in a Macro
• Reversing Cell Contents
• Swapping Two Strings
• Determining if Calculation is Necessary
• Running a Macro When a Worksheet is Activated
• Selecting the First Cell In a Row
• Editing Macros
• Converting Numbers to Strings
• Determining a Random Value
• Spreading Out a Table
• Making Common Functions Available to Others
• Determining an Integer Value
• Running a Macro When a Worksheet is Activated
• Running a Macro When a Worksheet is Deactivated
• Running a Macro When a Workbook is Closed
• Renaming a File
• Deleting a File in a Macro
• Creating a Directory in a Macro
• Noting the Workbook Creation Date
• Displaying the Print Dialog Box in a Macro
• Macro Runs Slowly, but Steps Quickly
• Removing a Directory
• Deriving an Absolute Value in a Macro
• Pulling Apart Cells
• Assigning Macros to Graphics
• Clearing the Undo Stack in a Macro
• Positioning a Column on the Screen
• Determining if Caps Lock is On
• Determining the Current Directory
• Changing Directories in a Macro
• Using Seek In a Macro
• Removing Pictures for a Worksheet in VBA
• Making a Cell's Contents Bold within a Macro
• Using a Progress Indicator in Macros
• Easily Adding Blank Rows
• Turning Off Screen Updating
• Quickly Dumping Array Contents
• DOS From Macros
• Determining the Day of the Month
• Determining the Hour of the Day
• Inserting the Current Time with Seconds
• Determining Differences Between Dates
• Running Macros in the Background
• Swapping Two Numbers
• Progression Indicator in a Macro
• Determining an ANSI Value in a Macro
• Controlling the Printer in a Macro
• Detecting Types of Sheets in VBA
• Aborting a Macro and Retaining Control
• Changing the Default Drive
• Setting Row Height in a Macro
• Easily Changing the Default Drive and Directory
• Running Macros on Hidden Worksheets
• Conditionally Displaying a Message Box
• Setting Column Width in a Macro
• Making a Cell's Contents Italics within a Macro
• Pausing Macros for User Input
• Develop Macros In Their Own Workbook
• Worksheet Events
• Workbook Events
• Moving Macros from the Personal Workbook
• Getting Big Macros to Run
• Removing a Macro from a Shortcut Key
• Getting Rid of the "Enable Macros" Notice
• Expiration Date for Excel Programs
• Adjusting Values with Formulas
• Friendly and Informative Error Handling
• Adding Leading Zeroes to ZIP Codes
• Unhiding Multiple Worksheets
• Self-Aware Macros
• Waiting for Update Completion
• Controlling Display of Toolbar Buttons
• Hiding Macros

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.