Excel comes with a number of built-in programs that make using it simpler. Therefore, you should know excel formulas and functions to make your work in much more efficient manner.
You should rethink your opinion if you believe Excel to be entry-level software. Excel is appropriate for important data mining, storage, and collection tasks, but it is now capable of much more.
Microsoft has updated its systems and now provides a wide range of cutting-edge features that can assist in quickly organizing and interpreting mountains of data.
Top 30 Excel Formulas And Functions
However, you should not accept it just by listening. Try this kind of formula instead, which can be used in software as simple and user-friendly as Excel to help simplify a complex set of data:
Excel sheet formulas are one such feature that makes Excel unique. Here, we will examine the top 25+ Excel formulas that anyone using the program for work should know. The following are the subjects that will be covered in this article:
KEY TAKEAWAYS:
- Excel’s Built-in Programs: Excel offers built-in programs that simplify usage, highlighting the importance of knowing Excel formulas and functions to enhance efficiency.
- Excel’s Capabilities Beyond Entry-Level: Contrary to the perception of Excel as entry-level software, it is suitable for important data tasks like mining, storage, and collection, showcasing its expanded capabilities.
- Microsoft’s Updated Features: Microsoft’s updates provide cutting-edge features in Excel, aiding in quick organization and interpretation of vast amounts of data, underscoring the software’s evolution beyond its traditional functions.
Let us learn some important Excel formulas in this article.
1. SUM
As suggested by its name, the SUM() function returns the sum of the values in the selected range of cells. It executes the addition mathematical operation. Following is a simple illustration of it:
Sum =SUM(A2:A4)
2. Lookup value
In this function, your typed variable will start finding the values from all the cells of the broad set of information.
3. AVERAGE Function
The main objective of the AVERAGE() function is to compute the average of the selected range of cell values. The following example demonstrates how to enter the following information to determine the average of all sales:
AVERAGE = AVERAGE (A2, A3, A4)
The average is calculated automatically; you can store the outcome wherever you like.
4. COUNT
The function Count() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric.
COUNT =COUNT(C1:C4)
5. Table Array
In this one, you can specify the data range from which you want to look. It establishes a limit and gives the software valuable time back. As a result, you will save time and have the option to set the range of the data manually.
6. Range lookup
The range look-up option shows either true or false. The right end of the data will provide you with information close to the variable you put in. The function will try to determine whether your information is true or accurate.
On the other hand, false would not provide you anything and show NA if it doesn’t find the exact result.
7. Sum Function
One of the most common and popular functions, Sum, simply sums up the selected values. You can choose any part of the column to add up or choose the whole row or column to see the total value. It comes in very handy for invoices, sales figures, and more.
8. IF Function
The IF function segregated information between true and false based on particular criteria. You write the criteria function or variable like this:
IF (Criteria, True and False values)
9. MAX Function
This function lets you find out the maximum value of a particular data. It shows the range of the data. You just have to lodge MAX in the function tab to check out the maximum amount in the selected data.
10. MIN function
The MIN function enables you to determine the minimum value within the chosen set of data, in contrast to the max function. You will be informed of the value that is the lowest in the main data table.
11. SUBTOTAL
The subtotal in a database is returned by the SUBTOTAL() function. You can choose between average, count, sum, minimum, maximum, and other options based on your needs.
12. COUNTIF Function
The CountIF function counts the values instead of adding them up. Between it and SUMIF, that is the only distinction. It will only count when the condition stated is met.
13. AND function
You can specify multiple criteria for the search using this function. The variables that satisfy the requirements appear on the TRUE side, while those that do not comply with the requirements display FALSE. Furthermore, the wizard box allows you to define logical standards.
14. LEFT Function
This function allows you to move the left side of the data in the selected column into a different column. You can also specify using commands how much information you want in the new column.
15. RIGHT Function
You can also pull a specific portion of the information from the selected column on the right side of the data set.
16. CONCATENATION Function
This function is used to combine the Left and Right actions. By combining a particular variable from both the left and right, you can create a new column of data.
17. OR Function
In contrast to the AND function, which checks against multiple criteria, the OR function only considers one. At least one bar enters the right side regardless of which variable passes.
18. PROPER Function
The first letters of each sentence that is selected can be capitalized using this function. The format can also be altered as required.
19. ROUND Function
The Round function aids in rounding up data for simplification if your data contains a lot of decimals. However, you are not required to format the cells.
20. NOW Function
You can create a new column that displays the time for each variable by using the Now column. The variable can also be changed only to include dates.
21. TRIM advanced Excel formula
This function eliminates any extra spaces between variables that might have developed after copying a sizable amount of data to an Excel document.
22. Modify the case
Using this formula, you can change the case of all the cells together. With the appropriate command, you can bold letters and switch the case from lower to upper or vice versa.
23. Customized MAX MIN
With the help of this advanced Excel formula, you can extract customized MAX and MIN. Then, according to the orders and degrees, you can see the highest and lowest value in the selected cells. If you enter the right commands, you can select the fifth- or sixth-highest amount from a large set.
24. CONVERT ()
You can simply enter the unit values in the Convert function rather than using Google to perform a different conversion. This allows you to convert lengths, currencies, and many other things.
25. REPT()
With the help of this function, you can retrieve different character types in a cell.
26. Choose ()
This function can be used to remove the lengthy IF function statements. You can extract a particular set from the data in this manner. This function is useful for selecting one of several possible outcomes in a given situation.
27. TYPE ()
The TYPE command will allow you to identify the type of data each cell contains if you are working with someone else’s data.
28. CONVERT ()
It is used mainly for unit conversion. You can simply enter the unit values in the Convert function rather than using Google to perform a different conversion. This allows you to convert lengths, currencies, and many other things.
29. R AND BETWEEN ()
This function allows you to add random numbers between specified variables. It is beneficial when you want to simulate a behavior or data change.
30. PV function
The function makes it easier to figure out things like rate, payment per period, future value, and investment period. Excel acts as if it has a built-in financial expert, making it simple to perform intricate financial forecasting.
What is Excel in Short Answer?
Excel is a software program from Microsoft that is part of the Microsoft Office suite. It’s used for creating spreadsheets, which are documents made up of rows and columns. Excel allows you to organize data, perform calculations using formulas, create charts, and analyze information to make informed decisions. It’s widely used in businesses, schools, and by individuals for various data management tasks.
What is the Formula Excel?
Imagine you have a magic notebook that can do math problems for you. This magic notebook is like Excel, which is a program on computers where you can keep all kinds of numbers and lists neatly in little boxes called cells.
Now, a formula in Excel is like a special instruction you write down to tell Excel what math to do for you. For example, if you want to add the number 5 and the number 3 in your magic notebook, you write a formula to tell Excel to do this job.
Here’s how you write this formula in Excel:
- Click on a cell where you want the answer to appear.
- Type the equal sign
=, which tells Excel that you’re going to give it a math job. - Type
5+3after the equal sign. - Press the Enter key on your keyboard, and Excel will show the answer,
8, in the cell where you typed the formula.
You can do all sorts of math with formulas in Excel, like adding, subtracting, multiplying, and dividing! This makes Excel very helpful for all kinds of tasks that involve numbers.
What is Excel Formula List?
Think of Excel, the program on the computer where you can put numbers in boxes (which are called cells). Excel can do all kinds of math for you if you tell it what to do. An “Excel formula list” is like a special cheat sheet that tells you all the different math tricks Excel can do with those numbers.
For example, if you want to add numbers, there’s a formula for that. If you want to find the biggest number in a list, there’s a formula for that too! Here are a few formulas you might find in an Excel formula list:
- Adding numbers: If you want to add two or more numbers, you use a formula like
=SUM(number1, number2). So, if you type=SUM(5, 3), Excel will show the answer8. - Finding the biggest number: If you have a list of numbers and want to know the biggest one, you use a formula like
=MAX(number1, number2, ...). So, if you type=MAX(5, 3, 10), Excel will tell you the biggest number is10. - Finding the smallest number: Just like finding the biggest number, if you want to find the smallest number, you use
=MIN(number1, number2, ...). For=MIN(5, 3, 10), Excel will say the smallest number is3. - Finding an average: If you want to know the average of some numbers, you use
=AVERAGE(number1, number2, ...). If you type=AVERAGE(5, 3, 10), Excel will calculate the average which is6.
These formulas help you do math quickly and accurately in Excel, which can be super helpful for school projects, budgeting, or just having fun with numbers!
How to use VLOOKUP in Excel?
Using VLOOKUP in Excel can seem a bit tricky at first, but once you get the hang of it, it’s a powerful tool for finding specific information in your spreadsheet. Let’s break down how to use VLOOKUP step-by-step, so it’s easy to understand.
What is VLOOKUP?
VLOOKUP stands for “Vertical Lookup.” It is used to search for a specific piece of information in one column and then bring back a piece of information from a different column in the same row.
Steps to Use VLOOKUP in Excel
Heres a simple guide on how to use VLOOKUP:
Step 1: Organize Your Data
Make sure your data is organized in columns, and the information you want to search by (like a name or ID number) is in the first column. VLOOKUP only looks to the right, so the value you want to find must be in the first column of your range.
Step 2: Click Where You Want the Result
Click on the cell where you want the answer from the VLOOKUP to appear.
Step 3: Start the VLOOKUP Formula
Type =VLOOKUP( to start your formula.
Step 4: Enter the Lookup Value
This is the information you know and want to search for in the spreadsheet. It could be a name, number, or anything that appears in the first column of your data range. For example, if you’re looking up someone’s phone number and you know their name, you would use their name as the lookup value. Type this value (or select the cell containing it), then add a comma.
Step 5: Define the Data Range
Select the range of columns where Excel should search for the lookup value. Make sure to include both the column containing the lookup value and the column with the information you want to retrieve. Then, add a comma.
Step 6: Specify the Column Number
Enter the number of the column (starting from the first column of your selected range) that contains the information you want to find. For instance, if the names are in the first column and phone numbers are in the second column, and you want the phone number, you type 2. Then, add a comma.
Step 7: Choose True or False
Type TRUE if it’s okay for Excel to find an approximate match or FALSE if you need an exact match. Usually, you will use FALSE to avoid incorrect or approximate results.
Step 8: Close the Formula
Type ) to close your formula and press Enter.
Example Formula
Imagine you have a list where:
- Column A has names
- Column B has phone numbers
You want to find the phone number for “John Doe.” Your VLOOKUP formula in Excel might look like this:
=VLOOKUP("John Doe", A2:B100, 2, FALSE)This formula tells Excel to look for “John Doe” in the range from A2 to B100, find it, and then return the value from the second column of that range (the phone number), ensuring it’s an exact match.
Practice Makes Perfect
Try using VLOOKUP with different data sets to get comfortable with changing parts of the formula. It’s a fantastic tool that can save you a lot of time once you know how to use it effectively!
How to Write Excel Formulas?
Writing Excel formulas is like giving the computer a set of instructions to calculate numbers or organize data for you. It’s a powerful tool that can help you do everything from simple math to complex data analysis. Here’s a beginner-friendly guide on how to write Excel formulas:
Step 1: Start with an Equal Sign
Every formula in Excel starts with an equal sign (=). This tells Excel that the cell will contain a formula, not just numbers or text.
Step 2: Enter the Formula
After the equal sign, you put in the formula. This could be as simple as adding, subtracting, or more complex operations like looking up data or calculating averages.
Basic Operations:
- Addition (
+): To add two or more numbers, use the+sign. For example,=3+2will give you5. - Subtraction (
-): To subtract one number from another, use the-sign. For example,=5-2will result in3. - Multiplication (
*): To multiply numbers, use the*sign. For example,=3*2will give you6. - Division (
/): To divide one number by another, use the/sign. For example,=6/2will result in3.
Step 3: Use Cell References
Instead of typing numbers directly into your formulas, you can refer to the cells where the numbers are. For example, if you have 5 in cell A1 and 3 in cell B1, and you want to add these, your formula in cell C1 would be =A1+B1. If you change the numbers in A1 or B1, C1 will automatically update to show the new total.
Step 4: Combine Functions and Cell References
Excel has many built-in functions like SUM, AVERAGE, and VLOOKUP that you can use to perform more complex tasks:
- SUM: Adds up all the numbers in a range of cells. For example,
=SUM(A1:A10)adds all the numbers from cellA1toA10. - AVERAGE: Calculates the average of numbers in a range of cells. For example,
=AVERAGE(A1:A10)finds the average of numbers fromA1toA10. - VLOOKUP: Looks for a value in the first column of a range and returns a value in the same row from a specified column. For example,
=VLOOKUP("apple", A1:B10, 2, FALSE)would look for the word “apple” in the first column ofA1:B10and return the corresponding value from the second column.
Step 5: Close the Formula
Once you’ve entered your operation or function, hit Enter. Excel will compute the formula and display the result in the cell.
Additional Tips
- Use Parentheses to Control the Order of Operations: Just like in math class, you can use parentheses to make sure Excel performs operations in the correct order. For example,
=(2+3)*5makes sure Excel adds2and3before multiplying the result by5. - Copying Formulas: If you drag the fill handle (a small square at the bottom-right corner of the cell) across other cells, Excel will copy the formula to these cells, adjusting the cell references automatically.
Writing formulas in Excel might seem tricky at first, but with a bit of practice, you’ll be able to handle tasks quickly and accurately, making your work or school projects much easier to manage!
Why Excel is Important?
Excel is really important for many reasons, especially when it comes to dealing with numbers and organizing information. Here’s why Excel is so useful:
1. Organizes Data
Excel makes it super easy to store and organize data. You can keep all sorts of information in neat rows and columns, and it’s easy to find and update whenever you need to.
2. Performs Calculations
One of the biggest reasons Excel is so useful is that it can do math very quickly. Whether it’s adding up your total expenses, calculating averages, or working out percentages, Excel can handle it all with simple formulas you input.
3. Analyzes Information
Excel is not just about doing basic math; it’s great for deeper analysis too. With features like charts, graphs, and pivot tables, you can take a bunch of numbers and turn them into visual diagrams that make it easier to understand trends and patterns.
4. Saves Time
Imagine having to add up hundreds of numbers or sort through data manually. It would take forever! Excel does these tasks in seconds, which saves a lot of time. This efficiency makes it a valuable tool in schools, businesses, and everyday life.
5. Helps in Decision Making
With all the data nicely organized and easy to analyze, making decisions becomes much easier. For example, a business can look at sales data in Excel to decide which products are selling well and which ones aren’t. This can help them decide what to focus on to improve their business.
6. Widely Used in Many Industries
Excel is used by almost every industry, from education where teachers track students’ grades, to finance where analysts forecast stock prices. Knowing Excel can really help you in almost any career.
7. Supports Complex Problem Solving
Excel can handle very complex mathematical problems, making it a great tool for engineers, statisticians, and researchers who need to solve complicated equations.
8. Improves Productivity
Because Excel can automate tasks like calculations and data analysis, it allows you to be more productive. Instead of spending time on tedious tasks, you can focus on more important things.
9. Easy to Learn and Use
While Excel has many advanced features, the basics are quite easy to learn. This makes it accessible to people of all ages and backgrounds. Schools often teach students how to use Excel because it’s a skill they’ll find useful no matter what they do later in life.
10. Integrates With Other Tools
Excel works well with other Microsoft Office applications like Word and PowerPoint. You can easily copy charts from Excel into a PowerPoint presentation or export data into a Word document. This integration makes it a versatile tool that enhances other software you might use.
How to Lock Cells in Excel?
Locking cells in Excel is useful if you want to prevent changes to specific parts of your spreadsheet while still allowing edits to other areas. Here’s a simple guide on how to lock cells in Excel:
Step 1: Open Your Excel Spreadsheet
Start by opening the Excel file where you want to lock certain cells.
Step 2: Select the Cells You Want to Lock
- If you want to lock specific cells, click on each cell while holding down the Ctrl key to select multiple cells.
- If you want to lock an entire row or column, click on the row number or column letter.
Step 3: Open the Format Cells Dialog
Right-click on the selected cells and choose ‘Format Cells’ from the context menu, or you can press Ctrl + 1 on your keyboard to open the Format Cells dialog box directly.
Step 4: Go to the Protection Tab
In the Format Cells dialog box, switch to the Protection tab.
Step 5: Check the ‘Locked’ Option
Ensure that the ‘Locked’ checkbox is checked. This is usually checked by default, but it’s good to make sure.
Step 6: Protect the Worksheet
Locking the cells won’t take effect until you protect the worksheet. To do this:
- Go to the ‘Review’ tab on the Ribbon.
- Click on ‘Protect Sheet.’
- Enter a password to protect the sheet (this step is optional but recommended if you want to ensure security).
- Click ‘OK.’
- If you set a password, you’ll be asked to re-enter it for verification. Enter the password again and click ‘OK.’
Step 7: Test the Lock
Try editing the locked cells. If everything was done correctly, Excel will display a message saying that the cell you are trying to change is protected.
To Unlock Cells:
If you need to unlock the cells later, just go back to the ‘Review’ tab, click on ‘Unprotect Sheet,’ and enter the password if you set one. Then you can go back and uncheck the ‘Locked’ option in the Format Cells dialog for any cells you want to unlock.
By following these steps, you can control which parts of your spreadsheet can be edited and which parts stay protected, helping you manage your data more securely and effectively.
How to Hide Formulas in Excel?
Hiding formulas in Excel is a handy technique, especially when you want to share a spreadsheet without revealing the underlying calculations. Here’s a simple guide on how to hide formulas in Excel:
Step 1: Open Your Excel Spreadsheet
Start by opening the Excel file where you have formulas that you want to hide.
Step 2: Select the Cells with Formulas
Click on the cells that contain the formulas you want to hide. If you want to hide formulas in multiple cells, hold down the Ctrl key and click on each cell. You can also click and drag to select a range of cells.
Step 3: Open the Format Cells Dialog
Right-click on the selected cells and choose ‘Format Cells’ from the context menu, or press Ctrl + 1 on your keyboard to open the Format Cells dialog box directly.
Step 4: Go to the Protection Tab
In the Format Cells dialog box, switch to the Protection tab.
Step 5: Check the ‘Hidden’ Option
In the Protection tab, check the box next to ‘Hidden’. This option works in conjunction with sheet protection to hide the formulas in the formula bar when a cell is selected.
Step 6: Protect the Worksheet
To make the hiding effective, you need to protect the worksheet:
- Go to the ‘Review’ tab on the Excel Ribbon.
- Click on ‘Protect Sheet.’
- In the Protect Sheet dialog, make sure that the ‘Protect worksheet and contents of locked cells’ option is checked.
- You can set a password to protect the sheet if you want added security (this step is optional). Enter a password, then click ‘OK.’
- If you entered a password, you’ll be asked to confirm it. Re-enter the password and click ‘OK’ again.
Step 7: Verify the Hiding
Now, when you or anyone else clicks on a cell with a hidden formula, the formula will not be visible in the formula bar, making it hidden from view.
To Unhide Formulas:
If you need to view or edit the formulas again, you’ll have to unprotect the sheet first:
- Go to the ‘Review’ tab, click on ‘Unprotect Sheet’ (enter the password if you set one).
- Then, follow the same steps to unformat the cells: Right-click on the cell, choose ‘Format Cells’, go to the Protection tab, and uncheck ‘Hidden’.
By hiding formulas, you can keep your work secure and maintain the professionalism of your reports or data sheets when sharing them with others.
Can you Lock Columns in Excel?
Yes, you can lock columns in Excel to prevent them from being edited or altered. This is useful if you have columns with formulas or important data that you want to protect while allowing changes to other parts of the worksheet. Here’s how to lock columns in Excel:
Step 1: Open Your Excel Spreadsheet
Open the Excel file where you want to lock the columns.
Step 2: Select the Columns to Lock
Click on the letter at the top of the column that you want to lock. If you want to lock multiple columns, click on the first column letter, hold down the Shift key, and click on the last column letter in the range you want to lock. This will select all those columns.
Step 3: Open the Format Cells Dialog
Right-click on the selected columns and choose ‘Format Cells’ from the context menu, or press Ctrl + 1 on your keyboard to open the Format Cells dialog box.
Step 4: Go to the Protection Tab
In the Format Cells dialog, go to the Protection tab.
Step 5: Check the ‘Locked’ Option
Make sure the ‘Locked’ checkbox is ticked. This option is typically checked by default, but it’s good to confirm.
Step 6: Protect the Worksheet
Locking the columns won’t be effective until you protect the worksheet:
- Go to the ‘Review’ tab on the Ribbon.
- Click on ‘Protect Sheet.’
- You can choose to set a password to protect the sheet (optional). Enter a password, then click ‘OK.’
- If you set a password, re-enter it when prompted for verification and click ‘OK’ again.
Step 7: Test the Lock
Try to make changes in the locked columns. If everything was done correctly, Excel will display a message telling you that the cell or chart you’re trying to change is protected and therefore read-only.
To Unlock Columns:
If you need to unlock the columns later, you can easily do so by:
- Going to the ‘Review’ tab.
- Clicking on ‘Unprotect Sheet’ (enter the password if you set one).
- Selecting the columns you want to unlock, right-click, and go to ‘Format Cells’.
- Uncheck the ‘Locked’ option under the Protection tab.
FAQs
What is the use of Excel?
Excel is a very effective tool for making sense of massive amounts of data. However, it also performs admirably when used to track virtually any type of information and perform basic calculations. The grid of cells holds the key to realizing all of that potential. Numbers, text, and formulas can all be found in cells.
Why is learning Excel Formulas important?
Excel formulas are excellent at reducing time spent on tasks. You can easily finish your work, which would have otherwise taken more than an hour to complete, in a very short amount of time with the aid of sophisticated Excel formulas. Excel formulas are pre-built components that can be used to perform a variety of tasks in an Excel sheet easily.
How do I use the Excel formula in the sheet?
You can follow these simple steps to use the Excel formulas easily on the sheets.
Choose a cell that is empty.
Next, type the function, followed by the equals sign =. For example:
use =SUM to obtain the total sum of more than one cell.
Add an open parenthesis at the beginning (.
Type a closing parenthesis after choosing the range of cells.
To view the result, you need to press Enter button.
