Excel > Introduction

Functions and Formulas in Excel

How to work with functions and formulas in MS Excel?



What is Formula & Function in Excel?

A formula is a mathematical relationship or rule expressed in symbols that calculates the value of a cell.

A function is a pre-defined formula available in Excel that is ready to use.

A formula in excel starts with '=' inside a cell.

For example, to show the addition of B2 and B3 value in B4, we have written '=B3+B2' in B4 and hit Enter key. This gives the calculated value to B4.

Addition in excel

The result will be shown like this.

Addition result in excel

If we try to add two string value instead of two integer values, we will get error like shown in the image below

add two string


Operators in Formula

Excel understand the operators in formula as it is treated in normal mathematical calculations. Look at the image below.

In B5, we wrote '=B2+B3*2' and this resulted as 96 because first it multiplied 8 by 2 and then added to 80.

In B5, we wrote '=(C2+C3)*2' and this resulted as 176 because first it added C2 and C3 and then multiplied by 2.

operators in formula

Similarly, the precedene of division, additions and substraction work.

How to display a mathematical or expressive data as string in Excel?

Notice the data in B6 and C6 in above picture; normally when we copy-paste these strings in any cells, Excel tries to evaulate the values of those cells. But what if you just want to display them as it is? In that case, we need to prefix the formula or mathematical expression with single quote (') like displayed in the picture below.

formula as string

The result will be like 

operators in formula


How to Copy-Paste a formula to other relevant cells in Excel?

Let's assume a scenario as shown below where we want to copy the formula of D2 to D3, D4 so that we get total of all the records. 

There are two ways to copy the formula

  1. Go to D2 and Copy (either right click and Copy or press CTRL+C). Now select both D3 and D4 cells and Paste (right click and Paste or CTRL+V).
     
  2. Go to D2 and hold the bottom-right corner of the cell (the mouse cursor becomes + as shown below) and drag to D3 and D4.

copy formula in excel

The result would be like this

copy paste formula result

How to quickly know the sum & average of selected cells value in Excel?

It is very easy, just select all the cells you want to know the sum & average of and look at the right side of MS Excel status bar and you would be seeing them like picture below.

sum or average of selected cells in excel

Here

  1. AVERAGE is the average value of selected cells (D2, D3 & D4)
  2. COUNT is the number of cells selected
  3. SUM is the sum of selected cells value.

Quickies Wink

  • Can we add multiple cell value by separating with + symbol (eg. =A1+A2+A3+A4) ? - YES
  • Can we add, subtract, multiply and divide at once using cells reference (eg. =A1-A2*A3/A4) ? - YES
  • Can we directly add, subtract, multiply and divide numbers in cells (eg. =20+30-20*40/3)? - YES

How to select range of cells in MS Excel?

Range of cells is the selection of all the cells between starting and ending of the cell. To select range of cells, we separate two cells using colon (:).

For example:

If we want to select B2 to B4, we can write B2:B4. This will select B2, B3 & B4 cells. Generally, range is used within the inbuilt functions of MS Excel.

How to insert a function in Excel?

To insert an inbuilt function in MS Excel, select a cell and then press Fx icon as shown in the picture below (In this case, function will be inserted into D5 cell).

insert a function in excel

A dialog box opens with the list of functions available in MS Excel and by default 'Most Recently Used' function apears in the list. We can change the categroy dropdown to choose a function available in that category.

For this demo, let's use AVERAGE function from the list (In case this function is not available in the default list of your screen, simply use the Search for a function box to search it).

insert a function

After selecting AVERAGE from the list of functions, click OK. This opens another dialog box that ask us the argument to pass into this function.

Average baseically returns the average of its argument. So either we can write all cells one by one in Number1, Number2 etc. boxes or we can specify them by selecting the first cell (D2) and then start dragging (the screen changes as below). Keep dragging till you reach to your target cell (D4). Excel automatically surrounds selected cells with dashed border to notify that those cells have been selected.

select range in ms excel

We will get the range of cells written in Number1 text box as displayed in below picture. The result also appears on the dialog box. Now click OK.

Select range of cells in function

and here is the result.

average in excel

Quickies Wink

  1. Can we write the function directly into the D5 cell? - YES
  2. If we can directly write functions into the Cell, why excel has given long steps like above? - All users are not as intelligent as you are Tongue Out...... OK, Actually many functions have more than one arguments and it might be difficult for many to remember those arguments. 


 Views: 652 | Post Order: 3




Write for us