ExcelTips 2006 Archive

The ExcelTips 2006 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
• Controlling Display of the Formula Bar
• Arranging Workbook Windows
• Quickly Changing Windows
• Seeing Excel’s Program Window
• Odd Arrow Key Behavior
• Shortcut to Merge Cells
• Viewing Workbook Statistics
• Renaming a Toolbar
• Large Toolbar Buttons
• Getting Rid of Stubborn Icons
• Moving Items On a Menu
• Adding Items to a Context Menu
• Removing Items from a Context Menu
• Disabling the F1 Key
• Zooming With the Keyboard
• Reversing Dimensions
• Generating Random Testing Data
• Seeing All Open Workbook Names
• Excel Won't Display Different Windows in the Taskbar
• Tasks for Each Workbook
• Saving Non-Existent Changes
• Saving Versions
• Running Out of Memory
• Changing Gridline Color
• Changing Coordinate Colors
• Defining and Using Custom Colors
• Removing Add-ins
• Saving Changes in Personal.xls
• Understanding Lists
• Working with Record Numbers
• Rounding in Results
• Changing Your Name
• Changing Your Company Name
• Easily Deploying Customizations
• Getting Audible Feedback
• Adding a Little Animation to Your Life
• Buttons for Workbooks
• Drop-Down List Font Sizes
• Backing Up Your AutoCorrect Entries
• Making the Formula Bar Persistent
• Changing the Office Assistant
• Giving the Office Assistant Focus

Editing Tips

• Quickly Entering Data
• Editing Individual Cells
• Entering Formulas in Excel
• Clearing and Deleting Cells
• Moving and Copying Cells
• Can't Copy Data between Workbooks
• Combining Columns
• Quickly Deleting Rows and Columns
• Adjusting a Range's Starting Point
• Cell Movement After Enter
• Choosing Direction After Enter On a Workbook Basis
• Default Cell Movement when Deleting
• Viewing Formula Results
• Checking for a Value in a Cell
• Enhanced Filling
• Reference Shortcut
• End-of-Month Calculations
• Tab Key Jumps a Screen at a Time
• Automatically Numbering Rows
• A Fast Find-Next
• Searching for Wildcards
• Searching Comments
• Anchoring Comment Boxes in Desired Locations
• Changing Months in a Workbook
• Removing Duplicate Cells
• Removing Duplicate Rows
• Getting Rid of Spaces in Cells
• Inserting Different Dashes
• Moving Subtotals
• Jumping to a Range
• Selecting the Current Region
• Counting with Subtotals
• Formulas Don't Calculate as Formulas
• Limiting Entry of Names
• Synchronizing Lists
• Pasting Leading Zeroes
• Removing Cells from a Selected Range
• Turning Off Capital Corrections
• Fixing the Decimal Point
• Magnifying Only the Current Cell
• AutoFilling with the Alphabet
• AutoFilling with Weekdays
• Cleaning Up Lists
• Noting When a Workbook was Changed
• Using Go To to Jump to a Chart Sheet
• Limiting Choices in a Cell
• Changing Links
• Updating Links

Formatting Tips

• Removing All Formatting
• Shrinking Cell Contents
• Changing Font Sizes
• Accurate Font Sizes
• Understanding Monospace Fonts
• Underlining
• No New Fonts Error
• Changes in Font Size when Copying
• Setting Row Height
• Hiding and Unhiding Rows
• Hiding and Unhiding Columns
• Hiding and Unhiding Worksheets
• Hiding Individual Cells
• Shading Rows for Ease in Reading Output
• Removing Borders
• Changing Page Number Format
• Using Copy and Paste for Formatting
• Checking All Cell Formatting in VBA
• Automatically Copying Formatting
• Exporting Latitude and Longitude
• Setting Orientation of Cell Values
• Flashing Cells
• Shading a Cell Until Something is Entered
• Adding a Custom Format to those Offered by Excel
• Understanding Color and Conditional Formatting Codes
• Displaying Negative Percentages in Red
• Creating Two-Line Custom Formats
• No More Custom Formats Can Be Added
• Understanding Conditional Formatting Conditions
• Using Multiple Test Conditions
• Shading Rows with Conditional Formatting
• Conditionally Formatting an Entire Row
• Conditionally Formatting for Multiple Date Comparisons
• Conditional Page Breaks
• Counting Colors from Conditional Formats

Header and Footer Tips

• Setting Header/Footer Margins
• Selective Headers and Footers
• Ampersands in Headers and Footers
• Specifying Date Formats in Headers
• Changing Section Headers
• Last Saved Date in a Footer

Online Tips

• Inserting Hyperlinks
• Editing a Hyperlink
• Unwanted Hyperlinks
• Hyperlinks in Shared Workbooks
• Converting a Range of URLs to Hyperlinks
• Extracting E-mail Addresses from Hyperlinks
• Extracting URLs from Hyperlinked Images
• Get Rid of Web Stuff
• Suppressing the Reviewing Toolbar on E-mailed Workbooks
• Jumping to Text in Worksheet from an Index
• Adding a ScreenTip

Worksheet Tips

• Moving from Sheet to Sheet
• Picking Worksheets Quickly
• Jumping to a Specific Worksheet
• Relative Worksheet References
• Turning Off Worksheet Tabs
• Dynamic Worksheet Tab Names
• Ordering Worksheets Based on a Cell Value
• Unlocking a Worksheet with an Unknown Password
• Functioning Check Boxes in a Protected Worksheet
• Hiding and Protecting Columns

Worksheet Function Tips

• Establishing a FLOOR and CEILING
• Counting the Number of Blank Cells
• Returning an ANSI Value
• Adjusting the VLOOKUP Function
• Nesting IF Worksheet Functions
• Determining the Least Common Multiple
• Using the WEEKNUM Function
• Specifying Proper Case
• Using the EOMONTH Function
• Calculating Combinations
• Counting with Formulas
• Using the REPT Function
• Indirect References to a DSUM Parameter
• Selecting Random Names
• Using the IRR Function
• Random Numbers in a Range
• Summing Only Visible Values
• Adding Up Tops and Bottoms
• Converting Radians to Degrees
• Rounding to the Nearest $50
• Using the COLUMN Function
• Determining a Value of a Cell
• Converting to Octal
• Converting to Hexadecimal

Formula Tips

• Reordering Last Name and First Name
• Separating Names into Individual Columns
• Concatenating Names with Delimiters
• Getting a Count of Unique Names
• Comparing Lists for Duplicates
• Counting with Two Criteria
• Calculating Statistical Values on Different-Sized Subsets of Data
• Checking for Messages in Cells
• External Data Validation
• Complex Lookup Formulas
• Simulating Alt+Enter in a Formula
• Counting Cells with Text Colors
• Selective Summing
• Displaying Messages When Automatic Data Changes
• Referring to the Last Cell
• Picking Different Random Numbers from a Range
• Non-adjusting References in Formulas
• Finding the Nth Root of a Number
• Deriving Antilogs
• Patterns of Numbers with a Formula
• Solving Simultaneous Equations
• Problems with Nested Subtotals
• Deriving Monthly Median Values
• Throwing Out the Lowest Score
• Iterating Circular References
• Returning Zero When a Referenced Cell is Blank
• Maintaining Text Formatting in a Lookup
• Summing Absolute Values

PivotTable Tips

• Maintaining Formatting when Refreshing PivotTables
• Weighted Averages in a PivotTable
• Updating Multiple PivotTables at Once
• Removing Subtotals from Many PivotTable Fields
• Missing PivotTable Data

Sorting and Filtering Tips

• Sorting an Entire List
• Understanding Ascending and Descending Sorts
• Controlling Sorting Order
• Sorting Huge Lists
• Sorting by Five Columns
• Sorting ZIP Codes
• Sorting by Colors
• Using AutoFiltering
• Filtering Columns for Unique Values
• Limits to Filtering
• Setting Up Custom AutoFiltering
• Turning Off AutoFiltering
• Advanced Filtering
• Copying the Results of Filtering
• Removing Duplicates Based on a Partial Match
• Quickly Identifying Applied AutoFilters

Date and Time Tips

• Entering Dates in Excel
• Quickly Entering Dates and Times
• Recording a Data Entry Time
• Converting Numeric Values to Times
• Formatting for Hundredths of Seconds
• Elapsed Days as Years, Months and Days
• Unique Date Displays
• Leap Years and Fiscal Periods
• Pulling All Fridays
• Weekdays in a Month
• Calculating TV Time
• Calculating Business Days
• Calculating the Day of the Year

Graphics Tips

• Creating a Drawing Object
• Creating an AutoShape
• Selecting Drawing Objects
• Changing an AutoShape
• Changing the Size of a Graphic
• Changing the Size of a Drawing Object
• Cropping Graphics
• Moving Drawing Objects
• Moving and Copying Graphics Objects
• Rotating a Drawing Object
• Sending Drawing Objects to the Back or Front
• Filling a Drawing Object
• Understanding Fill Effects
• Making an Image Color Transparent
• Adding Drop Shadows
• Capturing a Screen
• Taking Pictures
• Non-Tiled Background Pictures
• Protecting a Graphic
• Hiding Objects
• Displaying Images based on a Result

Chart Tips

• Creating a Chart
• Make that Chart Quickly!
• Creating Custom Chart Formats
• Colorizing Charts
• Changing Chart Types
• Changing Chart Size
• Using Chart Titles
• Adding Data Labels to Your Chart
• Specifying the Size of Chart Objects
• Excel Charts in PowerPoint
• Multiple Data Points in a Chart Column
• Automatically Updating Charts for Additional Data
• Changing Elements in Lots of Charts at One Time
• Y-Axis Chart Titles are Truncated

Printing Tips

• Using Print Preview
• Setting Up Your Printer
• Setting Default Print Margins
• Setting the Print Area
• Setting Print Titles
• Printing More Than One Copy
• Collating Copies
• Print Quantity is Stuck
• Using Duplex Printing
• Setting Print Quality
• Centering Your Worksheet
• Printing Only Selected Pages
• Printing a Portion of a Worksheet
• Printing a Short Selection
• Printing Selected Worksheets
• Printing Multiple Worksheet Ranges
• Printing Multiple Worksheets on a Single Page
• Multiple Print Areas on a Single Printed Page
• Printing a Single Column in Multiple Columns
• Printout Doesn't Match Display for Some Cells
• Printing a Worksheet List
• Printing a List of Named Ranges
• Changing Paper Size for a Complete Workbook
• Working With Multiple Printers
• Non-Printing Controls
• Black and White Blues
• Massive Printouts
• Flipping Landscape Orientation when Printing
• Preparing a Chart Sheet for Printing
• Printing a Chart

File Tips

• Opening Multiple Workbooks at Once
• Opening a Workbook as Read-Only
• Opening a Workbook with Two Windows
• Creating Individual Workbooks
• Creating a Workbook Clone
• Saving in Multiple Locations
• Use Filenames That Sort Properly
• Renaming a Workbook
• Closing Multiple Files
• How Excel Treats Disk Files
• Sudden Increases in Workbook File Size
• Reducing File Sizes for Workbooks with PivotTables
• Unwanted Read-Only Workbook Status
• Sharing Your Workbook
• Turning Off Sharing
• Adding a File Path and Filename
• Full Path Names in Excel
• Displaying Path Names in the Menu Bar
• Protecting an Entire Workbook
• Protecting an Entire Folder of Workbooks
• Using a Single Password for Multiple Workbooks
• Selectively Importing Records
• Faster Text File Conversions
• Getting Rid of Extra Quote Marks in Exported Text Files
• Personal.xls File Not Opening
• Displaying the "Last Modified" Date
• Calculating Only the Active Workbook

Tools Tips

• Understanding Auditing
• Tracing Dependent Cells
• Tracing Precedent Cells
• Tracing Errors
• Accessing Dependant and Precedent Information
• Creating Scenarios
• Showing a Scenario
• Editing a Scenario
• Creating Scenario Summaries
• Understanding Views
• Using Stored Views
• Deleting a View
• Using the Report Manager
• Adding a Report
• Printing Reports
• Editing Reports
• Using Revision Tracking
• Making Revisions
• Resolving Revisions
• Inserting a Voice Annotation in Your Worksheet
• Inserting a Sound File in Your Worksheet
• Turning Off Speech Capabilities
• Undoing Smart Tag Exclusions
• Counting All Characters
• Using AutoSave in Excel
• Using List Box Controls
• Using Check Boxes

Macro Tips

• Understanding Macros
• Recording a Macro
• Continuing Macro Lines
• Renaming a Macro
• Clean Up Your Macro List
• Debugging a Macro
• Adding a Macro to a Toolbar
• Declaring Variables
• Understanding the While…Wend Structure
• Selecting the First Cell In a Row
• Selecting a Specific Cell in a Macro
• Skipping Hidden Rows in a Macro
• Inserting Worksheet Values with a Macro
• Relative VBA Selections
• Relative References when Recording Macros
• Using InputBox to Get Data
• Hiding Entries in an InputBox
• Offering Options in a Macro
• Pulling Apart Characters in a Long String
• Dissecting a String
• Converting Strings to Numbers
• Determining the Length of a String
• Converting Text to Numbers
• Reversing Cell Contents
• Trimming Spaces from Strings
• Creating a String in a Macro
• Numbers Spelled Out
• Telling which Worksheets are Selected
• Copying Worksheets in a Macro
• Deleting Worksheets in a Macro
• Running a Macro When a Worksheet is Activated
• Deleting Every X Rows
• Updating Automatically When Opening Under Macro Control
• Running a Procedure when a Workbook is Opened
• Checking if a Workbook is Already Open
• Automatically Closing a Workbook
• Pulling Cell Names into VBA
• Official Color Names in VBA
• Playing with a Full Deck
• Resizing Checkboxes
• One Shortcut for Two Macros
• Replacing and Converting in a Macro
• Error Using ATAN2 Function in Macro
• Macro for Month Name
• Selecting Columns in VBA when Cells are Merged
• Using the Status Bar
• Delimited Text-to-Columns in a Macro
• Reorganizing Data
• Using Named Ranges in a Macro
• Engineering Calculations
• Displaying the Selected Cell's Address
• Determining if Calculation is Necessary
• Displaying a Set Column Range
• Self-Deleting Macros
• Digital Signatures for 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.