ExcelTips Ribbon 2024 Archive (Table of Contents)

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

• Excel Dark Mode
• Changing Excel's Background Color
• Jumping to a Specific Worksheet
• Moving Between and Selecting Sheets with the Keyboard
• Generating Random Testing Data
• Saving Changes in the Personal Workbook
• Setting a Default Workbook Window Size and Zoom Level
• Getting Audible Feedback
• Controlling Automatic Backups
• Measuring Efficiency of Formulas and Macros
• Displaying a Count of Zeros on the Status Bar
• Single Clicking Enters Edit Mode
• Slowing Down Mouse Selection
• Message about a Problem with the Clipboard
• Seeing How a Worksheet Will Print While Editing
• Enabling Circular References by Default
• Controlling How Excel Interprets Percentages
• Different Cell Movement in a Single Worksheet
• Using Copilot

Editing Tips

• How Many Rows and Columns Have I Selected?
• Understanding Auto-Population of Cells
• Limiting Input to Two Decimal Places
• Defaulting Input to Negative Values
• Inserting Different Dashes
• Repeating a Pattern when Copying or Filling Cells
• Replacing Links with Values
• Converting Text Notation Values to Numeric Values
• Deleting Duplicate Columns
• Deleting Rows Containing Struck-Through Text
• Deleting Everything Up to a Character Sequence
• Splitting Sentences to Cells
• Automatically Capitalizing Day Names
• Clearing Everything Except Formulas
• Counting with Subtotals
• Limiting Choices in a Cell
• Answering Questions in Order
• Returning Item Codes Instead of Item Names
• Automatically Adding 20% to an Entry
• Relative Worksheet References when Copying
• Pasting Numeric Values in Other Programs

Find and Replace Tips

• Inconsistent Behavior of Find and Replace Dialog Box
• Superscripts in Find and Replace
• Find and Replace in Headers
• Getting a List of Matching Cells
• Searching for Dates
• Searching for Line Breaks
• Searching Comments

Formatting Tips

• Adjusting Center Across Selection with a Cell Value
• Cannot Use Dotted Diagonal Borders
• Extra Blank Lines in Some Cells
• Adjusting Row Height for a Number of Worksheets
• Understanding Color and Conditional Formatting Codes
• Adding a Custom Format to those Offered by Excel
• Applying Table Formats
• Understanding Cell Indenting
• Adjusting Cell Margins for More White Space
• Determining Font Formatting
• Leaving Leading Zeros in Place
• Formatted Dates Appear Differently on Different Systems

Conditional Formatting Tips

• Copying Conditional Formatting
• Conditional Formatting Not Reliably Working
• Detecting Errors in Conditional Formatting Formulas
• Conditionally Formatting Non-Integers
• Conditionally Formatting Cells Containing Dates
• Conditionally Highlighting a Milestone Cell
• Conditionally Formatting an Entire Row
• Comparing Dollar Values in a Conditional Formatting Rule
• Finding Duplicate Data Across Worksheets
• Automatic Lines for Dividing Lists
• Coloring Identical Company Names

Online Tips

• Inserting Hyperlinks
• Using Drag-and-Drop to Create a Hyperlink
• Converting a Range of URLs to Hyperlinks
• Opening an HTML Page in a Macro
• Restrictions Blocking Hyperlinks
• Get Rid of Web Stuff
• Extracting URLs from Hyperlinked Images

Worksheet Tips

• Specifying the Number of Worksheets in a New Workbook
• Unhiding Multiple Worksheets
• Finding a Worksheet to Unhide among Many Hidden Sheets
• Shortening Worksheet Tabs
• Hiding and Protecting Columns
• Password Protecting Specific Columns in a Worksheet
• Properties for Worksheets

Worksheet Function Tips

• Performing Integer Divisions
• Returning the Left-most Characters
• Ignoring Special Characters in COUNTIF
• Using SUMIF with Text Parameters
• Understanding the POWER Function
• Using a Week Number as One Criterion in a Formula
• Calculating Fractions of Years
• Calculating Months of Tenure
• Converting Radians to Degrees
• Determining a Value of a Cell
• Converting to Octal
• Doing Math Using Roman Numerals

Formula Tips

• Counting Odds and Evens
• Counting Unique Values with Functions
• Getting a Count of Unique Names
• Counting Values within 10% of a Target
• Summing Absolute Values
• Selective Summing
• Summing Only Cells Containing Formulas
• Averaging a Non-Contiguous Range
• Filling Cells with Decreasing Cell References
• Displaying a Value and a Percentage in a Single Cell
• Generating a Gift Exchange List
• Determining a State from an Area Code
• Referencing Every Third External Cell
• Extracting Street Numbers from an Address
• Identifying Digit-Only Part Numbers Excluding Special Characters
• Identifying Values that Don't Follow a Specific Pattern
• Generating a Unique ID Number
• Selecting Random Names
• Projects in Process for Each Day in a Range of Dates
• Values Beginning with a Specific Letter or Digit
• Extracting Foreign-Language Characters
• Adding Rows without Changing a Cell Reference
• Calculating Unique IDs Based on Names and Initials
• Finding the Address of the Lowest Value in a Range
• Indirectly Referencing a Cell on a Different Worksheet
• Formula Shows Instead of Formula Result
• Finding the Smallest Even Value
• Adding Dashes between Letters
• Iterating Circular References
• Adding a Missing Closing Bracket
• Producing an Array of Numbers
• Unique Date Displays
• Converting Mainframe Date Formats
• Generating Double-Digit Random Numbers
• Creating a Static Cell Reference
• Removing Everything Except Duplicates
• Deriving Monthly Median Values

PivotTable Tips

• Easy Filtering Specifications for a PivotTable
• Pointing PivotTables to Different Data
• Using Classic PivotTable Layout as the Default

Sorting and Filtering Tips

• Controlling Sorting Order
• Sorting Letters and Numbers
• Separating Cells Based on Text Color
• Too Many Formats when Sorting
• Advanced Filtering
• Limits on Filtering
• Copying the Results of Filtering

Date and Time Tips

• Finding the Previous Work Day
• Date for Next Wednesday
• Pulling All Fridays
• Weekdays in a Month
• Calculating Dates for Thanksgiving
• Determining Contract Weeks
• Beginning of a Future Week
• Years in Which a Date Occurred on a Particular Day
• Determining Month Names for a Range of Dates
• ISO Week Numbers in Excel
• Taking the Time into Account in a Formula

Graphics and Charting Tips

• Taking Pictures
• Capturing a Screen
• Creating a Shape
• Adding Drop Shadows
• Adding a Line Shape of a Given Slope and Length
• Setting the Default Fill Color for a Shape to None
• Protecting a Graphic
• Watermarks in Excel
• Making the Default Image Resolution Persistent
• Hiding Graphics when Filtering
• Sizing Text Boxes and Cells the Same
• Creating a Chart
• Make that Chart Quickly!
• How to Choose the Type of Chart to Use
• Plotting Times of Day
• Changing Chart Size
• Locking Graphic Annotations to Chart Data Points

Header and Footer Tips

• Copying Headers and Footers
• Full-Width Headers and Footers in Excel
• Using X of Y in a Page Footer
• Leading Zeros in Page Numbers

Printing Tips

• Setting Default Print Margins
• Printing a Range of Pages
• Printing a Short Selection
• Printing Selected Worksheets
• Printing a Number of Different Pages
• Printing More than One Copy
• Specifying Print Quantity in a Cell
• Using Duplex Printing
• Printing Gridlines by Default
• Custom Page Numbers on Printouts
• Limiting Printing to a Workbook from a Set Location
• Hiding a Hyperlink on a Printout
• Printing a Single Column in Multiple Columns
• Specifying a Paper Tray in a Macro
• Showing Print Preview for the Current Page
• Adding Page Borders to a Printout
• Multiple Print Areas on a Single Printed Page
• Creating Individual PDFs by Worksheet
• Repeating Columns at the Right
• Printing a Draft of a Worksheet
• Dynamically Setting a Print Area

Workbook and File Tips

• Remembering Workbook Settings from Session to Session
• Embedding Your Phone Number in a Workbook
• Merging Many Workbooks
• Copying Large, Object-Rich Worksheets from a Corrupted Workbook
• Opening Multiple Workbooks at Once
• Automatically Hiding the Personal Workbook
• Problems with Default Workbook and Worksheet Templates
• Limiting Who Can Delete Data
• Excel Opens Additional, Unwanted Files
• Checking for the Existence of a File
• Use Filenames that Sort Properly
• Specifying the Number of MRU Files
• Getting Rid of Extra Quote Marks in Exported Text Files
• Opening a Workbook as Read-Only
• Closing a Read-Only Workbook
• Full Path Names in Excel
• Adding a File Path and Filename
• Different CSV Formats
• Creating a CSV File
• Stopping Date Parsing when Opening a CSV File
• Determining the Length of a Text File

Macro Tips

• Recording a Macro
• Clean Up Your Macro List
• Creating a String in a Macro
• Dissecting a String
• Trimming Spaces from Strings
• Setting Column Width in a Macro
• Displaying a Set Column Range
• Stepping Through a Non-Contiguous Range of Cells
• Reversing Cell Contents
• Relative VBA Selections
• Deleting Blank Rows
• Selecting Blanks
• Sorting
• Macros
• Deleting Every X Rows
• Testing if a Workbook is Open
• Stopping Fonts from Changing
• Pulling Cell Names into VBA
• Mouse Click Event in VBA
• Using Macros in Protected Workbooks
• Disabling All Function Keys Except One
• Preserving the Undo List
• Counting Empty Colored Cells
• Triggering an Event when a Worksheet is Deactivated
• Automatically Enabling Macros for Specific Workbooks
• Putting the Last Saved Date in a Cell
• Saving an Unsavable Workbook
• Offering Options in a Macro
• Setting Row Height in a Macro
• Recovering Macros from Corrupted Workbooks
• Removing Pictures for a Worksheet in VBA
• Using Seek In a Macro
• Determining the Current Directory

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 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
ExcelTips Ribbon 2024 (for Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365 users)	6 Jan 24 — 28 Dec 24	247 tips	(342 pages) $32.99 $23.09	(7.6 MB) $24.99 $17.49

Important Ordering Notes:

If you live in a European Union (EU) country, please do NOT order the CD-ROM. Your order will go through, but I will need to then cancel the order. The reason is simple: The EU requires the procurement of a special shipping license (for the lack of a better term) that makes it onerous and economically untenable for a small company such as mine to ship physical products into EU countries. Please, order the archive download, instead. There are no restrictions (yet) on downloadable products.

If you live in a country that imposes value-added taxes (VAT) and you order a CD-ROM, you will need to pay the VAT when the order is delivered to you. I do not collect VAT at the time of ordering, nor is the VAT included in your purchase price.