CELL REFERENCE AND ITS TYPES
The cell address that we use in the formula is known as the cell reference. For example, A1 where A denotes the Column name and 1 denotes the Row number. With references, you can use the data from different parts of the worksheet or different sheets of the workbook. There are three types of cell references:
RELATIVE REFERENCE
When you create a formula, references to a cell or ranges are usually based on the position relative to the cell. when you copy or move the formula to other cells, the reference to the cell automatically gets changed. For example, let’s say, the formula in A3 is = A1+A2. When you copy the formula from A3 to B3, Excel automatically changes the references to match the location of cells, i.e., B1 in place of A1 and B2 in place of A2. Let us understand it practically:
Select the cells B8, in which formula =SUM(B2:B7) is written. Click on the copy button.
Select cell C8 and click on the paste button.
Observe that the cell reference in C8 changes automatically from B2:B7 to C2:C7.
ABSOLUTE REFERENCE
An absolute reference is used when we do not want to change the address of the cells while copying the formula to another cell. To use absolute reference in a cell, you need to add the dollar ($) sign before the column and row number.
For example = $A$1+$A$2.
Let us calculate the incentive at the rate of 10 % on monthly sales:
Type 10% in cell E11.
Click on cell D2.
Type = B2*$E$11 and press the enter key.
Cell D2 displays the value 100. The first cell address has no dollar sign, so it is relative reference and the second cell address is fully absolute. Therefore when the cell content is copied, the first one will change the reference in the new location but the second will remain the same i.e.,$E$11.
Now copy the formula as the pointer changes to a copy pointer (+) symbol. Drag the selected formula to the rest of the cells.
MIXED REFERENCE
It is a combination of Relative and Absolute reference. In this type of reference, either row or column has to remain fixed. $A1+A$2 is an example of Mixed Reference. Let us calculate the actual monthly expenses :
Type 100 in cell E12.Click on cell E2.
Type =$C2-E$12.Press the enter key.
You can calculate the Actual Expenses of other cells by dragging the mouse pointer to the rest of the cells when the pointer changes to a copy pointer symbol.
CIRCULAR REFERENCE
A circular reference occurs when a formula in a cell refers to its own cell value directly or indirectly.
For example, if you enter the formula = A1=A2*3 in cell A2, you are making the formula refer to its own cell value, which is not possible. As soon as you click on the OK button of the warning dialog box, 0 will be returned in the cell containing the formula. The Status bar will also notify you that a circular reference is found in the workbook and will display the cell address.
CHARTS IN EXCEL
A chart is a graphical representation of data in a worksheet. It is an effective way to display data in pictorial form. It helps to provide a better understanding of large quantities of data. Charts make it easier to draw comparisons and see growth and relationship among the values and trends in data. They provide an accurate analysis of information.
COMPONENTS OF CHART
Let us learn about various components of a chart that are depicted in the figure:
1.) CHART AREA: Chart area includes all the areas and objects in the chart.
2.) CATEGORY AXIS: The category axis or X-axis is the horizontal axis of the chart.
3.) VALUE AXIS: The value axis or X-axis is the vertical axis used to plot the values. It is located on the left side.
4.) DATA SERIES: Data series are the bar, slices, or other elements that show the data values. If there are multiple data series in the chart, each will have a different color or style.
5.) CATEGORY NAME: Category names are the labels, which are displayed on the X and Y-axis.
6.) PLOT AREA: The plot area is a window within the Chart area. It contains the actual chart itself, and includes plotted data, data series, category, and value axis.
7.) LEGEND: It depicts the colors, patterns, or symbols assigned to the data series. It helps to differentiate the data.
8.) CHART TITLE: It describes the aim and contents of the chart.
9.) GRIDLINES: These can either be Horizontal or Vertical axis lines depending on the selected chart type. They extend across the plot area of the chart. Gridlines make it easier to read and understand the values.
0 Comments