Top 100 most used Excel Functions / Formulae’s With Examples

Most Used Top 100 Excel Formulas

Certainly, here is a list of 100 Excel formulas along with brief descriptions and examples for each:

Mathematical Formulas:

1. SUM Formula:

• Description: Adds up a range of numbers.
• Example: =SUM(A1:A5) adds the values in cells A1 through A5.

1. AVERAGE Formula:

• Description: Calculates the average of a range of numbers.
• Example: =AVERAGE(B1:B4) calculates the average of values in cells B1 to B4.

1. MAX Formula:

• Description: Returns the maximum value in a range.
• Example: =MAX(C1:C6) finds the largest value in cells C1 to C6.

1. MIN Formula:

• Description: Returns the minimum value in a range.
• Example: =MIN(D1:D7) finds the smallest value in cells D1 to D7.

1. IF Formula:

• Description: Performs a conditional test and returns values based on the result.
• Example: =IF(E1>10, “Yes”, “No”) checks if the value in E1 is greater than 10 and returns “Yes” or “No” accordingly.

1. VLOOKUP Formula:

• Description: Searches for a value in a table and returns a corresponding value.
• Example: =VLOOKUP(F1, A1:B10, 2, FALSE) looks for the value in F1 within the table A1 to B10 and returns the corresponding value in the second column.

1. HLOOKUP Formula:

• Description: Similar to VLOOKUP but searches horizontally.
• Example: =HLOOKUP(G1, A1:D5, 3, FALSE) searches for G1 horizontally in the table A1 to D5 and returns the value from the third row.

1. CONCATENATE Formula:

• Description: Combines text from multiple cells into one cell.
• Example: =CONCATENATE(“Hello”, ” “, “World”) combines the text to form “Hello World.”

1. TEXT Formula:

• Description: Converts a value to text with a specified format.
• Example: =TEXT(TODAY(), “mm/dd/yyyy”) converts the current date to the format “mm/dd/yyyy.”

1. COUNTIF Formula:

• Description: Counts the number of cells that meet a specific condition.
• Example: =COUNTIF(I1:I20, “>50”) counts the cells in the range I1 to I20 that are greater than 50.

Statistical Formulas:

1. STDEV Formula:

• Description: Calculates the standard deviation of a dataset.
• Example: =STDEV(A1:A10) calculates the standard deviation of values in cells A1 to A10.

1. MEDIAN Formula:

• Description: Returns the median (middle) value of a dataset.
• Example: =MEDIAN(B1:B5) finds the median value in cells B1 to B5.

1. MODE Formula:

• Description: Returns the most frequently occurring value in a dataset.
• Example: =MODE(C1:C8) finds the mode in cells C1 to C8.

1. CORREL Formula:

• Description: Calculates the correlation coefficient between two datasets.
• Example: =CORREL(D1:D10, E1:E10) calculates the correlation between the two sets of data.

1. RANK Formula:

• Description: Returns the rank of a value in a dataset.
• Example: =RANK(F1, F1:F10) calculates the rank of the value in cell F1 within the range F1 to F10.

1. QUARTILE Formula:

• Description: Returns a specific quartile value from a dataset.
• Example: =QUARTILE(G1:G15, 3) returns the third quartile value from cells G1 to G15.

1. PERCENTILE Formula:

• Description: Returns the value at a specified percentile in a dataset.
• Example: =PERCENTILE(H1:H20, 75) returns the value at the 75th percentile in cells H1 to H20.

1. TREND Formula:

• Description: Predicts future values in a linear dataset.
• Example: =TREND(I1:I5, J1:J5, K1) predicts a future value based on the linear trend of the data in I1:I5, J1:J5, and K1.

1. GROWTH Formula:

• Description: Predicts future values in an exponential dataset.
• Example: =GROWTH(L1:L5, M1:M5, N1) predicts a future value based on the exponential growth of the data in L1:L5, M1:M5, and N1.

1. DEVSQ Formula:

• Description: Calculates the sum of squares of deviations from the mean.
• Example: =DEVSQ(O1:O10) calculates the sum of squares of deviations in cells O1 to O10.

Text Formulas:

1. LEN Formula:

• Description: Counts the number of characters in a cell.
• Example: =LEN(P1) counts the number of characters in cell P1.

1. LEFT Formula:

• Description: Extracts a specified number of characters from the beginning of a text string.
• Example: =LEFT(Q1, 3) extracts the first three characters from cell Q1.

1. RIGHT Formula:

• Description: Extracts a specified number of characters from the end of a text string.
• Example: =RIGHT(R1, 2) extracts the last two characters from cell R1.

1. MID Formula:

• Description: Extracts a specified number of characters from the middle of a text string.
• Example: =MID(S1, 2, 4) extracts four characters starting from the second character in cell S1.

1. CONCATENATE Formula (Alternative):

• Description: Combines text from multiple cells into one cell (alternative method).
• Example: =S2 & ” ” & S3 combines the text in cell S2, a space, and the text in cell S3.

1. PROPER Formula:

• Description: Converts text to proper case (capitalizes the first letter of each word).
• Example: =PROPER(T1) converts the text in cell T1 to proper case.

1. UPPER Formula:

• Description: Converts text to uppercase (capital letters).
• Example: =UPPER(U1) converts the text in cell U1 to uppercase.

1. LOWER Formula:

• Description: Converts text to lowercase (small letters).
• Example: =LOWER(V1) converts the text in cell V1 to lowercase.

1. TRIM Formula:

• Description: Removes extra spaces from a text string.
• Example: =TRIM(W1) removes extra spaces from the text in cell W1.

1. SUBSTITUTE Formula:

• Description: Replaces a specific text within a text string with another text.
• Example: =SUBSTITUTE(X1, “old”, “new”) replaces “old” with “new” in cell X1.

Date and Time Formulas:

1. TODAY Formula:

• Description: Returns the current date.
• Example: =TODAY() returns today’s date.

1. NOW Formula:

• Description: Returns the current date and time.
• Example: =NOW() returns the current date and time.

1. DATE Formula:

• Description: Creates a date value from year, month, and day.
• Example: =DATE(2023, 5, 12) creates the date May 12, 2023.

1. TIME Formula:

• Description: Creates a time value from hours, minutes, and seconds.
• Example: =TIME(14, 30, 0) creates the time 2:30 PM.

1. DATEDIF Formula:

• Description: Calculates the difference between two dates in various units (days, months, years).
• Example: =DATEDIF(Y1, Y2, “d”) calculates the number of days between the dates in cells Y1 and Y2.

1. EOMONTH Formula:

• Description: Returns the last day of the month from a given date.
• Example: =EOMONTH(Z1, 3) returns the last day of the month, three months after the date in cell Z1.

1. NETWORKDAYS Formula:

• Description: Calculates the number of working days between two dates, excluding weekends and specified holidays.
• Example: =NETWORKDAYS(A1, A10, H1:H5) calculates the working days between the dates in cells A1 and A10, excluding holidays listed in cells H1 to H5.

1. WEEKDAY Formula:

• Description: Returns the day of the week corresponding to a date.
• Example: =WEEKDAY(B1) returns a number representing the day of the week for the date in cell B1.

1. HOUR Formula:

• Description: Extracts the hour from a time value.
• Example: =HOUR(C1) extracts the hour from the time in cell C1.

1. MINUTE Formula:

• Description: Extracts the minute from a time value.
• Example: =MINUTE(D1) extracts the minute from the time in cell D1.

Logical Formulas:

1. IF Formula (Nested):

• Description: Performs nested conditional tests and returns values based on the results.
• Example: =IF(E1>10, “High”, IF(E1>5, “Medium”, “Low”)) assigns “High,” “Medium,” or “Low” based on the value in E1.

1. AND Formula:

• Description: Returns TRUE if all conditions are TRUE; otherwise, returns FALSE.
• Example: =AND(F1>5, F1<10) checks if the value in F1 is between 5 and 10.

1. OR Formula:

• Description: Returns TRUE if at least one condition is TRUE; otherwise, returns FALSE.
• Example: =OR(G1=”Red”, G1=”Blue”) checks if the value in G1 is either “Red” or “Blue.”

1. NOT Formula:

• Description: Returns TRUE if the condition is FALSE; otherwise, returns FALSE.
• Example: =NOT(H1=”Closed”) checks if the value in H1 is not “Closed.”

1. IFERROR Formula:

• Description: Returns a specified value if a formula results in an error; otherwise, returns the formula result.
• Example: =IFERROR(I1/J1, “Division Error”) calculates I1 divided by J1 but returns “Division Error” if there’s an error.

1. IFNA Formula:

• Description: Returns a specified value if a formula results in a #N/A error; otherwise, returns the formula result.
• Example: =IFNA(K1/L1, “Not Available”) calculates K1 divided by L1 but returns “Not Available” if there’s a #N/A error.

1. TRUE Formula:

• Description: Returns the logical value TRUE.
• Example: =TRUE() returns TRUE.

1. FALSE Formula:

• Description: Returns the logical value FALSE.
• Example: =FALSE() returns FALSE.

1. IFLOGICAL Formula:

• Description: Returns one value if the argument is TRUE and another value if it’s FALSE.
• Example: =IFLOGICAL(M1, “Yes”, “No”) returns “Yes” if M1 is TRUE and “No” if M1 is FALSE.

1. CHOOSE Formula:

• Description: Selects a value from a list of values based on a specified index.
• Example: =CHOOSE(N1, “Option 1”, “Option 2”, “Option 3”) selects an option based on the value in N1.

Lookup and Reference Formulas:

1. INDEX Formula:

• Description: Returns the value of a cell in a specific row and column of a range.
• Example: =INDEX(O1:O10, 3, 1) returns the value in the third row and first column of the range O1:O10.

1. MATCH Formula:

• Description: Searches for a value in a range and returns its relative position.
• Example: =MATCH(P1, P1:P10, 0) finds the position of the value in P1 within the range P1:P10.

1. OFFSET Formula:

• Description: Returns a reference to a cell relative to a specified starting cell.
• Example: =OFFSET(Q1, 2, 3) returns a reference to a cell that is two rows down and three columns to the right of Q1.

1. LOOKUP Formula:

• Description: Searches for a value in a range and returns a corresponding value from another range.
• Example: =LOOKUP(R1, S1:S10, T1:T10) looks for the value in R1 within the range S1:S10 and returns the corresponding value from the range T1:T10.

1. HYPERLINK Formula:

• Description: Creates a clickable hyperlink in a cell.
• Example: =HYPERLINK(“https://www.example.com”, “Visit Example”) creates a hyperlink to “https://www.example.com” with the display text “Visit Example.”

1. ADDRESS Formula:

• Description: Returns the cell address as text, based on row and column numbers.
• Example: =ADDRESS(U1, U2) returns the cell address of the intersection of the row specified in U1 and the column specified in U2.

1. CELL Formula:

• Description: Returns information about a cell, such as its format, location, or content.
• Example: =CELL(“format”, V1) returns the format of the cell in V1.

1. INDIRECT Formula:

• Description: Returns a cell reference specified by a text string.
• Example: =INDIRECT(“X1”) returns the value in cell X1.

1. N Formula:

• Description: Converts text to a number.
• Example: =N(“123”) converts the text “123” to the number 123.

1. TRANSPOSE Formula:

• Description: Transposes rows and columns in a range.
• Example: =TRANSPOSE(W1:W5) transposes the range W1:W5.

Financial Formulas:

1. PV Formula:

• Description: Calculates the present value of an investment or loan.
• Example: =PV(0.05, 5, 1000) calculates the present value of $1,000 to be received in 5 years at a 5% annual interest rate.

1. FV Formula:

• Description: Calculates the future value of an investment or loan.
• Example: =FV(0.06, 10, -500) calculates the future value of a $500 investment at a 6% annual interest rate over 10 years.

1. PMT Formula:

• Description: Calculates the periodic payment for a loan or annuity.
• Example: =PMT(0.04, 5, 10000) calculates the monthly payment for a $10,000 loan at a 4% annual interest rate over 5 years.

1. NPER Formula:

• Description: Calculates the number of payment periods for a loan or investment.
• Example: =NPER(0.03, -200, 8000) calculates the number of payment periods required to reach a future value of $8,000 with a $200 monthly investment at a 3% annual interest rate.

1. RATE Formula:

• Description: Calculates the interest rate for a loan or investment.
• Example: =RATE(36, -500, 10000) calculates the annual interest rate required to reach a future value of $10,000 with a $500 monthly investment over 3 years.

1. IRR Formula:

• Description: Calculates the internal rate of return for a series of cash flows.
• Example: =IRR(X1:X5) calculates the internal rate of return for the cash flows in cells X1 to X5.

1. NPV Formula:

• Description: Calculates the net present value of a series of cash flows.
• Example: =NPV(0.1, Y1:Y5) calculates the net present value of cash flows in cells Y1 to Y5 at a discount rate of 10%.

1. DB Formula:

• Description: Calculates depreciation of an asset for a specific period using the double declining balance method.
• Example: =DB(1000, 5, 2) calculates the depreciation of a $1,000 asset over 5 years using a double declining balance method with a 2-year life.

1. SLN Formula:

• Description: Calculates depreciation of an asset for a specific period using the straight-line method.
• Example: =SLN(1000, 5, 0) calculates the depreciation of a $1,000 asset over 5 years using the straight-line method with no salvage value.

1. DDB Formula:

• Description: Calculates depreciation of an asset for a specific period using the double declining balance method with a factor.
• Example: =DDB(2000, 3, 2, 2) calculates the depreciation of a $2,000 asset over 3 years using a double declining balance method with a factor of 2 and a 2-year life.

Conditional Formatting Formulas:

1. CONDITIONAL FORMATTING Formula:

• Description: Applies formatting (colors, styles) to cells based on specified conditions.
• Example: Create a rule to highlight cells in column A where the value is greater than 100.

1. COLORSCALE Formula:

• Description: Applies a color scale to a range of values, allowing visualization of data variations.
• Example: Apply a color scale to a range of test scores to show variations in performance.

1. ICON SET Formula:

• Description: Applies icons (such as arrows or flags) to cells based on the comparison of cell values.
• Example: Add an icon set to a column of financial data to quickly identify trends.

1. DATA BARS Formula:

• Description: Adds data bars (horizontal bars) to cells to visually represent data values.
• Example: Use data bars to show the relative size of sales figures in a table.

1. TOP/BOTTOM RULES Formula:

• Description: Highlights the top or bottom values in a range based on specified criteria.
• Example: Apply a top 10% rule to highlight the top-performing products in a sales report.

1. FORMULA RULES Formula:

• Description: Applies formatting based on the result of a formula or expression.
• Example: Format cells with a formula rule that identifies values above a certain threshold.

1. TEXT RULES Formula:

• Description: Applies formatting based on the text content of cells.
• Example: Use text rules to format cells containing specific keywords or phrases.

1. DUPLICATE VALUES Formula:

• Description: Highlights duplicate values within a range.
• Example: Use duplicate values formatting to identify and remove duplicate entries in a list.

1. UNIQUE VALUES Formula:

• Description: Highlights unique values within a range.
• Example: Apply unique values formatting to highlight one-of-a-kind items in a dataset.

1. TIME PERIODS Formula:

• Description: Applies formatting based on date and time criteria.
• Example: Use time periods formatting to highlight weekends or holidays in a date range.

Array Formulas:

1. ARRAY Formula:

• Description: Performs calculations on arrays of data, often used with functions like SUMPRODUCT.
• Example: =SUMPRODUCT(A1:A5, B1:B5) multiplies corresponding values in two arrays and then sums the products.

1. FREQUENCY Formula:

• Description: Calculates the frequency distribution of values in a dataset.
• Example: =FREQUENCY(C1:C10, D1:D5) calculates the frequency of values in C1:C10 within the specified bins in D1:D5.

1. MMULT Formula:

• Description: Multiplies two matrices together.
• Example: =MMULT(E1:H3, I1:I4) multiplies the matrices represented by the ranges E1:H3 and I1:I4.

1. INDEX MATCH MATCH Formula:

• Description: Performs a two-dimensional lookup by combining INDEX and MATCH functions.
• Example: =INDEX(K1:N10, MATCH(K13, K1:K10, 0), MATCH(K14, L1:N1, 0)) returns the value at the intersection of K13 and K14 in the two-dimensional range K1:N10.

1. MODE.MULT Formula:

• Description: Returns multiple modes (most frequently occurring values) from a dataset.
• Example: {=MODE.MULT(O1:O20)} returns an array of the most frequently occurring values in cells O1 to O20.

1. TRANSPOSE Formula (Array Version):

• Description: Transposes rows and columns in an array.
• Example: {=TRANSPOSE(P1:S5)} transposes the array represented by the range P1:S5.

1. SUMIFS Formula:

• Description: Adds values in a range based on multiple criteria.
• Example: =SUMIFS(T1:T10, U1:U10, “Apples”, V1:V10, “Red”) adds values in T1:T10 where U1:U10 is “Apples” and V1:V10 is “Red.”

1. AVERAGEIFS Formula:

• Description: Calculates the average of values in a range based on multiple criteria.
• Example: =AVERAGEIFS(W1:W15, X1:X15, “Bananas”, Y1:Y15, “Yellow”) calculates the average of values in W1:W15 where X1:X15 is “Bananas” and Y1:Y15 is “Yellow.”

1. COUNTIFS Formula:

• Description: Counts the number of cells that meet multiple criteria.
• Example: =COUNTIFS(Z1:Z20, “Oranges”, AA1:AA20, “Orange”) counts cells in Z1:Z20 where Z1:Z20 is “Oranges” and AA1:AA20 is “Orange.”

1. SUMPRODUCT Formula:

• Description: Calculates the sum of the products of corresponding values in arrays.
• Example: =SUMPRODUCT(AB1:AB5, AC1:AC5) calculates the sum of the products of values in AB1:AB5 and AC1:AC5.

Data Analysis Formulas:

1. PivotTable Formula:

• Description: Creates a PivotTable for summarizing and analyzing data.
• Example: Create a PivotTable to analyze sales data by product category and region.

1. Subtotal Formula:

• Description: Performs calculations (sum, average, etc.) on a range with the ability to specify grouping levels.
• Example: =SUBTOTAL(9, AD1:AD100) calculates the sum for a range AD1:AD100 while ignoring other SUBTOTAL functions in the range.

1. GetPivotData Formula:

• Description: Retrieves data from a PivotTable.
• Example: =GETPIVOTDATA(“Sales”, PivotTable, “Region”, “West”, “Category”, “Electronics”) retrieves sales data for the West region and Electronics category from a PivotTable.

1. Slicer Formula:

• Description: Creates slicers to filter data in a PivotTable or PivotChart.
• Example: Add slicers to filter data by product category and date range in a PivotTable.

1. Power Query Formula:

• Description: Extracts, transforms, and loads data from various sources into Excel.
• Example: Use Power Query to import data from a CSV file and transform it for analysis.

1. Data Validation Formula:

• Description: Sets rules to validate data entry in cells.
• Example: Create data validation rules to ensure that only numeric values between 1 and 100 are entered in a column.

1. What-If Analysis Formula:

• Description: Allows users to explore different scenarios by changing input values.
• Example: Use Goal Seek to find the input value required to achieve a specific result in a formula.

1. Scenario Manager Formula:

• Description: Manages and compares multiple scenarios in a worksheet.
• Example: Set up and compare different budget scenarios with varying revenue and expense values.

1. Solver Formula:

• Description: Solves optimization problems by finding the optimal values for specific variables.
• Example: Use Solver to determine the optimal production quantities for maximizing profit while satisfying constraints.

1. Data Consolidation Formula: – Description: Combines data from multiple ranges into a single summary sheet. – Example: Consolidate sales data from multiple regional worksheets into a single master worksheet.

These are 100 Excel formulas with brief descriptions and examples from various categories. You can expand on each formula in your blog post, providing more detailed explanations and use cases to make it informative and engaging for your viewers.