MS Advance Excel Course

A cell reference refers to the name or address of a specific cell or range of cells within the spreadsheet which is a combination of a column letter and a row number. Each worksheet in Excel consists of several cells. Each cell has a specific cell reference which helps the users to find easily address the desired cell (s) within the functions. The address of a cell is defined by the letter (A to XFD) of the column and each number (1 to 1048576) represents a row in excel spreadsheet. For example, A1 refers to the cell at the intersection of column A and row 1; B2 here B refers to the second cell in column And 1 refer to the first row. A cell reference refers to a cell or a range of cells on a worksheet and can be used in MS Excel Function so that Microsoft Office Excel can find the values or data that you want.   What are the 3 types of cell references in Excel? Now there are three kinds of cell references in excel that you can use in MS Excel: 1 Relative Cell References. 2 Absolute Cell References. 3 Mixed Cell References. 1 Relative Cell Reference Relative cell references are useful when you have to create a formula for a range of cells and the formula needs to refer to a relative cell reference.   Now I want to calculate the total for each item, we need to multiply the price of each item with the quantity of that item. For the first item, the formula in cell D2 you have to type B2* C2 (as shown below) After it drag all item as shown below

Mixed cell references are more-tricky than the absolute cell references if we compare. There can be two types of mixed cell references: 1 The row is locked while the column changes when the formula is copied or dragged. 2 The column is locked while the row changes when the formula is copied or dragged. Below is a excel sheet where you need to calculate the three tiers of commission based on the percentage value in cell E2, F2, and G2.   Now I want to calculate all these commissions with just one formula by helping mix reference in excel. Enter the below formula in cell E4 and copy / drag for all cells. =$B4*$C4*E$2 The above formula uses both kinds of mixed cell references (one where the row is locked and one where the column is locked).   How to Change the Reference from Relative to Absolute (or Mixed)? To change the cell reference from relative to absolute, mix reference you need to add the dollar sign before the column notation and the row number. faster way to do this is by using the keyboard shortcut – F4. For example, A1 is a relative cell reference, and it would become absolute when you make it $A$1. Here is what happens when you select the reference and press the F4 key. Press F4 key once: The cell reference changes from A1 to $A$1 (becomes ‘absolute’). Press F4 key two times: The cell reference changes from A1 to A$1 (changes to mixed reference where the row is locked). Press F4 key three times: The cell reference changes from A1 to $A1 (changes to mixed reference where the column is locked). Press F4 key four times: The cell reference becomes A1 again.

Absolute cell references are useful when you don’t want the cell reference to change as you copy or drag formulas. This reference used when you have a fixed value that you need to use in the formula (such as tax rate, commission rate, percentage number of months, etc.) absolute cell references don’t change when you copy or drag the formula to other cells. For example, suppose you have the data set as shown below where you have to calculate the commission for each item’s total sales. The commission is 10% and is listed in cell E2.   To get the commission amount for each item sale, use the following formula in cell E4 and copy for all cells: =D4*$E$2 Note that there are two dollar signs ($) in the cell reference that has the commission – $E$2.   What does the Dollar ($) sign do? A dollar symbol, when we add in front of the row and column number, makes it absolute it means absolute cell references don’t change when you copy or drag the formula to other cells.

There is no spacefic function called Nested If function in MS Excel, but whenever we use multiple IF function together within IF in MS-Excel, it is called Nested If function. We use Nested If function in Excel when we need to apply more than one condition to analyze the data of excel, and whatever condition is true in these multiple conditions, we would have returned the required result. Is Nested If function. Syntax =IF(logical_test,result,IF(logical_test,result,IF(logical_test,result))) Example: For example, we have taken a data here where some conditions have been given. Based on these conditions, we have to issue bonus to all the employees.   Nested If Function In Excel Formula =IF(C3>D3,”8%”,IF(C3=D3,”3%”,IF(C3<D3,”No Incentive”)))

The MAX function in Excel returns the largest numeric value in a range of cells. you can easily extract the largest value / number out of thousands and millions of values ​​typed in the sheet of MS EXCEL. STEP 1 – First of all, open that MS EXCEL sheet in your computer, inside which you have to extract the biggest value out of all the values. STEP 2 – After opening the sheet of Ms Excel, you select a cell with your mouse inside the sheet and then apply the formula of Max. First put the sign of = and then type Max (after that select the area / cells in the table from which the maximum value is to be extracted and then press the enter button). Example =MAX(A2:A9) and then press the enter button of the keyboard.   As soon as you press the enter button, the maximum value will appear in front of you in the cell where you have applied the Max formula. As can be seen in the above image. #2 Method Max Function We can also use the Max function in such a way as can be seen in the image below.

When we type many values ​​on the sheet of MS EXCEL and there are small and big values ​​in it, then we have to extract the smallest value from all these values, then in this case you can use the Min formula. Using the MIN formula, you can easily extract the smallest value / number / number out of thousands and millions of values ​​typed in the sheet of MS EXCEL. STEP 2 – After opening the sheet of MS Excel, you select a cell with your mouse inside the sheet and then apply the formula of Min. First put the sign of = and then type Min (after that select the area / cells in the table from which the smallest value is to be extracted and then press the enter button). As shown in the image below. Example = MIN(A2:A8) and then press the enter button of the keyboard. As soon as you press the enter button, the smallest value will appear in front of you inside the use cell where the formula will be applied. #2 Method Min Function We can also use the Min function in such a way as can be seen in the image below. Example: =MIN(24,76,90,76,54)    

In this tutorial, you ’ll learn five different ways how to calculate percentage in Excel. All of them are correct and can be used interchangeably to get the same result. There are no specific functions for calculating percentage In MS Excel so you have to use the skills you were taught in your math’s class at school!   Finding a percentage of a value in this example you have a initial value and you have to find out a specific percentage of initial amount for example shown as below image. Example 1   Method #1 How to Calculate Percentage Increase in Excel this is another way how to calculate interest increase in excel, here we need to increase these values that appear in the column B with 10%, and we need show the result in the column C. Percentage Trick 10% = 10% 20% = 20% 50% = 50%   How to calculate pay rise with category wise behalf on old salary structure In this example we will calculate the rising value of a initial value , for example a company rise their employee salary in percentage behalf of old salary so how to calculate raised value. A company is about to give its staff a pay rise. The wages department need to calculate the increases. Staff on different grades get different pay rises.   Example 2 A company is about to give its staff a pay rise. The wages department need to calculate the new salary including the % increase. Staff on different grades get different pay rises.   Finding a percentage increased value This is another example of percentage calculation and in this example we will learn how to find a percentage value behalf of initial value.   Finding one value as percentage of another in this example suppose you have two value and you have required to find out the percentage one value of another value.  

The OR function in Microsoft Excel comes under the logical function category. The OR function is also used to test multiple conditions at a time just like the AND Function. This function also gives the result in True or False. If all the conditions are satisfied in the IF function, and if any one of the multiple conditions given in the OR function is true, then it returns TRUE. And it will return FALSE only when all the conditions are false. Syntax- =OR(logical1,[logical2],[logical3],[logical4],……) Arguments- logic 1 – to test the first condition or logic value. logic 2 – to test the second condition value. Example: If Function will return True when it is greater than 70 in Test 1 and either it is equal to 100 in Test 2 then only True will come otherwise False will come.    

IF function in Excel This is a very useful function. IF function is used to check the condition. In this, first of all you have to give a condition. And then if that condition is True then the If Function will return one value and if it is False then it will return another value which we have to define while applying the formula.   Syntax =IF (logical_test, [value_if_true], [value_if_false]) Conditional Operators   How To Use IF Function In Excel? As we just read that IF Function in Excel is used in any type of condition, like we are creating a result in excel and we have to find which students are passed and which students are failed. Using If Function In Excel.   Condition : If you get more than 35% then pass or fail   Formula : =IF(K2>=35%,”pass”,”fail”)   As you can see in the above image, here we have calculated the result of the students, here is our condition => 35% students whose marks will be equal to or more than 35% will pass and whose marks will be more than 35% If it is less, it will fail. After typing this formula, enter it.   If we see here, the students whose percentage value was less than 35%, those students failed as shown in the image above. In the same way, if the division of the students is to be declared, then like – if more than 60% then first division, if more than 45% and less than 60% then second division and more than 35% and less than 45% then third division. Can see in the image below.   #2 Second Example Of If Function In Excel In this method, we have derived the division from students by using If Function.   type formula Function =IF(K2>=60%,”1st division”,IF(K2>=45%,”2nd division”,”3rd division”)) Similarly take out the rest   #3 Third Example Of If Function In Excel another example of IF Function Function =IF(A2>=90,”Unsafe”,IF(A2>=60,”Risky”,”Safe”))