Excel Common Function Formulas

1. Basic Excel operations

1 Basic Concepts: Workbook, worksheet, row, column, cell

2. Data Entry

Batch input of the same data (CTRL + enter)

* ** Enter the number (set the cell attribute to "text" or "Add before ')

Score Input (1/3 Excel is set to January 3 by default, and should be written as 0 space 1/3)

Line feed in cells (Alt + enter)

3 search: Fuzzy search and value search

4. Freeze the window

5. Filtering: general filtering and empty row Filtering

6 transpose rows and columns

7. Insert object: Object format settings

8. Data deduplication and Data breakdown

9. Create a drop-down menu

10-level brush

 

2 General Excel Operators

Category	Operator number	Operator meaning	Example
Arithmetic Operations	+ (Plus sign)	Add	2 + 3 = 5
	-(Minus sign)	Subtraction	3-1 = 2
	* (Asterisk	Multiplication	3*2 = 6
	/(Slash)	Division	6/2 = 3
	% (Percent)	Percent sign	50%
	^ (Remove font size)	Multiplication party	4 ^ 3 = 43 = 64
Text	&	Connect text	"Zhang" & "yl"
Comparison	= (Equal sign)	Equal	B1 = C1. If the value in the cell B1 is indeed equal to the value in C1, the logical true value is true. If the value is not equal, the logical false value is generated.
	<(Yu no)	Less	B1 <C1
	> (Greater than the number)	Greater	B1> C1. If the value in B1 is 6 and the value in C1 is 4, the true logical value is true if the condition is true. Otherwise, the false logical false value is generated.
	> = (Greater than or equal to no)	Greater than or equal	B1> = C1
	<> (Not equal)	Not equal	B1 <> C1
	<= (Less than or equal to no)	Less than or equal	B1 <= C1
Reference Operator	:	Regional operators that generate references to all cells included in phase C references	(A5: A15)
	,	Union operator, which combines multiple references into one reference	Sum (A5: A15, C5: C15)
	(Space)	A crossover operator that generates a reference to two referenced cells.	(B7: D7 C6: C8)

 

 

3. Excel Functions

Cell Reference Mode

Relative applications A1 and B1

Absolutely reference $ a $5, $ C $3

Hybrid reference $ A5, C $3

4. Common Function errors

Error value type	Description
#####	An error occurs when a negative date or time is used.
# Value!	An error occurs when an incorrect parameter or calculation object type is used.
# DIV/o!	An error occurs when the formula is divided by zero (0 ).
# NAME?	An error occurs when an Excel unrecognized text is used in the formula.
# N/	An error occurs when no value is available in a function or formula.
# Ref!	An error occurs when the cell reference is invalid.
# Num!	An error occurs when a number in a formula or function is incorrect.
# Null!	An error occurs when an intersection is specified for two non-Intersecting regions.

 

 

 

5 text functions and formulas

&, Concatenate: connect the text

 

Find (find_text, within_text, start_num) Search ()

Search for text strings (find_text) in other text strings (within_text), and return the start position number of find_text starting from the first character of within_text.

 

Mid (text, start_num, num_chars)

Returns a specific number of characters starting from a specified position in a text string. The number is specified by the user.

Trim (text)

In addition to a single space between words, clear all spaces in the text

 

Len (text)

Returns the number of characters in a text string.

 

Text (value, format_text)

Converts a value to a text in the specified numeric format.

 

Left (text, num_chars)

Returns the first or first few characters in a text string based on the specified number of characters.

 

Right (text, num_chars)

Returns the last character of the text string based on the specified number of characters.

 

Upper, lower, proper

 

 

6. Statistical functions and formulas-count

Count (value1, value2 ,...)

Calculates the number of cells that contain numbers and the number of numbers in the parameter list. You can use count to obtain the number of input fields in the area or number array.

 

Counta (value1, value2 ,...)

Calculates the number of cells with non-null values in the parameter list. The counta function can be used to calculate the number of cells in the cell area or array containing data.

 

Countblank (range)

Calculates the number of blank cells in the specified cell area.

 

Countif (range, criteria)

Calculates the number of cells in the region that meet the given conditions.

Countifs (range1, criteria1, range2, criteria2 ,...)

-Calculates the number of cells that meet multiple conditions in the region.

 

7 statistical functions and formulas-AVG

Average (number1, number2 ,...)

Returns the average (arithmetic mean) of a parameter ).

 

Averagea (value1, value2,...) -- be careful not to use

Calculate the average value (arithmetic average value) of the values in the parameter list ). In addition to numbers, the calculation also includes text and logical values, such as true and false.

 

Averageif (range, criteria, average_range)

Returns the average value (arithmetic mean) of all cells that meet the given conditions in a region ).

 

Averageifs (average_range, criteria_range1, criteria1, criteria_range2, criteria2 ...)

Returns the average (arithmetic mean) of all cells that meet multiple conditions ).

 

8. Statistical functions and formulas-sum

Sum (number1, number2 ,...)

Calculates the sum of all values in the cell area.

 

Sumif (range, criteria, sum_range)

Calculates the number of cells according to the specified conditions.

 

Sumifs (sum_range, criteria_range1, criteria1, criteria_range2, criteria2 ...)

Sums cells that meet multiple conditions in the region.

 

Sumproduct (array1, array2, array3,...)-Note: Do not use

Multiply the corresponding elements in the given array and return the sum of these products.

Example: = sumproduct (A1: B3, C1: D3) multiply all elements in the two arrays, then add the product-3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. (156)

 

 

9 search functions and formulas

Vlookup (lookup_value, table_array, col_index_num, range_lookup)

Search for the values in the first column of the table, and then return the values in the same row in the specified column of the table. When the comparison value is in the left column of the data to be searched

 

Hlookup (lookup_value, table_array, row_index_num, range_lookup)

Returns the relative position of an item in the array that matches the specific sequence of values. If you want to obtain the position of an item in the region rather than the item itself, use the match function instead of a lookup function.

 

Match (lookup_value, lookup_array, match_type)

Returns the relative position of an item in the array that matches the specific sequence of values. If you want to obtain the position of an item in the region rather than the item itself, use the match function instead of a lookup function.

 

Index (reference, row_num, column_num, area_num)

Returns the cell reference at the intersection of the specified row and column. If the reference consists of discontinuous selected regions, you can select a selected region.

 

 

 

10 judgment functions and formulas

If (logical_test, value_if_true, value_if_false)

If the specified condition is calculated as true, a value is returned, and if it is calculated as false, another value is returned.

 

Iferror ()

Iserror (value)

Reference any error values (# N/A, # value! , # Ref! , # DIV/0! , # Num! , # NAME? Or # null !), True is returned. Otherwise, false is returned.

 

Iserr (value)

Reference error value (# value! , # Ref! , # DIV/0! , # Num! , # NAME? Or # null !)

 

ISNA (value)

If the error value (# N/A) is referenced, true is returned; otherwise, false is returned.

 

Isblank (value). If it is an empty cell, the logical value true is returned; otherwise, false is returned.

 

Istext (value). If it is a character, true is returned; otherwise, false is returned.

 

Isnumber (value). If it is a numerical value, true is returned; otherwise, false is returned.

 

 

 

11 wildcard

The following wildcard can be used as a comparison condition for filtering and searching and replacing content

Please use	To find
? (Question mark)	Any single character
	For example, SM? TH: Find "Smith" and "Smyth"
* (Asterisk)	Any Character Count
	For example, * East searches for "Northeast" and "southeast"

 

 

12 sorting functions and formulas

Rank (number, ref, order)

Returns the ranking of a number in the number list. Its size is related to its value in the number list.

 

Rank. eq (number, ref, order)

Returns the ranking of a number in the number list. Its size is related to other values in the list. If multiple values have the same qualifying value, the highest ranking of the group value is returned. (If you want to sort the list, the number ranking can be used as its position .)

 

Rank. AVG (number, ref, order)

Returns the ranking of a number in the number list. Its size is relative to other values in the list. If more than one value is the same, the average value is returned.

 

Small (array, K)

Returns the minimum K value in the dataset. This function can be used to return values at specific locations in a dataset.

 

Large (array, K)

Returns the maximum K value in the dataset. Use this function to select a value based on the relative standard. For example, you can use large to return the first, second, or third score.

 

13 numeric processing functions and formulas

Max (number1, number2 ,...)

Returns the maximum value of a group of values.

Min (number1, number2 ,...)

Returns the minimum value of a group of values.

Maxa (value1, value2 ,...)

Returns the maximum value in the parameter list. In addition to numbers, the calculation also includes text and logical values, such as true and false.

Mina (value1, value2 ,...)

Returns the minimum value in the parameter list. In addition to numbers, the calculation also includes text and logical values, such as true and false.

Round (number, num_digits)

A number can be rounded to a specified number of digits.

Rounddown (number, num_digits)

Rounded down to zero.

Roundup (number, num_digits)

Move away from the 0 (zero) value and round up the number.

INT (number)

Returns the number to the nearest integer.

Trunc (number, num_digits)

Truncates the fractional part of a number and returns an integer.

 

Excel Common Function Formulas