w3resource logo


Excel - 2013 Tutorial

Functions Basic - Excel 2013

Secondary Nav

Introduction

A formula is an expression which calculates the value of one or more cell(s). The functions are also predefined formulas and it can be used independently in Excel and performs calculations using specific values in a particular order. A simple formula can be created using add, subtract, multiply or divide values in your worksheet. Formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs.

Example :

Cell B4 below contains a formula which adds the value of cell B1, B2 and B3.

formula1

The example below shows that the cell B4 contains the SUM function which calculates the sum of the range B1 to B3.

sum

How Enter a Formula ?

To enter a formula, execute the following steps.

Select a cell.

If you want to enter a formula, type an equal sign (=).

Example : Type the formula =B4 B1+B2+B3 into the cell B4.

formula1

Instead of typing B1, B2, and B3, you can type an '=' sign first therefore simply select click or select the cell you want to add and type '+' operator between two selection.

Change the value of cell B1 to 8 and see the change of the result.

change-value

How Edit a Formula ?

When you select a cell which contains a formula, Excel shows the value or formula of the cell in the formula bar.

edit-formula

To edit a formula, click in the formula bar and change the formula.

edit-formula1

Press Enter, and the change the of the output.

edit-formula-output

Operator Precedence

Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that part will be calculated first. It then performs multiplication or division calculations. Once this is complete, Excel will add and subtract the remainder of the formula. Here is the example below.

operator

In the above formula excel first, performs multiplication (B1 * B2). Next, Excel adds the value of cell B3 to this result.

Here is another example below.

operator1

In the above example, excel first calculates the part in parentheses (B2+B3). Next, it multiplies this result by the value of cell B1.

Copy/Paste a Formula

When you copy a formula, Excel automatically changes the cell references for each new cell the formula is copied to. Here are steps below.

operator2

Select cell B4, right-click, and then click Copy (or press CTRL + c) as shown below.

copy

Next, select cell the C4 and right-click, and then click Paste under 'Paste Options:' (or press CTRL + v).

paste

You can also drag the formula horizontally or vertically according to your data in the sheet. Here in the picture below shows that select cell B4, click on the lower right corner of cell B4 and drag it across to cell C4.

paste1

Result. The formula in cell C4 references the values in column C.

output

Insert a Function

Every function has a structure. Excel includes many common functions that can be useful for quickly finding the sum, average,count, maximum value, and minimum value for a range of cells.

A function must have a syntax which indicates how to write a specific function. The basic syntax for a function is an equals sign (=), then the function name (SUM, for example), and one or more arguments within parentheses. Arguments contain the information you want to calculate. Suppose, SUM(B1:B4). This function adds the values in cells B1,B2, B3, and B4. Here is the picture below shows the parts of the function.

output

Arguments :

Arguments may be a single cell or a range of cells and must be enclosed in parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =SUM(B1:B3) would calculate the sum of the values in the cell range B1:B3. This function contains only one argument. Here is the picture below.

output

Multiple arguments can be used but it must be separated by a comma. For example, the function =SUM(B1:B3, D1:D2,F1) will add the values of all the cells in the three arguments. Here is the picture below.

output

Here are the steps below describes how to insert a function.

Select a cell D2 then click the Insert Function button.

function

The 'Insert Function' dialog box appears.

function-dialog-box

Search for a function or select a function from a category. For example, choose AVERAGE from the Statistical category.

Then click ok

The 'Function Arguments' dialog box appears.

function-arguments

Click Ok.

Output : Average of B2 and C2 is 5.

average

Another way to write the function is to click D2 and simply type '=AVERAGE(B2:C2)' or Click B2 and drag upto C2, then press Enter key. Here is the picture below.

output1

 



Join our Question Answer community to learn and share your programming knowledge.