ExcelTips Ribbon 2020 Archive (Table of Contents)

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

• Determining Your Version of Excel
• Excel 2007
• Excel 2010
• Excel 2013, Excel 2016, Excel 2019, and Excel in Office 365
• Finding Information in a Macro
• Selecting Noncontiguous Ranges with the Keyboard
• Setting the Calculation Default
• Understanding Manual Calculation
• Spotty Recalculation
• Finding the Number of Significant Digits
• Permanently Turning On Set Precision As Displayed
• Disabled Page Setup Tools
• Turning Headers On and Off
• Getting Rid of Numbered Columns
• Making AutoComplete Work for an Entire Column
• Turning Off Error Checking
• MRU Files Won't Display
• Stopping an Excel Window from Maximizing
• Changing the Ribbon's Size and Look
• Disappearing Status Bar
• Adjusting Status Bar Font Size
• Turning Off Synchronous Scrolling
• Disabling Shift+Ctrl
• Locking the Zoom Factor
• Disabling the F1 Key
• Making a Named Range Non-Scrollable
• Slash Key No Longer Works as Expected
• Controlling the Behavior of the Mouse Wheel
• Weird Mouse Shortcut

Editing Tips

• Quickly Selecting Cells
• Entering Numbers in Excel
• Entering Data as Thousands
• Entering Characters with Diacritical Marks
• Easily Entering Dispersed Data
• Controlling Entry Order on Unprotected Cells
• Turning Off Insert Options
• Moving and Selecting Rows
• Quickly Updating Values
• Dates Copied Incorrectly
• Disabling Dragging and Dropping
• Dragging to Clear Cells
• Removing Spaces
• Shortening ZIP Codes
• Ensuring Unique Values in a Column
• Converting Text to Values
• Unwanted Data Changes
• Deleting Everything Except Formulas
• Deleting Every X Rows without a Macro
• Deleting All Rows Except for the End of Month
• Ignoring Special Characters when Double-Clicking
• Three-Dimensional Transpositions
• Checking for an Entry in a Cell
• Checking for a Value in a Cell
• Moving Subtotals
• Deleting Stubborn Links
• Quick AutoFill Variations
• Turning Off AutoFill
• AutoFill with Random Numbers
• AutoFilling Numbers with a Trailing Period
• Copying Rows between Worksheets Based on a Text Value
• Merging Cells to a Single Sum
• Getting Rid of Non-Printing Characters Intelligently
• Stopping Feet and Inches from Converting to Dates
• Getting Help when Entering Functions
• Displaying an Input Format in a Cell
• Ranges on Multiple Worksheets

Formatting Tips

• Number Formatting Shortcuts
• Indenting Cell Contents
• Adjusting Row Height for Your Text
• Automatic Row Height for Wrapped Text
• Automatic Row Height for Merged Cells with Text Wrap
• Repeating Cell Contents
• Filling a Cell
• Changing Page Number Format
• Flashing Cells
• Determining If a Cell is Bold
• Removing Dashes from ISBN Numbers
• Select One Cell and Make Another Cell Bold
• Handling Leading Zeros in CSV Files
• Professional Looking Fractions
• Changing Currency Formatting for a Single Workbook
• Adding Diagonal Borders
• Replacing Cell Formats
• Displaying Numbers as Spaced Pairs
• Dates with Periods
• Saving Custom Formats
• Monitoring the Number of Formats Defined
• Creating Long Page Footers
• Putting Headers and Footers on Multiple Worksheets

Conditional Formatting Tips

• Converting Conditional Formatting to Regular Formatting
• Leaving a Cell Value Unchanged If a Condition Is False
• Conditionally Formatting for a Pattern

Online Tips

• Pasting HTML without Hyperlinks
• Creating a Dynamic Hyperlink
• Changing Huge Numbers of Hyperlinks
• Generating Automatic Links to Audio Files

Worksheet Tips

• Speeding Up Large Worksheets
• Determining the Complexity of a Worksheet
• Quickly Copying Worksheets
• Stopping a Worksheet from being Moved or Copied
• Viewing Two Worksheets at Once
• Colors and Fonts for Worksheet Tabs
• Using a Protected Worksheet
• Getting Rid of the Bothersome Lock Symbol
• Finding the Widest Cell Contents
• Condensing Multiple Worksheets Into One
• Counting the Times a Worksheet is Used
• Disabling Moving Between Worksheets

Worksheet Function Tips

• Outstanding Function Reference
• Checking for Text
• Making PROPER Skip Certain Words
• Using the FORECAST Function
• Converting Codes to Characters
• Using GEOMEAN With a Large List
• The EDATE Function
• Using the ABS Function
• Disabling #SPILL! Errors
• Determining Columns in a Range
• Returning the MODE of a Range
• Returning a Weight and a Date
• Returning Blanks or Asterisks from a Lookup

Formula Tips

• Retrieving the Last Value in a Column
• Counting Cells Containing a Formula
• Counting Cells with Specific Characters
• Counting Non-Blank Cells
• Counting Employees in Classes
• Summing Every Fourth Cell in a Row
• Ignoring N/A Values in a Sum
• Excluding Values from Averaging
• Averaging without Hidden Cells
• Averaging Values for a Given Month and Year
• Calculating Statistical Values on Different-Sized Subsets of Data
• Where Is that Text?
• Calculating Monthly Interest Charges
• Outlining Cells Referenced in a Formula
• Alerts About Approaching Due Dates
• Checking for Either of Two Text Values
• Combining Cell Contents
• Changing Limited Relative References to Absolute
• Adjusting Formulas for Top-Added Rows
• Getting the Name of the Worksheet Into a Cell
• Determining "Highest Since" or "Lowest Since"
• Returning the Smallest Non-Zero Value
• Removing the Last Digit in a Number
• Unbreakable Formula References to Worksheets
• Rounding to Two Significant Digits
• Pulling a Phone Number with a Known First and Last Name
• Moving the House Number to Its Own Cell
• Extracting a State and a ZIP Code
• Finding the First Non-Digit in a Text Value
• Dealing with Circular References
• Finding Columns of a Certain Width

PivotTable and PivotChart Tips

• Counting with PivotTables
• Excluding Zero Values from a PivotTable

Sorting and Filtering Tips

• Sorting a Range of Cells
• Recognizing a Header Row when Sorting
• Sorting Data on Protected Worksheets
• Sorting Decimal Values
• Sorting while Ignoring Leading Characters
• Sorting Serial Numbers by Their Trailing Characters
• Moving Cell Borders when Sorting
• Incorrect Links after Sorting Hyperlinks
• Can't Sort Imported Data
• Printing Rows Conditionally
• Filtering for Purchases within a Given Month

Comment Tips

• Formatting Text in Comment Boxes
• Pasting Pictures into a Comment
• Hyperlinks in Comments

Date and Time Tips

• Inserting Tomorrow's Date
• Setting a Default Date Format
• Limiting Entry of Prior Dates
• Incrementing Months in Dates
• Calculating Week-Ending Dates
• Calculating Averages by Date
• Parsing Non-Standard Date Formats
• Calculating the First Business Day of the Month
• Determining Business Quarters from Dates
• Monthly Close-Out Dates
• Calculating Months for Billing Purposes
• Calculating a Group Retirement Date
• Returning Nothing If Two Values are Empty
• Calculating a Sum for a Range of Dates
• Determining If a Year is a Leap Year
• Entering the Current Time
• Entering Large Time Values
• Dealing with Small Time Values
• Automatically Entering a Data Entry Time
• Entering Negative Times

Graphics and Charting Tips

• Nudging a Graphic
• Flipping a Drawing Object
• Hiding Graphics
• Sorting with Graphics
• Changing How Arrows Look
• Setting Default Attributes for Lines and Arrows
• Taking a Picture
• Positioning a Graphic in a Macro
• Creating an Organization Chart
• Creating Venn Diagrams with Excel Data
• Changing Chart Types
• Unselecting a Chart Item
• Unwanted Weekend Dates in Chart
• Removing a Trendline Error Message
• Using Dynamic Chart Titles
• Ignoring Empty Cells in a Chart
• Controlling the Plotting of Empty Cells
• Making Sure that Data Accompanies a Chart
• Outside End Data Label for a Column Chart
• Modifying Axis Scale Labels
• Changing Axis Tick Marks
• Changing Y-Axis Label Width
• Missing Bounds Options for a Chart
• Sorting within a Chart
• Creating Charts in VBA

Printing Tips

• Setting the Print Area
• Printing an Entire Workbook by Default
• Selecting a Paper Source
• Defining a Custom Paper Size
• Printing in Black and White and Color
• Specifying an Order for Page Printing
• Top Margin Ignored when Printing
• Adjusting Comment Printouts
• Out of Kilter Borders
• Incrementing Copy Numbers for Printouts
• Using the Keyboard to Control Page Display in Print Preview
• Printing a Chart Across Multiple Pages
• Preventing Changes to Multiple Selected Worksheets
• Using a Macro to Set a Print Range
• Printing Only Non-Blank Worksheets

Workbook and File Tips

• Saving All Open Workbooks
• Making Data Universally Accessible to Workbooks
• Sudden Increases in Workbook File Size
• Remembering Commonly Used Workbooks
• Error Opening Second Workbook
• Frequent Workbook Recovery Prompts
• Comparing Workbooks for Differences
• Forcing a Workbook to Close after Inactivity
• Comma-Delimited and MS-DOS CSV Variations
• Aligning Cells when Importing from CSV
• Specifying a Delimiter when Saving a CSV File in a Macro
• Determining If a File Exists
• Setting a Default File Format
• Making Changes in a Group of Workbooks
• Who Has the File Open?
• Getting Input from a Text File
• Saving a Workbook Using Passwords
• Don't Update Links to Other Programs

Tools Tips

• Using Find and Replace to Find Conditionally Formatted Cells
• Limitations On Finding Characters
• Finding and Replacing with Subscripts
• Checking Lock Status of Cells
• Changing Default Search Settings
• Counting All Characters
• Spell-Checking in a Protected Worksheet
• Embedding an Excel Chart in a Word Document
• Turning Off Sharing
• Showing a Scenario
• Converting Imported Information to Numeric Values
• Understanding Outlining
• Using AutoCorrect
• Increasing the Capacity of AutoCorrect

Macro Tips

• Macro Runs Slowly, but Steps Quickly
• Comparing Strings
• Swapping Two Strings
• Quickly Dumping Array Contents
• Changing the Default Drive
• Easily Changing the Default Drive and Directory
• Making a Cell's Contents Bold within a Macro
• Making Worksheet Copies for Daily Shifts
• Running a Macro while in Edit Mode
• Running a Macro when a Worksheet is Activated
• Workbook Events
• Adding a Calendar to a Worksheet
• Turning Off Screen Updating
• Jumping to the Start of the Next Data Entry Row
• Conditionally Displaying a Message Box
• Creating an Animated Count Up
• Pulling Apart Cells
• Creating Add-Ins
• Using Custom Add-Ins
• Copying a Set Range from Multiple Worksheets to a New Worksheet
• Adding Differently Formatted Text to a Cell
• Removing a Macro from a Shortcut Key
• Showing RGB Colors in a Cell
• Resizing a Text Box in a Macro
• Moving Macros from the Personal Workbook
• Detecting Types of Sheets in VBA
• Making Modal Dialog Boxes Appear in Front of Workbooks
• Using SUM In a Macro
• Macro Fails after Filter
• Easily Adding Blank Rows
• Noting the Workbook Creation Date
• Sheets for Days
• Seeing the Difference on the Status Bar
• Clearing the Undo Stack in a Macro
• Determining an Integer Value
• Counting Precedents and Dependents
• Deleting VBA Code in a Copied Worksheet
• Replacing Letters with Numbers
• Unprotecting Groups of Worksheets
• Adding Leading Zeroes to ZIP Codes
• Setting Program Window Size in a Macro
• Adjusting a Path Based on System and User
• Maximum Length Limit for a Macro
• Requiring Input
• Determining a Format's Currency Symbol
• Deleting Worksheet Code in a Macro
• Executing a Macro Every 15 Minutes
• Determining How Many Windows are Open
• Removing All Macros
• Determining an ANSI Value in a Macro
• Calculating an ISBN Check Digit
• Making a Macro Button Stay Put
• Hiding Macros
• Understanding the Personal Workbook Filename
• Macro, while Running, Stops Excel from Responding
• Running Macros in the Background

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.

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