You may have heard how powerful Microsoft Excel is as a software, but you’ve only used it to enter some data and keep track of a few things. If you want to do more with Excel, the first step is to learn how to use formulas in Excel.
Formulas in Excel can be used to automate calculations, reduce errors, save time, create visualizations, and more. These formulas can be used to perform arithmetical (such as addition, subtraction, and multiplication), logical (such as AND, OR, and NOT), and statistical operations (such as the average of a set of numbers) on the data entered into the cells of the spreadsheet.
How to Use Formulas in Excel? (Examples Included)
Here is a short guide on how to use formulas in Excel, along with a few common examples.
- How to Begin Using Formulas
- How to find the SUM in Excel
- How to find the AVERAGE in Excel
- How to find the COUNT in Excel
- How to find the CEILING in Excel
Make sure to experiment with multiple data and validate using a calculator to ensure your results are accurate. If the formula is incorrect, Microsoft Excel will instantly warn you about it.
1] How to Begin Using Formulas?
To start using a formula – follow these steps:
- Click on a cell.
- Enter “=”. Every formula in Excel needs to begin with an “=”.
- Select the cell or cells from which you collect data for the formula.
- Enter the operator—for example, press “+” for addition.
- Press Enter to see the result appear in the cell where you entered the formula.
The picture below shows what a formula will look like.
When you type a formula in a cell, the formula can always be seen on the Formula bar at the top.
There are several such formulas (also called functions) that you can use in Microsoft Excel. The most common ones include SUM, AVERAGE, COUNT, CEILING, POWER, SUBTOTAL, FLOOR, and many more.
This guide will take you through SUM, AVERAGE, COUNT, and CEILING.
2] How to find the SUM in Excel?
How do you add several numbers in Excel?
Suppose you have the data below and want to find the revenue for all the months.
Follow these steps to use the SUM formula:
- Select the B12 cell (since this is where you need the total amount to appear).
- Enter “=” to begin your formula.
- Type “SUM” since that is the function you want to use.
- Add a bracket “(“. This is necessary to tell Excel to apply the SUM function to the following cells.
- Select B2 to B11 since those are the cells you need the data from.
Close the bracket “)”. - Press “Enter”.
- The sum of all the figures will appear in the B12 cell.
The SUM formula will look like this:
3] How to find the AVERAGE in Excel?
How do you find the average of several numbers in Excel?
Suppose you have the data below and want to find the average revenue.
Here are the steps to use the AVERAGE formula:
- Select the B12 cell (since this is where you need the total amount to appear).
- Enter “=” to begin your formula.
- Type “AVERAGE” since that is the function you want to use.
- Add a bracket “(“. This is necessary to tell Excel to apply the AVERAGE function to the following cells.
- Select B2 to B11 since those are the cells you need the data from.
Close the bracket “)”. - Press “Enter”.
- The average of all the figures will appear in the B12 cell.
The AVERAGE formula and result will look like this:
Related Reading: How to Transpose or Rotate Excel Cells (Row to Column and Vice-Versa)
4] How to find the COUNT in Excel?
How do you count the number of cells which have numeric data? This function cannot be used to find the count for cells with any other type of data apart from numeric data (blank cells, cells with letters, etc.).
Suppose you have the data below and want to find the number of months in which revenue was collected.
You can follow these steps to use the COUNT formula
- Select the B12 cell (since this is where you need the total amount to appear).
- Enter “=” to begin your formula.
- Type “COUNT” since that is the function you want to use.
- Add a bracket “(“. This is necessary to tell Excel to apply the COUNT function to the following cells.
- Select B2 to B11 since those are the cells from which you need the data.
- Close the bracket “)”.
- Press “Enter”.
- The count of all the cells will appear in the B12 cell.
The COUNT formula and result will look like this:
If you need to count cells with numeric data, text, and any other data format, you can use the function COUNTA. However, even COUNTA does not count any blank cells.
5] How to find the CEILING in Excel?
How do you get a round figure in Excel?
Suppose you have entered the price of a product as Rs. 23.65, but you need the nearest whole figure.
Here are the necessary steps to use the CEILING formula
- Select the cell where you need the round amount to appear.
- Enter “=” to begin your formula.
- Type “CEILING” since that is the function you want to use.
- Add a bracket “(“. This is necessary to tell Excel to apply the CEILING function to the following cells.
- Select the cell from which you need the data.
- Select the significance parameter. Since you need to round off to the nearest number, enter “1”. If you need to round off to the nearest multiple of 5, you would need to enter “5”, and so on.
- Close the bracket “)”.
- Press “Enter”.
- The rounded number will appear in the cell.
The CEILING formula and result will look like this:
Conclusion
Excel formulas can be used for arithmetic, logical, and statistical operations. You can use Excel formulas to get results quickly without manual checking and calculation. The most common Excel formulas include SUM, AVERAGE, COUNT, CEILING, POWER, SUBTOTAL, FLOOR, etc.
