ExcelTips Ribbon 2022 Archive (Table of Contents)

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

• Displaying Excel's Developer Tab
• Enlarging the Formula Bar
• Arranging Workbook Windows
• Moving from Sheet to Sheet
• Turning Off Worksheet Tabs
• Missing Row Numbers
• Choosing Direction After Enter On a Workbook Basis
• Weird Actions for Arrow Keys and Enter
• Starting in Safe Mode
• Getting Notification a Recalculation is Necessary
• Clearing Large Clipboard Entries
• Nifty Zooming
• Can't Access the Registry
• Where Is that Name?
• Synchronous Scrolling with More than Two Windows
• Saving Excel Configuration Settings
• Displaying Page Breaks
• Ensuring Compatibility with Older Excel Versions
• Status Bar Summing No Longer Available
• Saving Non-Existent Changes
• Forcing Stubborn Recalculation
• Date Last Edited
• Thoughts and Ideas on Significant Digits in Excel
• Jumping to the Last Possible Cell
• Mouse Scroll Wheel Doesn't Work with Some Worksheets
• Excel Self-Tests
• Changing the Outline Color of the Selected Cell
• Working with Record Numbers

Editing Tips

• Entering Formulas in Excel
• Clearing and Deleting Cells
• Selecting Formulas
• Editing Individual Cells
• Undoing an Edit
• Moving and Copying Cells
• Moving Cells Using the Mouse
• Changing Multiple Cells at Once
• Pasting without Updating References
• Turning Off Capital Corrections
• Using Subtotals and Totals
• Automatically Breaking Text
• Finding Wayward Links
• Identifying Duplicates
• Importing a Subset of Records
• Deleting Old Data from a Worksheet
• Understanding AutoComplete
• Setting a Default for Shifting when Inserting
• Turning Off AutoFill for a Workbook
• Viewing Formula Results
• Automatically Numbering Rows
• When Clicking a Cell, Excel Jumps to a Different Cell
• Quickly Entering Dates and Times
• Defeating Date Parsing when Pasting Information
• Limiting the Scope of the Undo Command
• Inserting a Radical Symbol
• Limiting the Number of Characters in a Cell
• Ensuring Rows and Columns are Empty
• Skipping Rows when Filling
• Symbols Convert to Numbers in Excel
• Tab Key Jumps a Screen at a Time
• Combining Columns
• Pasting Multiple Paragraphs Into a Single Cell
• Ensuring Conditional Formatting and Data Validation is Copied
• Changing Months in a Workbook
• Counting Words
• Conditionally Deleting Rows
• Activating the Formula Bar with the Keyboard
• Deleting All Names but a Few
• Picking a Group of Cells

Find and Replace Tips

• Finding All Instances of a Value
• Finding Cells Filled with a Particular Color
• Finding Based on Displayed Results
• Replacing Tildes at the Beginning of a Cell
• Using Find and Replace to Pre-Pend Characters
• Allowing for Prefixes and Suffixes in Find and Replace
• Finding a Worksheet with a Specific Value in a Specific Cell
• Making All Occurrences Bold

Formatting Tips

• Removing All Formatting
• Accurate Font Sizes
• Changing Cell Colors
• Selecting a Paper Size
• Shading Based on Odds and Evens
• Setting Orientation of Cell Values
• Hiding Individual Cells
• Setting Cell Width and Height Using the Keyboard
• Setting Row Height
• Hiding and Unhiding Rows
• Hiding Rows Based on Two Values
• Hiding a Huge Number of Rows
• Widening Multiple Columns Proportionally
• Hiding and Unhiding Columns
• Unhiding a Limited Number of Columns
• Unhiding Columns that are Persistently Hidden
• Changes in Font Size when Copying
• Automatically Formatting for Decimal Places
• Converting Dates to Text
• Changing Number Display Settings for Single Workbooks
• Creating a Center Across Selection Button
• Merge and Center Not Available
• Shrinking Cell Contents
• Decimal Tab Alignment
• Displaying Negative Percentages in Red
• Colors No Longer Work
• Can't Set Custom Format in VBA
• Displaying Latitude and Longitude

Conditional Formatting Tips

• Finding Cells that Use Conditional Formatting
• Turning a Cell Red when a Threshold is Exceeded
• Controlling Data Entry in a Cell
• Highlighting Cells Containing both Letters and Numbers
• Returning a Value Based on Text Color

Online Tips

• Turning Off Hyperlink Activation
• Editing a Hyperlink
• Changing Portions of Many Hyperlinks
• Setting Web Fonts
• Extracting E-mail Addresses from Hyperlinks
• Opening Sites in a Browser

Worksheet Tips

• Quickly Inserting a New Worksheet
• Hiding and Unhiding Worksheets
• Naming Tabs for Weeks
• Alphabetizing Worksheet Tabs
• Enabling Editing Erases Worksheet
• Freezing Top Rows and Bottom Rows
• Protecting a Worksheet's Format
• Tab Key Won't Move from Cell to Cell in Locked Worksheet
• Copying a Worksheet
• Identifying the Last Cell Changed in a Worksheet
• Forcing a Worksheet to be Protected Again
• Creating Worksheets from a List of Names
• Creating a Worksheet Copy by Default
• Protecting Many Worksheets
• Stopping Worksheets from being Reprotected
• Protecting Worksheets from Deletion
• Limiting Input by Time of Day

Worksheet Function Tips

• Finding the Nth Root of a Number
• Using the UNIQUE Function
• Establishing a FLOOR and CEILING
• Random Numbers in a Range
• Finding the Date Associated with a Negative Value
• Making VLOOKUP Trigger a Macro
• Finding Unique Rows Based on Two Columns
• Rounding to the Nearest $50
• Making Your Formulas Check for Errors
• Using the WEEKNUM Function
• Using the REPT Function
• Counting the Number of Blank Cells
• Calculating Combinations
• Using the IRR Function
• Using the EOMONTH Function
• Returning a Worksheet Name
• Converting to Hexadecimal
• Using the COLUMN Function

Formula Tips

• Developing Reciprocal Conversion Formulas
• Address of a Cell in Which a Threshold is Exceeded
• Rounding Up to the Next Half
• Modified Rounding
• Counting Unique Values
• Counting Filtered Rows
• Counting Only Money Winners
• Counting Alphabetic Characters in a String
• Counting Wins and Losses
• Adding a Statement Showing an Automatic Row Count
• Getting a Conditional Count of Cells Containing Values
• Determining a Simple Moving Average
• Calculating an Average within a Date Range
• Calculating the Median Age of a Group of People
• Generating Random Strings
• Generating Random Strings of Characters
• Generating Random Door Access Codes
• Picking Different Random Numbers from a Range
• Reversing Names In Place
• Cell Address of a Maximum Value
• Combinations for Members in Meetings
• Referencing the Last Six Items in a Formula
• Exact Formula Copies
• Determining Winners, by Category
• Copying Named Ranges
• How Many Times Does Each Name Appear in a List?
• Returning the Minimum of Integers of a Range
• Throwing Out the Lowest Score
• Determining a Zodiac Sign from a Birthdate
• Searching for a Value Using a Function
• Non-adjusting References in Formulas
• Extracting First and Last Words
• Patterns of Numbers with a Formula
• Incrementing Numeric Portions of Serial Numbers
• Removing Duplicates at a Reduced Precision Level
• Summing Filled Cells
• Summing When the First Character Matches a Value
• Summing Digits in a Value
• Links between Two Desktop Workbooks
• Using a Formula to Replace Spaces with Dashes
• Deriving Antilogs
• Referring to the Last Cell

PivotTable Tips

• Turning Off Automatic Sorting in PivotTables
• PivotTable Aggregating Incorrect Data
• Displaying a PivotTable's Name in the PivotTable

Sorting and Filtering Tips

• Sorting Text as Numbers
• Non-standard Sorting
• Ignoring Selected Words when Sorting
• Taking Bold Text into Account in a Sort
• Fixing Odd Sorting Behavior
• Entering a Date in a Filter
• Filtering to a Standard Deviation
• Filtering Based on Comparing Two Cells
• Performing Calculations while Filtering
• Copying Comments when Filtering
• Clearing Only Filtering Settings
• Removing Filters and Unhiding Rows and Columns on Multiple Worksheets
• Non-PivotTable Slicers and Timelines

Date and Time Tips

• Calculating Future Workdays
• Calculating a Date Five Days before the First Business Day
• Converting European Dates to US Dates
• Calculating Differences in Months using Non-Standard Date Values
• Automatically Advancing by a Month
• Advancing Dates to a New Year
• Forcing Dates Forward
• Listing Dates at Regular Intervals
• Backwards Date Parsing
• Using a Text Function with a Date/Time Returns an Error
• Converting Numeric Values to Times
• Formatting for Hundredths of Seconds
• Working with Elapsed Time
• Converting UTC Times to Local Times
• Displaying Elapsed Time in Decimal Format

Graphics and Charting Tips

• Changing Chart Location
• Negatives in Pie Charts
• Two-Level Axis Labels
• Changing the Axis and Gridline Default Color
• Adding Data Labels to Your Chart
• X-Axis Dates Lose Formatting
• Using Chart Titles
• Reading Values from Graphs
• Changing a Shape
• Placing Textbox Text Into a Worksheet
• Selecting Drawing Objects
• Having an Image Appear Multiple Times in a Worksheet
• Changing the Size of a Graphic
• Cropping Graphics

Header and Footer Tips

• Putting Cell Contents in Footers
• Using a Formula in a Footer

Printing Tips

• Setting Print Titles
• Setting Page Margins
• Centering Your Worksheet
• Printing a Portion of a Worksheet
• Locking the Print Area
• Printing Columns and Rows
• Printing Multiple Selections
• Printing Multiple Pages On a Piece of Paper
• Printing Two Worksheets on a Single Page
• Printing Multiple Worksheet Ranges
• Printing Row Numbers
• Forcing Worksheets to Print on a New Sheet
• Changing Paper Size for a Complete Workbook
• Printing a Worksheet List
• Printing Comments
• Printing a Week of Planner Sheets
• Printing All or Nothing
• Massive Printouts
• Printing Workbook Properties

Workbook and File Tips

• Using Strong Workbook Protection
• Visually Showing a Protection Status
• Seeing a Worksheet Thumbnail in Windows
• When Excel Starts, Nothing Shows
• Finding the Size of a Workbook
• Cannot Add Worksheets to a Workbook
• Excel Not Responding
• Creating Workbooks for Individual Worksheets
• Creating Default Formatting for Workbooks and Worksheets
• Correctly Saving Delimited Files
• Saving in Two Locations
• Reducing File Size
• Invalid Names when Opening Workbook
• Finding the Parent Folder

Tools Tips

• Adding Buttons to Your Worksheet
• Editing a Scenario
• Understanding Views
• Printing Formatted Comments
• Changing the Comment Font
• Counting Comments in a Worksheet

Macro Tips

• Understanding Variables in VBA Macros
• Declaring Variables
• Understanding Functions in Macros
• Working while a Macro is Running
• Calling a Subroutine from a UDF
• Determining the Length of a String
• Macros Not Available in Later Versions of Excel
• Clearing the Clipboard in a Macro
• Creating and Naming a Worksheet Using a Macro
• Detecting Hidden Rows
• Finding the Last-Used Cell in a Macro
• Expiration Date for Excel Programs
• Finding Positions of Formatted Characters in a Cell
• Macros in Two Workbooks Interfere with Each Other
• Generating Unique Numbers for Worksheets
• Getting Rid of the "Enable Macros" Notice
• Converting Numbers Into Words
• Magnifying Only the Current Cell
• Progression Indicator in a Macro
• Getting a File Name
• Notification when Recalculation is Done
• Deleting Zero Values from a Data Table
• Getting User Input in a Dialog Box
• Calling a Macro from the Workbook_Open Event
• Converting Phone Numbers
• E-mailing PDF Reports Results in Consistent Crash
• Using the Status Bar
• Displaying the First Worksheet in a Macro
• Telling which Worksheets are Selected
• Selecting All Visible Worksheets in a Macro
• Selecting a Specific Cell in a Macro
• Filling a Range of Cells with Values
• Selecting Columns in VBA when Cells are Merged
• Stopping Excel from Deleting Macros from a Workbook
• Generating Unique, Sequential Names
• Stopping a Checked Box from being Unchecked
• Bypassing the BeforeClose Event
• Determining the Number of Visible Columns
• Generating a Keyword Occurrence List
• Copying Data between Worksheets Using a Macro

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