Tips and Tricks

How to use Excel and Google Sheets

Search our growing list of Excel (and Google Sheets!) "How To" tutorials. This list started March 2021 and will be growing rapidly!

Excel "How To" Tips & Tricks
AutoFill	yes
AutoFill Custom and Alphabetic Character Lists
AutoFill Dates or Months
AutoFill Numbers
AutoFill Numbers Without Dragging
Auto-Number Rows in a Column
Disable AutoFill
Make a List of Numbers
Turn On AutoFill
Use Fill Handle
Use Flash Fill to Fill a Range
AutoSave	yes
Recover a Deleted File
Recover a File That Was Not Saved
Set Up AutoSave Location and Time
Turn On or Off AutoSave and AutoRecover
Why is AutoSave Not Working or Grayed Out?
Calculation	yes
Add Values to Cells and Columns
Anchor a Cell
Change the Signs of Values
Divide Cells and Columns
Keep Variable Cell Constant
Lock / Freeze Cell in Formula
Multiply Cells and Columns
Stop Auto Calculation
Subtract Cells and Columns
Comments	yes
Copy Comments
Delete All Comments
Edit Comments
Insert a Comment or Note to a Cell
Show or Hide All Comments
Compare	yes
Compare Two Columns and Highlight Differences
Compare Two Columns for Matches
Compare Two Files for Differences
Compare Two Rows
Compare Two Sheets for Differences
Conditional Formatting	yes
Apply Conditional Formatting Based on an Adjacent Cell
Apply Conditional Formatting Multiple Sheets
Apply Conditional Formatting to Dates
Apply Conditional Formatting to Entire Column
Apply Conditional Formatting to Multiple Rows
Conditional Format Greater Than Or Less Than
Conditional Formatting Based on Another Cell
Conditional Formatting Based on Date in Another Cell
Conditional Formatting Dates Overdue
Conditional Formatting Formulas Not Working
Conditional Formatting Grayed Out
Conditional Formatting If Between Two Numbers
Conditional Formatting If Cell Contains Any Text
Conditional Formatting If Cell Contains Specific Text
Conditional Formatting Not Equal
Conditional Formatting Tips for 2022
Conditional Formatting with Multiple Conditions (And)
Copy Conditional Formatting
Highlight a Cell if a Specific Value Exists in Another Column
Highlight Blank Cells (Conditional Formatting)
Highlight Cells If (Conditional Formatting)
Highlight Cells That Begin (Or End) With
Highlight Duplicate Rows
Highlight Duplicate Values
Highlight Every Other Line In Excel
Highlight Rows If (Conditional Formatting)
Highlight Rows That Contain
Highlight the Highest Value
If Formula to Set Cell Color w/ Conditional Formatting
Insert Harvey Balls
Remove Conditional Formatting
Use Comparison Icon Sets
Use Conditional Formatting With a Checkbox
Use Custom Icon Sets
Copy & Paste	yes
Apply Formula to Entire Column (Copy Down)
Copy a Cell From Another Sheet
Copy and Paste a Range or Object as a Picture
Copy and Paste an Exact Formula
Copy and Paste as Text or Value
Copy and Paste Cells
Copy and Paste With Skip Blanks
Copy and Paste Without Borders
Copy and Paste Without Changing the Format
Copy Data From One Cell to Another Automatically
Copy Every Other Row
Copy Number Not Formula
Duplicate Rows
Freeze Random Numbers
Paste & Match Destination Formatting
Paste Excel Table into Gmail
Paste Horizontal Data Vertically
Rearrange Columns
Swap Cells
Swap or Move Columns
Transpose Rows to Columns
Data Validation	yes
Add a ToolTip
Create an Input Message
Data Validation Based on Another Cell
Enable Error Alert
Find Restricted Values
Restrict Cell Values
Set a Character Limit
Validate Phone Number Format
Database	yes
Clean Up Data
Create a Searchable Database
Drop-Down List	yes
Add a Drop-Down List With Color Formatting
Alphabetize a Drop Down List
Create a Cascading Drop-Down List
Create a Default Value for a Drop-Down List
Create a Drop-Down List
Create a Drop-Down List Filter
Create a Yes / No Drop Down
Drop-Down List With If Statement
Make Drop Down Categories and Subcategories
Remove a Drop-Down List
Update a Drop-Down List
Duplicates	yes
Clear Duplicate Cells
Combine Duplicate Rows
Merge Lists Without Duplicates
Prevent Duplicate Entries
Remove Both Duplicates
Remove Duplicate Rows
Remove Unique Values
Show Only Duplicates
Email	yes
Email a Spreadsheet
Email Address Format Validation
Send Email With Formula (No Macros!)
Send Mass Email From a List
Filters	yes
Apply Multiple Filters
Clear All Filters
Copy Filtered (Visible) Data
Delete Filtered Rows
Filter by Color
Filter Duplicate Values
Filter Merged Cells
Filter Rows
Hide Blank Rows
Hide Rows Based on Cell Value
Horizontal Filter
Remove Filters
Show or Hide AutoFilter Arrows
Show Rows With Specific Text
Use Custom AutoFilters
Find & Select	yes
Deselect Cells
Find and Delete Words
Find and Highlight
Find and Replace Multiple Values
Find and Select Specific Cells
Find Blank Cells
Find Errors
Find Merged Cells
Find Unique Values
Fix Excel Find Not Working
Highlight Blank Cells
Highlight Multiple Rows
Jump to a Row With Go To
Remove Asterisks (*)
Replace Blank Cells With Zeros
Replace Space with Underscore
Search All Sheets
Select All Cells With Values
Select Every Other Row
Select Multiple Cells
Select Non-Adjacent Cells or Columns
Use Go To Special
Use the Go To Command to Jump to a Cell
Forecast	yes
Add Goal Seek to Google Sheets
Automatically Vary the Contents of a Cell
Calculate the Break-Even Analysis Point
Calculate Total Revenue
Do a Sensitivity / What-If Analysis
Enable Solver Add-In
Insert Line and Column Sparklines
Install the Data Analysis Toolpak for PC & Mac
Quick Analysis Tool
Format Cells	yes
Add Lines (Borders)
Alternate Row Color
Apply and Change Themes
Apply Cell Styles
Automatic Formatting
Cell Margins
Change Gridline or Cell Border Color
Change the Cell Background Color
Clear All Formatting
Copy Cell Color
Copy Column Widths
Copy Row Height
Make All Rows and Columns the Same Height and Width
Merge and Center Cells
Merge Multiple Cells
Outline Cells (Apply All Borders)
Resize Cells to Default Row Height
Resize Multiple Rows or Columns at Once
Split a Cell Diagonally
Unmerge Cells
Use Format Painter for Multiple Cells
Format Text	yes
Add and Remove Strikethrough
Bold Lines
Center Across Selection
Change Alignment
Create a Bulleted List
Create Barcodes
Go Down to the Next Line
Hide Overflow Text in a Cell
Increase Indent
Make Cells Bigger to Fit Text
Make Subscripts and Superscripts
Rotate Text in a Cell
Shrink to Make Text Fit
Two Lines in One Cell
Underline Text
Use Line Spacing
Use Word Wrap
Write Paragraphs
Write Text Vertically
X Out a Cell
Formula Auditing	yes
#REF Error
Allow Circular References
Camera Tool
Find a Circular Reference Error
Fix the #NAME? Error
Formula View Mode
Get Rid of Green Triangle
Get Rid of the Red Triangle in Google Sheets
Hide All Dependency Tracer Arrows at Once
Hide Precedent Tracer Arrows
Ignore All Errors
Insert Functions With Dialog Box
Show Dependent Tracer Arrows
Show Precedent Tracer Arrows
Use Formula AutoComplete
Use the Error Checking Command
Watch Window
Insert & Delete	yes
Clear Cell Contents
Delete a File
Delete Blank Columns
Delete Blank Rows
Delete Cells and Shift Cells Left
Delete Cells and Shift Cells Up
Delete Every Other Column
Delete Every Other Row
Delete Hidden Rows and Columns
Delete Infinite Rows or Columns
Delete Multiple Cells
Delete Multiple Rows or Columns
Delete Rows if Cell Contains Specific Text
Delete Rows with Blank Cells
Insert Blank Row
Insert Blank Row
Insert Cells and Shift Cells Down
Insert Cells and Shift Cells Right
Insert Timestamp
Reset the Last Cell
Links	yes
Break All Links
Create a Hyperlink
Cross-Reference
Find External Links
Hyperlink to Another Sheet or Workbook
Link Files
Mirror Cells by Linking
Update Links Automatically
Miscellaneous	yes
32-bit vs. 64-bit Office - Which do I have? What's the Difference?
Change Author Property
Change Cell Size in Pixels or Inches
Change Document Properties
Clear the Cache
Customize the Ribbon
Excel Cannot Open or Save Any More Documents
Find the Last Saved Date
Generate All Permutations
Make a File Smaller
Merge Multiple Workbooks into One
Open Files in New Windows
Show the Formula Bar
Show the Toolbar
Take a Screenshot
What is the Formula Bar?
Name Manager	yes
Create Range Names From Selection
Delete Named Range(s)
Edit Named Ranges
Find the Location of a Named Range
Paste Range Names
Resolve a Name Conflict for a Named Range
Use the Name Box
What are Name Manager and Name Box?
Number Formats	yes
Add a Plus Sign for Positive
Add More Decimal Places
Add Percentage Style to a Number
Add Units to Numbers
Apply Accounting Number Format
Convert Date to Month and Year
Convert Decimal to Fraction
Display Fraction Without Reducing
Format Phone Numbers With Dashes
Format Time to Milliseconds
Get Rid of the Dollar Sign
Hide / Suppress Zeros
Limit Decimal Places
Make Negative Numbers Red
Mask Data Input
Move Decimal Places
Number Format in Millions
Number Format in Thousands (000s)
Pad Numbers (Add Leading Zeros)
Prevent Rounding
Remove Decimals
Remove Scientific Notation
Round Numbers Without a Formula
Stop Changing Numbers to Dates
Use Scientific Notation
Objects	yes
Add and Group Radio (Option) Buttons
Change the Color of an ActiveX Control Button
Circle Something
Create a Shape With Drawing Tools
Create a Slider Bar
Delete a Checkbox
Draw Lines
Embed a File
Insert a Digital Signature
Insert a Drop-Down Calendar With Date Picker Control
Insert a Logo
Insert a Shape
Insert a SmartArt Graphic
Insert a Text Box
Insert an Arrow
Insert Multiple Checkboxes
Insert Multiple Hyperlinks in One Cell
Link a Text Box
Make a Combo Box
Organize Sheets	yes
Add Multiple Worksheets
Copy a Spreadsheet
Copy Multiple Sheets to a New Workbook
Count Number of Worksheets
Delete Multiple Tabs
Delete Worksheets
Group and Ungroup Worksheets
Hide and Unhide Worksheets
Insert a New Tab
Merge Two Spreadsheets into One
Pull Data From Multiple Sheets and Consolidate
Rename a Worksheet
Select Multiple Sheets
Separate (Split) Sheets
View a List of Worksheet Tabs
View Two Sheets at Once
Outline	yes
Collapse an Outline to Show Just the Subtotals
Create an Outline
Create Auto Outline
Expand or Collapse Rows or Columns
Group Cells
Page Layout	yes
Add Element to Display Current Date in the Header
Change Orientation to Landscape
Define Print Titles on Every Page
Format the Header and Footer
Insert (or Remove) Headers and Footers
Make a Header Only on the First Page
Make a Sheet Fit on One Page
What is the Page Setup Function?
How to Create a Cover Page in Excel & Google Sheets
Pictures	yes
Drag and Drop Pictures
Insert a Background Image
Insert a Picture into a Cell
Insert a Picture into a Cell Automatically
Insert a Picture into the Header
Insert Pictures
Make a Picture Transparent
Remove All Pictures From a Workbook
Show a MouseOver Picture
Power Query	yes
How to Export Folder and Subfolder Structure
Import an HTML Table
Set Up Automatic Currency Conversion
Print	yes
Add a Page Border
Add a Watermark
Center Worksheets Horizontally and Vertically
Delete Unwanted or Blank Pages
Display & Print Formulas
Hide Page Breaks
Insert a Page Number
Insert and Remove Page Breaks
Make Graph Paper
Print a Background Image
Print All Tabs in a Workbook
Print Comments
Print Gridlines on a Blank Sheet
Print Only Selected Cells
Print Row Numbers and Column Headings
Print Selected Worksheets on One Page
Print Selected Worksheets Without Print Preview
Print Two Sheets on One Page
Remove Watermarks
Set the Print Area
Proofing	yes
Check Spelling
Disable Automatic Hyperlinks
Remove Hyperlinks
Stop AutoCorrect
Turn On or Change AutoCorrect
Protect	yes
Change a Read Only File
Hide Formulas
Lock a Sheet for Viewing
Lock Cells
Lock Column Width and Row Height
Lock Formatting
Mark a Workbook as Final
Password Protect a File
Protected View: Turn Off and Enable Editing
Remove a Password From a File
Unlock / Unprotect Cells
Unprotect a Workbook
Save As	yes
Change the Default Directory
Convert a CSV File to XLSX
Convert a Spreadsheet to a Delimited Text File
Convert an Excel File to Google Sheets
Convert to a Pipe Delimited Text File
Copy Entire Workbook
Create a Word Document
Embed Tables into HTML
Export a File to PDF
Import a Word Document
Open a Text (.txt) File
Open Old XLS Files
Rename a File
Save a File as CSV
Save a File as CSV With UTF-8 Encoding
Save a File to the Old Format (.xls)
Save a Macro-Enabled Workbook
Save a Table as an Image
Save File as Read-Only
Save Just One Sheet
Save Multiple Sheets as a PDF
Save Workbook as PDF
What is the Difference Between CSV Files and Excel Files?
How to Recover a Deleted Sheet in Excel & Google Sheets
Share Files	yes
Make a Shared Spreadsheet for Multiple Users
Track Changes
Unshare a Workbook
Use Document Inspector
Sort Data	yes
Advanced Sorting
Keep Rows Together During Sort
Put Things in Alphabetical Order
Random Sort
Reverse the Order of Data
Sort a Column Alphabetically
Sort and Ignore Blanks
Sort by Color (Highlighted Cells)
Sort by Last Name
Sort By Month
Sort By Number
Sort Dates in Chronological Order
Sort IP Addresses
Sort Multiple Columns
Sort Multiple Rows Horizontally
Sort Subtotals
Sort With a Custom List
Sort Without Duplicates
Undo a Sort
Symbols	yes
Add Bullet Points
Division Sign
Find and Replace Question Marks and Asterisks
Insert a Check Mark (Tick)
Insert Bitcoin Symbol
Insert Cent Symbol
Insert Cross Text Symbol
Insert Delta Symbol
Insert Euro Symbol
Insert Plus or Minus Symbol
Insert Pound Symbol
Insert Rupee Symbol
Insert Signs and Symbols
Insert Sum Symbol
Insert Yen Symbol
Star Symbol
What Do the Symbols (@,$,{, etc.) Mean in Formulas?
Tables	yes
Add a Column and Extend a Table
Add a Total or Subtotal Row to a Table
Compare Two Tables
Convert a Table to a Normal Range
Display Data With Banded Rows
Remove a Table or Table Formatting
Rename a Table
Rotate Data Tables (Row to Column)
Text to Columns	yes
Convert a String to a Date
Paste CSV Data into Columns
Separate Numbers / Values
Split a Cell into Two or More Columns
Split a Text Cell to Two or More Rows
Use a Line Break in Text to Columns
Use Text to Columns to Parse Data
View	yes
Close Header and Footer
Dark Mode
Enable and Disable Smart Tags
Get Rid of Dashed Print Area Lines
Hide and Unhide Rows and Columns
Hide and Unhide Workbooks
Hide Column and Row Headings
Hide Unused Cells
Hide Vertical and Horizontal Scroll Bars
Lock Columns
Lock the Top Row When Scrolling
Remove Page Number in Preview Mode
Return to Normal View
Show and Hide Gridlines
Show or Hide ScreenTips
Show the Ruler
Use Split Screen
View Full Screen
Zoom In or Out