ExcelTips Ribbon 2011 Archive (Table of Contents)

Summary: ExcelTips is a weekly newsletter that provides tips on how to best use Microsoft's word processing software. 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 archive for the ExcelTips (ribbon) newsletter for the year 2011.

ExcelTips Ribbon 2011 Archive (Table of Contents)

ExcelTips Ribbon 2011 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

  • History of Excel
  • Determining Your Serial Number
  • Viewing Your Work Full-Screen
  • Jumping to the Real Last Cell
  • Changing Excel's Background Color
  • Speeding Up Large Worksheets
  • Grabbing a User's Name from Excel
  • Embedding Your Phone Number in a Workbook
  • Selecting Noncontiguous Ranges with the Keyboard
  • Going to the Corners of a Selected Range
  • Maintaining the Active Cell
  • A Shortcut for Switching Focus
  • Weird Mouse Shortcut
  • Selecting a Suggestion with the Keyboard
  • Understanding Manual Calculation
  • Working with Imperial Linear Distances
  • Viewing More than Two Places in a Worksheet
  • Getting Rid of Workbook Links
  • Floating Information in a Frozen Row
  • Nifty Zooming
  • Finding the Analysis ToolPak Add-In
  • Turning Off Error Checking
  • Changing Error Checking Rules
  • Clearing Large Clipboard Entries
  • Countering Compressed Columns
  • Using a Single Instance of Excel with Two Monitors
  • Saving All Open Workbooks
  • Making AutoComplete Work for an Entire Column
  • Turning Off ScreenTips
  • Adding Pop-Up Documentation to a Cell
  • Single-Use Drop-Down List
  • Cycling through Colors
  • Disabling a Function Key
  • Turning Off AutoFill
  • Increasing the Capacity of AutoCorrect
  • Synchronous Scrolling with More than Two Windows

Editing Tips

  • Entering Numbers in Excel
  • Starting Out Formulas
  • Converting from Relative to Absolute
  • Quickly Selecting Cells
  • Switching Editing Location
  • Canceling an Edit
  • Editing the Same Cell in Multiple Sheets
  • Inserting and Copying Rows
  • Deleting Blank Columns
  • Deleting Every X Rows
  • Deleting Everything Except Formulas
  • Highlighting the Rows of Selected Cells
  • Relative Worksheet References when Copying
  • Displaying Row and Column Labels
  • Can't Empty the Clipboard
  • Character Limits for Cells
  • Forcing Input to Uppercase
  • Quickly Updating Values
  • Setting a Length Limit on Cells
  • Ensuring Unique Values in a Column
  • Inserting Dashes between Letters and Numbers
  • Fast AutoFill
  • Copying Cells to Fill a Range
  • Dragging to Clear Cells
  • Displaying a Hidden First Row
  • Increasing Undo Levels
  • Creating New Windows
  • Working in Feet and Inches
  • Turning Off Automatic Capitalization
  • Correcting a Capital Mistake
  • Copying Subtotals
  • Dividing Values
  • Retaining Formatting After a Paste Multiply
  • Removing Spaces
  • Shortening ZIP Codes
  • Deleting Old Data from a Worksheet
  • Getting Rid of Non-Printing Characters Intelligently
  • Getting Rid of 8-Bit ASCII Characters
  • Finding Unused Names
  • Automatically Protecting After Input
  • Controlling Entry Order on Unprotected Cells
  • Inserting and Deleting Rows in a Protected Worksheet
  • Referencing the Last Cell in a Column
  • Unwanted Data Changes
  • Formatting Canadian Postal Codes
  • Finding Wayward Links
  • Easily Changing Links
  • Updating Links in Copied Files

Find and Replace Tips

  • Replacing Characters at the End of a Cell
  • Limiting Searching to a Column
  • Searching a Workbook by Default
  • Wildcards in 'Replace With' Text

Formatting Tips

  • Indenting Cell Contents
  • Replacing Cell Formats
  • Getting Rid of Negative Zero Amounts
  • Removing Dashes from ISBN Numbers
  • Partially Blocking Social Security Numbers
  • Specifying Superscript Text
  • Using Strikethrough Formatting
  • Number Formatting Shortcuts
  • Adding Diagonal Borders
  • Drawing Borders
  • Changing Fonts in Multiple Workbooks
  • Double-Clicking to Widen Columns Won't Work
  • Adjusting Row Height when Wrapping Text
  • Automatic Row Height for Wrapped Text
  • Automatic Row Height For Merged Cells with Text Wrap
  • Preventing Changes to Formatting and Page Size
  • Saving Custom Formats
  • Converting Forced Text to Numbers
  • Using an Exact Number of Digits
  • Getting Rid of Leading Zeros in a Number Format
  • Handling Negative Numbers in a Complex Custom Format
  • Altering the Displayed Format of Numbers to the Nearest 100
  • Notation for Thousands and Millions
  • Checking Lock Status of Cells
  • Problems with Custom Views
  • Referencing External Cell Colors
  • Handling Leading Zeros in CSV Files

Conditional Formatting Tips

  • Highlighting Cells Containing Specific Text
  • Hiding Columns Based on a Cell Value
  • Conditionally Highlighting Cells Containing Formulas
  • Changing Shading when a Column Value Changes
  • Diagonal Borders in a Conditional Format
  • Protecting Conditional Formatting

Header and Footer Tips

  • Putting Headers and Footers on Multiple Worksheets
  • Changing Section Headers
  • Putting a Different Date in a Header
  • Dynamic Headers and Footers
  • Moving Part of a Footer Down a Line
  • Printing a Multi-Line Footer
  • Last Saved Date in a Footer

Online Tips

  • Using Drag-and-Drop to Create a Hyperlink
  • Dynamic Hyperlinks in Excel
  • Copying a Hyperlink to Lots of Worksheets
  • Tying a Hyperlink to a Specific Cell
  • Changing Portions of Many Hyperlinks
  • Extracting URLs from Hyperlinks
  • Extracting Hyperlink Information
  • Links to Hyperlinks
  • Deleting a Hyperlink
  • Removing Hyperlinks without a Macro
  • Removing Hyperlinks without a Macro
  • Getting Rid of Many Hyperlinks
  • Getting Rid of All Hyperlinks
  • Specifying a Browser in a Hyperlink
  • Hyperlinks to Charts
  • Specifying Your Target Monitor
  • Setting Web Fonts
  • Generating a Web Page
  • Adding Excel Information to a Web Page
  • ScreenTip for an Image
  • Converting to Hyperlinks in a Shared Workbook
  • Retrieving Web Query Data without Interruption

Worksheet Tips

  • Freezing Top Rows and Bottom Rows
  • Stopping the Deletion of Cells
  • Quickly Copying Worksheets
  • Changing the Color of Worksheet Gridlines
  • Viewing Two Worksheets at Once
  • Locking Worksheet Names
  • Counting the Times a Worksheet is Used
  • Referencing Worksheet Tabs
  • Multiple Rows of Worksheet Tabs
  • Shifting Objects Off a Sheet
  • Finding the Size of Individual Worksheets
  • Using a Protected Worksheet

Worksheet Function Tips

  • Returning the MODE of a Range
  • Using the CONCATENATE Worksheet Function
  • Determining Columns in a Range
  • Cleaning Text
  • Making PROPER Skip Certain Words
  • Exact Matches with DSUM
  • Returning a Weight and a Date
  • Counting Displayed Cells
  • Getting the Name of the Parent Workbook
  • Using the IF Worksheet Function
  • Colors in an IF Function
  • Checking for Text
  • Finding the Lowest Numbers
  • Replacing Dashes with Periods
  • Determining a Name for a Week Number
  • Rounding to Powers of 10
  • Using the MROUND Worksheet Function
  • Converting Codes to Characters
  • Returning Blanks or Asterisks from a Lookup

Formula Tips

  • Shortcut for Viewing Formulas
  • Evaluating Formulas
  • Summing Only Positive Values
  • Finding the Sum of a Sequential Integer Range
  • Counting Unique Values
  • Counting Cells with Specific Characters
  • Counting Cells According to Case
  • Counting Consecutive Negative Numbers
  • Counting Employees in Classes
  • Counting Jobs Completed On a Date
  • Counting Non-Blank Cells
  • Averaging the Last Numbers in a Column
  • Calculating Averages by Date
  • Excluding Values from Averaging
  • Averaging Values for a Given Month and Year
  • Calculating the Median Age of a Group of People
  • Using a Numeric Portion of a Cell in a Formula
  • Iterating Circular References
  • Deriving High and Low Non-Zero Values
  • Where Is that Text?
  • Deriving a Secant and Cosecant
  • Determining "Highest Since" or "Lowest Since"
  • Ignoring N/A Values in a Sum
  • Calculating the Interval Between Occurrences
  • Calculating Monthly Interest Charges
  • Breaking Up Variable-Length Part Numbers
  • Rounding to Two Significant Digits
  • Rounding To the Nearest Even Integer
  • Pulling a Phone Number with a Known First and Last Name
  • Filling References to Another Workbook
  • Finding the Smallest Even Value
  • Adding a Missing Closing Bracket
  • Combinations for Members in Meetings
  • Checking for Proper Entry of Array Formulas
  • Adjusting Test Scores Proportionately
  • Finding the Nth Occurrence of a Character
  • Errors when Subtracting
  • Finding the First Non-Digit in a Text Value
  • Generating Double-Digit Random Numbers
  • Figuring Out the Low-Score Winner
  • Referencing the Last Six Items in a Formula
  • Returning Least-Significant Digits
  • Exact Formula Copies
  • Stopping a Formula from Updating References
  • Unbreakable Formula References to Worksheets
  • Pulling Formulas from a Worksheet
  • Only Showing the Maximum of Multiple Iterations
  • Determining If a Value is Out of Limits
  • Ignoring Case in a Comparison

PivotTable Tips

  • Using a PivotTable to Count Items
  • Editing PivotTables without Underlying Data
  • Formatting a PivotTable
  • Refreshing PivotTable Data
  • Setting Stable Column Widths in a PivotTable
  • Error in Linked PivotTable Value
  • Suppressing Zero Values in PivotTables

Sorting and Filtering Tips

  • Sorting a Range of Cells
  • Sorting Data Containing Merged Cells
  • Fixing Odd Sorting Behavior
  • Incomplete and Corrupt Sorting
  • Too Many Formats when Sorting
  • Sorting Dates by Month
  • Sorting while Ignoring Leading Characters
  • Sorting with Graphics
  • Sorting Data on Protected Worksheets
  • Filtering to a Date Range in the Past
  • Copying Comments when Filtering
  • Finding Rows with Values in Two Columns
  • Checking for Duplicate Rows Based on a Range of Columns

Comment Tips

  • Pasting a Comment into Your Worksheet
  • Finding and Replacing Text in Comments
  • Moving Comment Background Pictures to Cells

Date and Time Tips

  • Limiting Entry of Prior Dates
  • Entering Large Time Values
  • Using Early Dates
  • Days Left in the Year
  • Calculating a Group Retirement Date
  • Dates with Periods
  • Adding Ordinal Notation to Dates
  • Calculating Week-Ending Dates
  • Calculating the First Business Day of the Month
  • The Last Business Day
  • Monthly Close-Out Dates
  • Determining Business Quarters from Dates
  • Parsing Non-Standard Date Formats
  • Converting an Unsupported Date Format
  • Converting European Dates to US Dates
  • Converting UNIX Date/Time Stamps
  • Inserting the Current Time with Seconds
  • Displaying Negative Times
  • Converting Time Notation to Decimal Notation
  • Converting UTC Times to Local Times
  • Is Daylight Savings Time in Effect?

Graphics Tips

  • Drawing Lines
  • Drawing Simple Objects
  • Duplicating Drawing Objects
  • Quickly Duplicating Drawing Objects
  • Flipping a Drawing Object
  • Nudging a Graphic
  • Specifying an Order for Drawing Objects
  • Grouping and Ungrouping Objects
  • Exporting a Graphics Group
  • Positioning Graphics Evenly
  • Positioning a Graphic in a Macro
  • Pulling Text from a Cell and Placing It in a Shape
  • Pictures inside Shapes
  • Hiding Graphics
  • Removing Pictures for a Worksheet in VBA
  • Using Text Boxes
  • Resizing a Text Box in a Macro
  • Displaying Images based on a Result
  • Using the Camera in VBA
  • Creating Venn Diagrams with Excel Data
  • Resize Graphics Outside of Excel

Charting Tips

  • Changing Chart Types
  • Creating a Log/Log Chart
  • Creating Charts in VBA
  • Selecting Fonts for a Chart
  • Controlling Chart Gridlines
  • Moving a Chart's Legend
  • Turning the Legend On and Off
  • Formatting Axis Patterns
  • Changing Axis Tick Marks
  • Two-Level Axis Labels
  • Adjusting Your View of 3-D Graphs
  • Reordering the Display of a Data Series
  • Using Graphics to Represent Data Series
  • Positive and Negative Colors in a Chart
  • Unselecting a Chart Item
  • Locking Callouts to a Graph Location
  • Negatives in Pie Charts
  • Exporting Black and White Charts
  • Identifying Scatter Plot Points
  • Reading Values from Graphs
  • Graphically Charting Geographic Data
  • Embedding an Excel Chart in a Word Document

Printing Tips

  • Selecting a Paper Source
  • Adding the Set Print Area Tool
  • Clearing the Print Area
  • Automatically Printing a Range
  • Printing Just the Visible Data
  • Printing a Number of Different Pages
  • Specifying an Order for Page Printing
  • Printing an Entire Workbook by Default
  • Printing Only Non-Blank Worksheets
  • Printing a Draft Watermark
  • Printing without Opening
  • Printing Workbooks in a Folder
  • Printing Multiple Pages On a Piece of Paper
  • Printing a Chart Across Multiple Pages
  • Out of Kilter Borders
  • Repeating Rows at the Bottom of a Page
  • Repeating Rows on a Printout Except On the Last Page
  • Automatic Selection of Portrait or Landscape
  • Hiding Errors on Printouts

Workbook and File Tips

  • Setting a Default File Format
  • Error Opening Second Workbook
  • Closing a Read-Only Workbook
  • Accessing a Problem Shared Workbook
  • Forcing a Workbook to Close after Inactivity
  • Grabbing the MRU List
  • Saving a Workbook Using Passwords
  • Sorting Files
  • Disabling Shift Key Use when Opening a Workbook
  • Importing Multiple Files to a Single Workbook
  • Opening Personal.xlsb
  • Changing Link References
  • Correctly Saving Delimited Files
  • Setting the AutoRecover Directory
  • Saving a Workbook with a Preview
  • Pulling Filenames into a Worksheet
  • Comma-Delimited Differences for PC and Mac
  • Getting Input from a Text File

Tools Tips

  • Using Data Forms
  • Creating Dependent Drop-Lists
  • Fixed-Width Settings when Converting Text to Columns
  • Understanding Outlining
  • Adjusting Spell Check for Internet Addresses
  • Setting Spell-Checking Options
  • Allowing for Words that Contain Numbers
  • Spell-checking Uppercase Words
  • Spell-Checking in a Protected Worksheet
  • Editing the Custom Spelling Dictionaries
  • Backing Up Custom Dictionaries
  • Adding Buttons to Your Worksheet
  • Restoring the Analysis ToolPak
  • Getting Contact Information from Outlook

Macro Tips

  • Editing Macros
  • Develop Macros in Their Own Workbook
  • Moving Macros from the Personal Workbook
  • Hiding Macros
  • Making Common Functions Available to Others
  • Trouble Recording Paste Special Formula
  • Maximum Length Limit for a Macro
  • Opening a Workbook but Disabling Macros
  • Getting Rid of the "Enable Macros" Notice
  • Stepping Through a Macro with a Worksheet Visible
  • Working while a Macro is Running
  • Removing All Macros
  • Clearing the Undo Stack in a Macro
  • Converting Numbers to Strings
  • DOS from Macros
  • Determining a Random Value
  • Determining an Integer Value
  • Pulling Apart Cells
  • Removing a Directory
  • Deleting a File in a Macro
  • Spreading Out a Table
  • Specifying Location for a Message Box
  • Determining the RGB Value of a Color
  • Swapping Two Strings
  • Expiration Date for Excel Programs
  • Noting the Workbook Creation Date
  • Displaying the Print Dialog Box in a Macro
  • Deriving an Absolute Value in a Macro
  • Magnifying Only the Current Cell
  • Renaming a File
  • Detecting Types of Sheets in VBA
  • Digital Signatures for Macros
  • Aborting a Macro and Retaining Control
  • Running a Macro when a Worksheet is Deactivated
  • Running a Macro when a Workbook is Closed
  • Macro Runs Slowly, but Steps Quickly
  • Positioning a Column on the Screen
  • Forcing a Macro to Run when a Worksheet is Recalculated
  • Determining How Many Windows are Open
  • Saving Changes when Closing
  • Searching for Leading Apostrophes
  • Saving a Workbook in a Macro
  • Limiting Scroll Area
  • Creating Worksheets with a Macro
  • Jumping to the Start of the Next Data Entry Row
  • Counting Precedents and Dependents
  • Storing a User's Location before Running a Macro
  • Setting Program Window Size in a Macro
  • Hiding Excel in VBA
  • Finding the Number of Significant Digits
  • Counting Commas in a Selection
  • Macro Fails after Filter
  • Determining a Worksheet's Number
  • Finding Cells Filled with a Particular Color
  • Default Worksheet when Opening
  • Sheets for Months

Order Your ExcelTips Archives Today!

Each download item shown below 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, 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.

Archive Year Dates Covered Tips CD Download
ExcelTips Menu 2011
(for Excel 97, 2000, 2002, and 2003 users)
1 Jan 11 — 31 Dec 11 424 tips Buy on CD
(487 pages)
$32.99
Buy Download
(6.2 MB)
$24.99
ExcelTips Ribbon 2011
(for Excel 2007 and 2010 users)
1 Jan 11 — 31 Dec 11 424 tips Buy on CD
(492 pages)
$32.99
Buy Download
(8.5 MB)
$24.99
ExcelTips Menu 2012
(for Excel 97, 2000, 2002, and 2003 users)
7 Jan 12 — 29 Dec 12 416 tips Buy on CD
(455 pages)
$32.99
Buy Download
(5.4 MB)
$24.99
ExcelTips Ribbon 2012
(for Excel 2007 and 2010 users)
7 Jan 12 — 29 Dec 12 415 tips Buy on CD
(482 page)
$32.99
Buy Download
(8.9 MB)
$24.99
ExcelTips Menu 2013
(for Excel 97, 2000, 2002, and 2003 users)
5 Jan 13 — 28 Dec 13 415 tips Buy on CD
(474 pages)
$32.99
Buy Download
(5.5 MB)
$24.99
ExcelTips Ribbon 2013
(for Excel 2007, 2010, and 2013 users)
5 Jan 13 — 28 Dec 13 416 tips Buy on CD
(490 pages)
$32.99
Buy Download
(6.1 MB)
$24.99
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
Buy Download
(4.4 MB)
$24.99
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
Buy Download
(8.2 MB)
$24.99
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
Buy Download
(3.4 MB)
$24.99
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
Buy Download
(6.1 MB)
$24.99

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

Links and Sharing
  • Ask a Question
  • Make a Comment
  • Free Calendars
  • Share