ExcelTips 2003 Archive

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

• Understanding Relative and Absolute Addressing
• Canceling a Menu
• Adding Your Own Menu Items
• Moving Items On a Menu
• Adding Items to a Context Menu
• Displaying Toolbars
• Changing Toolbar Location
• Renaming a Toolbar
• Creating a New Toolbar
• Deleting a Toolbar
• Customizing a Toolbar
• Changing a Toolbar Button Image
• Editing a Toolbar Button Image
• Large Toolbar Buttons
• Resetting Toolbars to Their Default
• Where is Toolbar Customization Information Stored?
• Toolbar Nuisances
• Saving Valuable Toolbar and Screen Space
• Controlling Display of the Formula Bar
• Making the Formula Bar Persistent
• Enlarging the Formula Bar
• Task Pane Doesn't Appear Properly
• Slowing Down Mouse Selection
• Weird Actions for Arrow Keys and Enter
• Odd Arrow Key Behavior
• Choosing Direction After Enter On a Workbook Basis
• Turning Headers On and Off
• Forcing Stubborn Recalculation
• Changing Your Company Name
• Reversing Dimensions
• Zooming With the Keyboard
• Generating Random Testing Data
• Quickly Changing Windows
• Seeing Excel's Program Window
• Giving the Office Assistant Focus
• Running Out of Memory
• Saving Non-Existent Changes
• Automatic Recalculation Not Happening
• Removing Add-ins
• Easily Deploying Customizations
• Understanding Lists
• Working with Record Numbers
• Drop-Down List Font Sizes
• Backing Up Your AutoCorrect Entries
• Removing Page Break Preview Page Numbers
• Disabling the F1 Key
• Defining and Using Custom Colors
• Changing Your Name
• Changing Gridline Color
• Adding a Little Animation to Your Life
• Arranging Workbook Windows
• Viewing Workbook Statistics
• Tasks for Each Workbook

Editing Tips

• Entering Numbers in Excel
• Entering Formulas in Excel
• Editing Individual Cells
• Quickly Filling a Column
• Entering Info into Multiple Cells
• Enhanced Filling
• Cell Movement After Enter
• Default Cell Movement when Deleting
• Clearing and Deleting Cells
• Creating Selections
• Adjusting a Range's Starting Point
• Selecting Formulas
• Viewing Formula Results
• Selecting the Current Region
• Referencing the Last Cell in a Column
• Jumping to a Range
• Ranges on Multiple Worksheets
• Changes in Font Size when Copying
• Counting Words
• Displaying a Hidden First Row
• Transposing Your Data
• Converting Text to Values
• Using an Input Mask
• Moving and Selecting Rows
• Quickly Deleting Rows and Columns
• Removing Duplicate Rows
• Removing Duplicate Cells
• Understanding AutoComplete
• Ensuring Rows and Columns are Empty
• Checking for an Entry in a Cell
• Checking for a Value in a Cell
• Reference Shortcut
• Combining Columns
• Tab Key Jumps a Screen at a Time
• Moving and Copying Cells
• A Fast Find-Next
• Inserting Different Dashes
• Changing Months in a Workbook
• Using Subtotals and Totals
• Counting with Subtotals
• Moving Subtotals
• Limiting Entry of Names
• Synchronizing Lists
• Pasting Leading Zeroes
• Turning Off Capital Corrections
• Filtering Columns for Unique Values
• Fixing the Decimal Point

Formatting Tips

• Setting Row Height
• Understanding Column Widths
• Hiding and Unhiding Rows
• Hiding and Unhiding Columns
• Changing Cell Colors
• Setting Horizontal Alignment
• Setting Vertical Alignment
• Setting Orientation of Cell Values
• Using Fractional Number Formats
• Stopping Fractions from Reducing
• Selecting a Paper Size
• Filling a Cell
• Adjusting Cell Margins for More White Space
• Changing Cell Patterns
• Shrinking Cell Contents
• Decimal Tab Alignment
• Coloring Cells with Formulas
• Shading Based on Odds and Evens
• Accurate Font Sizes
• Changing Page Number Format
• Checking All Cell Formatting in VBA
• Using Copy and Paste for Formatting
• Changing Font Sizes
• No New Fonts Error
• Shading Rows for Ease in Reading Output
• Automatically Copying Formatting
• Underlining
• Understanding Monospace Fonts
• Exporting Latitude and Longitude
• Conditional Page Breaks
• No More Custom Formats Can Be Added
• Sorting ZIP Codes
• Flashing Cells
• Removing All Formatting
• Deleting Unwanted Styles
• Understanding Conditional Formatting Conditions
• Using Multiple Test Conditions
• Copying Conditional Formatting
• Conditionally Formatting an Entire Row
• Shading Rows with Conditional Formatting
• Shading a Cell Until Something is Entered
• Understanding Color and Conditional Formatting Codes
• Using AutoFormat
• Placing Limits on AutoFormat
• Undoing an AutoFormat

Online Tips

• Inserting Hyperlinks
• Editing a Hyperlink
• Unwanted Hyperlinks
• Adding a ScreenTip
• Activating a Hyperlink
• Putting More than One Hyperlink in a Cell
• Finding Unknown Links

Worksheet Tips

• Quickly Inserting a New Worksheet
• Hiding and Unhiding Worksheets
• Moving from Sheet to Sheet
• Picking Worksheets Quickly
• Seeing All Open Workbook Names
• Turning Off Worksheet Tabs
• Dynamic Worksheet Tab Names
• Returning a Worksheet Name

Worksheet Function Tips

• Rounding Numbers
• Using the MROUND Worksheet Function
• Rounding to the Nearest $50
• Using the INT Worksheet Function
• Using the TRUNC Worksheet Function
• Establishing a FLOOR and CEILING
• Returning an ANSI Value
• Nesting IF Worksheet Functions
• Counting Records Matching Multiple Criteria
• Calculating Business Days
• Specifying Proper Case
• Using the EOMONTH Function
• Counting the Number of Blank Cells
• Calculating Combinations
• Selecting Random Names
• Using the IRR Function
• Counting with Formulas
• Using the REPT Function
• Random Numbers in a Range
• Adding Up Tops and Bottoms
• Converting Radians to Degrees

Formula Tips

• Dealing with Circular References
• Calculating Future Workdays
• External Data Validation
• Returning a Blank Value
• Ignoring Case in a Comparison
• Counting within Criteria
• Counting Unique Values
• Reordering Last Name and First Name
• Selective Summing
• Referring to the Last Cell
• Deriving Antilogs
• Finding the Nth Root of a Number
• Counting with Two Criteria
• Concatenating Names with Delimiters
• Returning Zero When a Referenced Cell is Blank
• Throwing Out the Lowest Score
• Iterating Circular References

Date and Time Tips

• How Excel Stores Dates and Times
• Changing Excel's Starting Date
• Entering Dates in Excel
• Quickly Entering Dates and Times
• Understanding Date and Time Formatting Codes
• Counting Dates in a Range
• Automatically Advancing by a Month
• End-of-Month Calculations
• Unique Date Displays
• Weekdays in a Month
• Elapsed Days as Years, Months and Days
• Automatically Converting to GMT
• Rounding Time
• Working with Elapsed Time
• Working with Minutes

Header and Footer Tips

• Using a Different Footer on Secondary Pages
• Specifying Date Formats in Headers
• Selective Headers and Footers
• Changing Section Headers
• Last Saved Date in a Footer
• Setting Header/Footer Margins

Graphics Tips

• Sizing Text Boxes and Cells the Same
• Inserting a Picture in Your Workbook
• Selecting Drawing Objects
• Changing the Size of a Graphic
• Cropping Graphics
• Creating a Drawing Object
• Rotating a Drawing Object
• Moving Drawing Objects
• Filling a Drawing Object
• Understanding Fill Effects
• Changing the Size of a Drawing Object
• Sending Drawing Objects to the Back or Front
• Making an Image Color Transparent
• Changing an AutoShape
• Watermarks in Excel
• Non-Tiled Background Pictures
• Exploded Pie Chart Sections
• Exporting Black and White Charts
• Capturing a Screen
• Taking Pictures
• Using the Camera in VBA
• Specifying the Size of Chart Objects
• Using the Keyboard to Select and Resize a Chart Object
• Colorizing Charts
• Changing Chart Types
• Changing Chart Size
• Using Chart Titles
• Creating a Chart
• Adding Data Labels to Your Chart
• Make that Chart Quickly!
• Creating Custom Chart Formats

Printing Tips

• Scaling Your Printing
• Setting Up Your Printer
• Setting Page Margins
• Setting Default Print Margins
• Centering Your Worksheet
• Setting the Print Area
• Printing More Than One Copy
• Collating Copies
• Using Duplex Printing
• Using Print Preview
• Speeding Up Printing
• Printing to a Disk File
• Specifying a Paper Tray in a Macro
• Printing Only Selected Pages
• Printing a Portion of a Worksheet
• Printing Multiple Worksheet Ranges
• Printing Multiple Selections
• Printing Selected Worksheets
• Printing Multiple Worksheets on a Single Page
• Printing Comments
• Setting Print Titles
• Printing a Worksheet List
• Black and White Blues
• Working With Multiple Printers
• Massive Printouts
• Printing a Single Column in Multiple Columns
• Printing a Chart
• Preparing a Chart Sheet for Printing
• Flipping Landscape Orientation when Printing

File Tips

• Opening Multiple Workbooks at Once
• Opening Two Workbooks with the Same Name
• Opening a Workbook as Read-Only
• Opening a Workbook with Two Windows
• Closing Multiple Files
• Importing Many Files Into Excel
• Finding the Parent Folder
• Getting Input from a Text File
• Using Your Own File Extensions
• Seeing Full File Names in the Files Menu
• Creating Individual Workbooks
• Protecting an Entire Workbook
• Creating a Workbook Clone
• Use Filenames That Sort Properly
• Renaming a Workbook
• Faster Text File Conversions
• How Excel Treats Disk Files
• Getting Rid of Extra Quote Marks in Exported Text Files
• Full Path Names in Excel
• Displaying Path Names in the Menu Bar
• Reducing File Sizes for Workbooks with PivotTables
• Selectively Importing Records
• Sorting Files

Tools Tips

• Using AutoCorrect
• Using Data Validation
• Setting Data Validation Input Messages
• Specifying a Data Validation Error Message
• Viewing Comments
• Editing Comments
• Managing Comments
• Using AutoSave in Excel
• Understanding Auditing
• Tracing Dependent Cells
• Tracing Precedent Cells
• Tracing Errors
• Using AutoFiltering
• Setting Up Custom AutoFiltering
• Advanced Filtering
• Copying the Results of Filtering
• Turning Off AutoFiltering
• Converting Imported Information to Numeric Values
• Creating Scenarios
• Showing a Scenario
• Editing a Scenario
• Creating Scenario Summaries
• Inserting a Sound File in Your Worksheet
• Understanding Views
• Using Stored Views
• Deleting a View
• Using the Report Manager
• Adding a Report
• Editing Reports
• Printing Reports
• Using Revision Tracking
• Making Revisions
• Resolving Revisions
• Inserting a Voice Annotation in Your Worksheet
• Changing the Office Assistant
• Sharing Your Workbook
• Turning Off Sharing
• Non-Printing Controls
• Changing Links
• Updating Links
• Comparing Lists for Duplicates
• Using List Box Controls
• Using Check Boxes
• Non-standard Sorting
• Performing Complex Sorts
• Creating a Sort Order
• Controlling Sorting Order
• Sorting Huge Lists
• Sorting an Entire List
• Understanding Ascending and Descending Sorts
• Sorting by Five Columns

Macro Tips

• Understanding Macros
• Recording a Macro
• Renaming a Macro
• Deleting a Macro
• Understanding Variables in VBA Macros
• Understanding Subroutines
• Understanding Functions
• Exiting a For ... Next Loop Early
• Understanding the While…Wend Structure
• Understanding the Select Case Structure
• Continuing Macro Lines
• Adding a Macro to a Toolbar
• Unlocking Charts
• Using Message Boxes
• Getting User Input in a Dialog Box
• Selecting a Cell in the Current Row
• Selecting a Range of Cells Relative to the Current Cell
• Converting Phone Numbers
• Numbers Spelled Out
• Finding the Last-Used Cell in a Macro
• Getting a File Name
• Determining If a Number is Odd or Even
• Retrieving Worksheet Names
• Unprotecting Groups of Worksheets
• Understanding Add-Ins
• Creating Add-Ins
• Using Custom Add-Ins
• Running a Macro in a Number of Workbooks
• Comparing Strings
• Playing with a Full Deck
• Automatically Closing a Workbook
• Resizing Checkboxes
• Pulling Cell Names into VBA
• Counting All Characters
• Updating Automatically When Opening Under Macro Control
• Displaying the "Last Modified" Date
• Skipping Hidden Rows in a Macro
• Updating Multiple PivotTables at Once
• Dissecting a String
• Running a Procedure when a Workbook is Opened
• Using InputBox to Get Data
• Replacing and Converting in a Macro
• Deleting Every X Rows
• Deleting Worksheets in a Macro
• Using Old Lotus Macros
• Converting Strings to Numbers
• Offering Options in a Macro
• Using the Status Bar
• Engineering Calculations
• Delimited Text-to-Columns in a Macro
• Clean Up Your Macro List
• Relative VBA Selections
• Determining the Length of a String
• Converting Text to Numbers
• Reorganizing Data
• Displaying the Selected Cell's Address
• Trimming Spaces from Strings

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.