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 
AutoFillyes
AutoFill Custom and Alphabetic Lists
AutoFill Dates or Months
AutoFill Numbers
AutoFill Numbers Without Dragging
Auto-Number Rows in a Column
Fill Column With Same Value
Fill Down or Fill Right
Fill Series Not Working
Make a List of Numbers
Turn Off AutoFill
Turn On AutoFill
Use Fill Handle
Use Flash Fill to Fill a Range
AutoSaveyes
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?
Calculationyes
Add Values to Cells and Columns
Anchor a Cell
Change the Signs of Values
Divide Cells and Columns
Keep Variable Cell Constant
Lock Cell in Formula
Multiply Cells and Columns
Stop Auto Calculation
Subtract Cells and Columns
Commentsyes
Copy Comments
Delete All Comments
Edit Comments
Insert a Comment or Note to a Cell
Show or Hide All Comments
Compareyes
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 Formattingyes
Apply Conditional Formatting Based on 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 Cell Value
Conditional Formatting Based on Date
Conditional Formatting Based on Formula
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 Not Equal
Conditional Formatting Tips for 2022
Conditional Formatting With Multiple Conditions
Copy Conditional Formatting
Edit Conditional Formatting
Formatting If Cell Contains Specific Text
Highlight a Cell if Specific Value Exists in Another Column
Highlight Blank Cells
Highlight Cells If
Highlight Cells That Begin or End With
Highlight Duplicate Rows
Highlight Duplicate Values
Highlight Entire Row
Highlight Every Other Line In Excel
Highlight Rows If
Highlight Rows That Contain
Highlight the Highest Value
If Formula to Set Cell Color
Insert Harvey Balls
Remove Conditional Formatting
Use Comparison Icon Sets
Use Conditional Formatting With Checkbox
Use Custom Icon Sets
Copy & Pasteyes
Command Cannot be Used on Multiple Selections
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 Cell Formatting
Copy Data From One Cell to Another Automatically
Copy Every Other Row
Copy Formula Down to Entire Column
Copy Number Not Formula
Copy, Cut, and Paste
Drag and Drop
Duplicate Rows
Excel Cannot Paste the Data Error
Freeze Random Numbers
Paste and Match Destination Formatting
Paste Horizontal Data Vertically
Paste Special
Rearrange Columns
Swap Cells
Swap or Move Columns
Transpose Rows to Columns
Data Validationyes
10 Data Validation Rules and Techniques
Add a ToolTip
Change Data Validation
Clear Data Validation
Copy Data Validation
Create an Input Message
Data Validation Based on Another Cell
Data Validation Not Working
Date and Time Data Validation Formats
Enable Error Alert
Find Restricted Values
Ignore Blanks in Data Validation
Restrict Cell Values
Set a Character Limit
Validate Phone Number Format
Databaseyes
Clean Up Data
Create a Searchable Database
Drop-Down Listyes
Add Drop-Down List With Color Formatting
Alphabetize a Drop-Down List
AutoComplete With Data Validation
Change 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 or No Drop Down
Create Drop Down Data Entry Form
Create Dynamic Drop Down From Table
Data Validation Unique Values
Drop-Down List With If Statement
Make Drop-Down Categories and Subcategories
Remove a Drop-Down List
Update a Drop-Down List
Duplicatesyes
Clear Duplicate Cells
Combine Duplicate Rows
Distinct (Unique) Values
Duplicates
Merge Lists Without Duplicates
Prevent Duplicate Entries
Remove Both Duplicates
Remove Duplicate Rows
Remove Unique Values
Show Only Duplicates
Emailyes
Email a Spreadsheet
Email Address Format Validation
Send Email With Formula
Send Mass Email From a List
Filtersyes
Advanced Filter
Apply Multiple Filters
Clear All Filters
Copy Filtered Data
Delete Filtered Rows
Filter by Color
Filter by Date
Filter Duplicate Values
Filter Merged Cells
Filter Rows
Filter Unique Values
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 & Selectyes
Deselect Cells
Fill Blank Cells With Value Above
Find and Delete Words
Find and Highlight
Find and Replace Multiple Values
Find and Replace With Wildcards
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
Forecastyes
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 Analysis
Enable Solver Add-In
Insert Line and Column Sparklines
Install the Data Analysis Toolpak for PC and Mac
Loading Solver Not Working
Quick Analysis Tool
Remove Solver
Solve for a Variable
Solver Encountered Error Value
Format Cellsyes
Add Border Lines
Alternate Row Color
Apply and Change Themes
Apply Cell Styles
AutoFit Columns
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
Remove Filll Color
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 Textyes
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 Auditingyes
#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 & Deleteyes
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 Highlighted Cells
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 Blank Row After Every Other Row
Insert Cells and Shift Cells Down
Insert Cells and Shift Cells Right
Insert Multiple Rows and Columns
Insert Timestamp
Reset the Last Cell
Linksyes
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
Miscellaneousyes
32-Bit Versus 64-Bit Office
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 Manageryes
Create Range Names From Selection
Delete Named Range
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 Formatsyes
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 Zeros
Limit Decimal Places
Make Negative Numbers Red
Mask Data Input
Move Decimal Places
Number Format in Millions
Number Format in Thousands
Pad Numbers With Leading Zeros
Prevent Rounding
Remove Decimals
Remove Scientific Notation
Round Numbers Without a Formula
Stop Changing Numbers to Dates
Use Scientific Notation
Objectsyes
Add and Group Radio Buttons
Add Gradient Fill to Shapes or Cells
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 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 Sheetsyes
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 Sheets
View a List of Worksheet Tabs
View Two Sheets at Once
Outlineyes
Collapse an Outline to Show Just the Subtotals
Create an Outline
Create Auto Outline
Expand or Collapse Rows or Columns
Group Cells
Page Layoutyes
Add Element to Display Current Date in the Header
Change Orientation to Landscape
Create Cover Page
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?
Picturesyes
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
Pivot Tableyes
Change Pivot Table Data Source
Create Pivot Table
Filter Pivot Table Values
Group Pivot Table by Date
Keep Pivot Table Formatting
Make a Pivot Table Chart
Refresh a Pivot Table
Power Queryyes
Export Folder and Subfolder Structure
Import an HTML Table
Import XML Files
Set Up Automatic Currency Conversion
Printyes
Add a Page Border
Add a Watermark
Center Worksheets Horizontally and Vertically
Delete Unwanted or Blank Pages
Display and Print Formulas
Hide Page Breaks
Insert a Page Number
Insert and Remove Page Breaks
Make Graph Paper
Print
Print a Background Image
Print a Chart or Graph
Print All Tabs in a Workbook
Print Comments
Print Gridlines on a Blank Sheet
Print Multiple Sheets
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
Proofingyes
Check Spelling
Disable Automatic Hyperlinks
Remove Hyperlinks
Stop AutoCorrect
Turn On or Change AutoCorrect
Protectyes
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
Protect Workbook Structure
Protected View: Turn Off and Enable Editing
Remove a Password From a File
Unlock Cells
Unprotect a Workbook
Save Asyes
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 File
Open Old XLS Files
Recover Deleted Sheet
Rename a File
Save a File as CSV
Save a File as CSV With UTF-8 Encoding
Save a File to the Old Format
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?
Share Filesyes
Make a Shared Spreadsheet for Multiple Users
Track Changes
Unshare a Workbook
Use Document Inspector
Sort Datayes
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 Last Name
Sort By Month
Sort By Number
Sort Data
Sort Dates in Chronological Order
Sort Highlighted Cells by Color
Sort IP Addresses
Sort Multiple Columns
Sort Multiple Rows Horizontally
Sort Subtotals
Sort With a Custom List
Sort Without Duplicates
Undo a Sort
Symbolsyes
Add Bullet Points
Division Sign
Find and Replace Question Marks and Asterisks
Insert a Check Mark
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 Mean in Formulas?
Tablesyes
Add a Column and Extend a Table
Add a Total or Subtotal Row to a Table
Add Rows to Table
Compare Two Tables
Convert a Table to a Normal Range
Create Table
Display Data With Banded Rows
Paste Excel Table Into Gmail
Remove a Table or Table Formatting
Rename a Table
Rotate Data Tables
Text to Columnsyes
Convert String to Date
Paste CSV Data Into Columns
Separate Numbers
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
Viewyes
Close Header and Footer
Dark Mode
Enable and Disable Smart Tags
Freeze and Unfreeze Panes
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