Top Excel Formulas and Functions :You Should Know

Excel offers powerful formulas and functions that simplify calculations, automate tasks, and improve data analysis. Mastering these essential formulas helps you work faster and more efficiently.

Excel Formulas vs Functions

1. Excel Formula: A custom expression starting with = that performs calculations.

Example: =A1+A2+A3+A4+A5, which adds values from cells A1 to A5.

2. Excel Function: A predefined formula built into Excel.

Example: =SUM(A1:A10). This function adds up all the values in cells A1 through A10.

Functions simplify complex logic and reduce manual errors.

Steps to Use Functions in Excel

In the most recent versions of Excel, you may discover and enter Excel formulas into particular cells of your spreadsheet by navigating to the top menu bar and clicking on Formulas and selecting the type of formula.

The formulas can be inserted into Excel using the following method:

Step 1: Open your Excel Sheet and Choose a cell.

Step 2: Type "=", to start a formula.

Step 3: Select or input the address of a cell.

Step 4: Enter an operator.

Step 5: Input the address of the next cell.

Step 6: Press Enter.

Excel Formulas and Functions — Insert Function

Steps to Use Formulas in Excel

1. Simple Insertion by Typing the Formula in Excel

Select a cell and type =
Enter a formula or function name
Select cells or values
Press Enter

2. Using the Insert Function Option from Formulas Tab in Excel

If you want complete control over your function insertion, use the Excel Insert Function dialogue box. To do so:

go to the Formulas tab
select the first menu-> Insert Function.
All the functions will be available in the dialogue box.

3. Selecting the Formula from Formula Tab in Excel

This option is for those who want to quickly dive into their favorite functions.

Navigate to the Formulas tab and select your preferred group to access this menu.
Click to reveal a sub-menu containing a list of functions.
You can then choose your preference. If your preferred group isn't on the tab, click the More Functions option it's most likely hidden there.

4. Using Quick Insert to Use Recently Used Tabs in Excel

If retyping your most recent formula becomes tedious, use the Recently Used menu. It's on the Formulas tab, the third menu option after AutoSum.

Essential Excel Formulas & Functions

Basic Math Functions

1. SUM: Adds values

=SUM(A1:A10)

2. MULTIPLICATION

=A1*B1

Multiply values using: =A1*B1

3. DIVISION

=A1/B1

4. AVERAGE: Finds the mean

=AVERAGE(A1:A10)

5. MOD: Returns remainder

=MOD(A1,B1)

6. POWER: Raises a number

=POWER(A1,2)

7. CEILING: Rounds a number up to the nearest specified multiple.

Example: The nearest highest multiple of 1 for 2.5 is 3.

Note: The #VALUE! error value is returned by CEILING if either input is not a number.

8. FLOOR: Rounds a number down to the nearest specified multiple.

Example: The nearest lowest multiple of 1 for 2.5 is 2.

Text Functions

1. TEXTJOIN: Combines text (replaces CONCATENATE)

=TEXTJOIN(" ", TRUE, A1, B1)

2. TRIM: Removes extra spaces

=TRIM(A1)

3. LEN: Counts characters

=LEN(A1)

4. REPLACE: Replaces text by position

Syntax: =REPLACE(old_text, start_num, num_chars, new_text).

Old_text: Text in which you want to replace some characters.
Start_num: in this case refers to the index position from which you wish to begin replacing the characters.
num_chars: The amount of characters you want to replace.
New_text: The text that will replace characters in old_text.

Example: =REPLACE(A2,1,5,"@") Replaces five characters in GeeksForGeeks with a single @ character, starting with the first character (G).

5. SUBSTITUTE: Replaces matching text

Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

Text: The text or the reference to a cell containing text for which you want to substitute characters.
Old_text: The text you want to replace.
New_text: The text you want to replace old_text with.
[instance_num]: refers to the index position of the present texts more than once.

Example: =SUBSTITUTE(A2, "Student", "Employee") Substitutes Employee for Student (Employee Details) making it Employee Details.

Date & Time Functions

1. TODAY: Current date

=TODAY()

2. NOW: Current date & time

=NOW()

3. DAY: Return the day of the month.

The value will fall between 1 and 31.
The first day of the month is 1 and the last day is 31.

4. MONTH: Returns function as a number between 1 and 12,

With 1 denoting January and 12 denoting December.

5. YEAR(): Returns the year from a date value.

6. TIME(): The Excel serial number with a time format is created by the TIME() function using the hours, minutes, and seconds provided as numbers.

7. DATE: The values entered in the parenthesis, including values from other cells, will be converted to a date using this formula.

=DATE(year, month, day).
Example: If A2 was 2019, B2 was 8, and C1 was 15, =DATE(A1,B1,C1) would return 15-08-2019.

8. DATEDIF: Provides the difference between two dates in terms of years, months, or days.

Example: Using DATEDIF function to calculate the current age of a person based on two given dates, the date of birth and today’s date.

Lookup & Reference

1. VLOOKUP Formula: The VLOOKUP formula searches for the value in the leftmost column of the table array and returns the value from the same row from the specified columns.

Formula: =VLOOKUP(lookup_value, table_array,col_index, range_lookup)
Lookup_value: The value for which you are looking.
Table_array: The range of the table, worksheet, or selected cell with multiple columns.
col_index: The position of the column to extract the value.
range_lookup: "TRUE" indicates the approximate match(default), and "FALSE" indicates the exact match.

Example: =VLOOKUP(A10,A1:C7,2,FALSE)

2. HLOOKUP: gives the value in the same column from a row you specify.

=HLOOKUP(lookup_value, table, row_index, range_lookup)
Lookup_value: This indicated the value for lookup.
Table_array: the range of the table, worksheet, or selected cell with multiple columns.
row_index: The position of the row to extract the value.
range_lookup: "TRUE" indicates the approximate match(default), and "FALSE" indicates the exact match.

Example: HLOOKUP(B5, A1:D3, 3, 0)

3. INDEX-MATCH: return a value in a column to the left.

The main advantage of using INDEX-MATCH is it only has to consider the lookup column and the return column.
=INDEX(reference, [row], [column])

Example: =INDEX(B2:B6,MATCH(D2,A2:A6,0))

Conditional Calculations

1. SUMIF() Function: adds all the values in a range of cells that meet a specified condition.

= SUMIF(range,criteria,[sum_range]).

Example: = SUMIF(C6:C11,">=20")

2. AVERAGEIF() Function: Calculates the average value in a range of cells that meet the specified criteria.

=AVERAGEIF(range,criteria,[average_range]).

Example: =AVERAGEIF(C6:C11,">=20")

3. COUNTIF() Function: counts the total number of cells in the given range that satisfy the condition.

=COUNTIF(range, criteria)

Example: =COUNTIF(C2:C6,"PASS")

Dynamic Array Functions

These automatically spill results into multiple cells:

1. FILTER: Extract rows that meet a condition

=FILTER(array, include, [if_empty])

Example:

=FILTER(A2:C10,C2:C10="Sales")
Returns all rows where the department is Sales.

2. SORT: Sorts data dynamically

=SORT(array, [sort_index], [sort_order], [by_col])

Example:

=SORT(A2:B10, 2, -1)
Sorts data by column 2 in descending order.

3. UNIQUE – Removes duplicates

=UNIQUE(array, [by_col], [exactly_once])

Example:

=UNIQUE(A2:A10)
Returns a list of unique values from column A.

4. SEQUENCE: Generates numbers

=SEQUENCE(rows, [columns], [start], [step])