Excel basic functions and formulas
1. Create a formula using absolute cell references
Absolute references are often used in formulas that calculate percentages or
share of total.
Select cell C6.
Type = in cell C6.
Excel will enter a formula in cell C6.
Click cell B6.
Excel adds B6 as an argument in the formula.
Type / after the B6 cell reference.
Click cell B18.
The formula in cell C6 now reads =B6/B18, which means Excel will divide the
value in cell B6 by the value in cell B18.
Press the F4 key.
Excel changes the relative cell reference B18 to absolute cell reference $B$18. If
you copy this formula into the cell below (C7), the reference to cell B18 will
remain static, while the cell reference in the numerator will change to B7.
Press ENTER.
2. Create a formula using arithmetic operators.
You can use arithmetic operators in Excel formulas to automatically perform
mathematical operations including addition, subtraction, multiplication, and
division.
Select cell G4.
You will create a formula to calculate the Projected 2021 Salary, less Healthcare,
for each employee. As with all formulas, you begin by typing an = (equal sign) in
the cell.
Type = (equal sign) in cell G4 to start the formula
The formula will use arithmetic operators to complete the calculation.
Click cell C4.
The value in cell C4 represents the employee's current salary.
Type + (plus) in cell G4.
Next, you add the amount of the employee's raise, in dollars…
Click cell C4.
…which you will calculate by multiplying the employee's current salary in cell
C4…
Type * in cell G4.
... by the raise percentage in cell E4.
Click cell E4.
Type - (minus) in cell G4.
Next, you will subtract the annual healthcare deduction shown in cell F4 from the
projected new salary result.
Click cell F4.
On the formula bar, click the Enter button.
Excel performs the calculations as specified and returns the result in cell G4.
,3. Create a formula using order of operations
The order of operations specifies when arithmetic operations are performed in a
formula. You can override this order by enclosing an operation in parentheses.
Select cell B14.
Type =( in cell B14.
You will enter a formula to calculate projected annual expenses. This formula will
add the monthly expenses in cells B9, B10, and B11, and then multiply that total
by 12 to calculate the annual total.
Click cell B9.
You will start the formula by adding the values in cells B9, B10, and B11.
Type + (plus) in cell B14.
Click cell B10.
Type + (plus) in cell B14.
Click cell B11.
Type ) in cell B14.
By enclosing the first part of the formula in parentheses, you ensure that Excel
will add these values together before performing any other calculations.
Type *12 in cell B14.
Excel will now multiply the sum of values in cells B9, B10, and B11 by 12.
On the formula bar, click the Enter button.
Excel performs the calculations as specified and returns the result in cell B14.
Note that if you hadn't enclosed those values in parentheses, Excel would have
multiplied the value of cell B11 by 12 before adding it to the other values in the
formula.
4. Create a formula using the AVERAGE function
Use the AVERAGE function to quickly calculate average scores, salaries, sales,
grades, and more.
Select cell B18.
Type =AV in cell B18.
The Formula AutoComplete list displays functions whose names are approximate
alphabetic matches to the typed letters.
In the Formula AutoComplete list, double-click AVERAGE.
Excel inserts the AVERAGE function into the formula.
Position the mouse pointer over cell B5. Press and hold the left mouse button as
you drag the pointer downward until it is over cell B16, and then release the
mouse button.
Excel enters the range B5:B16 into the formula as the AVERAGE function's
argument.
Type ) in cell B18 to complete the formula.
Press ENTER.
Excel calculates the average monthly sales and returns the result in cell B18.
5. Create a formula using the COUNT function
When counting the number of entries in a range, you might want to exclude those
that are blank or non-numeric. For example, to accurately count the number of
students that took a test, use the COUNT function to tally only those students
, with a numeric value in the Score column; blank entries will not be counted.
Select cell D19.
Type =COUNT( in cell D19.
As you type, Excel displays ScreenTips with prompts for function arguments
Position the mouse pointer over cell D5. Press and hold the left mouse button as
you drag the pointer downward until it is over cell D18, and then release the
mouse button.
You will count the number of bonuses paid by counting the number of entries in
the Bonus Paid column. Each value in the range D5:D18 corresponds to a
franchise owner.
Type ) to complete the formula.
Press ENTER.
Excel counts a total of 14 entries in the column and returns the result in cell D19.
6. Create a formula using the IF function
Use the IF function to sort through a list of values to determine which ones meet
a certain criterion, such as achieving a minimum score or grade. Assign one
result to values that meet your criteria (such as YES), and another for those that
do not (such as NO).
Select cell F4.
Click the Formulas tab on the ribbon.
In the Function Library group, click the Insert Function button.
Excel inserts an = (equal sign) in the formula bar, indicating you will be inserting
a formula in cell F4. The Insert Function dialog box opens.
Click the Or select a category arrow.
Excel displays a list of function categories.
Click Logical.
Excel displays a list of logical functions in the Select a function box.
Click IF.
Click OK.
The Function Arguments dialog box opens with boxes for entering function
arguments. The insertion point is in the Logical_test box, which will contain the
argument that represents the condition the function will check for.
Type E4>100000 in the Logical_test box.
The IF function will test for a value greater than 100,000 in cell E4.
Press TAB.
The insertion point moves to the Value_if_true box.
Type YES in the Value_if_true box.
If the value in cell E4 is greater than 100,000, the IF function will return a YES
value in cell F4. Excel automatically inserts quotation marks ("") around text
values, but the quotation marks won't appear in the value returned by the
formula.
Press TAB.
Type NO in the Value_if_false box.
If the value in cell E4 is less than or equal to 100,000, the IF function will return a
NO value in cell F4.
Click OK.
Excel returns a result of YES in cell F4 and the completed formula for the IF
function appears in the formula bar.
1. Create a formula using absolute cell references
Absolute references are often used in formulas that calculate percentages or
share of total.
Select cell C6.
Type = in cell C6.
Excel will enter a formula in cell C6.
Click cell B6.
Excel adds B6 as an argument in the formula.
Type / after the B6 cell reference.
Click cell B18.
The formula in cell C6 now reads =B6/B18, which means Excel will divide the
value in cell B6 by the value in cell B18.
Press the F4 key.
Excel changes the relative cell reference B18 to absolute cell reference $B$18. If
you copy this formula into the cell below (C7), the reference to cell B18 will
remain static, while the cell reference in the numerator will change to B7.
Press ENTER.
2. Create a formula using arithmetic operators.
You can use arithmetic operators in Excel formulas to automatically perform
mathematical operations including addition, subtraction, multiplication, and
division.
Select cell G4.
You will create a formula to calculate the Projected 2021 Salary, less Healthcare,
for each employee. As with all formulas, you begin by typing an = (equal sign) in
the cell.
Type = (equal sign) in cell G4 to start the formula
The formula will use arithmetic operators to complete the calculation.
Click cell C4.
The value in cell C4 represents the employee's current salary.
Type + (plus) in cell G4.
Next, you add the amount of the employee's raise, in dollars…
Click cell C4.
…which you will calculate by multiplying the employee's current salary in cell
C4…
Type * in cell G4.
... by the raise percentage in cell E4.
Click cell E4.
Type - (minus) in cell G4.
Next, you will subtract the annual healthcare deduction shown in cell F4 from the
projected new salary result.
Click cell F4.
On the formula bar, click the Enter button.
Excel performs the calculations as specified and returns the result in cell G4.
,3. Create a formula using order of operations
The order of operations specifies when arithmetic operations are performed in a
formula. You can override this order by enclosing an operation in parentheses.
Select cell B14.
Type =( in cell B14.
You will enter a formula to calculate projected annual expenses. This formula will
add the monthly expenses in cells B9, B10, and B11, and then multiply that total
by 12 to calculate the annual total.
Click cell B9.
You will start the formula by adding the values in cells B9, B10, and B11.
Type + (plus) in cell B14.
Click cell B10.
Type + (plus) in cell B14.
Click cell B11.
Type ) in cell B14.
By enclosing the first part of the formula in parentheses, you ensure that Excel
will add these values together before performing any other calculations.
Type *12 in cell B14.
Excel will now multiply the sum of values in cells B9, B10, and B11 by 12.
On the formula bar, click the Enter button.
Excel performs the calculations as specified and returns the result in cell B14.
Note that if you hadn't enclosed those values in parentheses, Excel would have
multiplied the value of cell B11 by 12 before adding it to the other values in the
formula.
4. Create a formula using the AVERAGE function
Use the AVERAGE function to quickly calculate average scores, salaries, sales,
grades, and more.
Select cell B18.
Type =AV in cell B18.
The Formula AutoComplete list displays functions whose names are approximate
alphabetic matches to the typed letters.
In the Formula AutoComplete list, double-click AVERAGE.
Excel inserts the AVERAGE function into the formula.
Position the mouse pointer over cell B5. Press and hold the left mouse button as
you drag the pointer downward until it is over cell B16, and then release the
mouse button.
Excel enters the range B5:B16 into the formula as the AVERAGE function's
argument.
Type ) in cell B18 to complete the formula.
Press ENTER.
Excel calculates the average monthly sales and returns the result in cell B18.
5. Create a formula using the COUNT function
When counting the number of entries in a range, you might want to exclude those
that are blank or non-numeric. For example, to accurately count the number of
students that took a test, use the COUNT function to tally only those students
, with a numeric value in the Score column; blank entries will not be counted.
Select cell D19.
Type =COUNT( in cell D19.
As you type, Excel displays ScreenTips with prompts for function arguments
Position the mouse pointer over cell D5. Press and hold the left mouse button as
you drag the pointer downward until it is over cell D18, and then release the
mouse button.
You will count the number of bonuses paid by counting the number of entries in
the Bonus Paid column. Each value in the range D5:D18 corresponds to a
franchise owner.
Type ) to complete the formula.
Press ENTER.
Excel counts a total of 14 entries in the column and returns the result in cell D19.
6. Create a formula using the IF function
Use the IF function to sort through a list of values to determine which ones meet
a certain criterion, such as achieving a minimum score or grade. Assign one
result to values that meet your criteria (such as YES), and another for those that
do not (such as NO).
Select cell F4.
Click the Formulas tab on the ribbon.
In the Function Library group, click the Insert Function button.
Excel inserts an = (equal sign) in the formula bar, indicating you will be inserting
a formula in cell F4. The Insert Function dialog box opens.
Click the Or select a category arrow.
Excel displays a list of function categories.
Click Logical.
Excel displays a list of logical functions in the Select a function box.
Click IF.
Click OK.
The Function Arguments dialog box opens with boxes for entering function
arguments. The insertion point is in the Logical_test box, which will contain the
argument that represents the condition the function will check for.
Type E4>100000 in the Logical_test box.
The IF function will test for a value greater than 100,000 in cell E4.
Press TAB.
The insertion point moves to the Value_if_true box.
Type YES in the Value_if_true box.
If the value in cell E4 is greater than 100,000, the IF function will return a YES
value in cell F4. Excel automatically inserts quotation marks ("") around text
values, but the quotation marks won't appear in the value returned by the
formula.
Press TAB.
Type NO in the Value_if_false box.
If the value in cell E4 is less than or equal to 100,000, the IF function will return a
NO value in cell F4.
Click OK.
Excel returns a result of YES in cell F4 and the completed formula for the IF
function appears in the formula bar.
