AutoMacro: The Ultimate VBA add-in for easy Excel Automation.

1000s of Excel / VBA Tutorials

All
VBA
How-To
Formulas
Charts
Shortcuts
VBA Code Examples
In Depth Tutorialsyes
PowerPoint VBA Macro Examples & Tutorial
Sorting
Find and Find and Replace
Cheat Sheets – Commands & Syntax Lists
Word Macro Examples & VBA Tutorial
Basicsyes
If, ElseIf, Else (Ultimate Guide to If Statements)
Value Paste & PasteSpecial
VBA – Cut, Copy, Paste from a Macro
VBA Save File – 20 Easy Examples
VBA Coding Conceptsyes
Commenting in VBA
Creating (Custom) User Defined Functions (UDFs)
Excel VBA – Named Ranges and Programming
How to Rename or Delete a Module or Form
Opening & Using the Visual Basic Editor (VBE) in Excel
Return a Value from a Sub Procedure
Shortcuts for the Visual Basic Editor
Call Function from a Sub
Function – Call, Return Value, & Parameters
Object Model
On Error – Error Handling Best Practices
Private vs Public Procedures (Subs & Functions)
Select Case Statement
Sub Procedure
What is a VBA Module?
What is a VBA Project? (And How to Edit)
VBA – Language References
VBA – Modify the Right-Click Menu to Call a Macro
VBA – Run a Macro from a Macro
VBA – Run a Macro Line by Line
VBA – Wait, Delay, Create a Timer While Running Code
Debug.Print and Immediate Window
VBA DoEvents
VBA End
VBA Exit Sub or Function
VBA On Error Exit Sub
VBA Option Explicit
VBA: Improve Speed & Other Best Practices
Loopsyes
VBA For Loop – Loop Through a Range of Cells
Loops - Ultimate Guide
For Each Examples (Quick Reference)
VBA Exit For
VBA Exit Loop
While Wend Loop
Ranges and Cellsyes
Ranges and Cells - Ultimate Guide
Formulas - Ultimate Guide
Union and Intersect
Resize Range
VBA – Autofill with a Macro
VBA – Change a Cell;s Background Color
VBA – ClearFormats – Remove Formatting For Each Cell In Range
VBA – Conditional Formatting – Highlight Duplicates in a Range
VBA – Delete All Named Ranges
VBA – Delete Hyperlinks
VBA – Excel’s Color Scheme
VBA – Find the Maximum Value For Each Column in a Range
VBA – Format Painter
VBA – Get the Active Cell’s Column or Row
VBA – Resize an Object to a Range Size
VBA – Select All Cells
VBA – Sum by Color
VBA – Test if Selection is a Range or Object
Cell Value – Get, Set, or Change
ClearContents / Clear Cells
Copy Destination (Copy Range to Another Sheet)
Current Region
Dynamic Range
VBA Function – Populating a Range With Random Values
Highlight Cell
VBA Merge Cells & Unmerge Cells
Offset Range or Cell
Range / Cell Address
Range.End (xlDown, xlUp, xlToRight, xlToLeft)
VBA Routine to return Column Letter of Cell
VBA Select Range or Cells
VBA Union – Join Multiple Ranges
VBA Wrap Text
Activecell Offset VBA
Rows & Columnsyes
Select (and work with) Entire Rows & Columns
Delete or Insert Rows Based on Cell Value
Delete Rows that Meet Certain Criteria in VBA
Hide / Unhide Columns & Rows
Unhide All Rows / Columns
VBA – Autofit Columns
VBA – Count the rows in a Selection
VBA – Delete Blank Rows
VBA – Select Every 5th Row
VBA – Select Non Contiguous Columns
Copy / Paste Rows & Columns
Delete Entire Row or Column
Group Rows & Columns
Insert Row or Column
VBA Set Column Width or Row Height
VBA Used Range – Count Number of Used Rows or Columns
Sheetsyes
Sheets – The Ultimate Guide
VBA – Count the Sheets in a Workbook
VBA – Create a Hyperlink Menu of Worksheets
VBA – Hide (or Unhide) a Worksheet
VBA – Hide WorkSheet Tabs
VBA – How to Use Worksheet Functions (and a Full List)
VBA – Loop Through all Worksheets with For Each
VBA – Macro to List all Sheets in a Workbook
Send Worksheets by Email as Separate Workbooks
VBA – Sort Sheets Alphabetically
VBA – Unhide All Worksheets
Clear Entire Sheet
VBA Copy Worksheet
VBA Delete or Clear Worksheet
Get Sheet Name / Rename Sheet
VBA Protect / Unprotect Worksheets
VBA Routine to Add and Name Worksheets
VBA Select Sheet, Activate Sheet, and Get Activesheet
Worksheet Range
VBA: Set the Default Sheet When a WorkBook Opens
How to Sort Tabs / Worksheets with a VBA Macro
Workbooksyes
Create New Workbook (Workbooks.Add)
Activate Workbook
ActiveWorkbook vs. ThisWorkbook
VBA Open / Close Workbook
Workbook Name (Get, Set, without Extension)
Workbook Protection (Password Protect / Unprotect)
Working with Workbooks (The Workbook Object)
Arraysyes
Arrays
VBA – Array Examples
Dictionary Objects
Collections
ArrayList
Array of Objects
Variant Array
Dynamic Array (Redim & Redim Preserve)
Declare & Initilize String Array
Declare (Dim), Create, and Initialize Array Variable
Array Length / Size
Assign Range to Array
Clear Array – Erase Function
Loop Through Array / For Each Item in Array
Sort Array
Transpose Array
Search for (Find) Value in Array
Populate Array with Unique Values from Column
Remove Duplicates From Array
Function Return Array
Multi-Dimensional Array (2D Arrays)
Output (Print) Array to Range
Errorsyes
Try Catch Errors
Object Required Error
Syntax Error VBA
Type Mismatch (Run-time Error 13)
Automation Error
Compile Error
Error 1004 – Application-Defined or Object-Defined Error
Ignore Error
On Error Resume Next or Goto 0
Out of Memory Error
Throw / Raise Error – Err.Raise – Custom Error Msg
Can’t Find Project or Library – VBA Compile Error
Compile Error in Hidden Module – VBA
Fix Error 400
Fix VBA Error 438 – Object Doesn’t Support Property or Method
Fix VBA Run Time Error 32809
Fix VBA Run Time Error 57121
Variablesyes
Dim VBA – Declare Variable
VBA – Determine a Variable’s Underlying Type
VBA Array Variables
Boolean Data Type (Dim Variable)
Constant
VBA Constant Expression Required
VBA Data Types – Variables and Constants
VBA Date Variable
Double Data Type (Dim Variable)
VBA Global Variable
Int / Integer Data Type (Dim Variable)
Long Data Type (Dim Variable)
VBA Public Variable – Use a Variable in Multiple Modules or Subs
VBA Range Object Variables (Dim / Set Range)
Set Object Variables – Workbooks, Worksheets, & More
Single Data Type (Dim Variable)
VBA Static Variable
String Data Type (Dim Variable)
Variant Data Type (Dim Variable)
Settingsyes
Get and Change User Name with VBA
VBA – Display Status Bar Message
VBA – Modify the titlebar
VBA – Prevent warning messages from a macro
VBA – Turn Automatic Calculations Off (or On)
VBA – Turn Formula Bar Off (or On)
VBA – Turn off Screen Updating
VBA Turn Scroll Bars On or Off
Text and Stringsyes
Split String into Cells
Prevent VBA Case Sensitive – Option Compare Text
VBA – Extract Number From String
VBA – Find the nth Word in a String of Text
VBA – Line Break or Line Feed in a Message Box
VBA – Loop Through a String
VBA – Remove Characters from Left or Right Side of Variable Length String
VBA – Reverse a String of Text
VBA Char / Chr Function
VBA Compare Strings – StrComp
VBA Comparison Operators – Not Equal to & More
VBA Concatenate Text Strings Together (& – Ampersand)
Convert Integer (Number) to String
Convert String to Date
Convert Text String to Number
VBA Function To Calculate Number of Words in a String
VBA INSTR – Find Text in a String
VBA Like Operator
VBA Logical Operators – OR, AND, XOR, NOT, IS, & LIKE
VBA New Line / Carriage Return
Parse String
VBA Space Function – Add Spaces to a String
VBA Strings and Substrings Functions
Text to Columns
Trim, LTrim, and RTrim Functions – Remove Spaces From Text
VBA Upper, Lower, and Proper Case – Case Functions
Viewyes
VBA – ActiveWindow.WindowState
VBA – Display (or Exit) Fullscreen
VBA – Freeze Panes
VBA – Hide Excel (The Entire Application)
VBA – Page Break Preview Mode On or Off
VBA – Scroll Vertically and Scroll Horizontally
VBA – Zoom – Fit Selection
VBA – Zoom in and Out of Worksheets
Filesyes
FileSystem Object
Move Files with VBA FileSystemObject (MoveFile)
VBA – Convert Excel to CSV (Comma Delimited Text File)
Create Text File with CreateTextFile
VBA – Delete files from the hard drive
VBA – Export Range to CSV (Delimited Text File)
Read Text File into String
Read Text File Line by Line
VBA – Test if Workbook is Open by Workbook Name
Validate Filenames
Write to Text File
VBA Check if File or Folder Exists
Combine Multiple Excel Files into One Workbook
Copy a Folder with CopyFolder (FSO)
VBA Copy File / Workbook
VBA Delete File / Workbook
Delete Folder with DeleteFolder (FSO)
FileCopy Statement
Get File Name with GetFileName (FSO)
GetFolder & GetFile (Get File & Folder Properties)
VBA List Files in Folder
VBA Open File Dialog Box
Open Text File with OpenTextFile
Read Text File (Read, Parse, and Import)
VBA Rename File
Shapes, Charts, Objectsyes
VBA AutoFilter
VBA Guide For Charts and Graphs
VBA Guide to Pivot Tables
VBA Objects
Excel Add VBA Button
How to use VBA Controls in UserForms
VBA – Delete All Autoshapes
VBA – Programmatically Draw Boxes with Code
Listbox – Selected Item
VBA Tables and ListObjects
TextBox
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Eventsyes
Events
VBA – Exit Without Save Prompt
VBA – Force a Workbook to Save Before Close
VBA – Run a Macro when Excel Closes – Auto_Close
VBA – Run a macro when Excel starts
VBA Worksheet Change Event – Run a Macro When a Cell Changes
VBA: Show or load a form when Excel starts
UserForms, MsgBoxes & Controlsyes
VBA – Yes No Message Box (Msgbox)
VBA InputBox – Get Input from a User
Creating VBA Userforms
Option Button Excel VBA
Spin button Excel VBA
VBA Checkbox
VBA ComboBox
VBA Listbox
VBA Open or Close UserForm
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Misc.yes
Clear Clipboard
Pause (Break / Stop) & Resume a Macro
Save As (Print) to PDF
ColorIndex Codes List
Format Numbers
How to Install (or Uninstall) a VBA add-in (.xlam file) for Microsoft Excel
How to use VBA Structures
Removing Duplicate Values in Excel VBA
Speed up your VBA Code
VBA – Add or Remove Cell Comments
All Operators Explained
Check if Office App is Running
Dialog Box
Get Today’s Date (Current Date)
Pivot Table Filter
Refresh Pivot Table / All Pivot Tables
Refresh Pivot Table when Data Changes
Using Goal Seek in VBA
Using the Analysis ToolPak in VBA
Advanced Filter
Application.CutCopyMode = False
Calculate – Now, Workbook, Worksheet, or Range
Call / Run Sub From another Subroutine
Copy to Clipboard
Find Value in Column
GoTo a Line Label
IIF Function
Input Statement
is Nothing
Is Operator
Line Input Statement
Multiple (Nested) If Statements
Project Password / Protect code
Reference
Solver
Wait & Sleep Functions – Pause / Delay Code
Wildcards
With / End With Statement
What is the Difference Between VB and VBA?
Personal Macro Workbook
VBA – Turn Off AutoFilter / Clear Filters
VBA Drop Down List (Data Validation)
VBA Hyperlinks
VBA Random Number
Round, RoundUp, and RoundDown
Second Function
Split Function – Split String of Text into Array
Sqr Function
Sum Function (Ranges, Columns, & More)
SUMIF and SUMIFS Functions
Switch Statement
Text Function
Time Function
Time Functions
Timer Function
TimeSerial Function
TimeValue Function
TypeName
TypeOf
UBound & LBound Functions
Val Function
VarType
Weekday Function
WeekdayName Function
Write Statement
Year Function
Vlookup – Multiple Results with VBA
Advanced Programmingyes
Advanced VBA Programming Examples
Early Late Binding
Class Constructor
Class Module Tutorial & Examples
Matrix – Create & More
Shell
VBA – Convert Matrix to Vector
VBA – Programming the VBE (Visual Basic Editor)
VBA Regex
VBA SendKeys
VBA Timer Function
Internetyes
Automate Internet Explorer (IE) Using VBA
VBA – Consume Soap Web Services
VBA – WinHttpRequest with Login and Password – Misc Bloglines
Send Emails from Excel through Gmail
Send Emails from Excel through Outlook
Accessyes
Access VBA Recordsets – Open, Count, Loop and More
Access VBA – Import / Export Excel – Query, Report, Table, and Forms
Access VBA – Open / Close Form
Access VBA – Open Excel File
Access VBA Database – Open, Connect, Login, & More
Access VBA Findfirst
Access VBA Me
Access VBA Query
Access VBA Recordsets – Open, Count, Loop and More
Access VBA Refresh Form
Access VBA Reports – Print, Export, Filter
Access VBA SQL Examples
Access VBA Tables – Update, Count, Delete, Create, Rename, Export
Wordyes
Word VBA – Macro to Open Word Document
Word VBA Macros – Find, Find & Replace
Word VBA Macros – SaveAs (PDF or New File Name)
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 the Same Value
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
Calculationyes
Add Values to Cells and Columns
Anchor a Cell
Change the Signs of Values
Divide Cells and Columns
Imaginary Numbers
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
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
Copy a Cell From Another Sheet
Copy a Page
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 Columns
Copy and Paste Formulas Between Excel and Google Sheets
Copy and Paste Hyperlinks
Copy and Paste Merged 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 Value to Another Cell With Formula
Drag and Drop
Duplicate Rows
Freeze Random Numbers
Paste and Match Destination Formatting
Paste Horizontal Data Vertically
Paste Into Filtered Cells
Rearrange Columns
Replace Formula With Value
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
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 Populates Another Cell
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
Count Duplicate Values Only Once
Distinct (Unique) Values
Duplicates
Find Duplicate Values
Merge Lists Without Duplicates
Paste Unique Values
Prevent Duplicate Entries
Remove Both Duplicates
Remove Duplicate Rows
Remove Unique Values
Show Only Duplicates
Emailyes
Copy Email Addresses to Outlook
Email a Spreadsheet
Email Address Format Validation
Embed a File
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
Save Filtered Data
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
Highlight Blank Cells
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
Quick Analysis Tool
Remove Solver
Solve for a Variable
Format Cellsyes
Add Border Lines
Alternate Row Color
Apply and Change Themes
Apply Cell Styles
Apply the Calculation Style
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
Highlight Columns
Highlight Rows
Make All Rows and Columns the Same Height and Width
Make Merged Cells the Same Size
Merge and Center Cells
Merge Multiple Cells
Outline Cells
Remove Fill 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
Line Spacing
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
Word Wrap
Write Paragraphs
Write Text Vertically
X Out a Cell
Formula Auditingyes
Allow Circular References
Camera Tool
Find a Circular Reference Error
Formula View Mode
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 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 After Every Other Row
Insert Cells and Shift Cells Down
Insert Cells and Shift Cells Right
Insert Multiple Rows and Columns
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
Delete a File
Find the Last Saved Date
Find What Version of Excel You Have
Generate All Permutations
Insert Timestamp
Make a File Smaller
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
Dynamic Named Range Based on Cell
Edit Named Ranges
Find the Location of 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
Insert Dates
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
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
Insert Toggle Button
Link a Text Box
Make a Combo Box
Make a Number Line
Select Objects
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 Multiple Workbooks Into One
Merge Two Spreadsheets Into One
Move Sheets
Pull Data From Multiple Sheets and Consolidate
Rename a Worksheet
Select Multiple Sheets
Separate Sheets
View a List of Worksheet Tabs
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
Scale to Fit Page Size
Set Custom Page Size
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
Add Calculated Field to Pivot Table
Advanced Pivot Table Techniques
Change Pivot Table Data Source
Count Unique Values With Pivot Table
Create Pivot Table
Delete Pivot Table
Filter Pivot Table Values
Get a Count in Pivot Table
Group Pivot Table by Date
Keep Pivot Table Formatting
Make a Pivot Table Chart
Pivot Table Slicers
Pivot Table Sorting Guide
Refresh a Pivot Table
Remove Field From 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
Change Print Area
Clear Print Area
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 Preview
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
Show 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 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
Shortcutsyes
Go To Cell, Row, or Column Shortcuts
Sort Datayes
Advanced Sorting
Keep Rows Together During Sort
Put Things in Alphabetical Order
Random Sort
Reverse the Order of Data
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
Escape Double Quotes
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
Center Table
Change Table Style
Compare Two Tables
Convert a Table to a Normal Range
Convert Data to Table
Copy and Paste Table
Copy Table From Word
Copy to Word and Fit Page
Create Floating Table
Create Lookup Table
Create Table
Display Data With Banded Rows
Flip a Table
Link Tables
Make Columns Sortable
Move Table
Paste Excel Table Into Gmail
Remove a Table or Table Formatting
Rename a Table
Rotate Data Tables
Table Tools
Types of 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
Troubleshootingyes
#REF Error
Advance Filter Not Working
All the Merged Cells Need to Be the Same Size
Command Cannot be Used on Multiple Selections
Data Validation Not Working
Excel Borders Not Printing
Excel Cannot Open or Save Any More Documents
Excel Cannot Paste the Data Error
Excel Not Sorting Numbers Correctly
Fill Series Not Working
Find Broken Links
Fix Excel Find Not Working
Fix the #NAME? Error
Formula Won't Calculate
Get Rid of Green Triangle
Get Rid of the Red Triangle in Google Sheets
Loading Solver Not Working
Printing Problems
Printing Too Small
Resolve a Name Conflict for a Named Range
Solver Encountered Error Value
The Pivot Table Field Name Is Not Valid
There Isn't Enough Memory to Complete This Action
Unlock a File Locked for Editing by Me
Waiting for OLE Action Error
What to Do When Excel Keeps Crashing or Freezing
Why is AutoSave Not Working or Grayed Out?
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
View Header
View Two Sheets at Once
Zoom In or Out
Excel Built-in Chartsyes
Column Charts: Stacked, Clustered
Bar Charts: Stacked, Clustered
Area Charts
Line Charts
Candlestick Charts
Pareto Chart
Waterfall Chart
Sales Funnel Chart
Advanced Custom Chartsyes
Gauge / Dial Chart
Tornado Chart
Burndown Chart
Step Chart
Thermometer Chart
Quadrant Chart
Bullet Chart
Ogive Chart
Bell Curve / Normal Distribution Plot
Stem & Leaf Chart
Mekko Chart
Venn Diagram
Polar Plot
Venn Diagram
Panel Chart
Timeline Chart
Progress Charts
Sales Funnel Chart
Floating Bar Chart
Forest Plot
Frequency Polygon
Arrow Chart
Percentage Graph
Time Series Graph
Percentage Change Chart
Show Percentage in Pie Chart
Dot Plot
Q-Q Plot
Log-Log Plot
Normal Probability Plot
Charts Tips & Tricksyes
Add or Move Data Labels
Add Data Series
Add Average Line
Add Data Points
Add Error Bars
Add Gridlines
Add Line of Best Fit
Add Title
Break Chart Axis
Calculate Area Under Curve
Plot Residuals
Change Bar Chart Width
Change Chart Colors
Chart Axis Text Instead of Numbers
Copy Chart Format
Create Chart with Date or Time
Curve Fitting
Export Chart as PDF
Add Axis Labels
Add Secondary Axis
Change Chart Series Name
Change Horizontal Axis Values
Create Chart in a Cell
Graph an Equation or Function
Overlay Two Graphs
Plot Multiple Lines
Rotate Pie Chart
Switch X and Y Axis
Insert Textbox
Move Chart to New Sheet
Move Horizontal Axis to Bottom
Move Vertical Axis to Left
Remove Gridlines
Reverse a Chart
Rotate a Chart
Rounded Corners or Shadows
Create, Save, & Use Excel Chart Templates
Dynamic Chart Titles
Chart Conditional Formatting
Dynamic Chart Range
Animated Charts
Interactive / Dynamic Charts
Dateyes
Add (Subtract) Days to a Date
Concatenate Dates
Convert Date to Number
Convert Date to Text
Month Name to Number
Create Date Range from Dates
Day Number of Year
Month Name from Date
First Day of Month
Add (Subtract) Weeks to a Date
If Functions with Dates
Max Date
Number of Days Between Dates
Number of Days in a Month
Number of Weeks Between Dates
Number of Years Between Dates
Split Date & Time into Separate Cells
Countdown Remaining Days
Insert Dates
Random Date Generator
Using Dynamic Ranges - Year to Date Values
Add (Subtract) Years to a Date
Date Formula Examples
Extract Day from Date
Get Day Name from Date
Count Days Left in Month / Year
Count Workdays Left in Month / Year
Get Last Day of Month
Last Business Day of Month / Year
Number of Work / Business Days in Month
Weekday Abbreviations
Auto Populate Dates
Number of Months Between Dates
Quarter from a Date
Years of Service
Change Date Format
Compare Dates
Timeyes
Add (Subtract) Hours to Time
Add (Subtract) Minutes to Time
Add (Subtract) Seconds to Time
Add Up time (Total Time)
Time Differences
Change Time Format
Convert Minutes to Hours
Convert Time to Decimal
Convert Time to Hours
Convert Time to Minutes
Convert Time to Seconds
Military Time
Round Time to Nearest 15 Minutes
Overtime Calculator
Number of Hours Between Times
Convert Seconds to Minutes, Hours, or Time
Count Hours Worked
Time Differences
Time Format - Show Minutes Seconds
Textyes
Add Commas to Cells
Get First Word from Text
Capitalize First Letter
Clean & Format Phone #s
Remove Extra Trailing / Leading Spaces
Add Spaces to Cell
Assign Number Value to Text
Combine Cells with Comma
Combine First and Last Names
Convert Text String to Date
Convert Text to Number
Extract Text From Cell
Get Last Word
Remove Unwated Characters
Extract Text Before or After Character
How to Split Text String by Space, Comma, & More
Remove Special Characters
Remove First Characters from Left
Substitute Multiple Values
Switch First & Last Names w/ Commas
Remove Specific Text from a Cell
Extract Text Between Characters (Ex. Parenthesis)
Add Leading Zeros to a Number
Remove Line Breaks from Text
Remove all Numbers from Text
Reverse Text
Remove Non-Numeric Characters
Remove Last Character(s) From Right
Separate First and Last Names
Separate Text & Numbers
Roundyes
Round Formulas
Round Price to Nearest Dollar or Cent
Round to Nearest 10, 100, or 1000
Round to Nearest 5 or .5
Round Percentages
Round to Significant Figures
Countyes
Count Blank and Non-blank Cells
Count Cells Between Two Numbers
Count Cells not Equal to
Count if Cells are in Range
Count Times Word Appears in Cell
Count Words in Cell
Count Specific Characters in Column
Count Total Number of Characters in Column
Count Cells that Equal one of two Results
Count Cells that do not Contain
Count Cells that Contain Specific Text
Count Unique Values in Range
Countif - Multiple Criteria
Count Total Number of Cells in Range
Count Cells with Any Text
Count Total Cells in a Table
Lookupyes
Two Dimensional VLOOKUP
VLOOKUP Simple Example
Vlookup - Multiple Matches
Case Sensitive Lookup
Case Sensitive VLOOKUP
Sum if - VLOOKUP
Case Sensitive Lookup
Case Sensitive VLOOKUP
Find Duplicates w/ VLOOKUP or MATCH
INDEX MATCH MATCH
Lookup - Return Cell Address (Not Value)
Lookup Last Value in Column or Row
Reverse VLOOKUP (Right to Left)
Risk Score Bucket with VLOOKUP
Sum with a VLOOKUP Function
VLOOKUP & INDIRECT
VLOOKUP Concatenate
VLOOKUP Contains (Partial Match)
17 Reasons Why Your XLOOKUP is Not Working
Double (Nested) XLOOKUP - Dynamic Columns
IFERROR (& IFNA) XLOOKUP
Lookup Min / Max Value
Nested VLOOKUP
Top 11 Alternatives to VLOOKUP (Updated 2022!)
VLOOKUP – Dynamic Column Reference
VLOOKUP – Fix #N/A Error
VLOOKUP – Multiple Sheets at Once
VLOOKUP & HLOOKUP Combined
VLOOKUP & MATCH Combined
VLOOKUP Between Worksheets or Spreadsheets
VLOOKUP Duplicate Values
VLOOKUP Letter Grades
VLOOKUP Return Multiple Columns
VLOOKUP Returns 0? Return Blank Instead
VLOOKUP w/o #N/A Error
XLOOKUP Multiple Sheets at Once
XLOOKUP Between Worksheets or Spreadsheets
XLOOKUP by Date
XLOOKUP Duplicate Values
XLOOKUP Multiple Criteria
XLOOKUP Return Multiple Columns
XLOOKUP Returns 0? Return Blank Instead
XLOOKUP Text
XLOOKUP with IF
XLOOKUP With If Statement
Misc.yes
Sort Multiple Columns
Use Cell Value in Formula
Percentage Change Between Numbers
Percentage Breakdown
Rank Values
Add Spaces to Cell
CAGR Formula
Average Time
Decimal Part of Number
Integer Part of a Number
Compare Items in a List
Dealing with NA() Errors
Get Worksheet Name
Wildcard Characters
Hyperlink to Current Folder
Compound Interest Formula
Percentage Increase
Create Random Groups
Sort with the Small and Large Functions
Non-volatile Function Alternatives
Decrease a Number by a Percentage
Calculate Percent Variance
Profit Margin Calculator
Convert Column Number to Letter
Get Full Address of Named Range
Insert File Name
Insert Path
Latitute / Longitude Functions
Replace Negative Values
Reverse List Range
Convert State Name to Abbreviation
Create Dynamic Hyperlinks
Custom Sort List with Formula
Data Validation - Custom Formulas
Dynamic Sheet Reference (INDIRECT)
Reference Cell in Another Sheet or Workbook
Get Cell Value by Address
Get Worksheet Name
Increment Cell Reference
List Sheet Names
List Skipped Numbers in Sequence
Return Address of Max Value in Range
Search by Keywords
Select Every Other (or Every nth) Row
Basicsyes
Cell Reference Basics - A1, R1C1, 3d, etc.
Add Up (Sum) Entire Column or Row
Into to Dynamic Array Formulas
Conversionsyes
Convert Time Zones
Convert Celsius to Fahrenheit
Convert Pounds to Kilograms
Convert Time to Unix Time
Convert Feet to Meters
Convert Centimeters to Inches
Convert Kilometers to Miles
Convert Inches to Feet
Convert Date to Julian Format
Convert Column Letter to Number
Testsyes
Test if a Range Contains any Text
Test if any Cell in Range is Number
Test if a Cell Contains a Specific Value
Test if Cell Contains Any Number
Test if Cell Contains Specific Number
Test if Cell is Number or Text
Ifyes
Percentile If
Subtotal If
Sumproduct If
Large If and Small If
Median If
Concatentate If
Max If
Rank If
TEXTJOIN If
Sumyes
Sum if - Begins With / Ends With
Sum if - Month or Year to Date
Sum if - By Year
Sum if - Blank / Non-Blank
Sum if - Horizontal Sum
Count / Sum If - Cell Color
INDIRECT Sum
Sum If - Across Multiple Sheets
Sum If - By Month
Sum If - Cells Not Equal To
Sum If - Not Blank
Sum if - Between Values
Sum If - Week Number
Sum Text
Sum if - By Category or Group
Sum if - Cell Contains Specific Text (Wildcards)
Sum if - Date Rnage
Sum if - Dates Equal
Sum if - Day of Week
Sum if - Greater Than
Sum if - Less Than
Averageyes
Average Non-Zero Values
Average If - Not Blank
Average - Ignore 0
Average - Ignore Errors
Mathyes
Multiplication Table
Cube Roots
nth Roots
Square Numbers
Square Roots
Calculationsyes
Calculate a Ratio
Calculate Age
Calculate Loan Payments
GPA Formula
Calculate VAT Tax
How to Grade Formulas
Findyes
Find a Number in a Column / Workbook
Find Most Frequent Numbers
Find Smallest n Values
Find nth Occurance of Character in Text
Find and Extract Number from String
Find Earliest or Latest Date Based on Criteria
Find First Cell with Any Value
Find Last Row
Find Last Row with Data
Find Missing Values
Find Largest n Values
Most Frequent Number
Conditional Formattingyes
Conditional Format - Dates & Times
Conditional Format - Highlight Blank Cells
New Functions
UNIQUEReturns a list of unique values in a list or range
XLOOKUPReplaces VLOOKUP, HLOOKUP, and INDEX / MATCH
Logicalyes
ANDChecks whether all conditions are met. TRUE/FALSE
IFIf condition is met, do something, if not, do something else.
IFERRORIf result is an error then do something else.
NOTChanges TRUE to FALSE and FALSE to TRUE.
ORChecks whether any conditions are met. TRUE/FALSE
XORChecks whether one and only one condition is met. TRUE/FALSE
Lookup & Referenceyes
FALSEThe logical value: FALSE.
TRUEThe logical value: TRUE.
ADDRESSReturns a cell address as text.
AREASReturns the number of areas in a reference.
CHOOSEChooses a value from a list based on it's position number.
COLUMNReturns the column number of a cell reference.
COLUMNSReturns the number of columns in an array.
HLOOKUPLookup a value in the first row and return a value.
HYPERLINKCreates a clickable link.
INDEXReturns a value based on it's column and row numbers.
INDIRECTCreates a cell reference from text.
LOOKUPLooks up values either horizontally or vertically.
MATCHSearches for a value in a list and returns its position.
OFFSETCreates a reference offset from a starting point.
ROWReturns the row number of a cell reference.
ROWSReturns the number of rows in an array.
TRANSPOSEFlips the oriention of a range of cells.
VLOOKUPLookup a value in the first column and return a value.
Date & Timeyes
DATEReturns a date from year, month, and day.
DATEDIFNumber of days, months or years between two dates.
DATEVALUEConverts a date stored as text into a valid date
DAYReturns the day as a number (1-31).
DAYSReturns the number of days between two dates.
DAYS360Returns days between 2 dates in a 360 day year.
EDATEReturns a date, n months away from a start date.
EOMONTHReturns the last day of the month, n months away date.
HOURReturns the hour as a number (0-23).
MINUTEReturns the minute as a number (0-59).
MONTHReturns the month as a number (1-12).
NETWORKDAYSNumber of working days between 2 dates.
NETWORKDAYS.INTLWorking days between 2 dates, custom weekends.
NOWReturns the current date and time.
SECONDReturns the second as a number (0-59)
TIMEReturns the time from a hour, minute, and second.
TIMEVALUEConverts a time stored as text into a valid time.
TODAYReturns the current date.
WEEKDAYReturns the day of the week as a number (1-7).
WEEKNUMReturns the week number in a year (1-52).
WORKDAYThe date n working days from a date.
WORKDAY.INTLThe date n working days from a date, custom weekends.
YEARReturns the year.
YEARFRACReturns the fraction of a year between 2 dates.
Engineeringyes
CONVERTConvert number from one unit to another.
Financialyes
FVCalculates the future value.
PVCalculates the present value.
NPERCalculates the total number of payment periods.
PMTCalculates the payment amount.
RATECalculates the interest Rate.
NPVCalculates the net present value.
IRRThe internal rate of return for a set of periodic CFs.
XIRRThe internal rate of return for a set of non-periodic CFs.
PRICECalculates the price of a bond.
YIELDCalculates the bond yield.
INTRATEThe interest rate of a fully invested security.
Informationyes
CELLReturns information about a cell.
ERROR.TYPEReturns a value representing the cell error.
ISBLANKTest if cell is blank. TRUE/FALSE
ISERRTest if cell value is an error, ignores #N/A. TRUE/FALSE
ISERRORTest if cell value is an error. TRUE/FALSE
ISEVENTest if cell value is even. TRUE/FALSE
ISFORMULATest if cell is a formula. TRUE/FALSE
ISLOGICALTest if cell is logical (TRUE or FALSE). TRUE/FALSE
ISNATest if cell value is #N/A. TRUE/FALSE
ISNONTEXTTest if cell is not text (blank cells are not text). TRUE/FALSE
ISNUMBERTest if cell is a number. TRUE/FALSE
ISODDTest if cell value is odd. TRUE/FALSE
ISREFTest if cell value is a reference. TRUE/FALSE
ISTEXTTest if cell is text. TRUE/FALSE
NConverts a value to a number.
NAReturns the error: #N/A.
TYPEReturns the type of value in a cell.
Mathyes
ABSCalculates the absolute value of a number.
AGGREGATEDefine and perform calculations for a database or a list.
CEILINGRounds a number up, to the nearest specified multiple.
COSReturns the cosine of an angle.
DEGREESConverts radians to degrees.
DSUMSums database records that meet certain criteria.
EVENRounds to the nearest even integer.
EXPCalculates the exponential value for a given number.
FACTReturns the factorial.
FLOORRounds a number down, to the nearest specified multiple.
GCDReturns the greatest common divisor.
INTRounds a number down to the nearest integer.
LCMReturns the least common multiple.
LNReturns the natural logarithm of a number.
LOGReturns the logarithm of a number to a specified base.
LOG10Returns the base-10 logarithm of a number.
MODReturns the remainder after dividing.
MROUNDRounds a number to a specified multiple.
ODDRounds to the nearest odd integer.
PIThe value of PI.
POWERCalculates a number raised to a power.
PRODUCTMultiplies an array of numbers.
QUOTIENTReturns the integer result of division.
RADIANSConverts an angle into radians.
RANDCalculates a random number between 0 and 1.
RANDBETWEENCalculates a random number between two numbers.
ROUNDRounds a number to a specified number of digits.
ROUNDDOWNRounds a number down (towards zero).
ROUNDUPRounds a number up (away from zero).
SIGNReturns the sign of a number.
SINReturns the sine of an angle.
SQRTCalculates the square root of a number.
SUBTOTALReturns a summary statistic for a series of data.
SUMAdds numbers together.
SUMIFSums numbers that meet a criteria.
SUMIFSSums numbers that meet multiple criteria.
SUMPRODUCTMultiplies arrays of numbers and sums the resultant array.
TANReturns the tangent of an angle.
TRUNCTruncates a number to a specific number of digits.
Statsyes
AVERAGEAverages numbers.
AVERAGEAAverages numbers. Includes text & FALSE =0, TRUE =1.
AVERAGEIFAverages numbers that meet a criteria.
AVERAGEIFSAverages numbers that meet multiple criteria.
CORRELCalculates the correlation of two series.
COUNTCounts cells that contain a number.
COUNTACount cells that are non-blank.
COUNTBLANKCounts cells that are blank.
COUNTIFCounts cells that meet a criteria.
COUNTIFSCounts cells that meet multiple criteria.
FORECASTPredict future y-values from linear trend line.
FREQUENCYCounts values that fall within specified ranges.
GROWTHCalculates Y values based on exponential growth.
INTERCEPTCalculates the Y intercept for a best-fit line.
LARGEReturns the kth largest value.
LINESTReturns statistics about a trendline.
MAXReturns the largest number.
MEDIANReturns the median number.
MINReturns the smallest number.
MODEReturns the most common number.
PERCENTILEReturns the kth percentile.
PERCENTILE.INCReturns the kth percentile. Where k is inclusive.
PERCENTILE.EXCReturns the kth percentile. Where k is exclusive.
QUARTILEReturns the specified quartile value.
QUARTILE.INCReturns the specified quartile value. Inclusive.
QUARTILE.EXCReturns the specified quartile value. Exclusive.
RANKRank of a number within a series.
RANK.AVGRank of a number within a series. Averages.
RANK.EQRank of a number within a series. Top Rank.
SLOPECalculates the slope from linear regression.
SMALLReturns the kth smallest value.
STDEVCalculates the standard deviation.
STDEV.PCalculates the SD of an entire population.
STDEV.SCalculates the SD of a sample.
STDEVPCalculates the SD of an entire population
TRENDCalculates Y values based on a trendline.
Textyes
CHARReturns a character specified by a code.
CLEANRemoves all non-printable characters.
CODEReturns the numeric code for a character.
CONCATENATECombines text together.
DOLLARConverts a number to text in currency format.
EXACTTest if cells are exactly equal. Case-sensitive. TRUE/FALSE
FINDLocates position of text within a cell.Case-sensitive.
LEFTTruncates text a number of characters from the left.
LENCounts number of characters in text.
LOWERConverts text to lower case.
MIDExtracts text from the middle of a cell.
PROPERConverts text to proper case.
REPLACEReplaces text based on it's location.
REPTRepeats text a number of times.
RIGHTTruncates text a number of characters from the right.
SEARCHLocates position of text within a cell.Not Case-sensitive.
SUBSTITUTEFinds and replaces text. Case-sensitive.
TEXTConverts a value into text with a specific number format.
TRIMRemoves all extra spaces from text.
UPPERConverts text to upper case.
VALUEConverts a number stored as text into a number.
VBA Code Examples
In Depth Tutorialsyes
PowerPoint VBA Macro Examples & Tutorial
Sorting
Find and Find and Replace
Cheat Sheets – Commands & Syntax Lists
Word Macro Examples & VBA Tutorial
Basicsyes
If, ElseIf, Else (Ultimate Guide to If Statements)
Value Paste & PasteSpecial
VBA – Cut, Copy, Paste from a Macro
VBA Save File – 20 Easy Examples
VBA Coding Conceptsyes
Commenting in VBA
Creating (Custom) User Defined Functions (UDFs)
Excel VBA – Named Ranges and Programming
How to Rename or Delete a Module or Form
Opening & Using the Visual Basic Editor (VBE) in Excel
Return a Value from a Sub Procedure
Shortcuts for the Visual Basic Editor
Call Function from a Sub
Function – Call, Return Value, & Parameters
Object Model
On Error – Error Handling Best Practices
Private vs Public Procedures (Subs & Functions)
Select Case Statement
Sub Procedure
What is a VBA Module?
What is a VBA Project? (And How to Edit)
VBA – Language References
VBA – Modify the Right-Click Menu to Call a Macro
VBA – Run a Macro from a Macro
VBA – Run a Macro Line by Line
VBA – Wait, Delay, Create a Timer While Running Code
Debug.Print and Immediate Window
VBA DoEvents
VBA End
VBA Exit Sub or Function
VBA On Error Exit Sub
VBA Option Explicit
VBA: Improve Speed & Other Best Practices
Loopsyes
VBA For Loop – Loop Through a Range of Cells
Loops - Ultimate Guide
For Each Examples (Quick Reference)
VBA Exit For
VBA Exit Loop
While Wend Loop
Ranges and Cellsyes
Ranges and Cells - Ultimate Guide
Formulas - Ultimate Guide
Union and Intersect
Resize Range
VBA – Autofill with a Macro
VBA – Change a Cell;s Background Color
VBA – ClearFormats – Remove Formatting For Each Cell In Range
VBA – Conditional Formatting – Highlight Duplicates in a Range
VBA – Delete All Named Ranges
VBA – Delete Hyperlinks
VBA – Excel’s Color Scheme
VBA – Find the Maximum Value For Each Column in a Range
VBA – Format Painter
VBA – Get the Active Cell’s Column or Row
VBA – Resize an Object to a Range Size
VBA – Select All Cells
VBA – Sum by Color
VBA – Test if Selection is a Range or Object
Cell Value – Get, Set, or Change
ClearContents / Clear Cells
Copy Destination (Copy Range to Another Sheet)
Current Region
Dynamic Range
VBA Function – Populating a Range With Random Values
Highlight Cell
VBA Merge Cells & Unmerge Cells
Offset Range or Cell
Range / Cell Address
Range.End (xlDown, xlUp, xlToRight, xlToLeft)
VBA Routine to return Column Letter of Cell
VBA Select Range or Cells
VBA Union – Join Multiple Ranges
VBA Wrap Text
Activecell Offset VBA
Rows & Columnsyes
Select (and work with) Entire Rows & Columns
Delete or Insert Rows Based on Cell Value
Delete Rows that Meet Certain Criteria in VBA
Hide / Unhide Columns & Rows
Unhide All Rows / Columns
VBA – Autofit Columns
VBA – Count the rows in a Selection
VBA – Delete Blank Rows
VBA – Select Every 5th Row
VBA – Select Non Contiguous Columns
Copy / Paste Rows & Columns
Delete Entire Row or Column
Group Rows & Columns
Insert Row or Column
VBA Set Column Width or Row Height
VBA Used Range – Count Number of Used Rows or Columns
Sheetsyes
Sheets – The Ultimate Guide
VBA – Count the Sheets in a Workbook
VBA – Create a Hyperlink Menu of Worksheets
VBA – Hide (or Unhide) a Worksheet
VBA – Hide WorkSheet Tabs
VBA – How to Use Worksheet Functions (and a Full List)
VBA – Loop Through all Worksheets with For Each
VBA – Macro to List all Sheets in a Workbook
Send Worksheets by Email as Separate Workbooks
VBA – Sort Sheets Alphabetically
VBA – Unhide All Worksheets
Clear Entire Sheet
VBA Copy Worksheet
VBA Delete or Clear Worksheet
Get Sheet Name / Rename Sheet
VBA Protect / Unprotect Worksheets
VBA Routine to Add and Name Worksheets
VBA Select Sheet, Activate Sheet, and Get Activesheet
Worksheet Range
VBA: Set the Default Sheet When a WorkBook Opens
How to Sort Tabs / Worksheets with a VBA Macro
Workbooksyes
Create New Workbook (Workbooks.Add)
Activate Workbook
ActiveWorkbook vs. ThisWorkbook
VBA Open / Close Workbook
Workbook Name (Get, Set, without Extension)
Workbook Protection (Password Protect / Unprotect)
Working with Workbooks (The Workbook Object)
Arraysyes
Arrays
VBA – Array Examples
Dictionary Objects
Collections
ArrayList
Array of Objects
Variant Array
Dynamic Array (Redim & Redim Preserve)
Declare & Initilize String Array
Declare (Dim), Create, and Initialize Array Variable
Array Length / Size
Assign Range to Array
Clear Array – Erase Function
Loop Through Array / For Each Item in Array
Sort Array
Transpose Array
Search for (Find) Value in Array
Populate Array with Unique Values from Column
Remove Duplicates From Array
Function Return Array
Multi-Dimensional Array (2D Arrays)
Output (Print) Array to Range
Errorsyes
Try Catch Errors
Object Required Error
Syntax Error VBA
Type Mismatch (Run-time Error 13)
Automation Error
Compile Error
Error 1004 – Application-Defined or Object-Defined Error
Ignore Error
On Error Resume Next or Goto 0
Out of Memory Error
Throw / Raise Error – Err.Raise – Custom Error Msg
Can’t Find Project or Library – VBA Compile Error
Compile Error in Hidden Module – VBA
Fix Error 400
Fix VBA Error 438 – Object Doesn’t Support Property or Method
Fix VBA Run Time Error 32809
Fix VBA Run Time Error 57121
Variablesyes
Dim VBA – Declare Variable
VBA – Determine a Variable’s Underlying Type
VBA Array Variables
Boolean Data Type (Dim Variable)
Constant
VBA Constant Expression Required
VBA Data Types – Variables and Constants
VBA Date Variable
Double Data Type (Dim Variable)
VBA Global Variable
Int / Integer Data Type (Dim Variable)
Long Data Type (Dim Variable)
VBA Public Variable – Use a Variable in Multiple Modules or Subs
VBA Range Object Variables (Dim / Set Range)
Set Object Variables – Workbooks, Worksheets, & More
Single Data Type (Dim Variable)
VBA Static Variable
String Data Type (Dim Variable)
Variant Data Type (Dim Variable)
Settingsyes
Get and Change User Name with VBA
VBA – Display Status Bar Message
VBA – Modify the titlebar
VBA – Prevent warning messages from a macro
VBA – Turn Automatic Calculations Off (or On)
VBA – Turn Formula Bar Off (or On)
VBA – Turn off Screen Updating
VBA Turn Scroll Bars On or Off
Text and Stringsyes
Split String into Cells
Prevent VBA Case Sensitive – Option Compare Text
VBA – Extract Number From String
VBA – Find the nth Word in a String of Text
VBA – Line Break or Line Feed in a Message Box
VBA – Loop Through a String
VBA – Remove Characters from Left or Right Side of Variable Length String
VBA – Reverse a String of Text
VBA Char / Chr Function
VBA Compare Strings – StrComp
VBA Comparison Operators – Not Equal to & More
VBA Concatenate Text Strings Together (& – Ampersand)
Convert Integer (Number) to String
Convert String to Date
Convert Text String to Number
VBA Function To Calculate Number of Words in a String
VBA INSTR – Find Text in a String
VBA Like Operator
VBA Logical Operators – OR, AND, XOR, NOT, IS, & LIKE
VBA New Line / Carriage Return
Parse String
VBA Space Function – Add Spaces to a String
VBA Strings and Substrings Functions
Text to Columns
Trim, LTrim, and RTrim Functions – Remove Spaces From Text
VBA Upper, Lower, and Proper Case – Case Functions
Viewyes
VBA – ActiveWindow.WindowState
VBA – Display (or Exit) Fullscreen
VBA – Freeze Panes
VBA – Hide Excel (The Entire Application)
VBA – Page Break Preview Mode On or Off
VBA – Scroll Vertically and Scroll Horizontally
VBA – Zoom – Fit Selection
VBA – Zoom in and Out of Worksheets
Filesyes
FileSystem Object
Move Files with VBA FileSystemObject (MoveFile)
VBA – Convert Excel to CSV (Comma Delimited Text File)
Create Text File with CreateTextFile
VBA – Delete files from the hard drive
VBA – Export Range to CSV (Delimited Text File)
Read Text File into String
Read Text File Line by Line
VBA – Test if Workbook is Open by Workbook Name
Validate Filenames
Write to Text File
VBA Check if File or Folder Exists
Combine Multiple Excel Files into One Workbook
Copy a Folder with CopyFolder (FSO)
VBA Copy File / Workbook
VBA Delete File / Workbook
Delete Folder with DeleteFolder (FSO)
FileCopy Statement
Get File Name with GetFileName (FSO)
GetFolder & GetFile (Get File & Folder Properties)
VBA List Files in Folder
VBA Open File Dialog Box
Open Text File with OpenTextFile
Read Text File (Read, Parse, and Import)
VBA Rename File
Shapes, Charts, Objectsyes
VBA AutoFilter
VBA Guide For Charts and Graphs
VBA Guide to Pivot Tables
VBA Objects
Excel Add VBA Button
How to use VBA Controls in UserForms
VBA – Delete All Autoshapes
VBA – Programmatically Draw Boxes with Code
Listbox – Selected Item
VBA Tables and ListObjects
TextBox
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Eventsyes
Events
VBA – Exit Without Save Prompt
VBA – Force a Workbook to Save Before Close
VBA – Run a Macro when Excel Closes – Auto_Close
VBA – Run a macro when Excel starts
VBA Worksheet Change Event – Run a Macro When a Cell Changes
VBA: Show or load a form when Excel starts
UserForms, MsgBoxes & Controlsyes
VBA – Yes No Message Box (Msgbox)
VBA InputBox – Get Input from a User
Creating VBA Userforms
Option Button Excel VBA
Spin button Excel VBA
VBA Checkbox
VBA ComboBox
VBA Listbox
VBA Open or Close UserForm
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Misc.yes
Clear Clipboard
Pause (Break / Stop) & Resume a Macro
Save As (Print) to PDF
ColorIndex Codes List
Format Numbers
How to Install (or Uninstall) a VBA add-in (.xlam file) for Microsoft Excel
How to use VBA Structures
Removing Duplicate Values in Excel VBA
Speed up your VBA Code
VBA – Add or Remove Cell Comments
All Operators Explained
Check if Office App is Running
Dialog Box
Get Today’s Date (Current Date)
Pivot Table Filter
Refresh Pivot Table / All Pivot Tables
Refresh Pivot Table when Data Changes
Using Goal Seek in VBA
Using the Analysis ToolPak in VBA
Advanced Filter
Application.CutCopyMode = False
Calculate – Now, Workbook, Worksheet, or Range
Call / Run Sub From another Subroutine
Copy to Clipboard
Find Value in Column
GoTo a Line Label
IIF Function
Input Statement
is Nothing
Is Operator
Line Input Statement
Multiple (Nested) If Statements
Project Password / Protect code
Reference
Solver
Wait & Sleep Functions – Pause / Delay Code
Wildcards
With / End With Statement
What is the Difference Between VB and VBA?
Personal Macro Workbook
VBA – Turn Off AutoFilter / Clear Filters
VBA Drop Down List (Data Validation)
VBA Hyperlinks
VBA Random Number
Round, RoundUp, and RoundDown
Second Function
Split Function – Split String of Text into Array
Sqr Function
Sum Function (Ranges, Columns, & More)
SUMIF and SUMIFS Functions
Switch Statement
Text Function
Time Function
Time Functions
Timer Function
TimeSerial Function
TimeValue Function
TypeName
TypeOf
UBound & LBound Functions
Val Function
VarType
Weekday Function
WeekdayName Function
Write Statement
Year Function
Vlookup – Multiple Results with VBA
Advanced Programmingyes
Advanced VBA Programming Examples
Early Late Binding
Class Constructor
Class Module Tutorial & Examples
Matrix – Create & More
Shell
VBA – Convert Matrix to Vector
VBA – Programming the VBE (Visual Basic Editor)
VBA Regex
VBA SendKeys
VBA Timer Function
Internetyes
Automate Internet Explorer (IE) Using VBA
VBA – Consume Soap Web Services
VBA – WinHttpRequest with Login and Password – Misc Bloglines
Send Emails from Excel through Gmail
Send Emails from Excel through Outlook
Accessyes
Access VBA Recordsets – Open, Count, Loop and More
Access VBA – Import / Export Excel – Query, Report, Table, and Forms
Access VBA – Open / Close Form
Access VBA – Open Excel File
Access VBA Database – Open, Connect, Login, & More
Access VBA Findfirst
Access VBA Me
Access VBA Query
Access VBA Recordsets – Open, Count, Loop and More
Access VBA Refresh Form
Access VBA Reports – Print, Export, Filter
Access VBA SQL Examples
Access VBA Tables – Update, Count, Delete, Create, Rename, Export
Wordyes
Word VBA – Macro to Open Word Document
Word VBA Macros – Find, Find & Replace
Word VBA Macros – SaveAs (PDF or New File Name)
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 the Same Value
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
Calculationyes
Add Values to Cells and Columns
Anchor a Cell
Change the Signs of Values
Divide Cells and Columns
Imaginary Numbers
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
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
Copy a Cell From Another Sheet
Copy a Page
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 Columns
Copy and Paste Formulas Between Excel and Google Sheets
Copy and Paste Hyperlinks
Copy and Paste Merged 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 Value to Another Cell With Formula
Drag and Drop
Duplicate Rows
Freeze Random Numbers
Paste and Match Destination Formatting
Paste Horizontal Data Vertically
Paste Into Filtered Cells
Rearrange Columns
Replace Formula With Value
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
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 Populates Another Cell
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
Count Duplicate Values Only Once
Distinct (Unique) Values
Duplicates
Find Duplicate Values
Merge Lists Without Duplicates
Paste Unique Values
Prevent Duplicate Entries
Remove Both Duplicates
Remove Duplicate Rows
Remove Unique Values
Show Only Duplicates
Emailyes
Copy Email Addresses to Outlook
Email a Spreadsheet
Email Address Format Validation
Embed a File
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
Save Filtered Data
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
Highlight Blank Cells
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
Quick Analysis Tool
Remove Solver
Solve for a Variable
Format Cellsyes
Add Border Lines
Alternate Row Color
Apply and Change Themes
Apply Cell Styles
Apply the Calculation Style
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
Highlight Columns
Highlight Rows
Make All Rows and Columns the Same Height and Width
Make Merged Cells the Same Size
Merge and Center Cells
Merge Multiple Cells
Outline Cells
Remove Fill 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
Line Spacing
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
Word Wrap
Write Paragraphs
Write Text Vertically
X Out a Cell
Formula Auditingyes
Allow Circular References
Camera Tool
Find a Circular Reference Error
Formula View Mode
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 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 After Every Other Row
Insert Cells and Shift Cells Down
Insert Cells and Shift Cells Right
Insert Multiple Rows and Columns
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
Delete a File
Find the Last Saved Date
Find What Version of Excel You Have
Generate All Permutations
Insert Timestamp
Make a File Smaller
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
Dynamic Named Range Based on Cell
Edit Named Ranges
Find the Location of 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
Insert Dates
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
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
Insert Toggle Button
Link a Text Box
Make a Combo Box
Make a Number Line
Select Objects
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 Multiple Workbooks Into One
Merge Two Spreadsheets Into One
Move Sheets
Pull Data From Multiple Sheets and Consolidate
Rename a Worksheet
Select Multiple Sheets
Separate Sheets
View a List of Worksheet Tabs
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
Scale to Fit Page Size
Set Custom Page Size
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
Add Calculated Field to Pivot Table
Advanced Pivot Table Techniques
Change Pivot Table Data Source
Count Unique Values With Pivot Table
Create Pivot Table
Delete Pivot Table
Filter Pivot Table Values
Get a Count in Pivot Table
Group Pivot Table by Date
Keep Pivot Table Formatting
Make a Pivot Table Chart
Pivot Table Slicers
Pivot Table Sorting Guide
Refresh a Pivot Table
Remove Field From 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
Change Print Area
Clear Print Area
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 Preview
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
Show 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 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
Shortcutsyes
Go To Cell, Row, or Column Shortcuts
Sort Datayes
Advanced Sorting
Keep Rows Together During Sort
Put Things in Alphabetical Order
Random Sort
Reverse the Order of Data
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
Escape Double Quotes
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
Center Table
Change Table Style
Compare Two Tables
Convert a Table to a Normal Range
Convert Data to Table
Copy and Paste Table
Copy Table From Word
Copy to Word and Fit Page
Create Floating Table
Create Lookup Table
Create Table
Display Data With Banded Rows
Flip a Table
Link Tables
Make Columns Sortable
Move Table
Paste Excel Table Into Gmail
Remove a Table or Table Formatting
Rename a Table
Rotate Data Tables
Table Tools
Types of 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
Troubleshootingyes
#REF Error
Advance Filter Not Working
All the Merged Cells Need to Be the Same Size
Command Cannot be Used on Multiple Selections
Data Validation Not Working
Excel Borders Not Printing
Excel Cannot Open or Save Any More Documents
Excel Cannot Paste the Data Error
Excel Not Sorting Numbers Correctly
Fill Series Not Working
Find Broken Links
Fix Excel Find Not Working
Fix the #NAME? Error
Formula Won't Calculate
Get Rid of Green Triangle
Get Rid of the Red Triangle in Google Sheets
Loading Solver Not Working
Printing Problems
Printing Too Small
Resolve a Name Conflict for a Named Range
Solver Encountered Error Value
The Pivot Table Field Name Is Not Valid
There Isn't Enough Memory to Complete This Action
Unlock a File Locked for Editing by Me
Waiting for OLE Action Error
What to Do When Excel Keeps Crashing or Freezing
Why is AutoSave Not Working or Grayed Out?
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
View Header
View Two Sheets at Once
Zoom In or Out
Excel Formula Examples
Dateyes
Add (Subtract) Days to a Date
Concatenate Dates
Convert Date to Number
Convert Date to Text
Month Name to Number
Create Date Range from Dates
Day Number of Year
Month Name from Date
First Day of Month
Add (Subtract) Weeks to a Date
If Functions with Dates
Max Date
Number of Days Between Dates
Number of Days in a Month
Number of Weeks Between Dates
Number of Years Between Dates
Split Date & Time into Separate Cells
Countdown Remaining Days
Insert Dates
Random Date Generator
Using Dynamic Ranges - Year to Date Values
Add (Subtract) Years to a Date
Date Formula Examples
Extract Day from Date
Get Day Name from Date
Count Days Left in Month / Year
Count Workdays Left in Month / Year
Get Last Day of Month
Last Business Day of Month / Year
Number of Work / Business Days in Month
Weekday Abbreviations
Auto Populate Dates
Number of Months Between Dates
Quarter from a Date
Years of Service
Change Date Format
Compare Dates
Timeyes
Add (Subtract) Hours to Time
Add (Subtract) Minutes to Time
Add (Subtract) Seconds to Time
Add Up time (Total Time)
Time Differences
Change Time Format
Convert Minutes to Hours
Convert Time to Decimal
Convert Time to Hours
Convert Time to Minutes
Convert Time to Seconds
Military Time
Round Time to Nearest 15 Minutes
Overtime Calculator
Number of Hours Between Times
Convert Seconds to Minutes, Hours, or Time
Count Hours Worked
Time Differences
Time Format - Show Minutes Seconds
Textyes
Add Commas to Cells
Get First Word from Text
Capitalize First Letter
Clean & Format Phone #s
Remove Extra Trailing / Leading Spaces
Add Spaces to Cell
Assign Number Value to Text
Combine Cells with Comma
Combine First and Last Names
Convert Text String to Date
Convert Text to Number
Extract Text From Cell
Get Last Word
Remove Unwated Characters
Extract Text Before or After Character
How to Split Text String by Space, Comma, & More
Remove Special Characters
Remove First Characters from Left
Substitute Multiple Values
Switch First & Last Names w/ Commas
Remove Specific Text from a Cell
Extract Text Between Characters (Ex. Parenthesis)
Add Leading Zeros to a Number
Remove Line Breaks from Text
Remove all Numbers from Text
Reverse Text
Remove Non-Numeric Characters
Remove Last Character(s) From Right
Separate First and Last Names
Separate Text & Numbers
Roundyes
Round Formulas
Round Price to Nearest Dollar or Cent
Round to Nearest 10, 100, or 1000
Round to Nearest 5 or .5
Round Percentages
Round to Significant Figures
Countyes
Count Blank and Non-blank Cells
Count Cells Between Two Numbers
Count Cells not Equal to
Count if Cells are in Range
Count Times Word Appears in Cell
Count Words in Cell
Count Specific Characters in Column
Count Total Number of Characters in Column
Count Cells that Equal one of two Results
Count Cells that do not Contain
Count Cells that Contain Specific Text
Count Unique Values in Range
Countif - Multiple Criteria
Count Total Number of Cells in Range
Count Cells with Any Text
Count Total Cells in a Table
Lookupyes
Two Dimensional VLOOKUP
VLOOKUP Simple Example
Vlookup - Multiple Matches
Case Sensitive Lookup
Case Sensitive VLOOKUP
Sum if - VLOOKUP
Case Sensitive Lookup
Case Sensitive VLOOKUP
Find Duplicates w/ VLOOKUP or MATCH
INDEX MATCH MATCH
Lookup - Return Cell Address (Not Value)
Lookup Last Value in Column or Row
Reverse VLOOKUP (Right to Left)
Risk Score Bucket with VLOOKUP
Sum with a VLOOKUP Function
VLOOKUP & INDIRECT
VLOOKUP Concatenate
VLOOKUP Contains (Partial Match)
17 Reasons Why Your XLOOKUP is Not Working
Double (Nested) XLOOKUP - Dynamic Columns
IFERROR (& IFNA) XLOOKUP
Lookup Min / Max Value
Nested VLOOKUP
Top 11 Alternatives to VLOOKUP (Updated 2022!)
VLOOKUP – Dynamic Column Reference
VLOOKUP – Fix #N/A Error
VLOOKUP – Multiple Sheets at Once
VLOOKUP & HLOOKUP Combined
VLOOKUP & MATCH Combined
VLOOKUP Between Worksheets or Spreadsheets
VLOOKUP Duplicate Values
VLOOKUP Letter Grades
VLOOKUP Return Multiple Columns
VLOOKUP Returns 0? Return Blank Instead
VLOOKUP w/o #N/A Error
XLOOKUP Multiple Sheets at Once
XLOOKUP Between Worksheets or Spreadsheets
XLOOKUP by Date
XLOOKUP Duplicate Values
XLOOKUP Multiple Criteria
XLOOKUP Return Multiple Columns
XLOOKUP Returns 0? Return Blank Instead
XLOOKUP Text
XLOOKUP with IF
XLOOKUP With If Statement
Misc.yes
Sort Multiple Columns
Use Cell Value in Formula
Percentage Change Between Numbers
Percentage Breakdown
Rank Values
Add Spaces to Cell
CAGR Formula
Average Time
Decimal Part of Number
Integer Part of a Number
Compare Items in a List
Dealing with NA() Errors
Get Worksheet Name
Wildcard Characters
Hyperlink to Current Folder
Compound Interest Formula
Percentage Increase
Create Random Groups
Sort with the Small and Large Functions
Non-volatile Function Alternatives
Decrease a Number by a Percentage
Calculate Percent Variance
Profit Margin Calculator
Convert Column Number to Letter
Get Full Address of Named Range
Insert File Name
Insert Path
Latitute / Longitude Functions
Replace Negative Values
Reverse List Range
Convert State Name to Abbreviation
Create Dynamic Hyperlinks
Custom Sort List with Formula
Data Validation - Custom Formulas
Dynamic Sheet Reference (INDIRECT)
Reference Cell in Another Sheet or Workbook
Get Cell Value by Address
Get Worksheet Name
Increment Cell Reference
List Sheet Names
List Skipped Numbers in Sequence
Return Address of Max Value in Range
Search by Keywords
Select Every Other (or Every nth) Row
Basicsyes
Cell Reference Basics - A1, R1C1, 3d, etc.
Add Up (Sum) Entire Column or Row
Into to Dynamic Array Formulas
Conversionsyes
Convert Time Zones
Convert Celsius to Fahrenheit
Convert Pounds to Kilograms
Convert Time to Unix Time
Convert Feet to Meters
Convert Centimeters to Inches
Convert Kilometers to Miles
Convert Inches to Feet
Convert Date to Julian Format
Convert Column Letter to Number
Testsyes
Test if a Range Contains any Text
Test if any Cell in Range is Number
Test if a Cell Contains a Specific Value
Test if Cell Contains Any Number
Test if Cell Contains Specific Number
Test if Cell is Number or Text
Ifyes
Percentile If
Subtotal If
Sumproduct If
Large If and Small If
Median If
Concatentate If
Max If
Rank If
TEXTJOIN If
Sumyes
Sum if - Begins With / Ends With
Sum if - Month or Year to Date
Sum if - By Year
Sum if - Blank / Non-Blank
Sum if - Horizontal Sum
Count / Sum If - Cell Color
INDIRECT Sum
Sum If - Across Multiple Sheets
Sum If - By Month
Sum If - Cells Not Equal To
Sum If - Not Blank
Sum if - Between Values
Sum If - Week Number
Sum Text
Sum if - By Category or Group
Sum if - Cell Contains Specific Text (Wildcards)
Sum if - Date Rnage
Sum if - Dates Equal
Sum if - Day of Week
Sum if - Greater Than
Sum if - Less Than
Averageyes
Average Non-Zero Values
Average If - Not Blank
Average - Ignore 0
Average - Ignore Errors
Mathyes
Multiplication Table
Cube Roots
nth Roots
Square Numbers
Square Roots
Calculationsyes
Calculate a Ratio
Calculate Age
Calculate Loan Payments
GPA Formula
Calculate VAT Tax
How to Grade Formulas
Findyes
Find a Number in a Column / Workbook
Find Most Frequent Numbers
Find Smallest n Values
Find nth Occurance of Character in Text
Find and Extract Number from String
Find Earliest or Latest Date Based on Criteria
Find First Cell with Any Value
Find Last Row
Find Last Row with Data
Find Missing Values
Find Largest n Values
Most Frequent Number
Conditional Formattingyes
Conditional Format - Dates & Times
Conditional Format - Highlight Blank Cells
Excel Charts
Excel Built-in Chartsyes
Column Charts: Stacked, Clustered
Bar Charts: Stacked, Clustered
Area Charts
Line Charts
Candlestick Charts
Pareto Chart
Waterfall Chart
Sales Funnel Chart
Advanced Custom Chartsyes
Gauge / Dial Chart
Tornado Chart
Burndown Chart
Step Chart
Thermometer Chart
Quadrant Chart
Bullet Chart
Ogive Chart
Bell Curve / Normal Distribution Plot
Stem & Leaf Chart
Mekko Chart
Venn Diagram
Polar Plot
Venn Diagram
Panel Chart
Timeline Chart
Progress Charts
Sales Funnel Chart
Floating Bar Chart
Forest Plot
Frequency Polygon
Arrow Chart
Percentage Graph
Time Series Graph
Percentage Change Chart
Show Percentage in Pie Chart
Dot Plot
Q-Q Plot
Log-Log Plot
Normal Probability Plot
Charts Tips & Tricksyes
Add or Move Data Labels
Add Data Series
Add Average Line
Add Data Points
Add Error Bars
Add Gridlines
Add Line of Best Fit
Add Title
Break Chart Axis
Calculate Area Under Curve
Plot Residuals
Change Bar Chart Width
Change Chart Colors
Chart Axis Text Instead of Numbers
Copy Chart Format
Create Chart with Date or Time
Curve Fitting
Export Chart as PDF
Add Axis Labels
Add Secondary Axis
Change Chart Series Name
Change Horizontal Axis Values
Create Chart in a Cell
Graph an Equation or Function
Overlay Two Graphs
Plot Multiple Lines
Rotate Pie Chart
Switch X and Y Axis
Insert Textbox
Move Chart to New Sheet
Move Horizontal Axis to Bottom
Move Vertical Axis to Left
Remove Gridlines
Reverse a Chart
Rotate a Chart
Rounded Corners or Shadows
Create, Save, & Use Excel Chart Templates
Dynamic Chart Titles
Chart Conditional Formatting
Dynamic Chart Range
Animated Charts
Interactive / Dynamic Charts
FunctionDescription
New Functions
UNIQUEReturns a list of unique values in a list or range
XLOOKUPReplaces VLOOKUP, HLOOKUP, and INDEX / MATCH
Logicalyes
ANDChecks whether all conditions are met. TRUE/FALSE
IFIf condition is met, do something, if not, do something else.
IFERRORIf result is an error then do something else.
NOTChanges TRUE to FALSE and FALSE to TRUE.
ORChecks whether any conditions are met. TRUE/FALSE
XORChecks whether one and only one condition is met. TRUE/FALSE
Lookup & Referenceyes
FALSEThe logical value: FALSE.
TRUEThe logical value: TRUE.
ADDRESSReturns a cell address as text.
AREASReturns the number of areas in a reference.
CHOOSEChooses a value from a list based on it's position number.
COLUMNReturns the column number of a cell reference.
COLUMNSReturns the number of columns in an array.
HLOOKUPLookup a value in the first row and return a value.
HYPERLINKCreates a clickable link.
INDEXReturns a value based on it's column and row numbers.
INDIRECTCreates a cell reference from text.
LOOKUPLooks up values either horizontally or vertically.
MATCHSearches for a value in a list and returns its position.
OFFSETCreates a reference offset from a starting point.
ROWReturns the row number of a cell reference.
ROWSReturns the number of rows in an array.
TRANSPOSEFlips the oriention of a range of cells.
VLOOKUPLookup a value in the first column and return a value.
Date & Timeyes
DATEReturns a date from year, month, and day.
DATEDIFNumber of days, months or years between two dates.
DATEVALUEConverts a date stored as text into a valid date
DAYReturns the day as a number (1-31).
DAYSReturns the number of days between two dates.
DAYS360Returns days between 2 dates in a 360 day year.
EDATEReturns a date, n months away from a start date.
EOMONTHReturns the last day of the month, n months away date.
HOURReturns the hour as a number (0-23).
MINUTEReturns the minute as a number (0-59).
MONTHReturns the month as a number (1-12).
NETWORKDAYSNumber of working days between 2 dates.
NETWORKDAYS.INTLWorking days between 2 dates, custom weekends.
NOWReturns the current date and time.
SECONDReturns the second as a number (0-59)
TIMEReturns the time from a hour, minute, and second.
TIMEVALUEConverts a time stored as text into a valid time.
TODAYReturns the current date.
WEEKDAYReturns the day of the week as a number (1-7).
WEEKNUMReturns the week number in a year (1-52).
WORKDAYThe date n working days from a date.
WORKDAY.INTLThe date n working days from a date, custom weekends.
YEARReturns the year.
YEARFRACReturns the fraction of a year between 2 dates.
Engineeringyes
CONVERTConvert number from one unit to another.
Financialyes
FVCalculates the future value.
PVCalculates the present value.
NPERCalculates the total number of payment periods.
PMTCalculates the payment amount.
RATECalculates the interest Rate.
NPVCalculates the net present value.
IRRThe internal rate of return for a set of periodic CFs.
XIRRThe internal rate of return for a set of non-periodic CFs.
PRICECalculates the price of a bond.
YIELDCalculates the bond yield.
INTRATEThe interest rate of a fully invested security.
Informationyes
CELLReturns information about a cell.
ERROR.TYPEReturns a value representing the cell error.
ISBLANKTest if cell is blank. TRUE/FALSE
ISERRTest if cell value is an error, ignores #N/A. TRUE/FALSE
ISERRORTest if cell value is an error. TRUE/FALSE
ISEVENTest if cell value is even. TRUE/FALSE
ISFORMULATest if cell is a formula. TRUE/FALSE
ISLOGICALTest if cell is logical (TRUE or FALSE). TRUE/FALSE
ISNATest if cell value is #N/A. TRUE/FALSE
ISNONTEXTTest if cell is not text (blank cells are not text). TRUE/FALSE
ISNUMBERTest if cell is a number. TRUE/FALSE
ISODDTest if cell value is odd. TRUE/FALSE
ISREFTest if cell value is a reference. TRUE/FALSE
ISTEXTTest if cell is text. TRUE/FALSE
NConverts a value to a number.
NAReturns the error: #N/A.
TYPEReturns the type of value in a cell.
Mathyes
ABSCalculates the absolute value of a number.
AGGREGATEDefine and perform calculations for a database or a list.
CEILINGRounds a number up, to the nearest specified multiple.
COSReturns the cosine of an angle.
DEGREESConverts radians to degrees.
DSUMSums database records that meet certain criteria.
EVENRounds to the nearest even integer.
EXPCalculates the exponential value for a given number.
FACTReturns the factorial.
FLOORRounds a number down, to the nearest specified multiple.
GCDReturns the greatest common divisor.
INTRounds a number down to the nearest integer.
LCMReturns the least common multiple.
LNReturns the natural logarithm of a number.
LOGReturns the logarithm of a number to a specified base.
LOG10Returns the base-10 logarithm of a number.
MODReturns the remainder after dividing.
MROUNDRounds a number to a specified multiple.
ODDRounds to the nearest odd integer.
PIThe value of PI.
POWERCalculates a number raised to a power.
PRODUCTMultiplies an array of numbers.
QUOTIENTReturns the integer result of division.
RADIANSConverts an angle into radians.
RANDCalculates a random number between 0 and 1.
RANDBETWEENCalculates a random number between two numbers.
ROUNDRounds a number to a specified number of digits.
ROUNDDOWNRounds a number down (towards zero).
ROUNDUPRounds a number up (away from zero).
SIGNReturns the sign of a number.
SINReturns the sine of an angle.
SQRTCalculates the square root of a number.
SUBTOTALReturns a summary statistic for a series of data.
SUMAdds numbers together.
SUMIFSums numbers that meet a criteria.
SUMIFSSums numbers that meet multiple criteria.
SUMPRODUCTMultiplies arrays of numbers and sums the resultant array.
TANReturns the tangent of an angle.
TRUNCTruncates a number to a specific number of digits.
Statsyes
AVERAGEAverages numbers.
AVERAGEAAverages numbers. Includes text & FALSE =0, TRUE =1.
AVERAGEIFAverages numbers that meet a criteria.
AVERAGEIFSAverages numbers that meet multiple criteria.
CORRELCalculates the correlation of two series.
COUNTCounts cells that contain a number.
COUNTACount cells that are non-blank.
COUNTBLANKCounts cells that are blank.
COUNTIFCounts cells that meet a criteria.
COUNTIFSCounts cells that meet multiple criteria.
FORECASTPredict future y-values from linear trend line.
FREQUENCYCounts values that fall within specified ranges.
GROWTHCalculates Y values based on exponential growth.
INTERCEPTCalculates the Y intercept for a best-fit line.
LARGEReturns the kth largest value.
LINESTReturns statistics about a trendline.
MAXReturns the largest number.
MEDIANReturns the median number.
MINReturns the smallest number.
MODEReturns the most common number.
PERCENTILEReturns the kth percentile.
PERCENTILE.INCReturns the kth percentile. Where k is inclusive.
PERCENTILE.EXCReturns the kth percentile. Where k is exclusive.
QUARTILEReturns the specified quartile value.
QUARTILE.INCReturns the specified quartile value. Inclusive.
QUARTILE.EXCReturns the specified quartile value. Exclusive.
RANKRank of a number within a series.
RANK.AVGRank of a number within a series. Averages.
RANK.EQRank of a number within a series. Top Rank.
SLOPECalculates the slope from linear regression.
SMALLReturns the kth smallest value.
STDEVCalculates the standard deviation.
STDEV.PCalculates the SD of an entire population.
STDEV.SCalculates the SD of a sample.
STDEVPCalculates the SD of an entire population
TRENDCalculates Y values based on a trendline.
Textyes
CHARReturns a character specified by a code.
CLEANRemoves all non-printable characters.
CODEReturns the numeric code for a character.
CONCATENATECombines text together.
DOLLARConverts a number to text in currency format.
EXACTTest if cells are exactly equal. Case-sensitive. TRUE/FALSE
FINDLocates position of text within a cell.Case-sensitive.
LEFTTruncates text a number of characters from the left.
LENCounts number of characters in text.
LOWERConverts text to lower case.
MIDExtracts text from the middle of a cell.
PROPERConverts text to proper case.
REPLACEReplaces text based on it's location.
REPTRepeats text a number of times.
RIGHTTruncates text a number of characters from the right.
SEARCHLocates position of text within a cell.Not Case-sensitive.
SUBSTITUTEFinds and replaces text. Case-sensitive.
TEXTConverts a value into text with a specific number format.
TRIMRemoves all extra spaces from text.
UPPERConverts text to upper case.
VALUEConverts a number stored as text into a number.

Excel and VBA Consulting

Work Faster. Work Better.

How to Automate Excel

Excel automation primarily involves coding in VBA. VBA stands for Visual Basic for Applications. VBA is a variation of the Visual Basic language designed to integrate seamlessly with Microsoft Office applications (hence the name) like Excel, Word, PowerPoint, and Outlook.

Our Interactive VBA Tutorial will teach you about the following topics:

  1. VBA Basics
  2. Variables
  3. Conditional Logic
  4. Loops
  5. Adv. Cell Referencing
  6. Msg & Input Boxes
  7. Events
  8. Settings
  9. Adv. Procedures
  10. Arrays

A screen shot from our interactive VBA tutorial.

Our tutorial is designed to get you coding as fast as possible!  We'll give you a brief description, show you an example, and then ask you to complete an exercise. We won't go in depth on coding theory, or give needlessly long descriptions. We keep things short and sweet!

Learn VBA Tutorial

A screen shot of the Macro Recorder in Excel.

Work Faster. Work Better.

Using the Macro Recorder

You can also automate Excel without any coding knowledge by using the Macro Recorder. The Macro Recorder records your actions as VBA code, allowing you to repeat those exact actions again by running the macro.

By using the Macro Recorder, you can "code" some of your actions and then review and edit the code to better fit your needs. This is how many Excel users become introduced to VBA! They slowly learn VBA as they try to adapt macros.  We love the Macro Recorder as it helps non-programmers get introduced to VBA, but we strongly recommend going through our VBA Tutorial to save time when trying to adapt macro recorded code.

Our Interactive VBA Tutorial will show you how to automate Excel by using the Macro Recorder.

Learn VBA Tutorial

AutoMacro

Excel Automation Tools

For advanced users, learn about Excel Automation Tools for VBA, Python, SQL, C#, M, Java, C++, and D.

 

For everyone else interested in Excel automation, we recommend reviewing our AutoMacro add-in:

AutoMacro is designed to make VBA coding easy(er) for everyone.  Instead of searching online for example code, simply use the the menus to insert ready-to-use code. Use the advanced code builders to build complex code for your specific needs, without needing to know how to code it yourself!

  • VBA Code library - 200+ code fragments for common tasks (insert a column, find last used row, etc.)
  • VBA Code Generators - Generate ready-to-use code without knowing any VBA at all. Simply use the code generator interfaces to select your desired options and insert the code directly into the Visual Basic Editor. (See example below)
  • Save Your Own Code - Save your own frequently used code fragments or share code with colleagues
  • Time-saving Features - Many other time-saving features like shortcuts to bookmark code, indent code, remove excess line-breaks, smart-commenting, and more

Quickly generate code to loop through objects.

VBA Code Examples

Other Excel Resources!

Other Excel Examples

AutomateExcel contains many other non-VBA/Macro related resources as well. Below you will find links to our amazing, one-of-a-kind, interactive tutorials. Below that you will find links to lists with hundreds of examples for Excel shortcuts, functions, and formulas examples.

You can also visit the Start Here page for a more detailed list of the free resources available at AutomateExcel.