ExcelTips Ribbon 2017 Archive (Table of Contents)

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

• Starting in Safe Mode
• Changing the Default Font
• Changing Your Name
• Turning Off Capital Corrections
• Relative Worksheet References
• Weird Actions for Arrow Keys and Enter
• Odd Arrow Key Behavior
• Making Pane Settings Persist
• Shortcut to Merge Cells
• Identifying Merged Cells
• Working with Record Numbers
• Problem with Missing Context Menu Option
• Personal Workbook Fails to Load
• Excluding a Specific Add-In at Startup
• Working with Huge Datasets
• Selecting Multiple Cells by Mistake
• Disabling Page Layout View
• Turning Off Display of Zeros for All Worksheets
• Sizing Columns and Rows Using the Keyboard
• Using the Ribbon Commands
• Using the Legacy Shortcut Keys
• Using the Context Menu
• Displaying Excel's Developer Tab

Editing Tips

• Creating Selections
• Picking a Group of Cells
• Adjusting a Range's Starting Point
• Quickly Entering Data
• Enhanced Filling
• Using the Fill Handle from the Keyboard
• Defeating Date Parsing when Pasting Information
• Copying to Very Large Ranges
• Quickly Deleting Rows and Columns
• Deleting Rows before a Cutoff Date
• Closing Up Cut Rows
• Combining Multiple Rows in a Column
• Reference Shortcut
• Entered Values are Divided by 100
• Fixing the Decimal Point
• Entering Dates in Excel
• Converting to ASCII Text
• Swapping Two Cells
• Activating the Formula Bar with the Keyboard
• Using the Same Range Name on Different Worksheets
• Getting Rid of Spaces in Cells
• Trimming Off All Spaces
• Limiting Entry of Names
• Using Slashed Zeroes
• Showing Text when a Cell is Empty
• Searching by Columns, by Default

Formatting Tips

• Accurate Font Sizes
• Creating 3-D Formatting for a Cell
• Increasing Row Height for Printing
• Locking the Background Color
• Adjusting Center Across Selection with a Cell Value
• Formatting Currency
• Unhiding a Single Column
• Mimicking Small Caps in Excel
• Superscripts in Custom Formats
• Underlining Text in Cells
• Enforcing a Desired Font
• Saving a Custom Table Style
• Using Copy and Paste for Formatting
• Changing Character Spacing

Conditional Formatting Tips

• Working with Multiple Conditions
• Protecting Conditional Formatting
• Shading Rows with Conditional Formatting
• Conditional Format that Checks for Data Type
• Changing Typeface with Conditional Formatting
• Returning a Value Based on Text Color

Online Tips

• Automatic Text in an E-mail
• Opening Sites in a Browser

Worksheet Tips

• Retrieving Worksheet Names
• Dynamic Worksheet Tab Names
• Unlocking a Worksheet with an Unknown Password
• Ordering Worksheets Based on a Cell Value
• Telling which Worksheets are Selected
• Renaming Worksheets Based On a List
• Resetting Default Names for New Worksheets

Worksheet Function Tips

• Ways to Concatenate Values
• Using the COLUMN Function
• Rounding to the Nearest $50
• Making Your Formulas Check for Errors
• Making VLOOKUP Case Sensitive
• An Average that Excludes Zero Values
• Using the WEEKNUM Function
• Specifying Different Weekends with NETWORKDAYS

Formula Tips

• Understanding Operators
• Getting Rid of Everything Except Numbers
• Referring to the Last Cell
• Grabbing the Second-to-Last Value in a Column
• Last Non-Zero Value in a Row
• Summing Based on Formatting in Adjacent Cells
• Summing Based on Part of the Information in a Cell
• Returning Item Codes Instead of Item Names
• Returning Zero When a Referenced Cell is Blank
• Counting String Occurrences in Odd Rows
• Extracting a Pattern from within Text
• Identifying Values that Don't Follow a Specific Pattern
• Identifying Missing Numbers in a Consecutive Series
• Using a Formula to Replace Spaces with Dashes
• Determining a Simple Moving Average
• Calculating an IRR with Varying Interest Rates
• Separating Names into Individual Columns
• Deriving Antilogs

PivotTable Tips

• Text Truncated in PivotTable
• Weighted Averages in a PivotTable

Sorting and Filtering Tips

• Understanding Ascending and Descending Sorts
• Sorting an Entire List
• Sorting ZIP Codes
• Preventing Jumbled Sorts
• Forcing Excel to Sort Cells as Text
• Sorting Text as Numbers
• Setting Up Custom AutoFiltering
• Copying the Results of Filtering
• Using AutoFiltering
• Removing Duplicates Based on a Partial Match

Comment Tips

• Automatically Placing Text in a Comment
• Viewing Comments

Date and Time Tips

• Automatically Converting to GMT
• Calculating Elapsed Time with Excluded Periods
• Calculating Business Days
• Calculating a Date Five Days before the First Business Day
• End-of-Month Calculations
• Counting Month Ends
• Converting Numeric Values to Times
• Tombstone Date Math
• Deciphering a Coded Date
• Formatting for Hundredths of Seconds
• Determining If a Date and Time is within Working Hours
• Counting Times within a Range

Graphics and Charting Tips

• Inserting a Picture in Your Worksheet
• Cropping Graphics
• Moving and Copying Graphics Objects
• Sending Drawing Objects to the Back or Front
• Creating a JPG File from a Chart
• Pop-Up Comments for Graphics
• Inserting Video into Worksheets
• Deleting Graphics when Deleting a Row
• Adding and Formatting a Shape via Macro
• Specifying Chart Sizes
• Excluding Some Data from a Chart
• Using Chart Titles
• Setting Grid Line Intervals for a Radar Chart
• Colorizing Charts
• Moving Objects with a Chart
• Specifying the Size of Chart Objects
• Changing Elements in Lots of Charts at One Time

Printing Tips

• Setting Up Your Printer
• Scaling Your Printing
• Using Multiple Print Settings
• Protecting Print Settings
• Printing Based on Cell Contents
• Printing Selected Cells by Default
• Specifying the Y Value in X of Y Page Numbering
• Sequential Page Numbers Across Worksheets
• Can Only Print to Default Printer
• Preparing a Chart Sheet for Printing

Workbook and File Tips

• Converting PDF to Excel
• Shrinking Workbook Size
• Saving in Multiple Locations
• Removing Protection from a Protected Workbook
• Workbook not Saving
• Automatically Hiding the Personal Workbook
• Limiting Who Can Delete Data
• Understanding Scope for Named Ranges
• Printing a List of Named Ranges
• Creating Individual Workbooks
• Inserting Workbook Comments Into a Cell
• Loading Unwanted Files at Startup
• Accessing Old Excel Data
• Avoiding Scientific Notation on File Imports
• Backing Up Quick Access Toolbars
• Faster Text File Conversions

Tools Tips

• Changing Links
• Specifying a Data Validation Error Message
• Stopping Validated Data from being Overwritten
• Protecting Formulas in Control-Linked Cells
• Using Stored Views
• Deleting a View
• Independent Radio Buttons
• Discovering Dependent Workbooks

Macro Tips

• Declaring Variables
• Understanding the If ... End If Structure
• Continuing Macro Lines
• Renaming a Macro
• Debugging a Macro
• Delaying in a Macro
• Determining the Length of a String
• Pulling Apart Characters in a Long String
• Converting Strings to Numbers
• Highlighting Pattern Violations
• Summing Only Visible Values
• Replacing Some Formulas with the Formula Results
• Page Numbers in VBA
• Inserting a Page Break Every X Rows
• Separating Evens and Odds
• Automating the Importing of Macros
• Automating Copying Macros
• Naming Tabs for Weeks
• Displaying the Selected Cell's Address
• Putting an X in a Clicked Cell
• Cropping Graphics in a Macro
• Deleting Unwanted Styles
• Automatically Loading Add-ins
• Understanding the While...Wend Structure
• Making Sure Cells are Filled In before Saving
• Macro for Month Name
• Controlling Window Size when Opening Additional Workbooks
• Calculating the Distance between the Top of the Window and Row 1
• Finding Workbooks Containing Macros
• Creating a Function Inventory for a Workbook

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 2017 (for Excel 2007, 2010, 2013, and 2016 users)	1 Jan 17 — 30 Dec 17	208 tips	(276 page) $32.99	(3.8 MB) $24.99
ExcelTips Ribbon 2018 (for Excel 2007, 2010, 2013, 2016, 2019, and Office 365 users)	6 Jan 18 — 29 Dec 18	249 tips	(321 pages) $32.99	(4.0 MB) $24.99
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	(7.2 MB) $24.99
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	(7.5 MB) $24.99
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	(6.6 MB) $24.99

You can also order any of the ExcelTips archives by calling our order line: 307-200-0450.