ExcelTips Ribbon 2014 Archive (Table of Contents)

Summary: ExcelTips is a weekly newsletter that provides tips on how to effectively use Microsoft's best-selling spreadsheet program. At the beginning of every year we put together an archive of all the tips published the previous year. Here is the complete table of contents for the ExcelTips (ribbon) newsletter archive for 2014.

ExcelTips Ribbon 2014 Archive (Table of Contents)

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


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

General Tips

  • Determining Your Version of Excel
  • Excel 2007
  • Excel 2010
  • Excel 2013
  • Finding Information in a Macro
  • Viewing Your Work Full-Screen
  • Seeing Excel's Program Window
  • Using a Single Instance of Excel with Two Monitors
  • Viewing More than Two Places in a Worksheet
  • Controlling Display of Page Breaks
  • Turning Off Synchronous Scrolling
  • Synchronous Scrolling with More than Two Windows
  • Floating Information in a Frozen Row
  • Making a Named Range Non-Scrollable
  • Nifty Zooming
  • Controlling the Behavior of the Mouse Wheel
  • Disappearing Status Bar
  • Saving Movement on Enter with a Workbook
  • Changing Excel's Background Color
  • Changing the Color of Worksheet Gridlines
  • Embedding Your Phone Number in a Workbook
  • Maintaining the Active Cell
  • Going to the Corners of a Selected Range
  • Cycling through Colors
  • Disabled Page Setup Tools
  • Understanding Manual Calculation
  • Setting the Calculation Default
  • Spotty Recalculation
  • Selecting a Suggestion with the Keyboard
  • Disabling the F1 Key
  • F4 No Longer Changes Cell References
  • Slash Key No Longer Works as Expected
  • Disabling Shift+Ctrl
  • Countering Compressed Columns
  • Empty Cells Triggers Error
  • Loading Lotus Spreadsheet Files
  • Pasting Excel Data within Word's Page Margins

Editing Tips

  • Starting Out Formulas
  • Displaying Row and Column Labels
  • Quickly Selecting Cells
  • Selecting Noncontiguous Ranges with the Keyboard
  • Controlling Where You Edit Cell Contents
  • Forcing Editing to Be Done in a Cell
  • Switching Editing Location
  • Automatically Moving from Cell to Cell when Entering Data
  • Easily Entering Dispersed Data
  • Sequentially Inputting Information
  • Controlling Entry Order on Unprotected Cells
  • Jumping to the Real Last Cell
  • Inserting and Copying Rows
  • Dragging to Clear Cells
  • Deleting Blank Columns
  • Turning Off AutoFill
  • Quick AutoFill Variations
  • AutoFill with Random Numbers
  • AutoFilling Numbers with a Trailing Period
  • Copying Cells to Fill a Range
  • Relative Worksheet References when Copying
  • Dates Copied Incorrectly
  • Easily Changing Links
  • Displaying a Hidden First Column
  • Zooming In On Your Worksheet
  • Entering Data as Thousands
  • Automatically Capitalizing Day Names
  • Forcing Input to Uppercase
  • Ignoring Paragraph Marks when Pasting
  • Removing Spaces
  • Getting Rid of Non-Printing Characters Intelligently
  • Merging Cells to a Single Sum
  • Segregating Numbers According to Their Sign
  • Getting Rid of All Rows Except the One for the Latest Date
  • Shortening ZIP Codes
  • Displaying Letter Grades
  • Copying Subtotals
  • Moving Subtotals
  • Getting Rid of 8-Bit ASCII Characters

Find and Replace Tips

  • Searching for All
  • Changing Default Search Settings
  • Finding and Replacing in Text Boxes
  • Replacing Characters at the End of a Cell
  • Limitations On Finding Characters

Formatting Tips

  • Adjusting Row Height for Your Text
  • Changing the Color of a Cell Border
  • Repeating Cell Contents
  • Indenting Cell Contents
  • Using Strikethrough Formatting
  • Automatically Copying Formatting
  • Checking All Cell Formatting in VBA
  • Professional Looking Fractions
  • Automatic Lines for Dividing Lists
  • Matching Formatting when Concatenating
  • Select One Cell and Make Another Cell Bold
  • Double-Clicking to Widen Columns Won't Work
  • Replacing Cell Formats
  • Excel Refuses to Put Page Breaks between Subtotal Groups
  • Highlighting Values in a Cell
  • Using an Exact Number of Digits
  • Converting Forced Text to Numbers
  • Defeating Automatic Date Parsing
  • Formatted Dates Appear Differently on Different Systems
  • Hiding Columns Not within a Date Range
  • Adding Ordinal Notation to Dates
  • Removing Dashes from ISBN Numbers
  • Changing Currency Formatting for a Single Workbook
  • Altering the Displayed Format of Numbers to the Nearest 100
  • Flashing Cells
  • Determining "Highest Since" or "Lowest Since"
  • Problems with Custom Views
  • Changing Page Number Format
  • Number Formatting Shortcuts
  • Moving Custom Formats to Number Formatting Categories
  • Understanding Date and Time Formatting Codes

Conditional Formatting Tips

  • Conditionally Highlighting Cells Containing Formulas
  • Conditionally Formatting for Multiple Date Comparisons
  • Noting Inactivity within a Timeframe
  • Changing Font Face and Size Conditionally
  • Diagonal Borders in a Conditional Format

Header and Footer Tips

  • Changing Section Headers
  • Printing a Multi-Line Footer
  • Moving Part of a Footer Down a Line

Online Tips

  • Pasting HTML without Hyperlinks
  • Specifying Default Hyperlink Text
  • Using Drag-and-Drop to Create a Hyperlink
  • Creating a Dynamic Hyperlink
  • Links to Hyperlinks
  • Converting to Hyperlinks in a Shared Workbook
  • Getting Rid of All Hyperlinks
  • Unwanted Hyperlinks
  • Changing Huge Numbers of Hyperlinks
  • Generating Automatic Links to Audio Files
  • References to Hyperlinks aren't Hyperlinks
  • Extracting Hyperlink Information
  • Pulling Apart a URL
  • Sending Single Worksheets via E-mail

Worksheet Tips

  • Colors and Fonts for Worksheet Tabs
  • Using Very Long Worksheet Tab Names
  • Dynamically Changing Worksheet Tab Color
  • Condensing Multiple Worksheets Into One
  • Determining the Complexity of a Worksheet
  • Stopping the Deletion of Cells
  • Using a Protected Worksheet
  • Adding Comments to Protected Worksheets
  • Locking Worksheet Names
  • Locking a Worksheet Automatically
  • Checking Lock Status of Cells

Worksheet Function Tips

  • Using the ABS Function
  • Converting Codes to Characters
  • Cleaning Text
  • Phantom Counts
  • Exact Matches with DSUM
  • The EDATE Function
  • Using the FORECAST Function
  • Using GEOMEAN With a Large List
  • Checking for Text
  • Returning the MODE of a Range
  • Making PROPER Skip Certain Words
  • Finding the Lowest Numbers
  • Counting Displayed Cells
  • Using the XIRR Function
  • Calculating Fractions of Years
  • Using a Week Number as One Criterion in a Formula
  • Counting Records Matching Multiple Criteria

Formula Tips

  • Saving Common Formulas
  • Exact Formula Copies
  • Maintaining Text Formatting in a Lookup
  • Calculating Monthly Interest Charges
  • Calculating Statistical Values on Different-Sized Subsets of Data
  • Calculating the Distance between Points
  • Summing Every Fourth Cell in a Row
  • Counting Asterisks
  • Counting Cells According to Case
  • Counting Cells Containing a Formula
  • Counting Groupings Below a Threshold
  • Counting Employees in Classes
  • Counting Jobs Completed On a Date
  • Rounding to the Nearest Half Dollar
  • Rounding To the Nearest Even Integer
  • Rounding to Two Significant Digits
  • Generating Double-Digit Random Numbers
  • Determining a Name for a Week Number
  • Checking for Proper Entry of Array Formulas
  • Extracting File Names from a Path
  • Numbers in Base 12
  • Concatenating Values from a Variable Number of Cells
  • Checking for Either of Two Text Values
  • Combining Cell Contents
  • Splitting Cells by Case
  • Returning the Smallest Non-Zero Value
  • Finding the Smallest Even Value
  • Finding the Sum of a Sequential Integer Range
  • Reversing Integer Values
  • Developing Reciprocal Conversion Formulas
  • Pulling a Phone Number with a Known First and Last Name
  • Iterating Circular References
  • Adding a Missing Closing Bracket
  • Totaling Across Worksheets
  • Filling References to Another Workbook
  • Using Named Formulas Across Workbooks
  • Relative References within Named Ranges
  • Breaking Up Variable-Length Part Numbers
  • Adding Dashes between Letters
  • Extracting a State and a ZIP Code
  • Averaging without Hidden Cells
  • Deriving a Secant and Cosecant
  • Number of Terms in a Formula

PivotTable Tips

  • Formatting a PivotTable
  • Editing PivotTables without Underlying Data
  • Rows in a PivotTable
  • Excluding Zero Values from a PivotTable

Sorting and Filtering Tips

  • Recognizing a Header Row when Sorting
  • Sorting Letters and Numbers
  • Sorting while Ignoring Leading Characters
  • Sorting Dates by Month
  • Storing Sorting Criteria
  • Moving Cell Borders when Sorting
  • Can't Sort Imported Data
  • Incorrect Links after Sorting Hyperlinks
  • Too Many Formats when Sorting
  • Filtering Columns
  • Enabling Filters by Default
  • Printing Rows Conditionally
  • Extracting Targeted Records from a List
  • Dealing with Text Length Limits and AutoFilter Drop-Down Lists

Comment Tips

  • Pasting a Comment into Your Worksheet
  • Comments Don't Appear when Cell is Pointed To
  • Static Sizes for Comment Boxes
  • Formatting Text in Comment Boxes
  • Placing Formula Results in a Comment
  • Placing a Picture in a Comment
  • Hyperlinks in Comments
  • Adjusting Comment Printouts

Date and Time Tips

  • Inserting Tomorrow's Date
  • Days Left in the Year
  • Checking for Time Input
  • Converting an Unsupported Date Format
  • Parsing Non-Standard Date Formats
  • Converting UNIX Date/Time Stamps
  • Unique Military Date Format
  • Calculating the First Business Day of the Month
  • Calculating Week-Ending Dates
  • Calculating Weekend Dates
  • Incrementing Months in Dates
  • Alerts About Approaching Due Dates
  • Calculating Months of Tenure
  • Finding the Dates for Minimums and Maximums
  • Averaging Values for a Given Month and Year
  • Calculating a Group Retirement Date
  • Calculating Months for Billing Purposes
  • Pushing Dates Into Last Month
  • Copying Dates a Year Into the Future
  • Using Early Dates
  • Entering the Current Time
  • Shortcut to Enter GMT
  • Entering Large Time Values
  • Dealing with Large Numbers of Seconds
  • Dealing with Small Time Values
  • Automatically Entering a Data Entry Time
  • Checking for Data Entry Errors for Times
  • Including Weeks in Elapsed Time
  • Taking the Time into Account in a Formula
  • Adjusting Times for Time Zones
  • Rounding to the Nearest Quarter Hour
  • Calculating Time Differences between Two Machines
  • Calculating TV Time

Graphics Tips

  • Drawing Simple Objects
  • Quickly Duplicating Drawing Objects
  • Flipping a Drawing Object
  • Positioning Graphics Evenly
  • Hiding Graphics
  • Drawing Lines
  • Styles for Lines, Dashes, and Arrows
  • Changing Line Color in a Drawing Object
  • Changing How Arrows Look
  • Grouping and Ungrouping Objects
  • Setting a Transparent Color for an Image
  • Pictures inside Shapes
  • Positioning a Graphic in a Macro
  • Creating an Organization Chart

Charting Tips

  • Changing Chart Types
  • Deleting a Chart
  • Adjusting Your View of 3-D Graphs
  • Controlling Chart Gridlines
  • Modifying Axis Scale Labels
  • Adjusting the Order of Items in a Chart Legend
  • Moving a Chart's Legend
  • Turning the Legend On and Off
  • Smoothing Out Data Series
  • Plotting Times of Day
  • Unwanted Weekend Dates in Chart
  • Identifying Scatter Plot Points
  • Automatically Creating Charts for Individual Rows in a Data Table
  • Dynamic Data Based on Chart Changes
  • Converting Charts to GIF Files
  • Copying a Chart and Related Shapes to a Word Document
  • Excel Charts in PowerPoint

Printing Tips

  • Defining a Custom Paper Size
  • Adding Page Borders to a Printout
  • Specifying an Order for Page Printing
  • Specifying a Print Tray for a Worksheet
  • Specifying Print Quantity in a Cell
  • Printing an Entire Workbook by Default
  • Printing Only Non-Blank Worksheets
  • Setting Print Ranges for Multiple Worksheets
  • Clearing the Print Area
  • Printing a Number of Different Pages
  • Printing Limited Pages from a Range of Worksheets
  • Roman Numerals for Page Numbers
  • Creating a Multi-Worksheet Report
  • Top Margin Ignored when Printing
  • Printing Columns and Rows
  • Repeating Rows at the Bottom of a Page
  • Printing Workbook Properties
  • Using Less Paper on Printouts
  • Automatic Selection of Portrait or Landscape
  • Conditional Printing
  • Printing without Opening

Workbook and File Tips

  • Remembering Commonly Used Workbooks
  • Tying Workbooks Together
  • Making Changes in a Group of Workbooks
  • Using a Single Password for Multiple Workbooks
  • Always Opening a Workbook that is Editable
  • Opening a Workbook with Two Windows
  • Disabling Shift Key Use when Opening a Workbook
  • Protecting a Workbook from Opening in Other Programs
  • Seeing a Worksheet Thumbnail in Windows
  • Closing a Read-Only Workbook
  • Setting a Default File Format
  • Error Opening Second Workbook
  • MRU Files Won't Display
  • Saving All Open Workbooks
  • Setting the AutoRecover Directory
  • Creating a Dated Backup File
  • Determining If a File Exists
  • Who Has the File Open?
  • Comma-Delimited Differences for PC and Mac
  • Comma-Delimited and MS-DOS CSV Variations
  • Aligning Cells when Importing from CSV

Tools Tips

  • Evaluating Formulas
  • Understanding Auditing
  • Leaving Trace Precedents Turned On
  • Turning Off Sharing
  • Adjusting Spell Check for Internet Addresses
  • Editing the Custom Spelling Dictionaries
  • Embedding an Excel Chart in a Word Document
  • Editing a Scenario
  • Showing a Scenario
  • Creating Scenario Summaries
  • Using Check Boxes
  • Linked Combo Boxes

Macro Tips

  • Outstanding Macro Function Reference
  • Assigning Macros to Graphics
  • Removing a Macro from a Shortcut Key
  • Storing a User's Location before Running a Macro
  • Creating Worksheets with a Macro
  • Worksheet Events
  • Workbook Events
  • Running a Macro when a Workbook is Opened
  • Running a Macro when a Worksheet is Activated
  • Running Macros in the Background
  • Macro, while Running, Stops Excel from Responding
  • Store Common Macros in the Personal Macro Workbook
  • Turning Off Screen Updating
  • Adjusting Values with Formulas
  • Converting Numbers to Strings
  • Swapping Two Strings
  • Inserting the Current Time with Seconds
  • Displaying the Print Dialog Box in a Macro
  • Determining Differences Between Dates
  • Expiration Date for Excel Programs
  • Noting the Workbook Creation Date
  • Easily Adding Blank Rows
  • Spreading Out a Table
  • Setting Column Width in a Macro
  • Positioning a Column on the Screen
  • Unhiding Multiple Worksheets
  • Unprotecting Groups of Worksheets
  • Making a Cell's Contents Bold within a Macro
  • Making a Cell's Contents Italics within a Macro
  • Deriving an Absolute Value in a Macro
  • Engineering Calculations
  • Copying Worksheet Code Automatically
  • Changing Macro Cell References Based on Edits
  • Quickly Dumping Array Contents
  • Conditionally Displaying a Message Box
  • Using a Macro to Select a Modified Table Body
  • Requiring Input
  • Determining an ANSI Value in a Macro
  • Using SUM In a Macro
  • Controlling the Printer in a Macro
  • Resizing Checkboxes
  • Specifying a Delimiter when Saving a CSV File in a Macro
  • Creating a Plus/Minus Button
  • Counting All Characters
  • Updating Automatically when Opening Under Macro Control
  • Finding Other Instances of Excel in a Macro
  • Friendly and Informative Error Handling
  • Determining the Day of the Month
  • Adding Leading Zeroes to ZIP Codes
  • Removing Pictures for a Worksheet in VBA
  • Jumping to the Start of the Next Data Entry Row
  • Finding the Number of Significant Digits
  • Showing RGB Colors in a Cell
  • Determining the RGB Value of a Color
  • Tracking Down Invalid References
  • Removing a Directory
  • Determining an Integer Value
  • Determining a Random Value
  • Maximum Length Limit for a Macro
  • Changing the Default Drive
  • Easily Changing the Default Drive and Directory
  • Determining the Current Directory
  • Renaming a File
  • Deleting a File in a Macro
  • Using Seek In a Macro
  • Adjusting a Path Based on System and User
  • Adding a Calendar to a Worksheet
  • Hiding Excel in VBA
  • Creating Add-Ins
  • Using Custom Add-Ins

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.

Get Adobe Acrobat ReaderThe 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.

To celebrate the release of the ExcelTips archives for 2018, for a limited time (through January 16, 2019) you can download your own copy of the archives for any of the following years at 30% off the regular price. Get your discount; place your order today!

Archive Year Dates Covered Tips CD Download
ExcelTips Menu 2014
(for Excel 97, 2000, 2002, and 2003 users)
4 Jan 14 — 27 Dec 14 312 tips Buy on CD
(357 pages)
$32.99 $23.09
Buy Download
(4.4 MB)
$24.99 $17.49
ExcelTips Ribbon 2014
(for Excel 2007, 2010, and 2013 users)
4 Jan 14 — 27 Dec 14 415 tips Buy on CD
(524 pages)
$32.99 $23.09
Buy Download
(8.2 MB)
$24.99 $17.49
ExcelTips Menu 2015
(for Excel 97, 2000, 2002, and 2003 users)
3 Jan 15 — 26 Dec 15 208 tips Buy on CD
(205 pages)
$32.99 $23.09
Buy Download
(3.4 MB)
$24.99 $17.49
ExcelTips Ribbon 2015
(for Excel 2007, 2010, 2013, and 2016 users)
3 Jan 15 — 26 Dec 15 416 tips Buy on CD
(499 pages)
$32.99 $23.09
Buy Download
(6.1 MB)
$24.99 $17.49
ExcelTips Menu 2016
(for Excel 97, 2000, 2002, and 2003 users)
1 Jan 11 — 31 Dec 11 211 tips Buy on CD
(236 pages)
$32.99 $23.09
Buy Download
(3.6 MB)
$24.99 $17.49
ExcelTips Menu 2017
(for Excel 97, 2000, 2002, and 2003 users)
1 Jan 17 — 30 Dec 17 208 tips Buy on CD
(241 pages)
$32.99 $23.09
Buy Download
(3.1 MB)
$24.99 $17.49
ExcelTips Ribbon 2016
(for Excel 2007, 2010, 2013, and 2016 users)
1 Jan 11 — 31 Dec 11 212 tips Buy on CD
(259 pages)
$32.99 $23.09
Buy Download
(3.2 MB)
$24.99 $17.49
ExcelTips Ribbon 2017
(for Excel 2007, 2010, 2013, and 2016 users)
1 Jan 17 — 30 Dec 17 208 tips Buy on CD
(276 page)
$32.99 $23.09
Buy Download
(3.8 MB)
$24.99 $17.49
ExcelTips Menu 2018
(for Excel 97, 2000, 2002, and 2003 users)
6 Jan 18 — 29 Dec 18 208 tips Buy on CD
(250 pages)
$32.99 $23.09
Buy Download
(3.9 MB)
$24.99 $17.49
ExcelTips Ribbon 2018
(for Excel 2007, 2010, 2013, 2016, 2019, and Office 365 users)
6 Jan 18 — 29 Dec 18 249 tips Buy on CD
(321 pages)
$32.99 $23.09
Buy Download
(4.0 MB)
$24.99 $17.49

You can also order any of the ExcelTips archives by calling our order line: 307-200-0450.