ExcelTips Ribbon 2025 Archive (Table of Contents)

ExcelTips Ribbon 2025 Archive includes hundreds of valuable tips. The following is the Table of Contents for the archive:

Introduction

• What's in This E-Book?
• Need More Tips?
• A Special Note of Thanks
• Sharing this Document

General Tips

• Always Open at 100% Zoom
• Freezing Both Rows and Columns
• Going to the Corners of a Selected Range
• Maintaining the Active Cell
• Controlling Display of Page Breaks
• Changing the Reference in a Named Range
• Tying a Named Range to a Value Instead of a Cell
• Changing the Color of Worksheet Gridlines
• Allowing for Words that Contain Numbers
• Inserting the User's Name in a Cell
• Locking Worksheet Names
• Saving Movement on Enter with a Workbook
• Empty Cells Triggers Error
• Excel Stops Recalculating
• Limiting Options when Saving
• Establishing a Naming Convention
• Editing the Custom Spelling Dictionaries
• Backing Up Custom Dictionaries
• Updating Workbook Links

Editing Tips

• Inserting Rows
• Changing the Default Paste Mode
• Switching Editing Location
• Forcing Editing to Be Done in a Cell
• Displaying a Hidden First Column
• Defeating Exponent Parsing
• Shortcut for Selecting a Data Range
• Pasting Leading Zeroes
• Non-Excel Sources
• Excel Sources
• Changing References in a Lot of Defined Names
• Displaying Row and Column Labels
• Proper Case Conversion with Exceptions
• Setting an Upper Threshold for a Cell
• Removing Spaces around Dashes
• Applying Range Names to Formulas
• Copying a Cell without Formatting
• Notification of When a Copy is Complete
• Pasting and Matching Destination Formatting
• Filling a Set Number of Cells
• Copying Subtotals
• Zooming In On Your Worksheet

Find and Replace Tips

• Replacing Characters at the End of a Cell
• Searching for All
• Finding and Replacing in Text Boxes
• Finding and Replacing Text in Notes and Comments

Formatting Tips

• Replacing Background Colors in Cells
• Changing the Color of a Cell Border
• Understanding Underlines
• Unwanted Strikethrough Formatting
• Copying Formats to a New Worksheet
• Hiding Columns Not within a Date Range
• Preventing Automatic Date Formatting Changes
• Removing All Formatting in a Worksheet
• Matching Formatting when Concatenating
• Moving Custom Formats to Number Formatting Categories
• Getting Rid of Leading Zeros in a Number Format
• Custom Formats for Scientific Notation
• Understanding Date and Time Formatting Codes

Conditional Formatting Tips

• Remove Conditional Formatting but Retain the Effects
• Noting Inactivity within a Timeframe
• Conditional Formatting for Errant Phone Numbers
• Conditional Formats for Odd and Even Columns
• Highlighting the Largest Values in a Column

Online Tips

• Adding a ScreenTip
• Pulling Apart a URL
• Removing Some Hyperlinks
• Hyperlink Doesn't Match Cell Contents

Worksheet Tips

• Dynamically Changing Worksheet Tab Color
• Moving Worksheet Tabs to a Different Place
• Locking All Non-Empty Cells
• Freezing Worksheet Tabs
• Adding Comments to Protected Worksheets

Worksheet Function Tips

• SUM not Providing the Correct Sum
• Limiting the Number of Results from a Function
• Counting Displayed Cells
• Cleaning Text
• Using COUNTIF with Colors
• Rounding to the Nearest Half Dollar
• Rounding to Even and Odd Values

Formula Tips

• Finding Circular References
• Summing Based on Part of a Control Cell
• Using a Helper Column
• Using SUMPRODUCT
• Using SUMIF Directly
• Summing the Largest Values
• Finding the Sum of a Sequential Integer Range
• Summing the Four Largest Unique Values
• Summing Only the Largest Portion of a Range
• Strange Formula Conversions
• Strange Value Results in a Cell
• Counting Cells According to Case
• Counting Names Based on Two Criteria
• Generating a List of Customers and Transaction Dates
• Breaking Up Variable-Length Part Numbers
• Aggregating Monthly Sales from a Larger Data Set
• Saving Common Formulas
• Creating a Calendar
• Calculating the Distance between Points
• Listing the Top Five Transactions
• Incrementing a Series of Integers Stored as a Text Value
• Listing Combinations
• Finding References to Nonexistent Cells
• Quickly Adding Formulas Referencing Multiple Worksheets
• Limiting a Calculated Value to a Range
• Deleting Duplicate Text Values
• Using the Delete Duplicates Tool
• Using Data Filtering
• Using a Formula
• Using a Macro
• Calculating the Average of Five Values Near the End of a Range
• Combining Numbers and Text in a Cell
• Deriving a Secant and Cosecant
• Counting Cells Containing a Word
• Finding the Directory Name
• Capitalizing the First Letter of a Cell
• Extracting File Names from a Path
• Highlighting an Unchanging Value
• Inserting Commas in a Value Excel Interprets as a Number
• Selecting Ten Random Numbers
• Calculating the Interval between Occurrences
• Extracting Values Appearing More than Twice

Sorting and Filtering Tips

• Getting Wrong Results from a Simple Sort
• Unknown Settings
• Data Range
• Data Type
• Data Inconsistency
• Extraneous Characters
• Sorting Data Containing Merged Cells
• Sorting by Military Rank
• Use a Helper Column
• Use the Person's Paygrade
• Create a Custom Sort Order
• Filtering Columns for Unique Values
• Displaying Multiple Filtered Colors
• Filtering Addresses for a Group of States
• Using Filtering
• Using Advanced Filtering
• Filtering in a Table
• Using a Formula
• Using a Macro
• Filtering is Unavailable when Selecting Entire Worksheet
• Filtering Columns

Date and Time Tips

• Converting Text Dates to Real Dates
• Copying Dates a Year into the Future
• Starting Date for a Week Number
• Converting UNIX Date/Time Stamps
• Ages in Years and Months
• Days Left in the Year
• Assuming Dates are in the Current Century
• Taking Time of Day into Account in a Formula
• Dealing with Large Numbers of Seconds
• Rounding to the Nearest Quarter Hour

Graphics and Charting Tips

• Editing Graphics Objects
• Deleting All Graphics
• Deleting All Pictures
• Pictures inside Shapes
• Pasting a Graphic to Multiple Worksheets
• Styles for Lines, Dashes, and Arrows
• Filling a Drawing Object
• Changing Line Color in a Drawing Object
• Using Graphics to Represent Data Series
• Changing the Axis Scale
• Changing the Size of a Drawing Object
• Setting a Transparent Color for an Image
• Charting with a Large Number of Data Set Formats
• Specifying Chart Sizes in Inches
• Understanding Custom Chart Templates
• Easily Changing Chart Data Ranges
• Formatting Axis Patterns

Header and Footer Tips

• First and Last Names in a Page Header
• Moving Part of a Footer Down a Line
• Determining Header Section Overlap in a Macro

Printing Tips

• Printing Odd or Even Pages
• Fitting Your Printout on a Page
• Conditional Printing
• Printing a Chart
• Black and White Blues
• Executing a Macro After Printing is Done
• Printing Individual Worksheets for Vendors
• Using Subtotals
• Using Filtered Data
• Using PivotTables
• Using Macros
• Setting Print Ranges for Multiple Worksheets
• Flipping Landscape Orientation when Printing
• Printing without Opening

Workbook and File Tips

• Protecting an Entire Workbook
• Using a Single Password for Multiple Workbooks
• Worksheet Deletions Don't Reduce Workbook File Size
• Making Ribbon Customizations Apply to All Workbooks
• Last Person to Save a Workbook
• Copying a Single Worksheet from Many Workbooks
• Stopping a Workbook from Persistently Auto-Loading
• Favorites and Recents Don't Show Up when Starting Excel
• Saving a Workbook with a Preview
• Bothersome 'File has Changed' Message
• How Excel Treats Disk Files
• Setting the AutoRecover Directory
• Renaming a Workbook
• Creating a Dated Backup File
• Opening a Workbook with Two Windows
• Appending to a Non-Excel Text File

Tools Tips

• Checking for Version Compatibility
• Evaluating Formulas
• Understanding Auditing
• Creating Scenarios
• Prohibiting Single-Digit Entries in a Cell
• Single-Use Drop-Down List
• Displaying a Worksheet from a Drop-Down List Choice
• Complex Data Input Limitations
• Missing PivotTable Data

Macro Tips

• Understanding Macros
• Understanding Add-Ins
• Storing Macros in Templates
• Copying Worksheet Code Automatically
• Creating a Directory in a Macro
• Removing a Directory
• Checking if a Workbook is Already Open
• Searching Very Large Strings in a Macro
• Positioning a Column on the Screen
• Delimited Text-to-Columns in a Macro
• Checking for Digits in a String
• Changing Directories in a Macro
• Enforcing Moving Cells Up
• Protect the Worksheet
• Intercepting Deletes
• Remapping the Delete Key
• The Complete Approach
• Deriving an Absolute Value in a Macro
• Tools on Developer Tab are Unavailable
• Replacing Commas with Periods
• Determining Differences Between Dates
• Converting Numbers to Strings
• Pulling First Letters from Parenthetical Text
• Setting the Zoom Level by User
• Splitting Information into Rows
• Adjusting Values with Formulas
• Creating a Function Inventory for a Workbook

Order Your ExcelTips Archives Today!

Each download item shown below includes the size of the download file. (When you're dealing with hundreds and hundreds of pages per archive volume, files can get large.) If you have a slow connection to the Internet, you can save precious time by ordering your archives on CD-ROM, instead of as a download.

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 to get the latest version.

When you click on a "Buy Now" link above, that product is added to your shopping cart. You can then click the shopping-cart icon, in the lower-right corner, to begin the checkout process.

Important Ordering Notes:

If you live in a European Union (EU) country, please do NOT order the CD-ROM. Your order will go through, but I will need to then cancel the order. The reason is simple: The EU requires the procurement of a special shipping license (for the lack of a better term) that makes it onerous and economically untenable for a small company such as mine to ship physical products into EU countries. Please, order the archive download, instead. There are no restrictions (yet) on downloadable products.

If you live in a country that imposes value-added taxes (VAT) and you order a CD-ROM, you will need to pay the VAT when the order is delivered to you. I do not collect VAT at the time of ordering, nor is the VAT included in your purchase price.