Performing Basic Calculations
To process data by performing basic calculations in a worksheet, we can use formulas or some functions, such as sum and if count-if, count ifs while performing calculations, you may need to refer to a single cell or multiple cells using the cell referencing feature. You can also assign the meaningful name to a cell or range of cells.
The formulas and functions can be applied to the data stored at different locations in the worksheet or in the different worksheets. To refer to this data from different locations, in Excel gives us a feature known as cell references. A reference is the address of a cell or a range of cells in a worksheet. By using this feature called cell referencing, you can refer to the data of various cells in a formula. You can also refer to the values in the worksheets of the same workbooks or of some other worksheets. Reference to the other workbooks are known as links:
Some most commonly used referencing types in the Excel are:
- Relative Referencing
- Absolute Referencing
- Mixed Referencing
- 3-D Referencing
Performing Basic Calculations -Relative Referencing:-
In this type of reference, when the position of the cell containing the formula changes, the reference also changes, For Example, You have C3, with a formula that reference A3 and B3. When you can copy the formula C3 to C4, Excel updates the cells from A3 to A4 and B3 to B4, inside the formula, respectively as shown in the following figure.
Performing Basic Calculations -Absolute Referencing:-
In absolute referencing, the cell reference contained in the formula does not change even if you copy the formulas in the other locations, you can use absolute referencing in a formula by prefixing the column name and the row number by the dollar ($) sign. For example, to refer to the cells A4 and B4, absolutely in C4, specify the cell names as $A$3 and $B$3. Thereafter, copy the formula from C3 to C4. The reference used in the formula used in the formula does not get changed, as shown in the following figure.
Performing Basic Calculations -Mixed Referencing:-
With the help of this kind of referencing we can use both referencing, relative referencing as well as absolute referencing, it has either an absolute column and a relative row or an absolute row and a relative column, for example, you have the cell C3, with the formula that references A3 relatively and B3 absolutely.
Now when you copy the formula from C3 to C4, the relative reference will change but the absolute will remain the same, as shown in the following figure,
Performing Basic Calculations -3-D Referencing:-
This kind of reference, refers to the same cell or range of cells in multiple worksheets. To understand 3-D reference better, consider a scenario where you have created a workbook that contains worksheets for sales of every month. The B4 cell contains the total sales of a month in each worksheet.
Now, you want to find the total sales first quarter in the new worksheet, Quarter_1, For this, you need all the total sales of the April, May, and June, You can use 3-D referencing to add all the cell references of three sheets. To refer the cell reference of three sheets, you need to click the April worksheet tab, hold then click the June worksheet tab.