ExcelTips Ribbon 2021 Archive (Table of Contents)

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

ExcelTips Ribbon 2021 Archive (Table of Contents)

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

  • Understanding Relative and Absolute Addressing
  • Creating New Windows
  • A Shortcut for Switching Focus
  • Viewing More than Two Places in a Worksheet
  • Dividing the Screen Unevenly between Two Workbooks
  • Seeing All Open Workbook Names
  • Inadvertently Getting Rid of Frozen Panes
  • Forcing Manual Calculation for a Workbook
  • Picking a Workbook Format
  • Setting the Width for Row Labels
  • F4 No Longer Changes Cell References
  • Grabbing a User's Name from Excel
  • Working with Imperial Linear Distances
  • Selecting a Suggestion with the Keyboard
  • Changing Error Checking Rules
  • Help for Older Excel Versions
  • Making Page Breaks More Visible
  • Stopping Screen Shifting
  • Disappearing Ribbon Buttons
  • Cycling through Colors
  • Disabling a Function Key
  • Fixing a Numeric Keypad Key
  • Adding a Tool to Clear Borders

Editing Tips

  • Controlling Where You Edit Cell Contents
  • Entering Info into Multiple Cells
  • Editing the Same Cell in Multiple Sheets
  • Sequentially Inputting Information
  • Automatically Moving from Cell to Cell when Entering Data
  • Canceling an Edit
  • Converting from Relative to Absolute
  • Fast AutoFill
  • Error Generated when Trying to Copy a Worksheet
  • Viewing the Contents of a Very Wide Cell on Demand
  • Previous Column Values in a Drop-Down List
  • Controlling the Automatic Copying of Formulas
  • Using AutoComplete with Disjointed Lists
  • Dividing Values
  • Easily Dividing Values by 1000
  • Retaining Formatting After a Paste Multiply
  • Adjusting Formulas when Pasting
  • Seeing Formula Cells on Different Worksheets
  • Copying Data without Leaving the Currently Selected Cell
  • Ignoring Paragraph Marks when Pasting
  • Setting a Length Limit on Cells
  • Turning Off Paste Options
  • Copying Cells to Fill a Range
  • Copying a Hyperlink to Lots of Worksheets
  • Limiting Entries to Numeric Values
  • Handling Validation for Proper Latitude
  • Contingent Validation Lists
  • Typing Check Marks into Excel
  • Extracting Numbers within a Range
  • Deleting Blank Columns
  • Formatting Canadian Postal Codes
  • Turning Off Automatic Capitalization
  • Correcting a Capital Mistake
  • Defining Shortcut Keys for Symbols

Find and Replace Tips

  • Limiting Searching to a Column
  • Counting the Results of a Formula Using Find and Replace
  • Deleting All Characters Up Through a Unique Character Sequence
  • Removing Cells Containing Specific Terms
  • Finding Boolean Values
  • Replacing Only Whole Words in Excel
  • Wildcards in 'Replace With' Text

Formatting Tips

  • Setting Vertical Alignment in Cells
  • Changing the Default Vertical Alignment
  • Using Strikethrough Formatting
  • Automatically Changing a Cell's Background Color
  • Automatically Copying Formatting
  • Setting Cell Color Based on Numeric Values
  • Specifying Font Color in a Formula
  • Highlighting Values in a Cell
  • Preventing Changes to Formatting and Page Size
  • Partially Blocking Social Security Numbers
  • Changing the Percent Symbol
  • Changing Cell Patterns
  • Stopping Fractions from Reducing
  • Adjusting to a Maximum Row Height
  • Changing Width and Height to Inches
  • Specifying Superscript Text
  • Drawing Borders
  • Random Width and Height Changes
  • Changing Fonts in Multiple Workbooks
  • Using an Exact Number of Digits
  • Widening a Column to a Particular Cell's Width
  • Double-Clicking to Widen Columns Won't Work
  • Setting Minimums and Maximums when AutoFitting Column Widths
  • Handling Negative Numbers in a Complex Custom Format
  • Notation for Thousands and Millions
  • Altering the Displayed Format of Numbers to the Nearest 100
  • Using a Custom Format to Add Dashes

Conditional Formatting Tips

  • Hiding Columns Based on a Cell Value
  • Conditionally Highlighting Cells Containing Formulas
  • Diagonal Borders in a Conditional Format
  • Protecting Your Conditional Formatting Rules
  • Stopping a Conditional Formatting Rule from Breaking into Smaller Ranges
  • Changing Font Face and Size Conditionally
  • Changing Shading when a Column Value Changes
  • Highlighting After-Hours Times

Online Tips

  • Dealing with Hyperlinks that Won't Work
  • Sending Single Worksheets via E-mail
  • Enabling Macros for an E-mailed Workbook
  • Tying a Hyperlink to a Specific Cell
  • Hyperlinks to Charts
  • Extracting URLs from Hyperlinks
  • Extracting Hyperlink Information
  • Deleting a Hyperlink
  • Getting Rid of All Hyperlinks
  • Excel Freezes when Removing Hyperlink
  • Specifying Your Target Monitor
  • Changing Link References
  • Updating Links in Copied Files
  • Getting Rid of Workbook Links

Worksheet Tips

  • Shortcut to Move between Two Worksheets
  • Copying a Single Cell to Multiple Worksheets
  • Limiting How Many Times a Worksheet Can Be Calculated
  • Testing for an Empty Worksheet
  • Inserting and Deleting Rows in a Protected Worksheet

Worksheet Function Tips

  • Using the IF Worksheet Function
  • Cell and Name References in COUNTIF
  • Using the TRUNC Worksheet Function
  • Using the INT Worksheet Function
  • Strange ATAN Results
  • Rounding Numbers
  • Using the MROUND Worksheet Function
  • Rounding by Powers of 10
  • Using the CONCATENATE Worksheet Function
  • Specifying Proper Case
  • Getting the Name of the Parent Workbook
  • Using a Cell Value as a Worksheet Name in a Formula
  • Specifying a Date Range in a SUMIFS Formula
  • Converting to Other Number Systems

Formula Tips

  • Shortcut for Viewing Formulas
  • How Operators are Evaluated
  • Errors when Subtracting
  • Summing Only Positive Values
  • Summing Cells Using a Particular Background Color
  • Averaging the Last Numbers in a Column
  • Averaging Based on Multiple Criteria
  • Counting Consecutive Negative Numbers
  • Counting Records Matching Multiple Criteria
  • Counting Occurrences of Words
  • Rounding To the Nearest Even Integer
  • Replacing Dashes with Periods
  • Deriving High and Low Non-Zero Values
  • Using a Numeric Portion of a Cell in a Formula
  • Splitting Cells by Case
  • Adjusting Test Scores Proportionately
  • Getting Rid of Alphabetic Characters
  • Ignoring Case in a Comparison
  • Looking Backward through a Data Table
  • Determining If a Value is Out of Limits
  • Figuring Out the Low-Score Winner
  • Returning Least-Significant Digits
  • Finding the Nth Occurrence of a Character
  • Stopping a Formula from Updating References
  • Only Showing the Maximum of Multiple Iterations
  • Filling References to Another Workbook
  • Reversing Integer Values
  • Returning the Rightmost Value in a Row
  • Keeping a Max Value in a Cell

PivotTable Tips

  • Refreshing PivotTable Data
  • Formatting a PivotTable
  • Setting Stable Column Widths in a PivotTable
  • Editing PivotTables without Underlying Data

Sorting and Filtering Tips

  • Sorting by the Last Digits in Variable Length Data
  • Undoing the Effects of a Sort
  • Storing Sorting Criteria
  • Sorting Dates by Month
  • Performing Complex Sorts
  • Incomplete and Corrupt Sorting
  • Filtering to a Date Range in the Past
  • Extracting Targeted Records from a List
  • Finding Rows with Values in Two Columns
  • Enabling Filters by Default

Date and Time Tips

  • How Excel Stores Dates and Times
  • Changing Excel's Starting Date
  • Using Early Dates
  • Separating a Date into Component Columns
  • Can't Enter Years in a Cell
  • Pushing Dates Into Last Month
  • Calculating an Average Time
  • Calculating an Age On a Given Date
  • Counting Dates in a Range
  • Counting Jobs Completed On a Date
  • Finding the Dates for Minimums and Maximums
  • Using a Single Digit for a Year
  • Checking for Time Input
  • Displaying Negative Times
  • Entering Dates without Separators
  • Converting an Unsupported Date Format
  • Checking for Data Entry Errors for Times
  • Rounding Time
  • Combining and Formatting Times
  • Converting Time Notation to Decimal Notation
  • Calculating Time Differences between Two Machines
  • Shortcut to Enter GMT
  • Using Excel for Timing
  • Dealing with Midnight Ending a Day
  • Displaying a Result as Minutes and Seconds

Graphics and Charting Tips

  • Drawing Simple Objects
  • Drawing Lines
  • Quickly Duplicating Drawing Objects
  • Positioning Graphics Evenly
  • Automatically Displaying Thumbnails of a Graphic File
  • Adding a Very Heavy Cell Border
  • Grouping and Ungrouping Objects
  • Adding Text to a Drawing Shape
  • Pulling Text from a Cell and Placing It in a Shape
  • Specifying an Order for Drawing Objects
  • Resize Graphics Outside of Excel
  • Using Text Boxes
  • Changing Text in Text Boxes on a Chart
  • Controlling Chart Gridlines
  • Selecting Fonts for a Chart
  • Smoothing Out Data Series
  • Turning the Legend On and Off
  • Moving a Chart's Legend
  • Creating a Log/Log Chart
  • Exporting Black and White Charts
  • Copying a Chart and Related Shapes to a Word Document

Header and Footer Tips

  • Dynamic Headers and Footers
  • Adding Ampersands in Headers and Footers
  • Using a Custom Date Format in a Header or Footer
  • Putting a Different Date in a Header
  • Last Saved Date in a Footer
  • Adding Last-Row Data to a Page Footer
  • Adding Graphics to a Header or Footer
  • Omitting Page Numbers on Some Pages
  • Roman Numerals for Page Numbers

Printing Tips

  • Resetting Page Setup
  • Clearing the Print Area
  • Adding the Set Print Area Tool
  • Printing Just the Visible Data
  • Changing Cell Values while Printing
  • Creating a Multi-Worksheet Report
  • Repeating Rows on a Printout Except On the Last Page
  • Using Less Paper on Printouts
  • Specifying a Print Tray for a Worksheet
  • Printing Workbooks in a Folder
  • Copying Print Areas when Copying Worksheets
  • Automatic Selection of Portrait or Landscape
  • Hiding Errors on Printouts
  • Repeating Rows at the Bottom of a Page
  • Automatically Printing a Range

Workbook and File Tips

  • Opening a Workbook to a Specific Worksheet
  • Importing Huge Data Files
  • Importing CUSIP Numbers Correctly
  • Grabbing the MRU List
  • Comma-Delimited Differences for PC and Mac
  • File Formats that Include Field Formats
  • Pulling Filenames into a Worksheet
  • Creating a Workbook Clone
  • Strange Message about Others Making Changes in a Workbook

Tools Tips

  • Fixed-Width Settings when Converting Text to Columns
  • Changing the Default Text Import Delimiter
  • Changing Your Regional Settings
  • Changing the Text to Column Settings
  • Writing Your Own Import Routine
  • Using Power Query
  • Is It Worth Converting Data to a Table?
  • Spell-checking Uppercase Words
  • Setting Spell-Checking Options
  • Using Data Validation
  • Setting Data Validation Input Messages
  • Watching Cell Values
  • Importing Custom Lists
  • AutoFilling from a Custom List
  • Pasting Excel Data within Word's Page Margins
  • Using Data Forms

Macro Tips

  • Editing Macros
  • Stepping Through a Macro with a Worksheet Visible
  • Understanding Subroutines
  • Understanding the Select Case Structure
  • Develop Macros in Their Own Workbook
  • Storing a User's Location before Running a Macro
  • Forcing a Macro to Run when a Worksheet is Recalculated
  • Store Common Macros in the Personal Macro Workbook
  • Making Common Functions Available to Others
  • Selecting to the Bottom of a Column in a Macro
  • Assigning Macros to Graphics
  • Making Sure Numbers Copy as Numbers
  • Displaying Worksheets in a Slideshow Fashion
  • Updating a Web Query Based on a Schedule
  • Creating Dependent Cells
  • Trouble Recording Paste Special Formula
  • Selecting a Range of Cells Relative to the Current Cell
  • Negating a Cell Using a Macro
  • DOS from Macros
  • Aborting a Macro and Retaining Control
  • Renaming a File
  • Deleting a File in a Macro
  • Determining the Day of the Month
  • Controlling the Printer in a Macro
  • Displaying the Print Dialog Box in a Macro
  • Copying Worksheets in a Macro
  • Making a Cell's Contents Italics within a Macro
  • Creating Worksheets with a Macro
  • Inserting the Current Time with Seconds
  • Saving a Workbook in a Macro
  • Spreading Out Worksheet Rows
  • Disabled Macros
  • Sheets for Months
  • Determining a Worksheet's Number
  • Limiting Scroll Area
  • Determining the RGB Value of a Color
  • Running a Procedure when a Workbook is Opened
  • Default Worksheet when Opening
  • Updating Automatically when Opening Under Macro Control
  • Determining a Random Value
  • Saving Changes when Closing
  • Hiding Excel in VBA
  • Specifying Location for a Message Box
  • Counting Commas in a Selection
  • Friendly and Informative Error Handling

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.

To celebrate the release of the ExcelTips archive for 2023, for a limited time (through January 17, 2024) you can download your own copy of the archive 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 Ribbon 2019
(for Excel 2007, 2010, 2013, 2016, 2019, and Office 365 users)
5 Jan 19 — 28 Dec 19 312 tips Buy on CD
(444 pages)
$32.99 $23.09
Buy Download
(7.2 MB)
$24.99 $17.49
ExcelTips Ribbon 2020
(for Excel 2007, 2010, 2013, 2016, 2019, and Office 365 users)
4 Jan 20 — 26 Dec 20 312 tips Buy on CD
(403 pages)
$32.99 $23.09
Buy Download
(7.5 MB)
$24.99 $17.49
ExcelTips Ribbon 2021
(for Excel 2007, 2010, 2013, 2016, 2019, and Microsoft 365 users)
2 Jan 21 — 25 Dec 21 312 tips Buy on CD
(376 pages)
$32.99 $23.09
Buy Download
(6.6 MB)
$24.99 $17.49
ExcelTips Ribbon 2022
(for Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365 users)
1 Jan 22 — 31 Dec 22 208 tips Buy on CD
(403 page)
$32.99 $23.09
Buy Download
(6.5 MB)
$24.99 $17.49
ExcelTips Ribbon 2023
(for Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365 users)
7 Jan 23 — 30 Dec 23 314 tips Buy on CD
(423 pages)
$32.99 $23.09
Buy Download
(8.2 MB)
$24.99 $17.49