Skip to content
Search

Microsoft Excel

lastmod 2026-05-24created 2020-10-2230 edits

Microsoft Excel is a spreadsheet program developed and sold by Microsoft that runs on Microsoft Windows and Mac OS. Excel is part of Microsoft Office.

Categories

Fill Handle

That "+" shaped thing!

Excel_-_Fill_Handle.png

How to use:

  • Drag it, or
  • Double-click the "fill handle".
  • Select the range to fill, then press the Ctrl + D shortcut.

Notes:

  • Absolute reference ($): to prevent automatic incrementing, put $ in front of the cell reference.
    • Example: $A$1 → refers to a fixed cell.
  • When there is no contiguous data: specify the range manually or select it with Shift + arrow keys, then enter the value with Ctrl + Enter.

Formulas

Referencing cell information from another sheet

Enter the following in the cell. 

=참조할시트명!참조할셀위치

And if you want to reference cell information from another Excel file in the cell: 

=[(엑셀파일경로\)엑셀파일명]참조할시트명!참조할셀위치

If you need to reference a file name or similar, use INDIRECT. 

=INDIRECT(INFO!$B$15&ADDRESS(ROW(),COLUMN(),4))

Fixing a value without automatic incrementing

For some formulas, the column value must stay fixed instead of incrementing automatically. In that case, put $ before the row and column as below to fix them. 

=$F$3

Extracting the file name 

=CELL("FILENAME")
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

Calculator for SME Employee Income Tax Reduction

Calculator_for_SME_Employee_Income_Tax_Reduction_-_example.png

Date of birth, hire date, military enlistment date, military discharge date, age at employment, military service period, applicable age: in Excel, enter the date of birth, hire date, enlistment date, and discharge date in that order, then enter the formulas below. 

취업시연령
=DATEDIF(A2,B2,"Y")&"년 "&DATEDIF(A2,B2,"YM")&"개월 "&DATEDIF(A2,B2,"MD")+1&"일"

군복무기간
=DATEDIF(C2,D2,"Y")&"년 "&DATEDIF(C2,D2,"YM")&"개월 "&DATEDIF(C2,D2,"MD")+1&"일"

적용연령
=DATEDIF(A2,B2-(D2-C2+1),"Y")&"년 "&DATEDIF(A2,B2-(D2-C2+1),"YM")&"개월 "&DATEDIF(A2,B2-(D2-C2+1),"MD")+1&"일"

Number formats

Hiding zero values in selected cells

Enter 0;;;@ in the Type box.

Excel functions (by category)

The results of formulas and some Excel worksheet functions may differ slightly between Windows PCs using the x86 or x86-64 architecture and Windows RT PCs using the ARM architecture.

List of Excel functions (by category)

VLOOKUP

Vertical lookup. Searches for a key in the first column of a range and returns the value of the specified cell in the row where the key is found. 

VLOOKUP(검색_키, 범위, 색인, [정렬됨])
  • search_key - The value to search for. Examples: 42, "cat", I24
  • range - The range to search. Looks for the key given in 'search_key' in the first column of the range. <- Do not include the header! Also, the table is laid out vertically; include both the Key (first column) and the Values (second column onward) in the range.
  • index - The column index of the value to return, where the first column of 'range' is 1.
  • is_sorted - [optional] - Indicates whether the column being searched (the first column of the specified range) is sorted. In that case, the value closest to search_key is returned. <- To check for an exact match without sorting, use 0.

In short, it finds the row (Row) where the first column (Col) of "range" matches "search_key", and returns the column (Col) given by "index".

XLOOKUP

Cross lookup. Returns the value in the result range at the position where a match was found in the lookup range. 

XLOOKUP(검색_키, 조회_범위, 결과_범위, [누락_값], [일치_모드], [검색_모드])

Use it to get the Val matching a Key from an unsorted table (Key, Val).

  • search_key - The value to search for (e.g. 42, "cat", I24).
  • lookup_range - The range to search. The range must be a single row or column.
  • result_range - The range to consider for the search result. The row or column size of this range must match the row or column size of lookup_range, depending on the lookup direction.
  • missing_value - [optional] - The value returned when there is no match. If not specified, an error is returned.
  • match_mode - [optional] - How to find a match for search_key. 0 is an exact match, 1 is an exact match or the next value greater than search_key, -1 is an exact match or the next value less than search_key, and 2 is a wildcard match. If not specified, an exact match is used by default.
  • search_mode - [optional] - How to search the lookup range. 1 searches from the first item to the last, -1 searches from the last item to the first, 2 searches the range using binary search assuming it is sorted in ascending order, and -2 searches the range using binary search assuming it is sorted in descending order. If not specified, the range is searched from the first item to the last by default.

INDEX

Returns the contents of the cell specified by row and column offsets. 

INDEX(참조, [행], [열])
  • reference - The array of cells to use as the base for the offset.
  • row - [optional] - The number of the offset row.
  • column - [optional] - The number of the offset column.

Example of use with MATCH 

// 범위 : {사과,귤,포도,배,복숭아}
=MATCH("배",범위,0) // 배의 순번인 '4'를 반환합니다.

Then, with the INDEX function, you can output the value at the nth position of the output range. 

// 출력범위 : {100,200,300,400,500}
=INDEX(출력범위,MATCH("배",범위,0)) // MATCH 함수로 배의 순번이 반환됩니다.
=INDEX(출력범위,4) // 출력범위에서 4번째 값을 출력합니다.
=400

MATCH

Returns the relative position of an item in a range that matches a specified value.

COUNTA

Returns the number of values in a dataset.

SUMIFS

Returns the sum of a range based on multiple criteria. 

SUMIFS(범위_합계, 기준_범위1, 기준1, [기준_범위2, …], [기준2, …])
  • sum_range - The range to sum.
  • criteria_range1 - The range to check against criterion1.
  • criterion1 - The pattern or test to apply to criteria_range1.
  • criteria_range2… - [optional] repeatable - Additional ranges to check.
  • criterion2… - [optional] repeatable - Additional criteria to apply.

Lets you summarize sums from another Source Table, based on multiple conditions, into a Destination Table.

Saving a CSV file as UTF-8

Go to "Save As > Tools > Web Options"; the Encoding tab has "Unicode (UTF-8)".

See also

Favorite site

author Zer0 sitemap