Microsoft Excel is one of the popular software used for data analysis, and one key feature is its ability to manipulate data through various formulas. Excel is widely used in businesses and organizations to create spreadsheets, perform calculations, track budgets, and many more functions. In this article, we will go through some common Excel formulas and how to use them.
How to Create Formulas in Excel
Before we dive into the different formulas, let us first learn how to create formulas in Excel. There are two methods of creating formulas in Excel, using the formula bar or directly from the cell.
Method 1: Using the Formula Bar
To use the formula bar, click on the cell where you want the result to appear. Next, type the equal (=) sign, followed by the formula. After typing the formula, press enter, and the result will appear.
For example, if we want to add two numbers, 10 and 20 in Excel, we would click on the cell where we want the result to appear, type =10+20, and press enter. The result will appear in the cell.
Method 2: Directly from the Cell
To create a formula directly from the cell, click on the cell where you want the result to appear. Next, type the equal sign, followed by the formula, including the cell references. After typing the formula, press enter, and the result will appear.
For example, if we have two numbers in cells A1 and A2 and we want to add them, we would click on the cell where we want the result to appear, type =A1+A2, and press enter. The result will appear in the cell.
Common Formulas in Excel
Now that we have learned how to create formulas in Excel, let us go through some common formulas used in Excel:
SUM
The SUM formula is used to add a range of numbers. To use the SUM formula, click on the cell where you want the result to appear and type =SUM( first cell : last cell ). The first cell refers to the first cell in the range, and the last cell refers to the last cell in the range. After typing the formula, press enter, and the result will appear.
AVERAGE
The AVERAGE formula is used to find the average of a range of numbers. To use the AVERAGE formula, click on the cell where you want the result to appear and type =AVERAGE( first cell : last cell ). The first cell refers to the first cell in the range, and the last cell refers to the last cell in the range. After typing the formula, press enter, and the result will appear.
MAX
The MAX formula is used to find the highest value in a range of numbers. To use the MAX formula, click on the cell where you want the result to appear and type =MAX( first cell : last cell ). The first cell refers to the first cell in the range, and the last cell refers to the last cell in the range. After typing the formula, press enter, and the result will appear.
MIN
The MIN formula is used to find the lowest value in a range of numbers. To use the MIN formula, click on the cell where you want the result to appear and type =MIN( first cell : last cell ). The first cell refers to the first cell in the range, and the last cell refers to the last cell in the range. After typing the formula, press enter, and the result will appear.
COUNT
The COUNT formula is used to count the number of cells in a range that contain numbers. To use the COUNT formula, click on the cell where you want the result to appear and type =COUNT( first cell : last cell ). The first cell refers to the first cell in the range, and the last cell refers to the last cell in the range. After typing the formula, press enter, and the result will appear.
How to Use Conditional Formulas in Excel
Conditional formulas are used to perform calculations or display values based on specific conditions. Let us go through some of the common conditional formulas:
IF
The IF formula is used to display different values based on a condition. To use the IF formula, click on the cell where you want the result to appear and type =IF( condition, value if true, value if false ). The condition is the expression that you want to test, the value if true is the value that must appear if the condition is true, and the value if false is the value that must appear if the condition is false. After typing the formula, press enter, and the result will appear.
COUNTIF
The COUNTIF formula is used to count the number of cells in a range that meet a specific condition. To use the COUNTIF formula, click on the cell where you want the result to appear and type =COUNTIF( range, criteria ). The range refers to the cells that you want to count, and the criteria is the condition that you want to test. After typing the formula, press enter, and the result will appear.
SUMIF
The SUMIF formula is used to add the values in a range that meet a specific condition. To use the SUMIF formula, click on the cell where you want the result to appear and type =SUMIF( range, criteria, sum_range ). The range refers to the cells that you want to test, the criteria is the condition that you want to test, and the sum_range is the cells that you want to sum. After typing the formula, press enter, and the result will appear.
Tips and Tricks in Excel Formulas
Here are some tips and tricks to help you use Excel formulas:
Use Absolute Cell References
When creating a formula in Excel, cell references are relative by default, which means that Excel adjusts the reference based on the position of the formula. To keep a cell reference constant, use an absolute cell reference by adding a dollar sign ($) before the column and row reference. For example, $A$1 would be an absolute reference to cell A1.
Use Named Ranges
Named ranges are a helpful way to refer to a specific range of cells in a formula. To create a named range, select the range of cells, click on the Formulas tab, and click on Define Name. After giving the range a name, you can refer to the range in a formula instead of using cell references.
Use Autofill
Autofill is a feature in Excel that allows you to quickly fill in a series of values or formulas in a column or row. To use Autofill, select the cell or cells that contain the values or formulas that you want to copy, hover your cursor over the bottom-right corner of the cell until it turns into a small black cross, and then drag the cell to the desired location.
Use Function Arguments
When using a formula with arguments, Excel provides a description of each argument and its purpose. To see the arguments of a function, type the function in a cell and open the formula bar. You will see the function name with a left parenthesis followed by a list of the arguments and a right parenthesis. Click on each argument to see the description and help text.
FAQs
Q1: How can I learn more about Excel formulas?
If you want to learn more about Excel formulas, there are many online resources that you can use, including tutorials, YouTube videos, and websites such as Excel Easy and Microsoft support. You can also take online courses and attend workshops to learn more about Excel formulas and advanced features.
Q2: How can I troubleshoot Excel formulas?
If you encounter issues with Excel formulas, you can use the Formula Auditing tools to identify and fix errors. The Formula Auditing tools allow you to evaluate formulas, find errors, and trace cell references. To access the Formula Auditing tools, click on the Formulas tab and click on the Formula Auditing group.
Conclusion
Excel formulas are a powerful tool that can help you perform calculations, manipulate data, and analyze information. By using some of the common formulas and following some tips and tricks, you can unlock the full potential of Excel and streamline your workflow. Keep practicing and exploring new functions to become an Excel expert.
