ExcelTips Ribbon 2023 Archive (Table of Contents)

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

• Ensuring Usability for Differing Excel Versions
• Controlling Display of the Formula Bar
• Changing Ribbon Tool Defaults
• Relative Worksheet References
• Relative References to Cells in Other Workbooks
• Picking Worksheets Quickly
• Selecting Multiple Cells by Mistake
• Arranging Workbook Windows
• Changing the Default Font
• Identifying Merged Cells
• Odd Arrow Key Behavior
• Freezing the Top Three Rows
• Making Pane Settings Persist
• Problem with Missing Context Menu Option
• Writing an Excel Help File
• Excluding a Specific Add-In at Startup
• Removing Add-ins
• Changing Your Name
• Removing Personal Information
• Modifying Error Alerts Received
• Changing Gridline Color
• Rounding in Results
• Disabling Page Layout View
• Animated Recalculation in Excel
• Turning Off Display of Zeros for All Worksheets
• Specifying the Behavior of the Enter Key

Editing Tips

• Creating Selections
• Jumping to a Range
• Quickly Deleting Rows and Columns
• Closing Up Cut Rows
• Not Enough Resources to Delete Rows and Columns
• Entering Dates in Excel
• AutoFilling with Weekdays
• Enhanced Filling
• Fixing the Decimal Point
• Controlling Formula Reference Jumping
• Swapping Two Cells
• Trimming Off All Spaces
• Getting Rid of Spaces in Cells
• Using the Same Range Name on Different Worksheets
• Entered Values are Divided by 100
• Using the Fill Handle from the Keyboard
• Numbering Filtered Rows
• Adjusting a Range's Starting Point
• Deleting Rows before a Cutoff Date
• Mouse Scroll Wheel Doesn't Work when Editing Formulas
• Showing Text when a Cell is Empty
• Moving More than One Cell to the Right
• Copying to Very Large Ranges
• Accepting Only a Single Digit
• Combining Multiple Rows in a Column
• Limiting Entry of Names
• Errors when Copying References to External Cells
• Using Slashed Zeroes
• Splitting Cells to Individual Columns
• Typing a Schwa Character in Excel
• Using an Input Mask
• Capitalizing Just a Surname
• Default Cell Movement when Deleting
• Finding Unused Names
• Weird Pasting Behavior
• Getting Rid of Cells Containing Only Spaces
• Ensuring Standard Units During Data Entry
• Using Go To to Jump to a Chart Sheet

Keyboard Shortcut Tips

• Quickly Entering Data
• Keyboard Shortcuts for Inserting Rows
• Keyboard Shortcut for Switching between Workbooks
• Reference Shortcut
• Keyboard Shortcut for Comments
• Sizing Columns and Rows Using the Keyboard
• Using the Ribbon Commands
• Using the Legacy Shortcut Keys
• Using the Context Menu
• Shortcut to Merge Cells

Find and Replace Tips

• A Fast Find-Next
• Searching by Columns, by Default
• Changing the Color Used to Highlight Found Information
• Searching for Wildcards

Formatting Tips

• Understanding Monospace Fonts
• Using Copy and Paste for Formatting
• Changing Character Spacing
• Mimicking Small Caps in Excel
• Locking the Background Color
• Changing Default Row Height
• Enforcing a Desired Font
• Superscripts in Custom Formats
• Underlining Text in Cells
• Unhiding a Single Column
• Formatting Currency
• Removing Borders
• Easy Value Hiding
• Wrapping Text in Merged Cells
• Controlling Automatic Formatting of Dates
• Hash Marks Displayed Instead of Cell Contents
• Saving a Custom Table Style
• Deleting Unwanted Styles
• Changing Font Sizes

Conditional Formatting Tips

• Defining a Single Conditional Formatting Condition
• Working with Multiple Conditions
• Protecting Conditional Formatting
• Deleting Conditional Formatting
• Shading Rows with Conditional Formatting
• Conditional Formats that Distinguish Blanks and Zeroes
• Conditional Format that Checks for Data Type
• Highlighting Greater Than Average Dry Durations
• Highlighting Values that are 10x a Baseline Value
• Highlighting an Entire Row for the Current Date

Online Tips

• Jumping to a Specific Cell Using a Hyperlink
• Automatic Text in an E-mail

Worksheet Tips

• Undeleting a Worksheet
• Retrieving Worksheet Names
• Ordering Worksheets Based on a Cell Value
• Locking Worksheet Tab Order
• Protecting a Single Worksheet
• Conditionally Setting the Color of Worksheet Tabs
• Unlocking a Worksheet with an Unknown Password
• Making Multiple Worksheet Copies
• Automatically Renaming Worksheets
• Preventing Someone from Recreating a Protected Worksheet
• Pasting Multiple Worksheets into a Word Document

Worksheet Function Tips

• Ways to Concatenate Values
• Understanding the LET Function
• Specifying Different Weekends with NETWORKDAYS
• An Average that Excludes Zero Values
• Transposing and Linking
• Returning an ANSI Value
• Choosing SUBSTITUTE or REPLACE
• Determining the Least Common Multiple
• Adding Up Tops and Bottoms

Formula Tips

• Understanding Operators
• Understanding Scope for Named Ranges
• Formulas Don't Calculate as Formulas
• Determining the Used Range
• Grabbing the Second-to-Last Value in a Column
• Finding Odd Values Greater Than 50
• First Value Less Than or Equal to 100
• Getting Rid of Unused Range Names
• Locating a Single-Occurrence Value in a Column
• Determining Combinations to Make a Total
• Summing Based on Formatting in Adjacent Cells
• Adding an Area Code
• Counting String Occurrences in Odd Rows
• Summing Based on Part of the Information in a Cell
• Extracting a Pattern from within Text
• Getting Rid of Everything Except Numbers
• Returning Zero When a Referenced Cell is Blank
• Avoiding Rounding Errors in Formula Results
• Rounding Up to a Value Ending in 9
• Calculating the Day of the Year
• Randomly Assigning Names to Items
• Pulling Initial Letters from a String
• Median of Selected Numbers
• Adding Area Codes to Phone Numbers
• Putting Amounts in Month Columns

PivotTable Tips

• Empty PivotTable Cells Don't Show as Blank
• Seeing What Changed in a PivotTable

Sorting and Filtering Tips

• Understanding Ascending and Descending Sorts
• Differentiating a Header Row
• Sorting an Entire List
• Sorting ZIP Codes
• Preventing Jumbled Sorts
• Forcing Excel to Sort Cells as Text
• Sorting by Colors
• Sorting Dates and Times
• Sorting for a Walking Tour
• Creating a Sort Order
• Removing Duplicates Based on a Partial Match
• Setting Up Custom AutoFiltering
• Using AutoFiltering
• Filtering for Comments (Notes)
• Deriving a List of Albums by a Music Artist
• Not All Rows are Filtered

Date and Time Tips

• Generating a 4 On/4 Off Work Schedule
• Tombstone Date Math
• Calculating the Last Day in a Week Number
• Calculating Business Days
• End-of-Month Calculations
• Counting Month Ends
• Deciphering a Coded Date
• Converting Coded Dates into Real Dates
• Displaying a Number as Years and Months
• Month for the Nth Sunday
• Leap Years and Fiscal Periods
• Counting Times within a Range
• Calculating Elapsed Time with Excluded Periods
• Rounding to a Half Hour and Formatting the Display

Graphics and Charting Tips

• Creating a Drawing Object
• Inserting a Picture in Your Worksheet
• Moving and Copying Graphics Objects
• Sending Drawing Objects to the Back or Front
• Inserting a Watermark Behind Merged Cells
• Deleting Graphics when Deleting a Row
• Pop-Up Comments for Graphics
• Understanding Fill Effects
• Adding and Formatting a Shape via Macro
• Inserting Video into Worksheets
• Moving Drawing Objects
• Specifying Chart Sizes
• Creating a JPG File from a Chart
• Showing Elapsed Time in a Graph
• Moving Objects with a Chart
• Excluding Some Data from a Chart
• Creating a Bar Chart for Temperatures
• Colorizing Charts
• Changing Elements in Lots of Charts at One Time
• Setting Grid Line Intervals for a Radar Chart
• Adjusting the Angle of Axis Labels
• Creating Sparklines
• Changing the Color Inside a Shape
• Creating a Year-to-Date Comparison Chart
• Converting HSL to RGB
• RGB Values for Automatic Colors
• Reliable Display of X-Y Values in a Chart

Header and Footer Tips

• Specifying the Y Value in X of Y Page Numbering
• Copying Headers or Footers within a Workbook
• Specifying Date Formats in Headers
• Setting Header/Footer Margins

Printing Tips

• Setting Up Your Printer
• Protecting Print Settings
• Using Multiple Print Settings
• Working with Multiple Printers
• Setting Print Quality
• Printing Based on Cell Contents
• Preparing a Chart Sheet for Printing
• Increasing Row Height for Printing
• Sequential Page Numbers Across Worksheets
• Scaling Your Printing
• Can Only Print to Default Printer
• Setting the Active Printer in VBA
• Collating Copies
• Printing Selected Cells by Default
• Printing Multiple Worksheets on a Single Page
• Printing a List of Named Ranges
• Printing Limited Pages from a Range of Worksheets

Workbook and File Tips

• Workbooks Opening as Read-Only
• Workbook Loses All Conditional Formatting
• Determining If a Workbook is Referenced by Another Workbook
• Needing to Save a Workbook Twice
• Workbook not Saving
• Unresponsive Excel when Double-Clicking a Workbook
• Calculating Only the Active Workbook
• Removing Protection from a Protected Workbook
• Shrinking Workbook Size
• Inserting the Workbook Name
• Changing a Workbook Password
• Protecting an Entire Folder of Workbooks
• Creating Individual Workbooks
• Saving in Multiple Locations
• Loading Unwanted Files at Startup
• Backing Up Quick Access Toolbars
• Avoiding Scientific Notation on File Imports
• Inserting Workbook Comments Into a Cell
• Excel Filename Is Too Long
• Saving Information in a Text File

Tools Tips

• Specifying a Data Validation Error Message
• Viewing Comments
• Copying Comments to Cells
• Editing Comments and Notes
• Discovering Dependent Workbooks
• Using Stored Views
• Deleting a View
• Tracing Errors
• Tracing Precedent Cells
• Tracing Dependent Cells
• Independent Radio Buttons

Macro Tips

• Relative References when Recording Macros
• Writing a Macro from Scratch
• Understanding the If ... End If Structure
• Continuing Macro Lines
• Renaming a Macro
• Converting Strings to Numbers
• Adding a Macro to the Quick Access Toolbar
• Calculating the Distance between the Top of the Window and Row 1
• Summing Only Visible Values
• Displaying the Selected Cell's Address
• Making Sure Cells are Filled In before Saving
• Checking the Scope of a Defined Name
• Extracting Proper Words
• Highlighting Pattern Violations
• Debugging a Macro
• Inserting a Page Break Every X Rows
• Macro for Month Name
• Importing Based on a Partial File Name
• Understanding the While...Wend Structure
• Renaming Worksheets Based On a List
• Separating Evens and Odds
• Resetting Default Names for New Worksheets
• Cropping Graphics in a Macro
• Finding Workbooks Containing Macros
• Pulling Apart Characters in a Long String
• Error Creating Event Handlers
• Item Not Available in Library
• Replacing Some Formulas with the Formula Results
• Page Numbers in VBA
• Controlling Window Size when Opening Additional Workbooks
• Automating the Importing of Macros
• Delaying in a Macro
• Automatically Loading Add-ins
• Excel Crashes when Running Macros
• Automating Copying Macros
• Finding the Path to the Desktop
• Using R1C1 Formula References in a Macro
• Sharing Macros with Others
• Using Named Ranges in a Macro
• Displaying the "Last Modified" Date
• Selecting Visible Cells in a Macro
• Skipping Hidden Rows in a Macro
• Generating Numeric Testing Data
• Selecting the First Cell in a Row
• Using InputBox to Get Data
• Determining if Calculation is Necessary
• Recognizing Notes and Comments in a Macro
• Inserting Worksheet Values with a Macro
• Ctrl+Break Won't Work to Stop a Macro

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