Comprehensive List of Commonly Used Excel Functions
1. ABS Function
Function Name: ABS
Main Function: Finds the absolute value of the corresponding number.
Usage Format: ABS(number)
Parameter Explanation: Number represents the value or referenced cell for which you need to find the absolute value.
Application Example: If you input the formula =ABS(A2) in cell B2, regardless of whether you enter a positive number (like 100) or a negative number (like -100) in A2, B2 will display a positive number (like 100).
Special Reminder: If the number parameter is not a numerical value but some characters (such as "A"), then B2 will return an error value "#VALUE!".
2. AND Function
Function Name: AND
Main Function: Returns a logical value; if all parameter values are logically "True (TRUE)", it returns "True (TRUE)", otherwise it returns "False (FALSE)".
Usage Format: AND(logical1, logical2, ...)
Parameter Explanation: Logical1, Logical2, Logical3... represent the condition values or expressions to be tested, with a maximum of 30.
Application Example: Input the formula =AND(A5>=60, B5>=60) in cell C5 and confirm. If C5 returns TRUE, it means that both the values in A5 and B5 are greater than or equal to 60. If it returns FALSE, it means at least one of the values in A5 and B5 is less than 60.
Special Reminder: If the specified logical premise parameters include non-logical values, the function will return an error value "#VALUE!" or "#NAME".
3. AVERAGE Function
Function Name: AVERAGE
Main Function: Finds the arithmetic average of all parameters.
Usage Format: AVERAGE(number1, number2, ...)
Parameter Explanation: Number1, number2,... are the numbers or referenced cells (regions) whose average needs to be calculated, with no more than 30 parameters.
Application Example: Input the formula =AVERAGE(B7:D7, F7:H7, 7, 8) in cell B8, confirm, and it will calculate the average of the numbers in regions B7 to D7, F7 to H7, and the numbers 7 and 8.
Special Reminder: If the referenced region contains "0" value cells, they will be included in the calculation; if the referenced region contains blank or character cells, they will not be included in the calculation.
4. COLUMN Function
Function Name: COLUMN
Main Function: Displays the column label value of the referenced cell.
Usage Format: COLUMN(reference)
Parameter Explanation: Reference is the referenced cell.
Application Example: Input the formula =COLUMN(B11) in cell C11, confirm, and it will show 2 (which refers to column B).
Special Reminder: If you input the formula =COLUMN() in cell B11, it will also show 2; there is also a function ROW(reference) that returns the row label value.
5. CONCATENATE Function
Function Name: CONCATENATE
Main Function: Concatenates multiple text strings or data from cells into one string displayed in a single cell.
Usage Format: CONCATENATE(Text1, Text...)
Parameter Explanation: Text1, Text2... are the text strings or referenced cells to be concatenated.
Application Example: Input the formula =CONCATENATE(A14,"@",B14,".com") in cell C14, confirm, and it will concatenate the characters in cell A14, "@", the characters in cell B14, and ".com" into one string displayed in cell C14.
Special Reminder: If the parameter is not a referenced cell and is in text format, enclose the parameter in double quotes under English mode. If you change the above formula to =A14&"@"&B14&".com", it will achieve the same result.
6. COUNTIF Function
Function Name: COUNTIF
Main Function: Counts the number of cells within a specified range that meet a given condition.
Usage Format: COUNTIF(Range, Criteria)
Parameter Explanation: Range represents the cell range to be counted; Criteria indicates the specified condition expression.
Application Example: Input the formula =COUNTIF(B1:B13, ">=80") in cell C17, confirm, and it will count the number of cells in the range B1 to B13 that have values greater than or equal to 80.
Special Reminder: Blank cells are allowed in the referenced cell range.
7. DATE Function
Function Name: DATE
Main Function: Provides the date for the specified values.
Usage Format: DATE(year, month, day)
Parameter Explanation: Year is the specified year value (less than 9999); Month is the specified month value (can be greater than 12); Day is the specified day value.
Application Example: Input the formula =DATE(2003,13,35) in cell C20, confirm, and it will show 2004-2-4.
Special Reminder: Since the month in the above formula is 13, which is one month over, it extends to January 2004; the day is 35, which is 4 days more than the actual days in January 2004, hence it extends to February 4, 2004.
8. DATEDIF Function
Function Name: DATEDIF
Main Function: Calculates and returns the difference between two date parameters.
Usage Format: =DATEDIF(date1, date2, "y"), =DATEDIF(date1, date2, "m"), =DATEDIF(date1, date2, "d")
Parameter Explanation: Date1 represents the earlier date, date2 represents the later date; y (m, d) requires returning the difference in years (months, days) between the two dates.
Application Example: Input the formula =DATEDIF(A23, TODAY(), "y") in cell C23, confirm, and it will return the difference in years between the system current date [represented by TODAY()] and the date in cell A23.
Special Reminder: This is a hidden function in Excel, which cannot be found in the function wizard. It can be directly inputted and used, very effective for calculating age, length of service, etc.
9. DAY Function
Function Name: DAY
Main Function: Finds out the day number of the specified date or the referenced cell.
Usage Format: DAY(serial_number)
Parameter Explanation: Serial_number represents the specified date or the referenced cell.
Application Example: Input the formula =DAY("2003-12-18"), confirm, and it will show 18.
Special Reminder: If the date is provided, enclose it in English double quotes.
10. DCOUNT Function
Function Name: DCOUNT
Main Function: Returns the number of cells containing numbers in a database or list column that satisfy specified conditions.
Usage Format: DCOUNT(database, field, criteria)
Parameter Explanation: Database represents the range of cells to be counted; Field represents the data column used by the function (with mandatory labels in the first row); Criteria represents the range of cells containing the conditions.
Application Example: As shown in Figure 1, input the formula =DCOUNT(A1:D11, "Chinese", F1:G2) in cell F4, confirm, and it will calculate the number of numeric cells in the "Chinese" column where the scores are greater than or equal to 70 and less than 80 (equivalent to the number of students in the score range).
Special Reminder: If using AutoFilter, regardless of the type selected for the function_num parameter, the SUBTOTAL function ignores any rows not included in the filtered results; the SUBTOTAL function applies to data columns or vertical ranges, not data rows or horizontal ranges.
29. SUM Function
Function Name: SUM
Main Function: Calculates the sum of all parameter values.
Usage Format: SUM(Number1, Number2....)
Parameter Explanation: Number1, Number2.... represent the values to be calculated, which can be specific values, referenced cells (regions), logical values, etc.
Application Example: As shown in Figure 7, input the formula =SUM(D2:D63) in cell D64, confirm, and it will calculate the total score for Chinese.
Special Reminder: If the parameter is an array or reference, only the numbers in it will be calculated. Empty cells, logical values, text, or error values in the array or reference will be ignored; if the above formula is modified to =SUM(LARGE(D2:D63,{1,2,3,4,5})), it will calculate the sum of the top 5 scores.
30. SUMIF Function
Function Name: SUMIF
Main Function: Calculates the sum of values within a specified range that meet a given condition.
Usage Format: SUMIF(Range, Criteria, Sum_Range)
Parameter Explanation: Range represents the cell range for condition determination; Criteria is the specified condition expression; Sum_Range represents the cell range where the values to be calculated are located.
Application Example: As shown in Figure 7, input the formula =SUMIF(C2:C63, "male", D2:D63) in cell D64, confirm, and it will calculate the total score for Chinese for "male" students.
Special Note: If the above formula is modified to =SUMIF(C2:C63, "female", D2:D63), it will calculate the total score for Chinese for "female" students; since "male" and "female" are text types, they need to be enclosed in English double quotes ("male", "female").
31. TEXT Function
Function Name: TEXT
Main Function: Converts the corresponding numbers into text format according to the specified numerical format.
Usage Format: TEXT(value, format_text)
Parameter Explanation: Value represents the number to be converted or the referenced cell; Format_text is the specified text format of the number.
Application Example: If cell B68 contains the number 1280.45, input the formula =TEXT(B68, "$0.00") in cell C68, confirm, and it will display "$1280.45".
Special Reminder: The format_text parameter can be determined based on the types in the "Number" tab of the "Cell Format" dialog box.
32. TODAY Function
Function Name: TODAY
Main Function: Provides the system date.
Usage Format: TODAY()
Parameter Explanation: This function does not require parameters.
Application Example: Input the formula =TODAY(), confirm, and it will immediately display the system date and time. If the system date and time changes, just press the F9 function key to let it update accordingly.
Special Reminder: The displayed date format can be re-set through cell formatting (see attachment).
33. VALUE Function
Function Name: VALUE
Main Function: Converts a text string representing a number into a numerical value.
Usage Format: VALUE(text)
Parameter Explanation: Text represents the text string that needs to be converted into a numerical value.
Application Example: If cell B74 contains a text string obtained through functions like LEFT, input the formula =VALUE(B74) in cell C74, confirm, and it will convert it into a numerical value.
Special Reminder: If the text-type numbers are not converted as mentioned above, errors often occur when processing these numbers with functions.
34. VLOOKUP Function
Function Name: VLOOKUP
Main Function: Searches for a specified value in the first column of a table and returns the value in a specified column of the same row of the table.
Usage Format: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Parameter Explanation: Lookup_value represents the value to be searched; Table_array represents the cell range where data needs to be searched; Col_index_num is the column sequence number of the matching value to be returned in the table_array region (when Col_index_num is 2, it returns the value in the second column of table_array, when it is 3, it returns the value in the third column...); Range_lookup is a logical value, if TRUE or omitted, it returns an approximate match value, meaning that if an exact match value cannot be found, it returns the largest value less than lookup_value; if FALSE, it returns an exact match value, if not found, it returns the error value #N/A.
Application Example: As shown in Figure 7, input the formula =VLOOKUP(B65, B2:D63, 3, FALSE) in cell D65, confirm, and as long as you input a student's name (like Ding48) in cell B65, cell D65 will immediately display the student's Chinese score.
Special Reminder: The Lookup_value must be in the first column of the Table_array region; if the Range_lookup parameter is ignored, the first column of the Table_array must be sorted; the usage of the Range_lookup parameter in this function's guide is incorrect.
35. WEEKDAY Function
Function Name: WEEKDAY
Main Function: Provides the corresponding weekday number for a specified date.
Usage Format: WEEKDAY(serial_number, return_type)
Parameter Explanation: Serial_number represents the specified date or a cell reference containing a date; Return_type represents the method of displaying the week [when Sunday (Sunday) is 1 and Saturday (Saturday) is 7, this parameter is 1; when Monday (Monday) is 1 and Sunday (Sunday) is 7, this parameter is 2 (this situation conforms to Chinese habits); when Monday (Monday) is 0 and Sunday (Sunday) is 6, this parameter is 3].
Application Example: Input the formula =WEEKDAY(TODAY(), 2), confirm, and it will provide the weekday number for the system date.
Special Reminder: If it is a specified date, place it in English double quotes, such as =WEEKDAY("2003-12-18", 2).
To apply cell color effects, select the entire table, go to Format - Conditional Formatting, choose "Formula" as the condition, use the following formula, then select the "Format" button, "Pattern", and choose the desired color.