ExcelTips Ribbon 2019 Archive (Table of Contents)

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

• Seeing Excel's Program Window
• Zooming In On Your Worksheet
• Always Open at 100% Zoom
• Understanding R1C1 References
• Moving Between and Selecting Sheets with the Keyboard
• Changing Excel's Background Color
• Choosing Direction After Enter On a Workbook Basis
• Different Cell Movement in a Single Worksheet
• Editing the Custom Spelling Dictionaries
• Backing Up Custom Dictionaries
• Accessing Stock Information
• Inserting the User's Name in a Cell
• Empty Cells Triggers Error
• Maintaining the Active Cell
• Freezing Both Rows and Columns
• Excel Refuses to Put Page Breaks between Subtotal Groups
• Changing the Reference in a Named Range
• Going to the Corners of a Selected Range
• Countering Compressed Columns
• Not Enough System Resources

Editing Tips

• Displaying Row and Column Labels
• Displaying a Hidden First Column
• Switching Editing Location
• Forcing Editing to Be Done in a Cell
• Inserting Rows
• Changing the Default Paste Mode
• Pasting and Matching Destination Formatting
• Problems Pasting Information into a Worksheet
• Separating Text by Color
• Working in Feet and Inches
• Understanding Auto-Population of Cells
• Setting an Upper Threshold for a Cell
• Limiting Choices in a Cell
• How Many Rows and Columns Have I Selected?
• Shortcut for Selecting a Data Range
• Counting with Subtotals
• Can't Empty the Clipboard
• Searching for Line Breaks
• Searching for All
• Finding and Replacing in Text Boxes
• Replacing Characters at the End of a Cell
• Copying from the Task Bar
• Copying Subtotals
• Copying a Cell without Formatting
• Can't Copy Data between Workbooks
• Relative Worksheet References when Copying
• Incrementing References by Multiples when Copying Formulas
• Pasting Leading Zeroes
• Adding Spaces in Front of Capital Letters
• Proper Case Conversion with Exceptions
• Segregating Numbers According to Their Sign
• Automatically Capitalizing Day Names
• Displaying Letter Grades
• Automatically Adding 20% to an Entry
• Getting Rid of All Rows Except the One for the Latest Date
• Clearing Everything Except Formulas
• Getting Rid of 8-Bit ASCII Characters
• Easily Changing Links
• Identifying Unused Named Ranges
• Changing References in a Lot of Defined Names
• Checking for Proper Entry of Array Formulas

Formatting Tips

• Copying Formats to a New Worksheet
• Understanding Cell Indenting
• Replacing Background Colors in Cells
• Changing the Color of a Cell Border
• Checking All Cell Formatting in VBA
• Matching Formatting when Concatenating
• Understanding Underlines
• Automatic Lines for Dividing Lists
• Shortcuts to Hide Columns
• Formatting Subtotal Rows
• Moving Custom Formats to Number Formatting Categories
• Exporting Latitude and Longitude
• Getting Rid of Leading Zeros in a Number Format
• Controlling Display of Page Breaks

Conditional Formatting Tips

• Conditional Formats for Odd and Even Columns
• Applying Conditional Formatting to Multiple Worksheets
• Conditionally Formatting for Multiple Date Comparisons
• Noting Inactivity within a Timeframe
• Conditional Formatting for Errant Phone Numbers
• Conditional Formatting with Data Imported from Access

Online Tips

• Adding a ScreenTip
• Pulling Apart a URL
• Links to Hyperlinks
• Hyperlinks in Shared Workbooks
• Specifying Default Hyperlink Text
• Unwanted Hyperlinks
• Hyperlink Doesn't Match Cell Contents
• References to Hyperlinks aren't Hyperlinks
• Uncovering and Removing Links

Worksheet Tips

• Properties for Worksheets
• Changing the Color of Worksheet Gridlines
• Freezing Worksheet Tabs
• Using Very Long Worksheet Tab Names
• Dynamically Changing Worksheet Tab Color
• Sorting Worksheets According to Region
• Locking Worksheet Names
• Locking All Non-Empty Cells
• Stopping the Deletion of Cells
• Hiding and Protecting Columns

Worksheet Function Tips

• Determining a Value of a Cell
• Cleaning Text
• Counting Displayed Cells
• Phantom Counts
• Using a Week Number as One Criterion in a Formula
• Determining a Name for a Week Number
• Calculating Fractions of Years
• Returning the Left-most Characters
• Returning Values to the Left of a VLOOKUP
• Converting to Octal
• Indirect References to a DSUM Parameter
• Custom Formats for Scientific Notation
• Rounding to Even and Odd Values
• Exact Matches with DSUM
• Using the XIRR Function

Formula Tips

• Starting Out Formulas
• Formula Shows Instead of Formula Result
• Saving Common Formulas
• Strange Formula Conversions
• Changing the Cycling Sequence for the F4 Cell Reference Shortcut
• Applying Range Names to Formulas
• Using Named Formulas Across Workbooks
• Functions that Can Access Closed Workbooks
• Quickly Adding Formulas Referencing Multiple Worksheets
• Indirectly Referencing a Cell on a Different Worksheet
• Combining Numbers and Text in a Cell
• Strange Value Results in a Cell
• Deriving Monthly Median Values
• Ages in Years and Months
• Calculating Months of Tenure
• Adjusting Times for Time Zones
• Converting Mainframe Date Formats
• Converting UNIX Date/Time Stamps
• Finding the Directory Name
• Extracting File Names from a Path
• Listing Combinations
• Adding Dashes between Letters
• Deleting Duplicate Text Values
• Alphabetic Column Designation
• Deriving a Secant and Cosecant
• Calculating the Distance between Points
• Adding a Missing Closing Bracket
• Finding the Smallest Even Value
• Breaking Up Variable-Length Part Numbers
• Generating Double-Digit Random Numbers
• Solving a Quadratic Equation
• Iterating Circular References
• Finding Circular References
• Counting Asterisks
• Counting Cells According to Case
• Counting Asterisks in a Column
• Counting Names Based on Two Criteria
• Summing Based on Part of a Control Cell
• Using a Helper Column
• Using SUMPRODUCT
• Using SUMIF Directly
• Summing Only the Largest Portion of a Range
• Limiting a Calculated Value to a Range
• Finding the Sum of a Sequential Integer Range
• Rounding to the Nearest Half Dollar
• Totaling Across Worksheets
• Concatenating Values from a Variable Number of Cells

PivotTable and PivotChart Tips

• Rows in a PivotTable
• Missing PivotTable Data
• Counting Ports of Call with a PivotTable
• Updating a PivotChart Automatically

Sorting and Filtering Tips

• Sorting Letters and Numbers
• Sorting Data Containing Merged Cells
• Too Many Formats when Sorting
• Filtering Columns
• Displaying Multiple Filtered Colors
• Filtering Columns for Unique Values

Comment Tips

• Pasting a Comment into Your Worksheet
• Changing the Comment Indicator Color
• Adding Comments to Protected Worksheets

Date and Time Tips

• Defeating Automatic Date Parsing
• Understanding Date and Time Formatting Codes
• Using a Two-Character Day of the Week in a Date Format
• Determining If a Date is between Other Dates
• Preventing Automatic Date Formatting Changes
• Formatted Dates Appear Differently on Different Systems
• Hiding Columns Not within a Date Range
• Unique Military Date Format
• Adding Ordinal Notation to Dates
• Calculating Weekend Dates
• EOMONTH Function is Flakey
• Changing to a Non-US Date Format
• Including Weeks in Elapsed Time
• Days Left in the Year
• Copying Dates a Year Into the Future
• Taking the Time into Account in a Formula
• Dealing with Large Numbers of Seconds
• Calculating TV Time
• Rounding to the Nearest Quarter Hour

Graphics and Charting Tips

• Creating a Shape
• Editing Graphics Objects
• Deleting All Graphics
• Changing the Size of a Drawing Object
• Filling a Drawing Object
• Styles for Lines, Dashes, and Arrows
• Changing Line Color in a Drawing Object
• Setting a Transparent Color for an Image
• Adding a Drop Shadow to a Text Box
• Pictures inside Shapes
• Images Pile Up when Filtering
• Non-Tiled Background Pictures
• Pasting a Graphic to Multiple Worksheets
• Using Graphics to Represent Data Series
• Changing Chart Size
• Deleting a Chart
• Formatting Axis Patterns
• Changing the Axis Scale
• Plotting Times of Day
• Understanding Custom Chart Templates
• Easily Changing Chart Data Ranges
• Adjusting Your View of 3-D Graphs
• Excel Charts in PowerPoint

Printing Tips

• Printing a Number of Different Pages
• Printing Odd or Even Pages
• Fitting Your Printout on a Page
• Flipping Landscape Orientation when Printing
• Specifying Print Quantity in a Cell
• Printing without Opening
• Printing a Draft of a Worksheet
• Printing a Draft Watermark
• Printing Selected Worksheets
• Printing Individual Worksheets for Vendors
• Setting Print Ranges for Multiple Worksheets
• Printing a Chart
• Black and White Blues
• First and Last Names in a Page Header
• Moving Part of a Footer Down a Line
• Conditional Printing
• Executing a Macro After Printing is Done

Workbook and File Tips

• Renaming a Workbook
• Limiting Where a Workbook is Used
• Protecting an Entire Workbook
• Always Opening a Workbook that is Editable
• Opening a Workbook with Two Windows
• Opening Two Workbooks at Once
• Using File Explorer
• Configuring Windows for Automatic Starting
• Using a Batch File
• Using the Open Dialog Box
• Using the XLSTART Folder
• Configuring Excel to Open the Workbooks
• Using a Macro
• Using a Workspace
• Closing a Read-Only Workbook
• Protecting a Workbook from Opening in Other Programs
• Incorrect In-Use Message
• Stopping a Workbook from Persistently Auto-Loading
• Embedding Your Phone Number in a Workbook
• Saving Movement on Enter with a Workbook
• Jumping to the Real Last Cell
• Making Ribbon Customizations Apply to All Workbooks
• Using a Single Password for Multiple Workbooks
• Creating a Dated Backup File
• How Excel Treats Disk Files
• Different CSV Formats
• Creating a CSV File
• Appending to a Non-Excel Text File
• Determining the Length of a Text File
• Jumping Around Folders
• Setting the AutoRecover Directory
• Changing an Invalid Autosave Folder

Tools Tips

• Evaluating Formulas
• Understanding Auditing
• Accessing Dependent and Precedent Information
• Leaving Trace Precedents Turned On
• Tracing Precedents and Dependents Clears Undo Stack
• Creating Scenarios
• Creating Scenario Summaries
• Hiding Outline Symbols
• Adjusting Spell Check for Internet Addresses
• Allowing for Words that Contain Numbers
• Prohibiting Single-Digit Entries in a Cell
• Controlling How Excel Interprets Percentages
• Complex Data Input Limitations
• Single-Use Drop-Down List
• Using Check Boxes

Macro Tips

• Understanding Macros
• Tools on Developer Tab are Unavailable
• Storing Macros in Templates
• Running a Macro when a Worksheet is Deactivated
• Checking if a Workbook is Already Open
• Deriving an Absolute Value in a Macro
• Converting Numbers to Strings
• Worksheet Events
• Unhiding Multiple Worksheets
• Copying Worksheet Code Automatically
• Changing Macro Cell References Based on Edits
• Pausing Macros for User Input
• Offering Options in a Macro
• Setting Column Width in a Macro
• Creating a Plus/Minus Button
• Counting Atoms in a Chemical Formula
• Playing with a Full Deck
• Pulling First Letters from Parenthetical Text
• Swapping Two Numbers
• Setting Row Height in a Macro
• Splitting Information into Rows
• Delimited Text-to-Columns in a Macro
• Enforcing Moving Cells Up
• Engineering Calculations
• Adjusting Values with Formulas
• Searching Very Large Strings in a Macro
• Determining Differences Between Dates
• Removing Pictures for a Worksheet in VBA
• Replacing Commas with Periods
• Positioning a Column on the Screen
• Changing Section Headers
• Understanding Add-Ins
• Using Seek In a Macro
• Creating a Directory in a Macro
• Determining the Current Directory
• Changing Directories in a Macro
• Removing a Directory
• Opening a Workbook and Suppressing Automatic Macros
• Self-Deleting Macros
• Out of Memory Errors for Macros
• Recovering Macros from Corrupted Workbooks
• Macros Run Fine Individually, but Not Collectively

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