Top 25 Excel Formulas walk-in thru Meme-verse
13 min readJun 18, 2023
“If you can excel in Excel, you can excel in Life”
Created a short post as an Educational Post for a quick ready reckoner on most commonly used Excel formulas, mostly can help during interview process or wanted to revise your fundamental concepts. Each function is tried to explain with a Meme or a Joke.
Table of Contents
∘ 1. SUM
∘ 2. AVERAGE
∘ 3. COUNT
∘ 4. SUBTOTAL
∘ 5. CORREL
∘ 6. MAX/MIN/MEAN/MEDIAN/STANDARD DEVIATION
∘ 7. VLOOKUP
∘ 8. IF
∘ 9. AND/OR/NOT (logical operators)
∘ 10. FIND/SEARCH
∘ 11. Loops (REPT/SEQ)
∘ 12. LEFT / RIGHT/MID
∘ 13. MONTH, DAY, YEAR
∘ 14. TRANSPOSE
∘ 15. CONCATENATE
∘ 16. TRIM
∘ 17. LEN
∘ 18. LOWER/UPPER
∘ 19.RANDOM
∘ 20. PROPER
∘ 21. TYPE
∘ 22. DATEVALUE
∘ 23. RANK
∘ 24. INT
∘ 25. TODAY
Explore thru Memes
1. SUM
Basic Syntax:
+------------------+-------------------------------------+
| Example | =SUM(B2:B6) |
+------------------+-------------------------------------+
| Description | Calculates the sum of a range of |
| | numbers. |
+------------------+-------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-------------------------------------+
| Output | 150 |
| (in cell B7) | |
+------------------+-------------------------------------+
| Syntax | =SUM(number1, [number2], ...) |
+------------------+-------------------------------------+
2. AVERAGE
Basic Syntax
+------------------+-----------------------------------------+
| Example | =AVERAGE(B2:B6) |
+------------------+-----------------------------------------+
| Description | Calculates the average of a range of |
| | numbers. |
+------------------+-----------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-----------------------------------------+
| Output | 30 |
| (in cell B7) | |
+------------------+-----------------------------------------+
| Syntax | =AVERAGE(number1, [number2], ...) |
+------------------+-----------------------------------------+
3. COUNT
Basic Syntax
+------------------+-----------------------------------------+
| Example | =COUNT(B2:B6) |
+------------------+-----------------------------------------+
| Description | Counts the number of cells that contain |
| | numbers within a specified range. |
+------------------+-----------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-----------------------------------------+
| Output | 5 |
| (in cell B7) | |
+------------------+-----------------------------------------+
| Syntax | =COUNT(value1, [value2], ...) |
+------------------+-----------------------------------------+
4. SUBTOTAL
Basic Syntax
+------------------+-----------------------------------------+
| Example | =SUBTOTAL(1, B2:B6) |
+------------------+-----------------------------------------+
| Description | Calculates the average of a range of |
| | numbers, ignoring hidden rows. |
+------------------+-----------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-----------------------------------------+
| Output | 30 |
| (in cell B7) | |
+------------------+-----------------------------------------+
| Syntax | =SUBTOTAL(function_num, ref1, [ref2],..)|
+------------------+-----------------------------------------+
5. CORREL
Basic Syntax
+------------------+-----------------------------------------+
| Example | =CORREL(B2:B6, C2:C6) |
+------------------+-----------------------------------------+
| Description | Calculates the correlation coefficient |
| | between two sets of numbers. |
+------------------+-----------------------------------------+
| Input | |
| (in cells B2:B6) | 10, 20, 30, 40, 50 |
| (in cells C2:C6) | 5, 15, 25, 35, 45 |
+------------------+-----------------------------------------+
| Output | 1 |
| (in cell B7) | |
+------------------+-----------------------------------------+
| Syntax | =CORREL(array1, array2) |
+------------------+-----------------------------------------+
6. MAX/MIN/MEAN/MEDIAN/STANDARD DEVIATION
Basic Syntax
+------------------+-------------------------------------------+
| Example | =MAX(B2:B6) |
+------------------+-------------------------------------------+
| Description | Returns the maximum value from a range of |
| | numbers. |
+------------------+-------------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-------------------------------------------+
| Output | 50 |
| (in cell B7) | |
+------------------+-------------------------------------------+
| Syntax | =MAX(number1, [number2], ...) |
+------------------+-------------------------------------------+For Syntax, ref to this link
In Excel’s rows and columns, I find a world of numbers, so unkind Mean, Median, Min, and Max And the standard deviation, all packed.
They tell me of my data’s spread and how it’s distributed, so widespread. But, what do these numbers really mean? In my mind, it’s all a dull routine.
The mean, a simple average, so plain. But, what about the outliers that remain? The median, the middle point of my array. But, what about the extremes that sway?
The minimum and maximum, so extreme. But, what about the values in between? And the standard deviation, so precise. But, what about the anomalies that arise?
In Excel’s world, I find no emotion Just numbers, devoid of any notion. But, Bukowski would have found a way to make this dull data sing and sway.
He’d have found beauty in the mean and median and made the min and Max sound like a hymn. And the standard deviation, so clinical Would become a thing of beauty, almost mystical.
In Bukowski’s hands, Excel’s numbers would come alive nd he’d make me care, make me strive to understand the data that I see and find the beauty in its complexity.
7. VLOOKUP
When you’re working on data analysis in Excel and you realize you forgot to lock your cell references in your VLOOKUP formula.
#RefError
+------------------+--------------------------------------------------+
| Example | =VLOOKUP(A2, B2:D6, 3, FALSE) |
+------------------+--------------------------------------------------+
| Description | Searches for a value in the first column of a |
| | range and returns a value in the corresponding |
| | row and specified column. |
+------------------+--------------------------------------------------+
| Input | "Apple" |
| (in cell A2) | |
| | |
| | B2:D6 |
| | +--------+-------+-------+ |
| | | Fruit | Color | Price | |
| | +--------+-------+-------+ |
| | | Apple | Red | $1.00 | |
| | | Banana | Yellow| $0.50 | |
| | | Orange | Orange| $0.75 | |
| | | Grapes | Purple| $2.00 | |
| | | Mango | Yellow| $1.50 | |
| | +--------+-------+-------+ |
+------------------+--------------------------------------------------+
| Output | "$1.00" |
| (in cell B7) | |
+------------------+--------------------------------------------------+
| Syntax | =VLOOKUP(lookup_value, table_array, col_index_num|
| | ,[range_lookup]) |
+------------------+--------------------------------------------------+
8. IF
Syntax for IF Function
+------------------+-----------------------------------------+
| Example | =IF(B2>20, "Pass", "Fail") |
+------------------+-----------------------------------------+
| Description | Checks if the value in cell B2 is > |
| | than 20. If true, it returns "Pass"; if |
| | false, it returns "Fail". |
+------------------+-----------------------------------------+
| Input | 15 |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | Fail |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =IF(logical_test, value_if_true, |
| | value_if_false) |
+------------------+-----------------------------------------+
IF Vs Switch case comic
+------------------+--------------------------------------------+
| Example | =SWITCH(C2, 1, "One", 2, "Two", 3, "Three")|
+------------------+--------------------------------------------+
| Description | Checks the value in cell C2 and returns |
| | a corresponding text based on the value. |
+------------------+--------------------------------------------+
| Input | 2 |
| (in cell C2) | |
+------------------+--------------------------------------------+
| Output | Two |
| (in cell C3) | |
+------------------+--------------------------------------------+
| Syntax | =SWITCH(expression, value1, result1, |
| | [value2, result2], ...) |
+------------------+--------------------------------------------+9. AND/OR/NOT (logical operators)
Basic Syntax
+------------------+-----------------------------------------+
| Example | =AND(B2>10, B2<50) |
+------------------+-----------------------------------------+
| Description | Checks if a number is greater than 10 |
| | and less than 50. |
+------------------+-----------------------------------------+
| Input | 25 |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | TRUE |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =AND(logical1, [logical2], ...) |
+------------------+-----------------------------------------+For basic syntax, click link here
10. FIND/SEARCH
Basic Syntax
+---------------------+-------------------------------------------------------+
| Example | =SEARCH("Excel", A2) |
+---------------------+-------------------------------------------------------+
| Description | Searches for the position of the word "Excel" within t|
| | the text in cell A2. | |
+---------------------+-------------------------------------------------------+
| Input | "I love working with Excel!" |
| (in cell A2) | |
+---------------------+-------------------------------------------------------+
| Output | 19 |
| (in cell B2) | |
+---------------------+-------------------------------------------------------+
| Syntax | =SEARCH(find_text, within_text, [start_num]) |
+---------------------+-------------------------------------------------------+Ah, the FIND and SEARCH functions in Excel! How they doth vex and bewilder the uninitiated, yet how they doth delight and gratify the seasoned user. Forsooth, they are like two peas in a pod, yet as different as chalk and cheese.
The FIND function doth seek out a specific value in a given range, and returneth the position of the first occurrence thereof. Like a sharp-eyed detective on the trail of a cunning criminal, it doth scour the cells for its quarry, and bringeth it to light with aplomb and alacrity.
The SEARCH function, on the other hand, doth cast a wider net, and searcheth for a specific substring within a larger string. Like a miner searching for nuggets of gold in a vast and rocky terrain, it doth dig deep and unearths the hidden gems that lie beneath the surface.
Truly, these functions are a boon and a blessing to all who work in the realm of Excel, and a tribute to the ingenuity and skill of those who created them. So let us raise a toast to FIND and SEARCH, those tireless and faithful servants of the spreadsheet world, and may they continue to serve us well for many a year to come!
[Oscar Wilde Style]
For Basic Syntax: click here
11. Loops (REPT/SEQ)
REPT()
+-------------------------------+-----------------------------------------------------+
| Example | =REPT("Hello ", 3) |
+-------------------------------+-----------------------------------------------------+
| Description | Repeats the text "Hello " three times. |
+-------------------------------+-----------------------------------------------------+
| Output | Hello Hello Hello |
| (in cell B7) | |
+-------------------------------+-----------------------------------------------------+
| Syntax | =REPT(text, number_of_times) |
+-------------------------------+-----------------------------------------------------+SEQ()
+-------------------------------+-----------------------------------------------------+
| Example | =SEQUENCE(5, 1, 10, 2) |
+-------------------------------+-----------------------------------------------------+
| Description | Creates a sequence of numbers starting from 10, |
| | with a step of 2, and generates 5 numbers. |
+-------------------------------+-----------------------------------------------------+
| Output | 10, 12, 14, 16, 18 |
| (in cells B2:B6) | |
+-------------------------------+-----------------------------------------------------+
| Syntax | =SEQUENCE(rows, columns, start, step) |
+-------------------------------+-----------------------------------------------------+
12. LEFT / RIGHT/MID
+------------------+------------------------------------------------------+
| Example | =LEFT(A2, 3) |
+------------------+------------------------------------------------------+
| Description | Retrieves the leftmost characters from a text string.|
+------------------+------------------------------------------------------+
| Input | "Hello World" |
| (in cell A2) | |
+------------------+------------------------------------------------------+
| Output | "Hel" |
| (in cell B2) | |
+------------------+------------------------------------------------------+
| Syntax | =LEFT(text, num_chars) |
+------------------+------------------------------------------------------+
+------------------+------------------------------------------------------+
| Example | =MID(A2, 7, 5) |
+------------------+------------------------------------------------------+
| Description | Retrieves a specific number of characters from a |
| | text string, starting at a specified position. |
+------------------+------------------------------------------------------+
| Input | "Hello World" |
| (in cell A2) | |
+------------------+------------------------------------------------------+
| Output | "World" |
| (in cell B2) | |
+------------------+------------------------------------------------------+
| Syntax | =MID(text, start_num, num_chars) |
+------------------+------------------------------------------------------+
13. MONTH, DAY, YEAR
Basic Syntax
+------------------+-----------------------------------------+
| Example | =MONTH(A2) |
+------------------+-----------------------------------------+
| Description | Returns the month number from a given |
| | date value. |
+------------------+-----------------------------------------+
| Input | 2023-01-15 |
| (in cell A2) | |
+------------------+-----------------------------------------+
| Output | 1 |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Syntax | =MONTH(serial_number) |
+------------------+-----------------------------------------+
14. TRANSPOSE
Basic Syntax
+------------------+-----------------------------------------+
| Example | =TRANSPOSE(B2:B6) |
+------------------+-----------------------------------------+
| Description | Transposes a range of cells. |
+------------------+-----------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+-----------------------------------------+
| Output | 10 |
| (in cell B7) | |
| | 20 |
| | |
| | 30 |
| | |
| | 40 |
| | |
| | 50 |
+------------------+-----------------------------------------+
| Syntax | =TRANSPOSE(array) |
+------------------+-----------------------------------------+
15. CONCATENATE
Basic Syntax
+------------------+-------------------------------------------------------+
| Example | =CONCATENATE(A2, " ", B2, " is a great combination!") |
+------------------+-------------------------------------------------------+
| Description | Concatenates the values in cells A2 and B2 |
| | with a space and a custom text. |
+------------------+-------------------------------------------------------+
| Input | "Hello", "World" |
| (in cells A2, B2)| |
+------------------+-------------------------------------------------------+
| Output | "Hello World is a great combination!" |
| (in cell C2) | |
+------------------+-------------------------------------------------------+
| Syntax | =CONCATENATE(text1, [text2], ...) |
+------------------+-------------------------------------------------------+
16. TRIM
Basic Syntax
+---------------------+------------------------------------------------------+
| Example | =TRIM(A2) |
+---------------------+------------------------------------------------------+
| Description | Removes leading and trailing spaces from a |
| | text string |
+---------------------+------------------------------------------------------+
| Input | " Hello World " |
| (in cell A2) | |
+---------------------+------------------------------------------------------+
| Output | "Hello World" |
| (in cell B2) | |
+---------------------+------------------------------------------------------+
| Syntax | =TRIM(text) |
+---------------------+------------------------------------------------------+
17. LEN
Basic Syntax
+------------------+-------------------------------------+
| Example | =LEN(A2) |
+------------------+-------------------------------------+
| Description | Returns the number of characters |
| | in a text string. |
+------------------+-------------------------------------+
| Input | "Hello, World!" |
| (in cell A2) | |
+------------------+-------------------------------------+
| Output | 13 |
| (in cell B2) | |
+------------------+-------------------------------------+
| Syntax | =LEN(text) |
+------------------+-------------------------------------+
18. LOWER/UPPER
LOWER(“Lower”) = lower
UPPER(“Upper”) = UPPER
19.RANDOM
+---------------------+----------------------------------------------------+
| Example | =RAND() |
+---------------------+----------------------------------------------------+
| Description | Generates a random decimal number between 0 and 1. |
+---------------------+----------------------------------------------------+
| Input | N/A |
+---------------------+----------------------------------------------------+
| Output | Random decimal number between 0 and 1 |
| (in cell B7) | |
+---------------------+----------------------------------------------------+
| Syntax | =RAND() |
+---------------------+----------------------------------------------------+
20. PROPER
Basic Syntax
+------------------+-----------------------------------------+
| Example | =PROPER("hello world") |
+------------------+-----------------------------------------+
| Description | Converts text to proper case, where the |
| | first letter of each word is capitalized|
| | and the rest of the letters are in lower|
| | case. |
+------------------+-----------------------------------------+
| Input | "hello world" |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | "Hello World" |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =PROPER(text) |
+------------------+-----------------------------------------+
21. TYPE
Basic Syntax
+------------------+-----------------------------------------+
| Example | =TYPE(B2) |
+------------------+-----------------------------------------+
| Description | Returns the type of value in a cell. |
+------------------+-----------------------------------------+
| Input | "Hello, World!" |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | 1 |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =TYPE(value) |
+------------------+-----------------------------------------+
22. DATEVALUE
Basic Syntax
+------------------+-----------------------------------------+
| Example | =DATEVALUE("10/01/2023") |
+------------------+-----------------------------------------+
| Description | Converts a date string into a serial |
| | number recognized by Excel. |
+------------------+-----------------------------------------+
| Input | "10/01/2023" |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | 44510 |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =DATEVALUE(date_text) |
+------------------+-----------------------------------------+
23. RANK
Basic Syntax
+------------------+--------------------------------------------------+
| Example | =RANK(B2, B$2:B$6, 1) |
+------------------+--------------------------------------------------+
| Description | Calculates the rank of a number in a range, |
| | where a higher number has a higher rank. |
+------------------+--------------------------------------------------+
| Input | 10, 20, 30, 40, 50 |
| (in cells B2:B6) | |
+------------------+--------------------------------------------------+
| Output | 3 |
| (in cell B7) | |
+------------------+--------------------------------------------------+
| Syntax | =RANK(number, ref, [order]) |
+------------------+--------------------------------------------------+
24. INT
Basic Syntax
+------------------+-----------------------------------------+
| Example | =INT(B2) |
+------------------+-----------------------------------------+
| Description | Rounds a number down to the nearest |
| | integer. |
+------------------+-----------------------------------------+
| Input | 3.7 |
| (in cell B2) | |
+------------------+-----------------------------------------+
| Output | 3 |
| (in cell B3) | |
+------------------+-----------------------------------------+
| Syntax | =INT(number) |
+------------------+-----------------------------------------+
25. TODAY
Basic Syntax
+------------------+---------------------------------------------------+
| Example | =TODAY() |
+------------------+---------------------------------------------------+
| Description | Returns the current date. |
+------------------+---------------------------------------------------+
| Input | No input required. |
+------------------+---------------------------------------------------+
| Output | Current date in the format MM/DD/YYYY |
| (in cell B7) | |
+------------------+---------------------------------------------------+
| Syntax | =TODAY() |
+------------------+---------------------------------------------------+
