Excel Shortcuts and Functions: Complete Reference Guide

Excel has hundreds of commands, shortcuts, formulas and functions. The problem is rarely finding something Excel can do. The harder part is remembering the fastest way to do it.

I use a simple distinction: shortcuts make Excel operations faster; formulas and functions make Excel calculations and data manipulation smarter. The real productivity gain comes from combining them.

This reference brings the two together. You’ll find Windows and Mac keyboard shortcuts, formula techniques, lookup functions, text and date functions, dynamic arrays, financial functions, error handling and practical examples.

Last verified: October 5, 2026
Scope: Microsoft Excel desktop for Windows and macOS, with Microsoft 365 and current Excel versions identified where functionality is version-dependent.

Excel continues to evolve, particularly in Microsoft 365. Function availability and behavior can change as Microsoft introduces new features, so version markers matter. Microsoft’s current function reference identifies functions introduced in specific versions.

Excel Shortcuts

Excel shortcut cheat sheet

If you only want the shortcuts you will use most often, start here.

Windows

Action	Shortcut
Save workbook	Ctrl+S
Copy	Ctrl+C
Paste	Ctrl+V
Cut	Ctrl+X
Undo	Ctrl+Z
Redo/repeat	Ctrl+Y
Edit active cell	F2
Find	Ctrl+F
Format Cells	Ctrl+1
Create table	Ctrl+T or Ctrl+L
Select entire column	Ctrl+Spacebar
Select entire row	Shift+Spacebar
Select current region	Ctrl+A
Go to first cell	Ctrl+Home
Go to last used cell	Ctrl+End
Move to edge of data	Ctrl+Arrow
Select to edge of data	Ctrl+Shift+Arrow
Next worksheet	Ctrl+Page Down
Previous worksheet	Ctrl+Page Up
Toggle filters	Ctrl+Shift+L
AutoSum	Alt+=
Flash Fill	Ctrl+E
Show formulas	Ctrl+`
Recalculate	F9
Absolute/relative reference	F4
Insert cells/rows/columns	Ctrl+Shift++
Delete cells/rows/columns	Ctrl+-
Bold	Ctrl+B
Italic	Ctrl+I
Underline	Ctrl+U
Percentage format	Ctrl+Shift+%
Currency format	Ctrl+Shift+$
Date format	Ctrl+Shift+#
Time format	Ctrl+Shift+@
Create embedded chart	Alt+F1
New chart sheet	F11
Open Power Query Editor	Alt+F12
Open VBA Editor	Alt+F11

Microsoft documents these Windows shortcuts directly, including navigation, formatting, formula, calculation, chart, Power Query and VBA shortcuts.

Mac

Excel for Mac uses a mixture of Command, Control and Option shortcuts. Do not assume that replacing every Windows Ctrl with Cmd will work: Microsoft explicitly notes that many Windows Ctrl shortcuts also work with Control on Mac, but not all do.

Action	Mac shortcut
Save	Cmd+S
Copy	Cmd+C
Paste	Cmd+V
Cut	Cmd+X
Undo	Cmd+Z
Redo	Cmd+Y or Cmd+Shift+Z
Bold	Cmd+B
Italic	Cmd+I
Underline	Cmd+U
Find	Cmd+F
Replace	Ctrl+H or Cmd+Shift+H
Format Cells	Cmd+1 or Ctrl+1
Go To	Ctrl+G or F5
Edit active cell	F2
Select all	Cmd+A
Toggle filters	Cmd+Shift+F or Ctrl+Shift+L
New worksheet	Shift+F11
Next worksheet	Ctrl+Page Down or Option+Right Arrow
Previous worksheet	Ctrl+Page Up or Option+Left Arrow
Beginning of sheet	Ctrl+Home
Last used cell	Ctrl+End
Next edge of data	Cmd+Arrow
AutoSum	Shift+Cmd+T
Recalculate	Cmd+= or F9
Formula Builder	Shift+F3
Power Query Editor	Option+F12
Visual Basic	Option+F11

These shortcuts are from Microsoft's current Excel for Mac reference. Function-key behavior can also depend on macOS keyboard settings, particularly on MacBooks.

Excel keyboard shortcuts for Windows

Windows has the largest collection of Excel-specific keyboard workflows, particularly through Ctrl, Alt, function keys and Ribbon access keys.

Workbook shortcuts

Shortcut	Action	When to use it
Ctrl+N	New workbook	Start a new file
Ctrl+O	Open workbook	Open an existing file
Ctrl+S	Save	Save changes
F12	Save As	Save another version or location
Ctrl+W	Close workbook	Finish working with a file
Ctrl+P	Print	Open print options
Ctrl+F4	Close workbook window	Close the active workbook
Alt+F4	Close Excel	Exit the application

The important distinction is that Ctrl+W closes the workbook, while Alt+F4 closes the Excel application.

Navigation shortcuts

Shortcut	Action
Ctrl+Home	Move to the beginning of the worksheet
Ctrl+End	Move to the last used cell
Ctrl+Arrow	Move to the edge of the current data region
Ctrl+Shift+Arrow	Select to the edge of the current data region
Page Up	Move one screen up
Page Down	Move one screen down
Alt+Page Up	Move one screen left
Alt+Page Down	Move one screen right
Ctrl+Page Up	Previous worksheet
Ctrl+Page Down	Next worksheet
F5	Open Go To
Ctrl+G	Open Go To

Microsoft defines Ctrl+End as the last cell on the worksheet based on the lowest used row and rightmost used column. It is therefore not necessarily the last cell containing visible data in the way a beginner might expect.

Selection shortcuts

Shortcut	Action
Shift+Arrow	Extend selection by one cell
Ctrl+Shift+Arrow	Extend selection to the edge of a data region
Ctrl+Spacebar	Select entire column
Shift+Spacebar	Select entire row
Ctrl+A	Select current data region; repeat for broader selections
Ctrl+Shift+Home	Select from active cell toward the beginning
Ctrl+Shift+End	Select through the last used cell
F8	Extend Selection mode
Shift+F8	Add a nonadjacent range

For a large contiguous dataset, Ctrl+Shift+Arrow is usually more useful than dragging with the mouse because the selection follows the data boundary.

Editing shortcuts

Shortcut	Action
F2	Edit active cell
Ctrl+C	Copy
Ctrl+X	Cut
Ctrl+V	Paste
Ctrl+Alt+V	Paste Special
Ctrl+Z	Undo
Ctrl+Y	Redo/repeat
Delete	Clear cell contents
Ctrl+Enter	Fill selected cells with the current entry
Alt+Enter	New line within a cell
Ctrl+D	Fill down
Ctrl+R	Fill right
Ctrl+E	Flash Fill

Practical example: If you have 500 rows and want the same formula in every selected cell, select the destination range, type the formula and press Ctrl+Enter. Microsoft documents this as a way to enter the same formula into a selected range.

Formatting shortcuts

Shortcut	Action
Ctrl+1	Format Cells
Ctrl+B	Bold
Ctrl+I	Italic
Ctrl+U	Underline
Ctrl+5	Strikethrough
Ctrl+Shift+&	Apply outline border
Ctrl+Shift+_	Remove outline border
Ctrl+K	Insert hyperlink
F7	Spell check

For number formats:

Shortcut	Format
Ctrl+Shift+~	General
Ctrl+Shift+$	Currency
Ctrl+Shift+%	Percentage
Ctrl+Shift+#	Date
Ctrl+Shift+@	Time
Ctrl+Shift+!	Number with two decimals and thousands separator
Ctrl+Shift+^	Scientific

These are Excel's documented built-in number-format shortcuts.

Rows and columns

For row and column work, selection is usually the first step.

Click inside the target row or column.
Press Shift+Spacebar for the row or Ctrl+Spacebar for the column.
Use Ctrl+Shift++ to insert or Ctrl+- to delete.

To hide selected rows, use Ctrl+9. To hide selected columns, use Ctrl+0. Microsoft also documents corresponding unhide shortcuts.

Data, tables, filters and sorting

What is the shortcut to create an Excel table?

On Windows, press Ctrl+T or Ctrl+L with your data selected. Excel opens the Create Table dialog.

A table is useful when your dataset will grow because it gives the range structured behavior, including filter controls and table-aware formulas.

What is the shortcut to turn filters on and off?

On Windows, use Ctrl+Shift+L.

On Mac, Microsoft lists Cmd+Shift+F or Ctrl+Shift+L for adding or removing a filter.

The filter command does not calculate anything. It changes which records are displayed.

What is the fastest way to filter data?

For a conventional range or Excel Table:

Select a cell in the dataset.
Turn on filters.
Open the column's filter menu.
Choose a value, text condition, number condition or date condition.
Clear the filter when finished.

Excel filters hide rows temporarily; they do not delete the underlying records. Microsoft documents filtering for both ranges and tables.

For formula-driven reports in modern Excel, FILTER can be more useful because it creates a result that updates as the source data changes.

How do you remove duplicates?

Use Data > Remove Duplicates when you want Excel to remove duplicate records from a dataset.

If you want to preserve the source and return a distinct list with a formula, use:

=UNIQUE(A2:A1000)

UNIQUE is available in Excel versions supporting dynamic arrays, including Excel 2021 and later listed by Microsoft.

Excel formulas and cell references

A formula is an expression you create in Excel. A function is a built-in operation that can be used inside a formula.

For example:

=A2+B2

is a formula using an arithmetic operator.

=SUM(A2:A20)

is a formula that uses the built-in SUM function.

Microsoft describes formulas as beginning with an equal sign and allowing references to individual cells, ranges, other worksheets and other workbooks.

Relative references

A relative reference changes when you copy a formula.

=A2*B2

If copied one row down, it becomes:

=A3*B3

This is useful when the same calculation should be repeated for every row.

Absolute references

An absolute reference stays fixed:

=$B$1

If you copy the formula, $B$1 remains $B$1.

This is useful for assumptions such as a tax rate, exchange rate or fixed percentage stored in one cell.

Mixed references

You can lock only the column:

=$B2

or only the row:

=B$2

This is particularly useful when building calculation grids.

What does F4 do in Excel formulas?

When a cell reference is selected while editing a formula, F4 cycles through relative, absolute and mixed references.

For example:

A1
$A$1
A$1
$A1

Microsoft documents F4 for cycling through these reference combinations.

This is one of the Excel shortcuts worth learning early because it prevents repeatedly typing $ symbols.

Formula calculation and auditing shortcuts

AutoSum

Windows: Alt+=
Mac: Shift+Cmd+T

AutoSum inserts a SUM formula based on nearby data.

For example, if monthly revenue is in B2:B13 and you select B14:

=SUM(B2:B13)

Excel attempts to identify the appropriate range automatically.

Always check the proposed range before accepting it. Blank rows, nearby totals and unusual layouts can affect what Excel selects.

Show formulas instead of results

Press:

Ctrl+`

The grave accent is the key typically located above the Tab key on a US keyboard.

Press it again to return to normal results. Microsoft documents this shortcut for displaying formulas in worksheet cells.

Recalculate formulas

Shortcut	Purpose
F9	Recalculate formulas that need recalculation in all open workbooks
Shift+F9	Recalculate the active worksheet
Ctrl+Alt+F9	Recalculate all formulas
Ctrl+Alt+Shift+F9	Recheck dependencies and recalculate all formulas

These shortcuts matter most when a workbook uses manual calculation or contains complex dependencies. Excel normally uses automatic calculation by default.

Formula auditing workflow

When a result looks wrong, I would check it in this order:

Press F2 and inspect the formula.
Check the referenced cells.
Look for relative references that moved unexpectedly.
Check whether an absolute reference should be used.
Press `Ctrl+`` to inspect surrounding formulas.
Check for errors such as #N/A, #REF! or #VALUE!.
If calculation is manual, recalculate with F9.

This is usually faster than rebuilding a formula from scratch.

Most useful Excel functions

Microsoft organizes worksheet functions by categories such as math, logical, lookup and reference, text, date and time, statistical, financial and information functions. Its current function reference also marks functions by the Excel version in which they were introduced.

The following is a practical reference rather than a claim to contain every Excel function ever released.

Core function reference

Function	Category	Common use
SUM	Math	Add values
SUMIF	Math	Sum using one criterion
SUMIFS	Math	Sum using multiple criteria
PRODUCT	Math	Multiply values
ROUND	Math	Round to specified digits
ROUNDUP	Math	Round upward
ROUNDDOWN	Math	Round downward
MROUND	Math	Round to a multiple
INT	Math	Round down to an integer
MOD	Math	Return a remainder
ABS	Math	Return absolute value
AGGREGATE	Math	Calculate while controlling hidden/error values
SUBTOTAL	Math	Calculate within filtered or grouped data
IF	Logical	Return one result when true and another when false
IFS	Logical	Evaluate multiple conditions
AND	Logical	Require all conditions to be true
OR	Logical	Require at least one condition to be true
NOT	Logical	Reverse a logical result
IFERROR	Logical	Replace formula errors
IFNA	Logical	Handle `#N/A` specifically
SWITCH	Logical	Match an expression against multiple values
XLOOKUP	Lookup	Modern lookup
XMATCH	Lookup	Return the position of a match
VLOOKUP	Lookup	Vertical lookup
HLOOKUP	Lookup	Horizontal lookup
INDEX	Lookup	Return a value by position
MATCH	Lookup	Find a position
FILTER	Dynamic array	Return records meeting criteria
SORT	Dynamic array	Sort an array
SORTBY	Dynamic array	Sort by another range or array
UNIQUE	Dynamic array	Return distinct values
CHOOSE	Lookup	Select from a list
LEFT	Text	Extract characters from the left
RIGHT	Text	Extract characters from the right
MID	Text	Extract characters from the middle
LEN	Text	Count characters
TRIM	Text	Remove excess spaces
CLEAN	Text	Remove nonprintable characters
FIND	Text	Find text, case-sensitive
SEARCH	Text	Find text, not case-sensitive
SUBSTITUTE	Text	Replace matching text
REPLACE	Text	Replace characters by position
TEXT	Text	Format a value as text
TEXTJOIN	Text	Join text with a delimiter
CONCAT	Text	Combine text
TEXTSPLIT	Text	Split text into rows/columns
TEXTBEFORE	Text	Return text before a delimiter
TEXTAFTER	Text	Return text after a delimiter
DATE	Date/time	Create a date
TODAY	Date/time	Return today's date
NOW	Date/time	Return current date and time
DAY	Date/time	Extract day
MONTH	Date/time	Extract month
YEAR	Date/time	Extract year
EDATE	Date/time	Shift a date by months
EOMONTH	Date/time	Return month-end date
WORKDAY	Date/time	Calculate a future/past workday
NETWORKDAYS	Date/time	Count workdays between dates
AVERAGE	Statistical	Calculate arithmetic mean
AVERAGEIF	Statistical	Average using one criterion
AVERAGEIFS	Statistical	Average using multiple criteria
COUNT	Statistical	Count numbers
COUNTA	Statistical	Count nonempty values
COUNTBLANK	Statistical	Count blank cells
COUNTIF	Statistical	Count using one criterion
COUNTIFS	Statistical	Count using multiple criteria
MAX	Statistical	Largest value
MIN	Statistical	Smallest value
MAXIFS	Statistical	Largest value meeting criteria
MINIFS	Statistical	Smallest value meeting criteria
MEDIAN	Statistical	Middle value
RANK.EQ	Statistical	Rank a number
STDEV.S	Statistical	Sample standard deviation
VAR.S	Statistical	Sample variance
PMT	Financial	Periodic loan/payment amount
PV	Financial	Present value
FV	Financial	Future value
NPV	Financial	Net present value
IRR	Financial	Internal rate of return
RATE	Financial	Interest rate per period
NPER	Financial	Number of periods
IPMT	Financial	Interest payment
PPMT	Financial	Principal payment
ISBLANK	Information	Test whether a cell is blank
ISNUMBER	Information	Test whether a value is numeric
ISTEXT	Information	Test whether a value is text
ISERROR	Information	Test whether a value is an error
ISNA	Information	Test specifically for `#N/A`
ISFORMULA	Information	Test whether a cell contains a formula

Microsoft's current function reference confirms these functions and identifies version-specific additions.

Excel math functions

SUM

=SUM(B2:B20)

Adds the values in B2:B20.

SUMIF

=SUMIF(A2:A100,"West",B2:B100)

Adds values in column B when column A equals West.

SUMIFS

=SUMIFS(D2:D100,B2:B100,"West",C2:C100,"Completed")

Adds column D only when both conditions are met.

SUMIFS is particularly useful for business reports because it lets you combine criteria without building long nested IF formulas. Microsoft includes SUMIFS among its featured functions.

ROUND

=ROUND(B2,2)

Rounds the value in B2 to two decimal places.

MOD

=MOD(A2,7)

Returns the remainder after division by 7.

This is useful for recurring patterns, such as identifying cycles or distributing items across groups.

Excel logical functions

IF

=IF(C2>=70,"Pass","Review")

The formula checks a condition and returns one of two results.

Multiple conditions

=IF(AND(B2>=70,C2="Complete"),"Ready","Review")

Use AND when every condition must be true.

Use OR when any condition can be true:

=IF(OR(B2="Urgent",C2="Overdue"),"Escalate","Normal")

IFS

For several conditions, IFS can be easier to read:

=IFS(
B2>=90,"A",
B2>=80,"B",
B2>=70,"C",
TRUE,"Needs review"
)

IFERROR

=IFERROR(A2/B2,0)

This replaces an error with 0.

Use it carefully. Hiding every error can make a broken calculation harder to diagnose.

For example, if a missing lookup should be reported differently from a calculation error, IFNA may be more appropriate.

Excel lookup functions

Lookup formulas are among the most useful Excel functions for business, finance, accounting and reporting.

What is XLOOKUP?

XLOOKUP searches one range or array and returns a corresponding value from another range or array.

A basic example:

=XLOOKUP(A2,E2:E100,F2:F100,"Not found")

If A2 contains an employee ID, Excel searches E2:E100 and returns the matching value from F2:F100.

Microsoft describes XLOOKUP as an improved alternative to older lookup methods because it can search in different directions and uses exact matching by default.

XLOOKUP vs. VLOOKUP

Feature	XLOOKUP	VLOOKUP
Exact match default	Yes	No
Search direction	Flexible	Vertical
Return column location	Independent	Must be to the right
Return multiple columns	Yes, where supported	More limited
Easier to insert columns safely	Generally	Less flexible
Older Excel compatibility	More limited	Very broad

A traditional VLOOKUP might look like:

=VLOOKUP(A2,E2:G100,3,FALSE)

The 3 means Excel returns the third column of the selected table array.

XLOOKUP separates the lookup and return ranges:

=XLOOKUP(A2,E2:E100,G2:G100,"Not found")

That makes the intent easier to read.

Microsoft continues to document VLOOKUP for current Excel versions, while recommending XLOOKUP where the version supports it.

When should you still use VLOOKUP?

VLOOKUP remains useful when:

You need compatibility with older Excel versions.
Existing workbooks already use it.
Your lookup value is in the first column of the lookup range.
You are maintaining a shared workbook where changing the formula would add unnecessary risk.

There is no need to rewrite a working VLOOKUP merely because XLOOKUP exists.

INDEX and MATCH

The traditional combination is:

=INDEX(C2:C100,MATCH(A2,A2:A100,0))

MATCH finds the position and INDEX returns the value at that position.

This approach remains important when working with older Excel versions or existing models.

XMATCH

XMATCH returns the relative position of an item in a range or array.

For example:

=XMATCH(A2,E2:E100,0)

The result is the position of the matching value.

This becomes useful when you need the position itself or want to combine it with another function.

Dynamic array functions

Dynamic arrays changed how Excel formulas can return multiple results.

Instead of creating a formula in every row, one formula can return a range of results that spills into neighboring cells.

Microsoft introduced functions such as FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and RANDARRAY with dynamic-array functionality in modern Excel versions.

FILTER

=FILTER(A2:D100,C2:C100="West","No results")

This returns rows where column C equals West.

Microsoft defines FILTER as filtering an array according to a Boolean include array.

SORT

=SORT(A2:D100,3,1)

This sorts the range by its third column in ascending order.

SORTBY

=SORTBY(A2:D100,D2:D100,-1)

This sorts the output according to another range.

UNIQUE

=UNIQUE(B2:B100)

Returns each distinct value once.

SEQUENCE

=SEQUENCE(12)

Returns a sequence from 1 through 12.

TAKE and DROP

Modern Excel versions support functions such as:

=TAKE(A2:D100,10)

and:

=DROP(A2:D100,1)

These are useful when working with arrays programmatically inside formulas.

Microsoft's current function list marks TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, VSTACK, HSTACK, WRAPROWS, WRAPCOLS and EXPAND as newer functions with version markers.

VSTACK and HSTACK

=VSTACK(A2:B10,D2:E10)

combines arrays vertically.

=HSTACK(A2:B10,D2:E10)

combines them horizontally.

These functions are especially useful for building formula-based reporting outputs without manually copying ranges.

What does #SPILL! mean?

A #SPILL! error occurs when a dynamic-array formula cannot place its results into the cells it needs.

Common causes include:

Something already occupies the spill range.
Merged cells interfere with the result.
The formula is inside a location where spilling is not possible.

The practical fix is to inspect the intended spill area and remove the blocking content when appropriate.

Advanced functional Excel functions

Modern Excel also includes functions that treat formulas more like reusable calculations.

LET

LET assigns names to intermediate results.

For example:

=LET(
revenue,B2*C2,
cost,B2*D2,
revenue-cost
)

Instead of repeating a calculation several times, you can give it a name.

Microsoft lists LET as a function introduced in Excel 2021.

LAMBDA

LAMBDA allows you to create reusable custom functions without traditional VBA.

Microsoft's current function reference lists LAMBDA with a 2024 version marker.

MAP, REDUCE, SCAN and BYROW/BYCOL

These functions apply LAMBDA logic across arrays.

Examples include:

=MAP(A2:A10,LAMBDA(x,x*2))

and:

=BYROW(A2:D10,LAMBDA(row,SUM(row)))

These are advanced tools. They are useful when ordinary functions become repetitive, but they are not the first functions I would teach a beginner.

Excel text functions

Text cleanup is common when importing customer names, product codes, addresses and data from other systems.

LEFT, RIGHT and MID

=LEFT(A2,5)

returns the first five characters.

=RIGHT(A2,4)

returns the last four.

=MID(A2,3,6)

starts at character 3 and returns six characters.

LEN

=LEN(A2)

counts the characters in a cell.

TRIM

=TRIM(A2)

removes unnecessary spaces from text.

FIND vs. SEARCH

Both locate text inside another text string.

The key difference is that FIND is case-sensitive, while SEARCH is not. Microsoft's text-function reference confirms this distinction.

For example:

=FIND("A",A2)

and:

=SEARCH("A",A2)

can return different results when letter case matters.

SUBSTITUTE vs. REPLACE

SUBSTITUTE replaces matching text:

=SUBSTITUTE(A2,"Old","New")

REPLACE replaces characters according to their position:

=REPLACE(A2,1,3,"New")

Use SUBSTITUTE when you know what text needs replacing.

Use REPLACE when you know where the characters are.

TEXTJOIN

=TEXTJOIN(", ",TRUE,A2:A10)

combines values with a comma and space between them while ignoring empty cells.

TEXTSPLIT

=TEXTSPLIT(A2,",")

splits text using a delimiter.

For example, a cell containing:

New York,NY,10001

can be split into separate columns.

Microsoft lists TEXTSPLIT for Microsoft 365 and Excel 2024, including Mac versions. Its syntax supports separate column and row delimiters.

TEXTBEFORE and TEXTAFTER

=TEXTBEFORE(A2,"@")

returns everything before the @.

=TEXTAFTER(A2,"@")

returns everything after it.

These functions are useful for parsing email addresses, codes, filenames and structured text.

Excel date and time functions

Dates in Excel become much easier to manage when you stop treating them as ordinary text.

TODAY vs. NOW

=TODAY()

returns today's date.

=NOW()

returns the current date and time.

The distinction matters when a report needs only a date rather than a timestamp.

Extract parts of a date

=YEAR(A2)
=MONTH(A2)
=DAY(A2)

These return the year, month and day components.

Add months

=EDATE(A2,3)

moves the date three months forward.

End of month

=EOMONTH(A2,0)

returns the final day of the month containing the date.

Working days

To calculate the number of workdays between two dates:

=NETWORKDAYS(A2,B2)

To calculate a date a certain number of workdays in the future:

=WORKDAY(A2,10)

These functions are useful for project schedules, invoice terms and operational reporting.

Statistical functions for analysis

For everyday analysis, these functions cover a large portion of common needs.

AVERAGE

=AVERAGE(B2:B100)

Calculates the arithmetic mean.

COUNT, COUNTA and COUNTBLANK

=COUNT(B2:B100)

counts numeric cells.

=COUNTA(B2:B100)

counts nonempty cells.

=COUNTBLANK(B2:B100)

counts blank cells.

These functions answer different questions. Choosing the wrong one can make a report look complete while silently producing the wrong count.

COUNTIF and COUNTIFS

=COUNTIF(C2:C100,"Completed")

counts one condition.

=COUNTIFS(B2:B100,"West",C2:C100,"Completed")

counts records satisfying multiple conditions.

MAXIFS and MINIFS

=MAXIFS(D2:D100,B2:B100,"West")

returns the largest value in D where B is West.

These are useful when you need an extreme value for a specific group rather than the entire dataset.

MEDIAN

=MEDIAN(B2:B100)

returns the middle value of the dataset.

For data containing extreme values, the median can tell a different story from the average.

Excel financial functions

Excel's financial functions are useful for loans, investments, cash-flow models and financial analysis.

PMT

=PMT(6%/12,60,-25000)

calculates a periodic payment using a 6% annual rate, 60 monthly periods and a $25,000 loan amount.

PV

PV calculates present value.

FV

FV calculates future value.

NPER

NPER calculates the number of periods required under specified assumptions.

RATE

RATE estimates the interest rate per period.

IPMT and PPMT

IPMT calculates the interest component of a payment.

PPMT calculates the principal component.

NPV and IRR

NPV is used for net present value calculations.

IRR estimates the internal rate of return for a series of cash flows.

For financial models, document the assumptions around these functions. A correct formula can still produce a misleading decision if the cash-flow timing, sign convention or assumptions are wrong.

Microsoft's current function reference categorizes PMT, PV, FV, NPV, IRR, RATE, IPMT, PPMT and NPER as financial functions.

Modern Excel analysis functions

Microsoft 365 continues to add functions aimed at working directly with arrays and data.

GROUPBY

GROUPBY can group, aggregate, sort and filter data based on specified fields.

PIVOTBY

PIVOTBY performs formula-based grouping and aggregation across row and column fields.

For example, you can use it to create a summary of sales by state and year.

It is important not to confuse PIVOTBY with a traditional Excel PivotTable. Microsoft explicitly notes that the formula can produce similar outputs but is not directly related to the PivotTable feature.

Newer text functions

Current Microsoft 365 also includes functions such as:

REGEXEXTRACT
REGEXREPLACE
REGEXTEST
TRANSLATE
TRIMRANGE

Microsoft marks these as Microsoft 365 functionality in its current function reference.

Version warning: Do not copy a modern function into an older workbook and assume it will work. Microsoft uses version markers because newer functions are not available in earlier Excel releases.

Common Excel errors

Excel errors are useful diagnostic signals. The goal is not to hide them automatically.

Error	What it usually means	First thing to check
`#N/A`	A lookup or matching operation found no result	Lookup value and match type
`#VALUE!`	Wrong type of value or argument	Data types and formula arguments
`#REF!`	Invalid cell reference	Deleted or moved cells
`#DIV/0!`	Division by zero or blank denominator	Divisor
`#NAME?`	Excel does not recognize something in the formula	Function name, text quotes or named range
`#NUM!`	Invalid numeric calculation	Function inputs
`#SPILL!`	Dynamic-array result is blocked	Cells in the intended spill range
`#CALC!`	Calculation problem, often involving array logic	Formula structure and array result

What does #N/A mean?

#N/A generally indicates that a value is not available to the calculation, commonly because a lookup did not find a match.

For example:

=XLOOKUP(A2,E2:E100,F2:F100,"Not found")

lets you provide a controlled result when there is no match.

What does #REF! mean?

#REF! means a formula contains an invalid reference.

It commonly appears after cells, rows or columns used by a formula have been deleted.

The safest response is to identify the broken reference rather than wrapping the entire formula in IFERROR.

What does #DIV/0! mean?

It usually means a formula is attempting to divide by zero or by an empty cell.

For example:

=A2/B2

will fail if B2 is zero.

A controlled version might be:

=IFERROR(A2/B2,"")

But first decide whether an error should actually be hidden.

How to combine shortcuts, formulas and functions

The biggest productivity improvement does not come from memorizing hundreds of shortcuts independently.

Think in workflows.

Example: monthly sales report

Suppose you receive a raw sales file.

A practical workflow could be:

1. Turn the data into a table

Ctrl+T

This gives the dataset a structured format.

2. Filter the relevant records

Ctrl+Shift+L

Then filter the region, date or status you need.

3. Calculate totals

Use:

=SUMIFS(...)

when multiple conditions determine the total.

4. Retrieve related information

Use:

=XLOOKUP(...)

to bring in a manager, category or target.

5. Build a distinct list

Use:

=UNIQUE(...)

when you need a list of distinct customers, products or regions.

6. Create a dynamic report

Combine:

=FILTER(...)

with SORT, SORTBY or other functions.

7. Verify the formulas

Press `Ctrl+`` to display formulas and inspect the results.

This is the relationship between shortcuts and functions: the shortcut reduces the mechanical work, while the formula handles the logic.

5 actionable Excel productivity tips

1. Learn shortcuts by task, not alphabetically

Do not try to memorize a giant list.

Start with:

Copy, paste, undo and save
Navigation
Selection
Editing
Formatting
Tables and filters
Formula work

You will remember shortcuts more reliably when they solve a recurring problem.

2. Learn functions in families

Instead of memorizing isolated formulas, learn related groups.

For example:

COUNT
COUNTIF
COUNTIFS

or:

SUM
SUMIF
SUMIFS

The pattern makes the next function easier to understand.

3. Prefer exact references over manual repetition

If a value is used repeatedly, store it in a cell and reference that cell.

Use:

=$B$1

rather than typing the same number into many formulas.

Microsoft recommends placing constants in individual cells where they can be changed easily and then referencing those cells in formulas.

4. Use modern functions when the workbook supports them

If you are working in Microsoft 365 or a recent Excel version, functions such as XLOOKUP, FILTER, UNIQUE, TEXTSPLIT and LET can make many formulas easier to maintain.

But compatibility matters. If a workbook must work in an older Excel environment, check function availability first.

5. Don't hide errors before understanding them

IFERROR is useful, but using it everywhere can conceal broken logic.

First identify why the error exists.

Then decide whether the user should see an error, a blank cell, zero or a custom message.

Excel shortcut categories worth learning

For a complete working reference, organize your shortcut knowledge into these groups.

Category	What to learn
Workbook	Open, save, close, print
Worksheet	Add, remove and move between sheets
Navigation	First cell, last used cell, data boundaries
Selection	Rows, columns, ranges and regions
Editing	Copy, paste, undo, redo, fill
Formatting	Fonts, borders and Format Cells
Number formatting	Currency, percentage, dates and times
Rows and columns	Insert, delete, hide and unhide
Data	Filter, sort, refresh and Flash Fill
Tables	Create and work with Excel Tables
Filters	Toggle and navigate filters
Sorting	Sort data and work with sorted results
Formulas	Edit, reference and insert functions
Calculation	Recalculate workbooks and worksheets
Formula auditing	Show formulas and inspect references
Charts	Create embedded or chart-sheet charts
PivotTables	Navigate and manage PivotTable data
Power Query	Open and refresh query workflows
Power Pivot	Work with data-model functionality
Macros/VBA	Open macro and Visual Basic tools
Function keys	F1-F12 Excel-specific commands
Ribbon	Alt-based access keys
Windows	Windows-specific Ctrl/Alt workflows
Mac	Command/Control/Option differences

Not every category has one universal shortcut. Some features depend on the selected object, Excel version or platform.

Ribbon and Alt shortcuts

Windows Excel has an additional shortcut system that many users overlook.

Press Alt to display Ribbon KeyTips.

Common tab shortcuts include:

Shortcut	Ribbon tab
Alt+F	File
Alt+H	Home
Alt+N	Insert
Alt+P	Page Layout
Alt+M	Formulas
Alt+A	Data
Alt+R	Review
Alt+W	View
Alt+Q	Search

Microsoft notes that older Alt menu sequences may still work in newer versions, but KeyTips are the more current method for navigating the Ribbon by keyboard.

This matters when a command has no convenient standalone shortcut.

Instead of reaching for the mouse, press Alt, follow the displayed KeyTips and execute the command from the keyboard.

Function keys in Excel

Function keys have several Excel-specific uses.

Key	Useful Excel action
F1	Excel Help
F2	Edit active cell
F3	Paste Name dialog
F4	Repeat action or cycle cell references while editing a formula
F5	Go To
F6	Move focus between Excel areas
F7	Spell check
F8	Extend Selection
F9	Recalculate
F10	Activate KeyTips
F11	New chart sheet
F12	Save As

Microsoft's current Windows reference documents these functions, including the multiple meanings of F4 and F9.

The important exception is that function-key behavior can differ between Windows and macOS.

Charts, PivotTables, Power Query and Power Pivot

Shortcuts are not limited to ordinary cell editing.

Charts

On Windows:

Alt+F1 creates an embedded chart from the current range.
F11 creates a chart sheet.

PivotTables

PivotTables are better suited to interactive summaries than trying to reproduce every grouping manually with formulas.

For formula-based summaries in modern Excel, functions such as GROUPBY and PIVOTBY offer another approach. The right choice depends on whether you need an interactive PivotTable or a formula-driven result.

Power Query

On Windows:

Alt+F12 opens the Power Query Editor.

On Mac, Microsoft documents Option+F12 for opening Power Query Editor.

Power Query is usually the better tool when the task is repeated data import, cleanup and transformation rather than a single formula calculation.

Power Pivot

Power Pivot is designed around Excel's data-model functionality.

Its workflow is different from ordinary worksheet formulas, so it should not be treated as simply another Excel function category.

Windows vs. Mac: what should you remember?

The safest rule is simple: do not assume a Windows shortcut has an identical Mac equivalent.

Some common commands map naturally:

Windows Ctrl+C → Mac Cmd+C
Windows Ctrl+V → Mac Cmd+V
Windows Ctrl+Z → Mac Cmd+Z
Windows Ctrl+B → Mac Cmd+B

But Excel also has platform-specific shortcuts using Control and Option.

For example, Microsoft lists:

Windows: Alt+=
Mac: Shift+Cmd+T

for AutoSum.

Mac users should also account for macOS keyboard settings and function-key behavior. Microsoft notes that system shortcuts can conflict with Excel shortcuts.

What I verified

I checked the core shortcut behavior and function availability against Microsoft's current Excel documentation rather than relying on generic shortcut lists.

The most important verification points were:

Windows navigation, selection and editing shortcuts.
Windows calculation and formula shortcuts.
Windows chart, Power Query and VBA shortcuts.
Mac-specific shortcut differences.
Relative, absolute and mixed cell references.
Current Excel function categories.
Version markers for newer functions.
Dynamic-array functions.
XLOOKUP, VLOOKUP and lookup behavior.
TEXTSPLIT and modern text functions.
GROUPBY and PIVOTBY.
Formula recalculation behavior.

Microsoft's function reference currently covers Excel for Microsoft 365, Excel 2024, Excel 2021 and earlier versions, with version markers identifying functions unavailable in earlier releases.

Limitations I found

There is no useful single number for "how many Excel shortcuts exist."

The available shortcuts vary by:

Excel version
Windows or macOS
Desktop or web Excel
Selected object
Keyboard layout
Excel feature being used
macOS system settings

The same problem applies to Excel functions. Microsoft maintains version-specific function references rather than presenting the concept as one permanently fixed list.

That is why this guide is better treated as a current practical Excel reference than a claim to contain every shortcut and function ever created.

How to learn Excel formulas quickly

If you're starting from scratch, don't memorize formulas randomly.

Use this progression.

Step 1: Learn cell references

Understand:

A1
$A$1
A$1
$A1

Without this, copying formulas will remain confusing.

Step 2: Learn basic calculations

Start with:

=A2+B2
=A2-B2
=A2*B2
=A2/B2

Step 3: Learn aggregation

Move to:

SUM
AVERAGE
COUNT
COUNTA
MIN
MAX

Step 4: Learn conditional calculations

Then:

IF
SUMIF
SUMIFS
COUNTIF
COUNTIFS
AVERAGEIF
AVERAGEIFS

Step 5: Learn lookups

Learn:

XLOOKUP
XMATCH
INDEX
MATCH

Then understand VLOOKUP so you can maintain existing workbooks.

Step 6: Learn arrays

Move to:

FILTER
SORT
SORTBY
UNIQUE
SEQUENCE

Step 7: Learn text and date manipulation

Add:

TEXTJOIN
TEXTSPLIT
TEXTBEFORE
TEXTAFTER
LEFT
RIGHT
MID
DATE
EDATE
EOMONTH
WORKDAY
NETWORKDAYS

Step 8: Learn advanced functions only when needed

Functions such as LET, LAMBDA, MAP, REDUCE, SCAN, BYROW and BYCOL are valuable, but there is little benefit in memorizing them before you have a problem that calls for them.

Excel formulas vs. functions: the simple distinction

Think of a formula as the complete instruction and a function as one of the tools inside that instruction.

For example:

=SUM(B2:B10)*1.05

is a formula.

SUM is the function.

The *1.05 is additional formula logic.

You can also create formulas without functions:

=B2*C2

This distinction makes Excel easier to learn because you don't need to treat every formula as a separate piece of knowledge.

Practical Excel reference by task

When you're stuck, start with the task rather than the function name.

I need to...	Start with
Add values	SUM
Add values using criteria	SUMIF / SUMIFS
Count records	COUNT / COUNTA
Count records using criteria	COUNTIF / COUNTIFS
Average values	AVERAGE
Average based on conditions	AVERAGEIF / AVERAGEIFS
Find a matching record	XLOOKUP
Find a position	XMATCH
Maintain an older lookup	VLOOKUP / INDEX + MATCH
Filter records with a formula	FILTER
Return distinct values	UNIQUE
Sort a dynamic result	SORT / SORTBY
Combine text	TEXTJOIN / CONCAT
Split text	TEXTSPLIT
Extract part of text	LEFT / RIGHT / MID
Find text	FIND / SEARCH
Replace text	SUBSTITUTE / REPLACE
Work with dates	DATE / EDATE / EOMONTH
Count workdays	NETWORKDAYS
Add workdays	WORKDAY
Handle errors	IFERROR / IFNA
Build conditional logic	IF / IFS / SWITCH
Calculate a loan payment	PMT
Calculate investment value	PV / FV
Analyze returns	NPV / IRR
Create a reusable calculation	LET / LAMBDA
Group array data	GROUPBY / PIVOTBY

Excel shortcuts and functions FAQ

What are the most useful Excel shortcuts?

For most users, start with:

Ctrl+C, Ctrl+V, Ctrl+Z, Ctrl+S, F2, Ctrl+F, Ctrl+1, Ctrl+T, Ctrl+Shift+L, Ctrl+Arrow, Ctrl+Shift+Arrow, Alt+=, F4, F9 and `Ctrl+`` on Windows.

The most useful set depends on your work. Someone building financial models will use formula and navigation shortcuts differently from someone cleaning imported data.

What are the most commonly used Excel shortcuts?

Copy, paste, undo, save, find, edit cell, navigation, selection and formatting shortcuts are among the most broadly useful. Microsoft's own shortcut reference begins with frequently used commands such as save, copy, paste, undo, bold and workbook navigation.

What is the shortcut to go to the first cell in Excel?

On Windows, press Ctrl+Home.

On Mac, Microsoft also documents Ctrl+Home, with Ctrl+Fn+Left Arrow on MacBook keyboards where needed.

What is the shortcut to go to the last used cell?

Press Ctrl+End on both Windows and Mac.

On MacBook keyboards, Microsoft documents Ctrl+Fn+Right Arrow as the corresponding combination.

How do you select an entire row in Excel?

On Windows, click a cell in the row and press Shift+Spacebar.

For a column, use Ctrl+Spacebar.

What is the shortcut to edit a cell?

Press F2.

This moves the insertion point into the active cell or formula bar depending on the editing state.

What is the shortcut for copy and paste?

Windows:

Copy: Ctrl+C
Paste: Ctrl+V

Mac:

Copy: Cmd+C
Paste: Cmd+V

What is the shortcut for undo and redo?

Windows:

Undo: Ctrl+Z
Redo/repeat: Ctrl+Y

Mac:

Undo: Cmd+Z
Redo: Cmd+Y or Cmd+Shift+Z

What is the shortcut for find and replace?

On Windows, use Ctrl+F for Find.

On Mac, use Cmd+F for Find and Ctrl+H or Cmd+Shift+H for Replace.

What is the shortcut to insert a new worksheet?

On Windows, Shift+F11 inserts a new worksheet.

Microsoft also documents this shortcut for Excel for Mac.

What is the shortcut for AutoSum?

Windows: Alt+=

Mac: Shift+Cmd+T

What does F4 do in Excel?

While editing a formula and a cell reference is selected, F4 cycles through relative, absolute and mixed references.

Outside formula editing, F4 can repeat the last command when applicable.

How do you show formulas instead of values?

On Windows, press Ctrl+`.

This toggles the worksheet between formula display and normal result display.

How do you quickly select a large range?

For a contiguous dataset, use Ctrl+Shift+Arrow on Windows.

Excel extends the selection toward the edge of the current data region.

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP is more flexible: it can search in different directions and uses exact matching by default.

VLOOKUP is older and widely compatible, but its lookup structure is more restrictive.

What is the difference between INDEX MATCH and XLOOKUP?

INDEX + MATCH separates finding a position from returning a value and remains useful for compatibility with older workbooks.

XLOOKUP packages the lookup operation into one function and is generally easier to read for modern Excel work.

What are dynamic array functions?

Dynamic array functions can return multiple values from one formula and spill those results into neighboring cells.

Examples include:

FILTER
SORT
SORTBY
UNIQUE
SEQUENCE

Microsoft introduced these capabilities in modern Excel releases and documents version markers for the relevant functions.

What does #SPILL! mean?

It means a dynamic-array formula cannot place its result into the required cells.

Check the cells around the formula for existing data or other conditions blocking the spill range.

How do you handle Excel errors?

First identify the cause.

Then use an appropriate function such as:

=IFERROR(...)

or:

=IFNA(...)

when replacing the error is actually the desired behavior.

Do not use error handling as a substitute for checking broken formulas.

A practical learning path

If you want to become faster in Excel rather than simply collect a large list of shortcuts, I would learn in this order:

1. Navigation

Master Ctrl+Arrow, Ctrl+Home, Ctrl+End, Ctrl+Page Up and Ctrl+Page Down.

2. Selection

Learn Ctrl+Shift+Arrow, Ctrl+Spacebar, Shift+Spacebar and Ctrl+A.

3. Editing

Learn copy, paste, undo, redo, F2, Ctrl+D and Ctrl+R.

4. Data

Learn Tables, filters, sorting and Flash Fill.

5. Formula mechanics

Understand relative, absolute and mixed references.

6. Core functions

Learn SUM, SUMIFS, COUNTIFS, AVERAGEIFS, IF and IFERROR.

7. Lookups

Learn XLOOKUP, then understand INDEX + MATCH and VLOOKUP for compatibility.

8. Dynamic arrays

Learn FILTER, SORT, SORTBY and UNIQUE.

9. Specialized functions

Move into text, dates, statistics and financial functions according to the work you actually do.

10. Advanced Excel

Only then consider LET, LAMBDA, Power Query, Power Pivot, VBA and advanced array functions.

This approach gives you a usable skill set instead of a memorized list.

Sources

The technical claims in this article were checked primarily against Microsoft's official Excel documentation.

Excel Shortcuts and Functions: Complete Reference Guide

Jump to

Excel shortcut cheat sheet

Windows

Mac

Excel keyboard shortcuts for Windows

Workbook shortcuts

Navigation shortcuts

Selection shortcuts

Editing shortcuts

Formatting shortcuts

Rows and columns

Data, tables, filters and sorting

What is the shortcut to create an Excel table?

What is the shortcut to turn filters on and off?

What is the fastest way to filter data?

How do you remove duplicates?

Excel formulas and cell references

Relative references

Absolute references

Mixed references

What does F4 do in Excel formulas?

Formula calculation and auditing shortcuts

AutoSum

Show formulas instead of results

Recalculate formulas

Formula auditing workflow

Most useful Excel functions

Core function reference

Excel math functions

SUM

SUMIF

SUMIFS

ROUND

MOD

Excel logical functions

IF

Multiple conditions

IFS

IFERROR

Excel lookup functions

What is XLOOKUP?

XLOOKUP vs. VLOOKUP

When should you still use VLOOKUP?

INDEX and MATCH

XMATCH

Dynamic array functions

FILTER

SORT

SORTBY

UNIQUE

SEQUENCE

TAKE and DROP

VSTACK and HSTACK

What does #SPILL! mean?

Advanced functional Excel functions

LET

LAMBDA

MAP, REDUCE, SCAN and BYROW/BYCOL

Excel text functions

LEFT, RIGHT and MID

LEN

TRIM

FIND vs. SEARCH

SUBSTITUTE vs. REPLACE

TEXTJOIN

TEXTSPLIT

TEXTBEFORE and TEXTAFTER

Excel date and time functions

TODAY vs. NOW

Extract parts of a date

Add months

End of month

Working days

Statistical functions for analysis

AVERAGE

COUNT, COUNTA and COUNTBLANK

COUNTIF and COUNTIFS

MAXIFS and MINIFS

MEDIAN