ExcelTips 2007 Archive

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

• Finding Commands in Excel 2007
• Getting Context-Sensitive Help
• Understanding R1C1 References
• History of Excel
• Determining Your Version of Excel
• Determining Your Serial Number
• Specifying the Behavior of the Enter Key
• Understanding Manual Calculation
• Nifty Zooming
• Weird Mouse Shortcut
• Viewing Your Work Full-Screen
• Floating Information in a Frozen Row
• Turning Off AutoFill
• Missing Equal Sign Tool
• Getting Rid of Workbook Links
• Speeding Up Large Worksheets
• Viewing More Than Two Places in a Worksheet
• Controlling Display of Page Breaks
• Maintaining the Active Cell
• Setting the Calculation Default
• Moving and Selecting Sheets With the Keyboard
• Appearance of Excel on the Taskbar
• Transferring an Older Personal.xls to Excel 2007
• Changes in Excel 2007 Color System
• Grabbing a User's Name from Excel
• Embedding Your Phone Number in a Workbook
• Controlling How Excel Interprets Percentages
• Controlling the Behavior of the Mouse Wheel
• Changing Excel's Background Color
• Turning on Placeholders
• Cycling Through Colors
• Working with Imperial Linear Distances
• Quickly Removing a Toolbar Button
• Disappearing Toolbar Buttons for Macros

Editing Tips

• Shortcut for Selecting a Data Range
• How Many Rows and Columns Have I Selected?
• Inserting Rows
• Displaying a Hidden First Column
• Displaying Row and Column Labels
• Relative Worksheet References when Copying
• Forcing Editing to Be Done in a Cell
• Controlling Where You Edit Cell Contents
• Sequentially Inputting Information
• Forcing Input to Uppercase
• Ctrl+V Pasting is Flakey
• Starting Out Formulas
• Deleting Every X Rows
• Getting Rid of Non-Printing Characters Intelligently
• Copying Cells to Fill a Range
• Quick AutoFill Variations
• Increasing Undo Levels
• Quickly Updating Values
• Setting a Length Limit on Cells
• Limiting Entry of Prior Dates
• Unique Name Entry, Take Two
• Pasting a Hyperlink
• Highlighting the Rows of Selected Cells
• Character Limits for Cells
• Ignoring Paragraph Marks when Pasting
• Zooming In On Your Worksheet
• Copying Values
• Copying a Cell without Formatting
• Limiting Searching to a Column
• Searching for Line Breaks
• Defining Shortcut Keys for Symbols
• Clearing Everything Except Formulas
• Merging Cells to a Single Sum
• Finding Wayward Links
• Easily Entering Dispersed Data
• Using AutoComplete with Disjointed Lists
• Inserting Dashes between Letters and Numbers
• Segregating Numbers According To Their Sign
• Displaying Letter Grades
• Automatically Capitalizing Day Names
• Increasing the Capacity of AutoCorrect

Formatting Tips

• Copying Formats to a New Worksheet
• Understanding Underlines
• Understanding Cell Indenting
• Indenting Cell Contents
• Adjusting Row Height when Wrapping Text
• Adjusting Row Height for Your Text
• Double-Clicking to Widen Columns Won't Work
• Random Width and Height Changes
• Easy Value Hiding
• Shortcut Key for Format Painter
• Using a Custom Format to Add Dashes
• Saving Custom Formats
• Professional Looking Fractions
• Automatic Row Height For Merged Cells with Text Wrap
• Changing the Color of a Cell Border
• When is Currency Not Currency?
• Matching Formatting when Concatenating
• Repeating Cell Contents
• Formatted Data Appears Differently On Different Systems
• Controlling Display of Page Breaks
• Highlighting Values in a Cell
• Changing the Font Size in Combo Boxes
• Changing Fonts in Multiple Workbooks
• Preventing Changes to Formatting and Page Size
• Changing Fonts in Data Validation Drop-Down Lists
• Displaying Latitude and Longitude
• Getting Rid of Negative Zero Amounts
• Specifying Superscript Text
• Problems with Custom Views
• Hiding Columns Based on a Cell Value
• Hiding Columns Not within a Date Range
• Automatic Lines for Dividing Lists
• Conditionally Highlighting Cells Containing Formulas
• Conditional Formatting for Errant Phone Numbers
• More than Three Conditional Formats

Header and Footer Tips

• Putting Headers and Footers On Multiple Worksheets
• Recognizing a Header Row when Sorting
• Putting Cell Contents in Footers
• Moving Part of a Footer Down a Line
• Roman Numerals for Page Numbers

Online Tips

• Dynamic Hyperlinks in Excel
• Using Drag-and-Drop to Create a Hyperlink
• Links to Hyperlinks
• References to Hyperlinks Aren't Hyperlinks
• Hyperlinks to Charts
• Deleting a Hyperlink
• Converting to Hyperlinks in a Shared Workbook
• Extracting URLs from Hyperlinks
• Generating a Web Page
• Adding Excel Information to a Web Page
• Can't Delete Web Page Created by Excel
• Sending Single Worksheets via E-mail
• Retrieving Web Query Data without Interruption
• Using Web Discussions
• Connecting To a Discussion Server
• Printing Web Discussions
• Filtering Web Discussions

Worksheet Tips

• Shortcut to Move Between Two Worksheets
• Shifting Objects Off a Sheet
• Multiple Rows of Worksheet Tabs
• Freezing Worksheet Tabs
• Colors and Fonts for Worksheet Tabs
• Testing for an Empty Worksheet
• Counting the Times a Worksheet is Used
• Freezing Top Rows and Bottom Rows
• Condensing Multiple Worksheets Into One
• Using a Protected Worksheet
• Inserting and Deleting Rows in a Protected Worksheet

Worksheet Function Tips

• Using the ABS Function
• Using the CONCATENATE Worksheet Function
• The EDATE Function
• Using the FORECAST Function
• Using GEOMEAN With a Large List
• Using the IF Worksheet Function
• Returning the MODE of a Range
• Understanding the VLOOKUP Function
• Using the XIRR Function
• Cleaning Text
• Converting Codes to Characters
• Determining Columns in a Range
• Returning the Left-most Characters
• Phantom Counts
• Rounding to the Nearest Half Dollar
• Rounding to Even and Odd Values

Formula Tips

• Totaling Across Worksheets
• Using Named Formulas Across Workbooks
• Filling References to Another Workbook
• Calculating the Interval Between Occurrences
• Deriving High and Low Non-Zero Values
• Counting Cells with Specific Characters
• Combinations for Members in Meetings
• Counting Unique Values
• Returning the Smallest Non-Zero Value
• Determining "Highest Since" or "Lowest Since"
• Breaking Up Variable-Length Part Numbers
• Saving Common Formulas
• Character Replacement in Simple Formulas
• Ignoring #N/A Values in a Sum
• Deriving a Secant and Cosecant
• Counting Groupings Below a Threshold
• Retrieving the Last Value in a Column
• Numbers in Base 12
• Getting Rid of Alphabetic Characters
• Relative References within Named Ranges
• Summing Every Fourth Cell in a Row
• Converting Mainframe Date Formats
• Alphabetic Column Designation
• Counting Employees in Classes
• Combining Numbers and Text in a Cell
• Combining Cell Contents
• Finding the Directory Name
• Number of Terms in a Formula
• Using a Numeric Portion of a Cell in a Formula
• Calculating the Distance Between Points
• Developing Reciprocal Conversion Formulas
• Ages in Years and Months

PivotTable Tips

• Refreshing PivotTable Data
• Formatting a PivotTable
• Counting with PivotTables
• Excluding Zero Values from a PivotTable
• Editing PivotTables without Underlying Data

Sorting and Filtering Tips

• Sorting Letters and Numbers
• Sorting Dates by Month
• Incomplete and Corrupt Sorting
• Sorting while Ignoring Leading Characters
• Sorting Data on Protected Worksheets
• Too Many Formats when Sorting
• Sorting Data Containing Merged Cells
• Extracting Targeted Records from a List
• Recalculating when Filtering

Comment Tips

• Static Sizes for Comment Boxes
• Copying Comments when Filtering
• Setting the Default Font Size for Comment Balloons
• Formatting Text in Comment Boxes
• Comments Don't Appear when Cell is Pointed To
• Editing a Comment Close to Its Cell
• Adjusting Comment Printouts

Date and Time Tips

• Entering the Current Time
• Inserting Tomorrow’s Date
• Defeating Automatic Date Parsing
• Adding Ordinal Notation to Dates
• Unique Military Date Format
• Calculating Fractions of Years
• Calculating Averages by Date
• Calculating Week-Ending Dates
• The Last Business Day
• Converting an Unsupported Date Format
• Parsing Non-Standard Date Formats
• Alerts About Approaching Due Dates
• Calculating Months for Billing Purposes
• Calculating Months of Tenure
• Monthly Close-Out Dates
• Calculating Time Differences between Two Machines
• Adjusting Times for Time Zones
• Checking for Time Input

Graphics Tips

• Drawing Simple Objects
• Drawing Lines
• Styles for Lines, Dashes, and Arrows
• Changing How Arrows Look
• Nudging a Graphic
• Flipping a Drawing Object
• Positioning Graphics Evenly
• Creating an Organization Chart
• Inserting Multiple Drawing Objects
• Hiding Graphics
• Using Text Boxes
• Duplicating Drawing Objects
• Quickly Duplicating Drawing Objects
• Sorting with Graphics
• Changing Line Color in a Drawing Object
• Symmetric Resizing of Graphics
• Editing Graphics Objects
• Deleting All Graphics
• Pasting a Graphic to Multiple Worksheets
• Adding a Drop Shadow to a Text Box
• Pulling AutoShape Text from a Worksheet Cell
• Grouping and Ungrouping Objects
• Exporting a Graphics Group
• ScreenTip for an Image

Chart Tips

• Changing Chart Type
• Unselecting a Chart Item
• Deleting a Chart
• Using Dynamic Chart Titles
• Controlling Chart Gridlines
• Moving a Chart’s Legend
• Turning the Legend On and Off
• Two-Level Axis Labels
• Changing the Axis Scale
• Modifying Axis Scale Labels
• Negatives in Pie Charts
• Locking Callouts to a Graph Location
• Changing the Pattern Used in a Data Series
• Smoothing Out Data Series
• Creating a Log/Log Chart
• Identifying Scatter Plot Points
• Adjusting Your View of 3-D Graphs
• Graphically Charting Geographic Data
• Plotting Times of Day
• Embedding an Excel Chart in a Word Document

Printing Tips

• Printing a Range of Pages
• Printing Columns and Rows
• Fitting Your Printout on a Page
• Clearing the Print Area
• Adding the Set Print Area Tool
• Specifying an Order for Page Printing
• Resetting Page Setup
• Repeating Rows at the Bottom of a Page
• Setting Print Ranges for Multiple Worksheets
• Printing a Draft of a Worksheet
• Printing a Draft Watermark
• Specifying a Print Tray for a Worksheet
• Using Less Paper on Printouts
• Printing Workbook Properties
• Printing without Opening
• Out of Kilter Borders
• Disabling Printing
• Printing a Chart Across Multiple Pages
• Specifying Print Quantity in a Cell
• Printing Limited Pages from a Range of Worksheets

File Tips

• Setting a Default File Format
• File Format Not Valid
• Saving a Workbook Using Passwords
• Importing Multiple Files to a Single Workbook
• Sorting Files
• Changing Link References
• Making Changes in a Group of Workbooks
• Error Opening Second Workbook
• Cannot Double-Click to Open a Workbook
• Creating a Workbook Clone
• Closing a Read-Only Workbook
• Reducing File Size
• Who Has the File Open?
• Grabbing the MRU List
• Disabling Shift Key Use when Opening a Workbook
• Opening Personal.xlsb in Excel 2007
• Jumping to the Real Last Cell
• Accessing a Problem Shared Workbook
• Visually Showing a Protection Status
• Determining If a File Exists
• Saving Information in a Text File
• Appending to a Non-Document Text File
• Determining the Length of a Text File
• Creating a CSV File
• Comma-Delimited Differences for PC and Mac
• Aligning Cells when Importing from CSV
• Handling Leading Zeros in CSV Files
• Stopping Date Parsing when Opening a CSV File
• Saving Worksheets in Lotus 1-2-3 Format
• Converting from SuperCalc

Tools Tips

• Using Data Forms
• AutoFill with Random Numbers
• Searching a Workbook by Default
• Creating Dependent Drop-Lists
• Making a Drop-Down List Indicator Visible
• Single-Use Drop-Down List
• Adding Buttons to Your Worksheet
• Fixed-Width Settings when Converting Text to Columns
• Understanding Outlining
• Hiding Outline Symbols
• Finding the Analysis ToolPak Add-In
• Restoring the Analysis ToolPak
• Turning Off Track Changes without Unsharing
• Spell-Checking in a Protected Worksheet
• Editing Custom Dictionaries

Macro Tips

• Editing Macros
• Worksheet Events
• Workbook Events
• Develop Macros In Their Own Workbook
• List of Macro Shortcuts in All Open Workbooks
• Removing a Macro from a Shortcut Key
• Making Common Functions Available to Others
• Hiding Macros
• Turning Off Screen Updating
• Running Macros in the Background
• Running a Macro When a Worksheet is Activated
• Running a Macro When a Worksheet is Deactivated
• Running a Macro When a Workbook is Closed
• Macro Runs Slowly, but Steps Quickly
• Getting Rid of the “Enable Macros” Notice
• Maximum Length Limit for a Macro
• Progression Indicator in a Macro
• Aborting a Macro and Retaining Control
• Moving Macros from the Personal Workbook
• Pausing Macros for User Input
• Determining a Random Value
• Determining an Integer Value
• Deriving an Absolute Value in a Macro
• Swapping Two Numbers
• Converting Numbers to Strings
• Swapping Two Strings
• Page Numbers in VBA
• Resizing a Text Box in a Macro
• Spreading Out a Table
• Trouble Recording Paste Special—Formula
• Displaying the Print Dialog Box in a Macro
• Pulling Apart Cells
• Opening a Workbook but Disabling Macros
• Assigning Macros to Graphics
• Clearing the Undo Stack in a Macro
• Positioning a Column on the Screen
• Determining if Caps Lock is On
• Using Seek In a Macro
• Removing Pictures for a Worksheet in VBA
• Using SUM In a Macro
• Quickly Dumping Array Contents
• Determining an ANSI Value in a Macro
• Specifying a Delimiter when Saving a CSV File in a Macro
• Using the Camera in VBA
• Creating a Plus/Minus Button
• Detecting Types of Sheets in VBA
• Conditionally Displaying a Message Box
• Setting Row Height in a Macro
• Setting Column Width in a Macro
• Running Macros on Hidden Worksheets
• Adjusting Values with Formulas
• Splitting Information into Rows
• Easily Adding Blank Rows
• Making a Cell's Contents Bold within a Macro
• Making a Cell's Contents Italics within a Macro
• Deleting Worksheet Code in a Macro
• Friendly and Informative Error Handling
• Unhiding Multiple Worksheets
• Adding Leading Zeroes to ZIP Codes
• Creating Charts in VBA
• Easily Changing the Default Drive and Directory
• Changing the Default Drive
• Determining the Current Directory
• Changing Directories in a Macro
• Creating a Directory in a Macro
• Removing a Directory
• Renaming a File
• Deleting a File in a Macro
• Noting the Workbook Creation Date
• Determining the Hour of the Day
• Determining Differences Between Dates
• Determining the Day of the Month
• Expiration Date for Excel Programs
• Creating a Splash Screen
• DOS From 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.