Excel is an essential tool used by millions of people worldwide for a variety of tasks, from simple data entry to complex financial modeling. Knowing various Excel formulas can significantly enhance your efficiency and accuracy, enabling you to handle data more effectively. In this post, we provide you with a detailed excel formulas list that every user should know, also you can download it for free from the link given bellow.
Excel Basic Formulas List
SUM: Adds up a range of cells.
- Example:
=SUM(A1:A10)adds all values from A1 to A10.
AVERAGE: Calculates the average of a range of cells.
- Example:
=AVERAGE(B1:B10)finds the average of values from B1 to B10.
COUNT: Counts the number of cells that contain numbers.
- Example:
=COUNT(C1:C10)counts all numeric values in the range C1 to C10.
MIN: Finds the smallest value in a range.
- Example:
=MIN(D1:D10)returns the smallest number in the range D1 to D10.
MAX: Finds the largest value in a range.
- Example:
=MAX(E1:E10)returns the largest number in the range E1 to E10.
How To Use Importrange in Google Sheets: A Step-by-Step Guide In Details.
Google Sheet Guide
Logical Formulas
IF: Performs a logical test and returns one value for TRUE and another for FALSE.
- Example:
=IF(F1>10, "High", "Low")returns “High” if F1 is greater than 10, otherwise returns “Low”.
AND: Checks if all conditions are met.
- Example:
=AND(G1>10, H1<20)returns TRUE if both conditions are met.
OR: Checks if at least one condition is met.
- Example:
=OR(I1>10, J1<20)returns TRUE if at least one condition is met.
NOT: Reverses the value of its argument.
- Example:
=NOT(K1>10)returns TRUE if K1 is not greater than 10.
4 Quick Ways to Find Duplicates in Google Sheets and Keep Your Data Clean
Learn Google Sheets
Lookup and Reference Formulas
VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
- Example:
=VLOOKUP(L1, M1:N10, 2, FALSE)looks up the value in L1 in the range M1and returns the corresponding value from the second column.
HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from a specified row.
- Example:
=HLOOKUP(O1, P1:Q10, 2, FALSE)looks up the value in O1 in the range P1and returns the corresponding value from the second row.
INDEX: Returns the value of a cell in a specified row and column.
- Example:
=INDEX(R1:S10, 3, 2)returns the value in the third row and second column of the range R1.
MATCH: Searches for a value in a range and returns the relative position of the value.
- Example:
=MATCH(T1, U1:U10, 0)returns the position of T1 in the range U1.
Text Formulas
CONCATENATE: Joins several text strings into one string.
- Example:
=CONCATENATE("Hello", " ", "World")results in “Hello World”.
LEFT: Returns the first character or characters in a text string, based on the number of characters you specify.
- Example:
=LEFT(V1, 3)returns the first three characters in cell V1.
RIGHT: Returns the last character or characters in a text string, based on the number of characters you specify.
- Example:
=RIGHT(W1, 3)returns the last three characters in cell W1.
MID: Returns a specific number of characters from a text string, starting at the position you specify.
- Example:
=MID(X1, 2, 3)returns three characters from cell X1, starting at the second character.
TRIM: Removes all spaces from a text string except for single spaces between words.
- Example:
=TRIM(Y1)removes extra spaces from the text in cell Y1.
LEN: Returns the number of characters in a text string.
- Example:
=LEN(Z1)returns the length of the text in cell Z1.
How to Effectively Recover Google Account: Step-by-Step Guide
Google Account
Date and Time Formulas
TODAY: Returns the current date.
- Example:
=TODAY()returns the current date.
NOW: Returns the current date and time.
- Example:
=NOW()returns the current date and time.
DATE: Returns the serial number of a particular date.
- Example:
=DATE(2024, 7, 7)returns the serial number for July 7, 2024.
TIME: Returns the serial number of a particular time.
- Example:
=TIME(12, 0, 0)returns the serial number for 12:00 PM.
YEAR: Returns the year of a date.
- Example:
=YEAR(AA1)returns the year of the date in cell AA1.
MONTH: Returns the month of a date.
- Example:
=MONTH(AB1)returns the month of the date in cell AB1.
DAY: Returns the day of a date.
- Example:
=DAY(AC1)returns the day of the date in cell AC1.
How to Take Screenshot in Laptop: 3 Best Way to take a screenshot on Any Computer
Computer Tricks
Statistical Formulas
AVERAGEIF: Calculates the average of cells that meet a specified criteria.
- Example:
=AVERAGEIF(AD1:AD10, ">20")calculates the average of cells in the range AD1that are greater than 20.
COUNTIF: Counts the number of cells that meet a specified criteria.
- Example:
=COUNTIF(AE1:AE10, "Yes")counts the number of cells in the range AE1that contain “Yes”.
SUMIF: Adds the cells specified by a given condition or criteria.
- Example:
=SUMIF(AF1:AF10, ">30")adds the cells in the range AF1that are greater than 30.
STDEV: Estimates the standard deviation based on a sample.
- Example:
=STDEV(AG1:AG10)calculates the standard deviation of the values in the range AG1.
MEDIAN: Returns the median of a given set of numbers.
- Example:
=MEDIAN(AH1:AH10)returns the median of the values in the range AH1.
Free Tools
Advanced Formulas
Array Formulas: Perform multiple calculations on one or more items in an array.
- Example:
{=SUM(AI1:AI10*AJ1:AJ10)}calculates the sum of the products of corresponding values in the ranges AI1and AJ1.
INDIRECT: Returns the reference specified by a text string.
- Example:
=INDIRECT("AK"&1)returns the value in cell AK1.
OFFSET: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
- Example:
=OFFSET(AL1, 2, 3)returns the reference to the cell that is 2 rows down and 3 columns to the right of cell AL1.
Free Templates
Tips and Tricks
- Shortcuts: Learn keyboard shortcuts for faster data entry and formula application.
- Best Practices: Always double-check your formulas for accuracy.
- Common Errors: Watch out for common errors like circular references and incorrect cell references.
Download Excel Formulas List PDF
Click on the given link below to download excel formulas list free pdf which will contain 150 excel formulas from basic to advanced with example and how to use the formula in details.
Free Cheat sheets
Conclusion
Mastering Excel formulas is crucial for anyone who works with data. This comprehensive list provides a solid foundation of basic, logical, lookup, text, date, statistical, and advanced formulas. By practicing and applying these formulas, you can enhance your productivity and efficiency in handling data. Start exploring these formulas today and see the difference they make in your Excel skills!
Frequently Asked Questions (FAQ)
- What are the most commonly used Excel formulas?
- The most commonly used Excel formulas include SUM, AVERAGE, COUNT, MIN, and MAX. These basic formulas are essential for performing everyday calculations and data analysis.
- How can I learn and remember Excel formulas effectively?
- The best way to learn and remember Excel formulas is through regular practice. Start with basic formulas and gradually move to more advanced ones. Use resources like this article, online tutorials, and Excel practice workbooks. Creating a cheat sheet of frequently used formulas can also be helpful.
- What is the difference between VLOOKUP and HLOOKUP?
- VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value in the same row from a specified column. HLOOKUP (Horizontal Lookup) searches for a value in the top row of a table and returns a value in the same column from a specified row. Essentially, VLOOKUP works with columns, and HLOOKUP works with rows.
Best 19 Attendance Sheet For Excel : Free Download Attendance sheet in excel
Free Templates
- Can I use multiple criteria in a single formula?
- Yes, you can use multiple criteria in a single formula. For example, you can use the
SUMIFS,COUNTIFS, andAVERAGEIFSfunctions to sum, count, or average values based on multiple criteria. You can also combine functions likeIFwithANDorORto evaluate multiple conditions.
- Yes, you can use multiple criteria in a single formula. For example, you can use the
- What are array formulas and when should I use them?
- Array formulas perform multiple calculations on one or more items in an array. They are useful for tasks that involve complex data manipulations, such as summing products of ranges or creating dynamic ranges. Array formulas can be identified by the curly braces
{}surrounding them. They are powerful but can be more complex to understand and use, so it’s best to start with simpler formulas and gradually work your way up.
- Array formulas perform multiple calculations on one or more items in an array. They are useful for tasks that involve complex data manipulations, such as summing products of ranges or creating dynamic ranges. Array formulas can be identified by the curly braces
