In this guide lets us understand the top 10 excel formulas or functions that are typically used in Excel.
1. SUM
Syntax :
=SUM(number1, [number2], [number3], ...)Example
=SUM(100, 200)Note: Excel also provides selecting multiple or range of cells if the number set is large as shown in the image below.
2. AVERAGE
Syntax :
=AVERAGE(number1, [number2], [number3], ...)Example
=AVERAGE(100, 200)Note: Excel also provides selecting multiple or range of cells if the number set is large as shown in the image below.
3. SUMIF
Syntax :
=SUMIF(range, criteria, [sum_range])Example
Find number of chocolates from the list -
4. COUNT
Syntax :
=COUNT(value1, [value2], [value3], ...)- The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments.
- Empty cells, logical values, text, or error values in the array or reference are not counted.
Example
Find count of numbers in the list -
5. COUNTA
Syntax :
=COUNTA(value1, [value2], [value3], ...)- The COUNTA function counts the number of cells that are not empty in a range.
- The COUNTA function counts cells containing any type of information, including error values and empty text (“”). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value.
- The COUNTA function does not count empty cells.
Example
Find count of items in the list -
Additional Notes :
- If you use COUNT to find the count of items in the list, it gives ZERO
6. COUNTIF
To count the number of times a particular text appears in a list.
Syntax :
=COUNTIF(range, criteria)Example
Find count of word “Chocolates” in the list
7. CONCATENATE
To join two or more text strings into one string.
Syntax :
=CONCATENATE(text1, [text2], [text3], ...)Example
8. IF
It allows you to make logical comparisons between a value and what you expect.
Syntax :
=IF(logical_test, value_if_true, [value_if_false])
9. VLOOKUP
Use VLOOKUP when you need to find things in a table or a range by row.
Syntax :
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])Example
Find the how many Strawberries are available from the list
10. Conditional Formatting
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value.
For Example, if the text says “This is chocolate” it should be GREEN else it should be RED.
On the Home tab, in the Styles group, click Conditional Formatting > Click Highlight Cells Rules > Equal To…
Create two rules, one for text “This is chocolate” which should be “Green fill with Dark Green Text” & another for “This is not a chocolate” for “Light Red fill with Dark Red Text”.
Originally published at https://www.inkeybit.com on April 28, 2022.
