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.

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.

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	(444 pages) $32.99 $23.09	(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	(403 pages) $32.99 $23.09	(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	(376 pages) $32.99 $23.09	(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	(403 page) $32.99 $23.09	(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	(423 pages) $32.99 $23.09	(8.2 MB) $24.99 $17.49