Excel & Google Sheets function and shortcut dictionary

Search "add only the matching rows", "the leading zero disappears" or "new line in the same cell" and get the function or shortcut you needed, without knowing its name. 50 functions and features and 22 shortcuts, each with syntax, an example, how Google Sheets differs, and the mistake that usually causes the error you are seeing.

Runs in your browser · nothing is uploaded

Search by what you want to do, or by function name

50 functions and features, 22 shortcuts. Add a second word to narrow it down.

Functions and features

Sums and counts

SUMadd everything up

Adds every number in the range, ignoring text and blank cells.

Syntax
SUM(범위)

Example (click to copy)
→ B2부터 B100까지의 합

What usually goes wrong: Numbers stored as text are skipped. If the total looks wrong, check for values left-aligned or flagged with a green triangle.

See also:

SUMIF / SUMIFSsum only matching rows

Adds only the rows that meet a condition. Use SUMIFS when there is more than one condition.

Syntax
SUMIF(조건범위, 조건, 합계범위) · SUMIFS(합계범위, 조건범위1, 조건1, …)

Example (click to copy)
→ 영업팀의 2026년 이후 금액 합계

What usually goes wrong: The argument order differs: SUMIF takes the criteria range first, SUMIFS takes the sum range first. Swapping them returns 0.

See also:

COUNT / COUNTA / COUNTBLANKcount cells

COUNT counts numbers only, COUNTA counts every non-empty cell, COUNTBLANK counts empty ones.

Syntax
COUNT(범위) · COUNTA(범위) · COUNTBLANK(범위)

Example (click to copy)
→ 이름이 적힌 칸의 개수

What usually goes wrong: A cell holding a formula that returns "" looks empty but COUNTA still counts it. Use COUNTBLANK for truly empty cells.

See also:

COUNTIF / COUNTIFScount matching rows

Counts the cells that meet a condition. Also the usual way to find duplicates.

Syntax
COUNTIF(범위, 조건) · COUNTIFS(범위1, 조건1, …)

Example (click to copy)
→ A2 값이 두 번 이상 나오면 TRUE

What usually goes wrong: To compare against a cell, join with &: ">="&B1. Writing ">=B1" compares against the literal text.

See also:

SUBTOTALsum only visible rows

Calculates only the rows left visible by a filter. Use 109 to also skip manually hidden rows.

Syntax
SUBTOTAL(함수번호, 범위)

Example (click to copy)
→ 필터로 보이는 행의 합계

See also:

ROUND / ROUNDUP / ROUNDDOWN / INTround a number

A digits argument of 0 gives a whole number and 2 keeps two decimals. Negative digits work too: -1 rounds to the nearest ten, so 12,345 becomes 12,350. ROUNDUP always goes up; ROUNDDOWN and INT always cut off.

Syntax
ROUND(수, 자릿수)

Example (click to copy)
→ 12350

What usually goes wrong: Reducing the decimals with number formatting is not rounding. The cell can show 100 while still holding 99.6, so totals drift by a unit. Use ROUND to change the value itself. INT and ROUNDDOWN also differ on negatives: INT(-2.5) is -3, ROUNDDOWN(-2.5, 0) is -2.

See also:

AVERAGE / AVERAGEIF / AVERAGEIFSaverage a column

Averages a range. With a condition it averages only the matching rows, using the same criteria syntax as SUMIF.

Syntax
AVERAGEIF(조건범위, 조건, 평균범위)

Example (click to copy)
→ 영업팀 행의 C열 평균

What usually goes wrong: Blank cells are skipped but zeros are counted, so filling non-responses with 0 drags the average down. Use =AVERAGEIF(range,"<>0") to exclude them. If nothing matches the condition you get #DIV/0!.

See also:

MAX / MIN / LARGE / SMALLlargest value

MAX and MIN give the largest and smallest value. For the second or third, use LARGE(range,2) or SMALL(range,3).

Syntax
LARGE(범위, k)

Example (click to copy)
→ 세 번째로 큰 값

What usually goes wrong: Usually you want the name of the row holding that value, not the value. Combine them: =INDEX(A:A, MATCH(MAX(B:B), B:B, 0)). If several rows tie for the maximum, only the first is returned.

See also:

RANK.EQrank the rows

Gives the position of a value within a range. Leave the third argument empty or 0 for largest-is-first, or set 1 for smallest-is-first.

Syntax
RANK.EQ(값, 범위, [0=내림차순])

Example (click to copy)
→ B2가 몇 등인지

What usually goes wrong: If the range is not locked with $, it shifts as you copy down and the ranks come out wrong — the usual mistake with this function. Ties share a rank and the next one is skipped: two firsts means no second, straight to third.

See also:

SUMPRODUCTquantity times price total

Multiplies two columns row by row and adds the results, so you get a total without a helper column. Divide it by the sum of the weights to get a weighted average.

Syntax
SUMPRODUCT(범위1, 범위2)

Example (click to copy)
→ 수량×단가의 총합

What usually goes wrong: The ranges must be the same size or you get #VALUE! — even one row off, like B2:B100 against C2:C101. Text inside the ranges causes the same error.

See also:

VALUE / NUMBERVALUEconvert text to number

Turns a value that looks numeric but is stored as text into a real number. This is the usual reason a total comes out as zero on a file exported from another system. To fix a whole column at once it is quicker to copy an empty cell and use Paste Special → Add over that column.

Syntax
VALUE(텍스트)

Example (click to copy)
→ 글자 "1,234"를 숫자 1234로

What usually goes wrong: If the thousands separator or currency symbol does not match your Excel locale, VALUE returns #VALUE! too — typically with data from countries that use a comma as the decimal mark. Use NUMBERVALUE, which lets you state the separators, or strip the symbols with SUBSTITUTE first.

See also:

Lookups

VLOOKUPpull a value from another table

Finds a value in the first column of a table and returns another column from the same row.

Syntax
VLOOKUP(찾을값, 표범위, 몇번째열, FALSE)

Example (click to copy)
→ 상품코드로 단가 가져오기

What usually goes wrong: Omitting the last argument makes it do an approximate match and return the wrong row. Always pass FALSE (or 0). The lookup value must also sit in the first column of the range.

See also:

XLOOKUPlook up to the leftExcel 365 / 2021

The successor to VLOOKUP. No column counting, the lookup column can sit to the right, and you can supply a fallback directly. Exact match is the default.

Syntax
XLOOKUP(찾을값, 찾을범위, 반환범위, [없을때])

Example (click to copy)
→ 이름으로 코드 찾기, 없으면 "없음"

In Google Sheets: Google Sheets has supported it since 2022.

What usually goes wrong: It does not exist in Excel 2019 or earlier. Sharing the file with someone on an older version produces #NAME? errors.

See also:

INDEX + MATCHlook left in older Excel

MATCH finds the position and INDEX pulls the value there. It does what XLOOKUP does, on versions that lack it.

Syntax
INDEX(반환범위, MATCH(찾을값, 찾을범위, 0))

Example (click to copy)
→ B열에서 찾아 A열 값을 반환

What usually goes wrong: Leaving out the 0 in MATCH switches it to approximate matching, which returns nonsense on unsorted data.

See also:

Conditions and errors

IFERROR / IFNAhide errors

Supplies a replacement when a formula errors. IFNA catches only "not found" (#N/A) and lets other errors show.

Syntax
IFERROR(수식, 오류일때) · IFNA(수식, 못찾았을때)

Example (click to copy)
→ 못 찾으면 빈칸

What usually goes wrong: IFERROR also hides genuine mistakes like #REF! and #DIV/0!. If you only mean to handle a failed lookup, IFNA is safer.

See also:

IFshow different values by condition

Returns the first value when the condition is true and the second when it is false.

Syntax
IF(조건, 참일때, 거짓일때)

Example (click to copy)
→ 60점 이상이면 합격

See also:

IFS / SWITCHmultiple conditions without nestingExcel 2019

Lists conditions side by side instead of nesting IFs, returning the first one that matches.

Syntax
IFS(조건1, 값1, 조건2, 값2, …)

Example (click to copy)
→ 점수를 등급으로

What usually goes wrong: Order decides the outcome. Put the lowest threshold first and high scores stop there. Finish with TRUE to catch the rest.

See also:

오류값 읽는 법 (#N/A, #VALUE!, #REF!)what does the error mean

Each error has one cause, so knowing the meaning points straight at the fix. #N/A: the lookup found nothing. #VALUE!: text where a number belongs. #REF!: the referenced cell was deleted. #DIV/0!: divided by zero. #NAME?: the function name is not recognised — a typo, or a function your version does not have. #SPILL!: something is sitting where the result needs to expand.

Syntax
수식 → 오류 검사

What usually goes wrong: Wrapping everything in IFERROR is the common mistake: a genuine #REF! from a broken reference ends up looking the same as a lookup that found nothing, and real breakage hides for months. If you only want to hide failed lookups, use IFNA.

See also:

수식이 글자로 보일 때formula shows as text

There are three causes. The cell format was Text, so the formula was stored as a string — set the format to General and then re-enter it with F2 and Enter, because changing the format alone does not fix it. Or calculation is set to Manual, which you switch back under the Formulas tab. Or Show Formulas is on, toggled with Ctrl + `.

Syntax
수식 탭 → 계산 옵션 → 자동

What usually goes wrong: If someone else’s file opens in Manual, that setting travelled with the file and may have been deliberate on a heavy workbook, so switching to Automatic can freeze it for a moment. The Text-format case usually affects a whole column at once, so fixing it in one pass with Paste Special or Flash Fill beats going cell by cell.

See also:

순환 참조 (Circular Reference)circular reference warning

Happens when a formula ends up referring back to its own cell — classically =SUM(B2:B10) typed into B10, where the total is inside the range it totals. Formulas → Error Checking → Circular References lists the exact cell.

Syntax
수식 탭 → 오류 검사 → 순환 참조

What usually goes wrong: Dismissing the warning leaves that cell quietly at zero and drags every calculation that uses it off with it — a closed warning is not a fixed problem. The status bar at the bottom keeps showing the offending cell address, so check there.

See also:

Working with text

텍스트 나누기 (Text to Columns)split one cell into columns

Splits values crammed into one cell across several columns, by a delimiter or a fixed width.

Syntax
데이터 탭 → 텍스트 나누기

What usually goes wrong: The result overwrites the columns to the right, so insert blank columns first. Values like "001" become numbers and lose the leading zero unless you set that column to Text.

See also:

TEXTJOIN / CONCATjoin cells togetherExcel 2019

Joins text from several cells with a separator. Pass TRUE as the second argument to skip blanks.

Syntax
TEXTJOIN(구분자, 빈칸무시, 범위)

Example (click to copy)
→ 빈 칸을 빼고 쉼표로 연결

See also:

TRIM / CLEAN / SUBSTITUTEremove extra spaces

TRIM removes leading, trailing and repeated spaces, CLEAN strips non-printing characters, SUBSTITUTE swaps specific text.

Syntax
TRIM(글자) · SUBSTITUTE(글자, 찾을것, 바꿀것)

Example (click to copy)
→ 눈에 안 보이는 찌꺼기까지 정리

What usually goes wrong: This is the usual cause of a stubborn #N/A in VLOOKUP. Text pasted from the web often contains a non-breaking space (U+00A0) that TRIM does not remove. Add =SUBSTITUTE(A2, CHAR(160), "").

See also:

LEFT / RIGHT / MIDtake the first characters

Takes a set number of characters from the start, end, or middle of the text.

Syntax
LEFT(글자, 개수) · RIGHT(글자, 개수) · MID(글자, 시작, 개수)

Example (click to copy)
→ 사업자번호 앞 6자리

See also:

TEXTshow the day of week

Converts a value into text in the format you specify. Unlike cell formatting the result really is text, so you can join it into a sentence. Use "yyyy-mm-dd" for dates, "ddd" for the day of week, "#,##0" for thousands separators.

Syntax
TEXT(값, "서식")

Example (click to copy)
→ A2 날짜의 요일 (예: 화요일)

In Google Sheets: The day-of-week code differs: Google Sheets uses "dddd", and so does Excel outside the Korean locale.

What usually goes wrong: The result is text, so it can no longer be calculated with and it sorts alphabetically. If you only want to change how it looks, change the cell format instead. Format codes also differ between language versions of Excel.

See also:

Dates

DATEDIFyears of service

Returns the whole years, months or days between two dates. Used for length of service and age.

Syntax
DATEDIF(시작일, 종료일, "Y"|"M"|"D")

Example (click to copy)
→ 입사일부터 오늘까지 만 몇 년

What usually goes wrong: It does not appear in the function list and has no help entry. It survives for compatibility, so type it by hand, and avoid the "MD" option, which is known to give wrong results.

See also:

NETWORKDAYSworking days between dates

Counts working days between two dates, skipping Saturdays and Sundays plus any holiday list you supply.

Syntax
NETWORKDAYS(시작일, 종료일, [공휴일범위])

Example (click to copy)
→ 공휴일까지 뺀 영업일 수

What usually goes wrong: Without a holiday list it only skips weekends. If Saturday is a working day where you are, use NETWORKDAYS.INTL to set which days are off.

See also:

EDATE / EOMONTHdate n months later

EDATE returns the same day a number of months later; EOMONTH returns the last day of that month.

Syntax
EDATE(날짜, 개월) · EOMONTH(날짜, 개월)

Example (click to copy)
→ 1년 뒤 같은 날

What usually goes wrong: If the result shows as a number like 45000, the cell format is General. Change it to Date.

See also:

TODAY / NOWinsert today

Inserts today’s date or the current time. Subtracting dates gives days, so =A2-TODAY() is how many days remain until a deadline.

Syntax
TODAY()

Example (click to copy)
→ 마감까지 남은 일수

What usually goes wrong: They recalculate every time the file opens, so never use them to record when something happened — yesterday’s stamp becomes today. Press Ctrl + ; to type a fixed date instead. If the subtraction shows as a date, set the cell format back to General.

See also:

WEEKDAY / YEAR / MONTH / DAY / DATEget the day of week

WEEKDAY returns the day as a number; with 2 as the second argument Monday is 1 and Sunday is 7, which makes weekend checks easy. YEAR, MONTH and DAY pull the parts out of a date, and DATE builds one from three separate numbers.

Syntax
WEEKDAY(날짜, 2)

Example (click to copy)
→ 주말이면 TRUE

What usually goes wrong: Without the second argument Sunday is 1, and not knowing that shifts every weekend test by a day. To display the day as a word use TEXT, not WEEKDAY. A value typed as 20260315 is a number, not a date, so rebuild it with DATE.

See also:

Dynamic arrays

UNIQUElist without duplicatesExcel 365 / 2021

Produces the list with duplicates removed, and updates when the source changes.

Syntax
UNIQUE(범위)

Example (click to copy)
→ 중복을 뺀 부서 목록

What usually goes wrong: The result spills downward, so the cells below must be empty or you get a #SPILL! error. The Remove Duplicates button on the Data tab is different: it deletes from the source.

See also:

FILTERpull rows matching a conditionExcel 365 / 2021

Extracts the rows that match a condition into another place. Unlike the filter button it leaves the source alone and refreshes automatically.

Syntax
FILTER(범위, 조건, [없을때])

Example (click to copy)
→ 금액이 100만 넘는 행만

See also:

SORT / SORTBYsort with a formulaExcel 365 / 2021

Outputs a sorted copy elsewhere, re-sorting automatically when the source changes.

Syntax
SORT(범위, [기준열], [1|-1])

Example (click to copy)
→ 중복 뺀 목록을 가나다순으로

See also:

Features and settings

피벗 테이블 (PivotTable)summarise by category

Groups many rows by category into one table of totals, counts and averages, by dragging rather than writing formulas.

Syntax
삽입 탭 → 피벗 테이블

What usually goes wrong: It will not build if the source has merged cells or a blank header. It also does not refresh on its own after you edit the source: press Refresh.

See also:

조건부 서식 (Conditional Formatting)highlight cells by rule

Automatically changes the colour or font of cells that meet a rule. Common for spotting duplicates and tracking deadlines.

Syntax
홈 탭 → 조건부 서식

What usually goes wrong: Watch the reference style in formula rules. To colour a whole row, lock only the column as $A2. Using $A$2 tests the first row for every cell.

See also:

절대 참조 ($)formula breaks when copied

A $ freezes that part when you copy the formula. $A1 locks the column, A$1 the row, $A$1 both.

Syntax
A1 · $A1 · A$1 · $A$1 (F4로 순환)

Example (click to copy)
→ 아래로 복사해도 F1은 그대로

What usually goes wrong: If the table range in VLOOKUP is not locked, copying down shifts the range and the lower rows start returning #N/A.

See also:

틀 고정 (Freeze Panes)keep the header row visible

Keeps the header row or left columns on screen as you scroll.

Syntax
보기 탭 → 틀 고정

What usually goes wrong: Select the cell just below and right of what you want frozen first. To freeze row 1, put the cursor on A2.

앞자리 0 유지leading zero disappears

Excel strips leading zeros from anything it reads as a number. Values you never calculate with, like phone or postal codes, should be entered as text.

Syntax
셀 서식 → 텍스트, 또는 ' 를 앞에 입력

What usually goes wrong: Opening a CSV directly loses the zeros before you can intervene. Use Data → From Text/CSV and set that column to Text during import.

See also:

중복 제거 (Remove Duplicates)remove duplicate rows

Deletes rows that repeat on the columns you tick, keeping the first of each. Because you choose the columns, you can keep two people with the same name but different phone numbers.

Syntax
데이터 탭 → 중복된 항목 제거

What usually goes wrong: It is a deletion you cannot undo later, so copy the sheet first; counting the duplicates with COUNTIF beforehand is safer. Values differing only by stray spaces count as different, so run TRIM first. If you want the distinct list without destroying the table, use UNIQUE instead.

See also:

드롭다운 목록 (데이터 유효성 검사)make a dropdown

Attaches a list to a cell so only those values can be entered. On a sheet many people fill in, it stops "Seoul" and "Seoul City" from coexisting. The list can be typed with commas or point at a range on another sheet.

Syntax
데이터 탭 → 데이터 유효성 검사 → 목록

What usually goes wrong: Pasting bypasses validation entirely, so a value not on the list still lands if someone pastes it — this is a guard, not a lock. Make the source range a Table (Ctrl+T) and the dropdown grows as you add items; otherwise you have to edit the range every time.

See also:

빠른 채우기 (Flash Fill)fill by example

Type the result you want for the first row or two and it fills the rest by the same pattern. Without knowing any function you can split a full name or add dashes to phone numbers in one step. The shortcut is Ctrl + E.

Syntax
Ctrl + E

In Google Sheets: Google Sheets has no direct equivalent; use the SPLIT function or its Smart Fill suggestion instead.

What usually goes wrong: The output is static values, so it does not follow changes in the source. Fine for a one-off cleanup, but use formulas on a sheet that keeps updating. On irregular data it will guess wrong without warning, so check the result.

See also:

표로 만들기 (Ctrl + T)convert range to table

Turning a range into a Table makes formatting, formulas, conditional formatting and PivotTable source ranges all grow when you add a row. Formulas also read as =SUM(Sales[Amount]) instead of =SUM(B2:B100). The real gain is never editing a range again.

Syntax
Ctrl + T

Example (click to copy)
→ 표가 늘어나도 범위를 고칠 필요가 없다

What usually goes wrong: Merged cells are not allowed inside a Table and some cross-sheet array formulas are blocked; existing merges get undone on conversion. Also, leaving the default name gives you Table1, Table2 and formulas end up less readable, not more — rename it right after you create it.

See also:

이름 정의 (Named Range)name a range

Gives a range or a value a name you then use in formulas, so =B2*Sheet2!$H$1 becomes =B2*VatRate and still makes sense months later. For a value many formulas share, such as a tax rate, one name means one place to change when it moves.

Syntax
수식 탭 → 이름 정의, 또는 이름 상자에 직접 입력

Example (click to copy)
→ =B2*Sheet2!$H$1 과 같지만 읽힌다

What usually goes wrong: Names cannot contain spaces, and anything that reads as a cell address such as A1 or R1C1 is rejected. Copying a sheet creates a second, sheet-scoped copy of the same name, and it stops being obvious which one a formula uses — tidy them up in the Name Manager (Ctrl + F3).

See also:

선택하여 붙여넣기 (Paste Special)paste values only

Chooses what part of the copy to paste: values only, formatting only, formulas, column widths, or transposed. The under-used option is Operation — copy a cell holding 1000, then Paste Special → Multiply over a column and the whole column is multiplied, no helper column needed for a unit change.

Syntax
Ctrl + Alt + V

What usually goes wrong: Pasting values is a one-way door: once the formulas are gone there is no way back to what the calculation was, so duplicate the sheet first. Paste Special is also unavailable after Cut — you have to Copy.

See also:

셀 병합의 문제merge cells

Merging looks tidy but breaks sorting, filtering, PivotTables and copying — "this operation requires the merged cells to be identically sized" is the classic symptom. If you only want a title centred across columns, use Home → Alignment → Center Across Selection instead: it looks the same and the cells stay separate.

Syntax
홈 탭 → 맞춤 → 선택 영역의 가운데로

What usually goes wrong: A merged block holds its value only in the top-left cell; the rest are genuinely empty, which is why VLOOKUP and filters find nothing on the rows below. On a sheet you inherited, unmerge first, select the blanks with Ctrl + G → Special → Blanks, fill them from the cell above, and then start working.

See also:

#### 표시와 열 너비hashes instead of a number

A number or date longer than the column shows as #### instead. It is not an error, just no room: double-click the column border to fit it to the content, or select several columns first and double-click any border to fit them all at once.

Syntax
열 경계선 더블클릭

What usually goes wrong: Text overflows into the neighbouring cell but numbers and dates do not, which is why #### only ever appears on number or date cells. Shrinking the font or turning on Wrap Text also works, but for a sheet you will print, fixing the width is the safer choice.

See also:

인쇄 설정 (한 장에 맞추기·제목 행 반복)fit to one page

Three settings cover most printing problems: fit the width to one page (set width to 1 page and leave height automatic — that is the trick), repeat the header row on every page (Page Layout → Print Titles → Rows to repeat at top), and print only part of the sheet (Set Print Area).

Syntax
페이지 레이아웃 탭 → 인쇄 제목 / 배율

What usually goes wrong: Fitting both width and height to one page shrinks the text past legibility — set only the width. Gridlines show on screen but do not print unless you tick Print under Gridlines. The print area is saved in the file, so if someone else’s workbook prints only part of a sheet, that setting is still in there.

See also:

정렬과 필터sort a table

Ctrl + Shift + L toggles the filter row. For sorting, Home → Sort & Filter → Custom Sort lets you stack levels — group by department first, then order by amount inside each.

Syntax
Ctrl + Shift + L

What usually goes wrong: Sorting with only one column selected moves that column alone and tears every row apart — the most common way a sheet is damaged beyond repair. Click any single cell inside the data and sort from there, or make it a Table with Ctrl + T first. Also, pasting while a filter is on writes into the hidden rows too; press Alt + ; to select visible cells only.

See also:

시트 보호와 일부 칸만 수정 허용stop people editing my formulas

The order is back to front, which is why it confuses people: every cell is already marked Locked, and protecting the sheet is what activates that. So to leave the input cells editable, first select them and untick Locked in Format Cells, and only then protect the sheet.

Syntax
검토 탭 → 시트 보호

What usually goes wrong: Sheet protection guards against mistakes, not against people: the password is trivially removable, so anything that must stay unseen should not be in the file at all. Protection also blocks column resizing and sorting, so tick the actions you want to allow in the protection dialog or the recipient will be stuck.

See also:

CSV 한글 깨짐 (인코딩)CSV shows garbled characters

A CSV does not record which character encoding it was saved in, so Excel guesses using the system default and the text breaks when the guess is wrong. Open it through Data → From Text/CSV and set File Origin to UTF-8 instead of double-clicking. When exporting, choose the "CSV UTF-8" format.

Syntax
데이터 탭 → 텍스트/CSV 가져오기 → 원본 파일 UTF-8

What usually goes wrong: Once you open it garbled and save, the original characters are gone for good — close without saving and re-import instead. A separate problem is everything landing in one column even with the right encoding; that is the delimiter, which you set in the same import dialog.

See also:

Keyboard shortcuts

Entering and editing

Windows	Mac
F4	Cmd + T	Toggle absolute reference ($) inside a formulaPut the cursor on the reference while editing the formula. Each press cycles $A$1 → A$1 → $A1 → A1. On laptops where F4 is a brightness key, hold Fn as well.
F4	Cmd + Y	Repeat the last action, when not editing a formula
Ctrl + ;	Cmd + ;	Enter today's date as a fixed valueUnlike TODAY(), it does not change when the file is reopened. Ctrl + Shift + ; enters the current time.
Alt + Enter	Ctrl + Option + Enter	Line break inside a cellSheets: Ctrl + Enter (Mac: Cmd + Enter)One of the clearest differences between Excel and Google Sheets.
Ctrl + Enter	Cmd + Enter	Fill every selected cell with the same entrySelect the range, type the entry, then finish with Ctrl + Enter. Handy after selecting only the blank cells.
Ctrl + D · Ctrl + R	Cmd + D · Cmd + R	Fill down from above, fill right from the left
Ctrl + Alt + V → V	Cmd + Ctrl + V	Paste values only, without formulas or formattingSheets: Ctrl + Shift + V
Ctrl + Shift + + · Ctrl + -	Cmd + Shift + + · Cmd + -	Insert or delete rows and columns
Ctrl + Alt + V	Cmd + Ctrl + V	Open the Paste Special dialogOnce the dialog opens, press V for values only. It is unavailable after Cut — you have to Copy.

Formulas

Windows	Mac
Alt + =	Cmd + Shift + T	Insert AutoSum
Ctrl + `	Cmd + `	Show or hide formulasThe ` key sits left of the 1 key, sharing with the tilde (~).
F9	F9	Evaluate the selected part of a formulaSelect part of the formula while editing and press it to see that part evaluated. Always leave with Esc: pressing Enter bakes the value into the formula.
Ctrl + F3	Cmd + F3	Open the Name ManagerCopying a sheet creates a second, sheet-scoped copy of the same name; this is where you sort out which one a formula uses.

Moving and selecting

Windows	Mac
Ctrl + 방향키	Cmd + 방향키	Jump to the edge of the data
Ctrl + Shift + 방향키	Cmd + Shift + 방향키	Select to the edge of the dataIt stops at the first blank cell, which also makes it a quick way to spot gaps in the data.
Shift + Space · Ctrl + Space	Shift + Space · Cmd + Space	Select the whole row or the whole columnWith a Korean IME active, Shift + Space may switch input language instead; switch to Latin input first.
Ctrl + PageUp · Ctrl + PageDown	Cmd + PageUp · Cmd + PageDown	Move to the previous or next sheet
Alt + ;	Cmd + Shift + Z	Select visible cells only, skipping hidden rowsWithout it, pasting while a filter is on writes into the hidden rows as well — damage you only discover after clearing the filter.

Data and tables

Windows	Mac
Ctrl + Shift + L	Cmd + Shift + L	Turn filters on and off
Ctrl + T	Cmd + T	Convert the range to a TableOnce it is a Table, added rows extend formulas and pivot ranges automatically, replacing the habit of referencing A:A.
F5 → Alt + S	F5 → Alt + S	Select only blanks, formulas or errorsPair it with Ctrl + Enter to fill every blank at once, or Ctrl + - to delete all empty rows in one go.

Formatting

Windows	Mac
Ctrl + 1	Cmd + 1	Open the Format Cells dialog

Assumptions

A curated set of what people actually get stuck on, not the full function reference.
Google Sheets differences are noted only where they were checked. No note does not mean the behaviour is identical.
Entries that require a particular version say so. Those without a note generally work on older builds as well.
Shortcuts assume Excel on Windows and macOS. Laptop function-key settings and input methods can change what a key does.
Searching happens entirely in your browser and nothing you type is transmitted.

How it is calculated

Search by task. Each entry carries several phrasings people actually use, so "duplicate", "how many times it appears" and "distinct values" all land in the same place.
Function names work too, in any language version of this page.
Two words narrow it. "conditional sum" keeps only entries containing both.
Functions and shortcuts are searched together. "absolute reference" returns both the $ explanation and the F4 key.

Common mistakes

Check the version first. XLOOKUP, FILTER and UNIQUE do not exist in older Excel, and the version that matters is the one used by whoever you send the file to.
Do not paper over errors with IFERROR. It hides genuine mistakes such as #REF! along with the failed lookup you meant to catch. Use IFNA for that.
Dynamic arrays need empty space below. Anything sitting where the result would spill produces a #SPILL! error.
This is a starting point, not the last word. Confirm against your own file.

FAQ

Can I find a function without knowing its name?

That is why this exists. If you know the name you can search for it anywhere. The hard case is knowing what you want but not what it is called, and that is the common one. So the key here is the task rather than the name: type "sum only the matching rows" and you get SUMIF, type "the leading zero disappears" and you get the answer about storing values as text.

Are the function names the same in every language version of Excel?

The menus and help are translated, but function names stay in English in most locales, and the same is true in Google Sheets. That is why this dictionary keeps names in English and translates only the descriptions and the task phrases. One thing that does change by locale is the argument separator: a comma in most, a semicolon in some European settings.

Does all of this work in Google Sheets too?

Most of it, but not all. Only differences that were checked are noted under "In Google Sheets"; where nothing is noted, it was not verified rather than confirmed identical. The clearest difference is the line break inside a cell: Alt + Enter in Excel, Ctrl + Enter in Sheets.

Why does XLOOKUP give a #NAME? error?

It does not exist before Excel 365 and 2021, and Google Sheets only added it in 2022. Entries that need a particular version say so. If you exchange files with someone on an older build, use INDEX + MATCH instead; it does the same job everywhere.

My VLOOKUP keeps returning #N/A

The usual causes are a short list: the last argument is not FALSE, the lookup value is not in the first column of the range, the table range is not locked with $ so it drifts when copied down, or, most often, the values contain invisible whitespace. Text pasted from a web page frequently carries a non-breaking space that TRIM will not remove. Search this dictionary for "values look equal but do not match".

A shortcut does nothing when I press it

On laptops the function keys often default to brightness or volume, so you need to hold Fn as well. Some shortcuts are also taken by the operating system or by an input method. On a Mac the usual rule is Cmd in place of Ctrl, but not always, so the Mac column lists the real key where it differs.

Related tools

Documents & forms

Resume builder

Build a resume with editable fields, optional photo and draggable sections. Save your project as JSON or use browser printing to save a PDF.

Excel & CSV tools

Excel table to PDF converter

Convert an Excel XLSX sheet to a table PDF. Choose portrait or landscape and repeating headers. The output does not reproduce the original Excel print layout.

Audio & video

Video to GIF converter

Choose a section of your video and save an animated GIF.

Audio & video

Trim video (save as WebM)

Choose a section of a video and save a short WebM file.