Excel Tips
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
User Tips by Category
= Tip has a companion file to download.
General
- Old-Style Menus In Excel 2007
- Getting A List Of Files Names - Another Method
- Clearing The Text To Columns Parameters
- Making An Exact Copy Of A Range Of Formulas, Take 2
- Create A Drop-Down List Of Possible Input Values
- Using Custom Number Formats
- Navigating Excel’s Sheets
- Override Excel’s Text Import Wizard
- Sharing Autocorrect Shortcuts
- Making A Worksheet Very Hidden
- Importing A Text File Into A Worksheet
- Protecting Cells, Sheets, Workbooks, And Files
- Changing The Number Of Sheets In A New Workbook
- Restrict Cursor Movement To Unprotected Cells
- Change The Color Of Worksheet Tabs
- Making An Exact Copy Of A Range Of Formulas
- Creating A Database Table From A Summary Table
- Getting A List Of File Names
- Spreadsheet Protection FAQ
- Extended Date Functions
Formatting
- Text Effects In Text Boxes
- Quantifying Color Choices
- Comparing Two Lists With Conditional Formatting
- Alternate Row Shading Using Conditional Formatting
- Duplicate Repeated Entries In A List
- Working With Fractions
- Using Conditional Formatting
- Fix Incorrect Decimal Places During Data Entry
- Display Text In Multiple Lines
- Changing The Default Cell Comment Formatting
- Change The Formatting Of Your Subtotal Rows
Formulas
- Is A Particular Word Contained In A Text String?
- Formulas To Perform Day Of Month Calculations
- Making An Exact Copy Of A Range Of Formulas, Take 2
- Calculating Easter
- Converting Unix Timestamps
- Naming Techniques
- Creating A List Of Formulas
- Cell Counting Techniques
- Summing And Counting Using Multiple Criteria
- Chart Trendline Formulas
- Making An Exact Copy Of A Range Of Formulas
- Comparing Two Lists With Conditional Formatting
- Dealing With Negative Time Values
- Converting Non-numbers To Actual Values
- Compare Ranges By Using An Array Formula
- Calculate The Number Of Days In A Month
- Identify Formulas By Using Conditional Formatting
- Calculating A Conditional Average
- Display Text And A Value In One Cell
- Automatic List Numbering
- Calculate The Day Of The Year And Days Remaining
- Rounding To “n” Significant Digits
- Working With Pre-1900 Dates
- Using Data Validation To Check For Repeated Values
- Sum The Largest Values In A Range
- Count Autofiltered Rows
- Perform Two-Way Table Lookups
- Referencing A Sheet Indirectly
- Delete All Input Cells, But Keep The Formulas
- Round Values To The Nearest Fraction
- Avoid Error Displays In Formulas
- Change Cell Values Using Paste Special
- Hiding Your Formulas
- Counting Distinct Entries In A Range
- Force A Global Recalculation
- Summing Times That Exceed 24 Hours
- Transforming Data With Formulas
- Creating A “Megaformula”
- Alternatives To Nested IF Functions
- A Formula To Calculate A Ratio
Charts & Graphics
- Saving A Range As A Graphic File
- A Quick And Dirty Slideshow Macro
- Pasting An Image To A UserForm Control
- Interactive Chart With No Macros
- Creating A Splash Screen For An Excel Workbook
- A Class Module To Manipulate A Chart Series
- Chart Trendline Formulas
- Removing Lines From A Surface Chart
- Creating A Non-Graphic Chart Directly In A Range
- Creating A Linked Picture Of A Range
- Creating A Thermometer Style Chart
- Handle Missing Data In A Line Chart
- Format Cells To Display In Thousands
- Unlink A Chart Series From Its Data Range
- Saving A Chart As A GIF FIle
- Animated Hypocycloid Charts
Printing
Developer Tips by Category
General VBA
- Playing MP3 Files From Excel
- Dual Monitors And UserForms
- Is A Particular Word Contained In A Text String?
- The Value, Formula, and Text Properties
- Clearing The Text To Columns Parameters
- A Macro To Count Word Frequencies
- Saving A Range As A Graphic File
- A Quick And Dirty Slideshow Macro
- Maximize Excel Across All Monitors
- Understanding The IsDate Function
- Controlling User Scrolling
- Creating A List Of Formulas
- Selecting All Unlocked Cells
- Looping Through A Range Efficiently
- Using Object Variables
- Referring To Ranges In Your VBA Code
- Understanding Object Parents
- Working With Variable-Size Ranges
- VBA Debugging Tips
- Selecting The Maximum Value In A Range
- Generating Permutations
- Displaying Help
- Deleting All Empty Rows
- Working With Names In VBA
- Using Auto List Members In The VB Editor
- Automatically Resetting The Last Cell
- Synchronizing Sheets In A Workbook
- Handling The Workbook Beforeclose Event
- Pausing A Macro To Get A User-selected Range
- Sending Personalized Email From Excel
- Clearing The Advanced Filter Dialog Box
- Mail Merge - Without Word
- Creating A Usage Log
- Determining If Access To The VB Project Is Allowed
- Ensuring That Data Validation Is Not Deleted
- Creating A Worksheet Map
- Creating A Splash Screen For An Excel Workbook
- Playing Sound From Excel
- A Class Module To Manipulate A Chart Series
- Removing Lines From A Surface Chart
- Using Controls On Worksheets
- Sharing Autocorrect Shortcuts
- Restrict Cursor Movement To Unprotected Cells
- Making An Exact Copy Of A Range Of Formulas
- Developer FAQ - General Questions
- Developer FAQ - Visual Basic Editor
- Developer FAQ - Subroutines
- Developer FAQ - Functions
- Developer FAQ - Objects, Properties, And Methods
- Developer FAQ - Add-Ins
CommandBars & Menus
UserForms
- Dual Monitors And UserForms
- Pasting An Image To A UserForm Control
- Displaying Help
- General Userform Tips
- Displaying A Progress Indicator
- Importing And Exporting Userforms
- Handle Multiple Userform Buttons With One Subroutine
- Filling A Listbox With Unique Items
- Creating A Color Picker Dialog Box
- Displaying A Chart In A Userform
- Adding A Hyperlink To A Userform
- Creating A Userform Programmatically
- Disabling A Userform’s Close Button
- Using Controls On Worksheets
- Developer FAQ - UserForms
VBA Functions
- User-Defined Function Argument Descriptions In Excel 2010
- Extracting An Email Address From Text
- Quantifying Color Choices
- Determining The User’s Video Resolution
- Identifying Unique Values In An Array Or Range
- Getting A List Of File Names Using VBA
- Looping Through Ranges Efficiently In Custom Worksheet Functions
- Undoing A VBA Subroutine
- Determining The Last Non-empty Cell In A Column Or Row
- Multifunctional Functions
- Some Useful VBA Functions
- Using The GetSetting & SaveSetting Functions
- Determining The Data Type Of A Cell
- A Custom Function For Relative Sheet References
- Determining If A Range Is Contained In A Range
- Determining If A Worksheet Or Workbook Has Code
- Searching Using Soundex Codes
- Getting A List Of Installed Fonts
- A VBA Function To Get A Value From A Closed File
- Playing A Sound Based On A Cell’s Value
- Determining The Drive Type
- The Versatile Split Function
- Retrieving The Computer Name Or Logged-in User Name
- Identifying The Newest File In A Directory
- Developer FAQ - Functions
- Extended Date Functions
Search for Tips
All Tips
Browse Tips by Category
Tip Books
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks
Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers