Inserting Rows and Ranges
You can select an entire column or an entire row by clicking on the column or row heading. To select a row or column, use the drag method. After selecting, Right-click on the selected items and choose Insert. (NB: the row insertion will affect the entire worksheet. Fortunately, Excel allows us to undo this insertion easily. Click on the Undo button.
One other thing is that when rows are inserted, the ranges of cells included in formulas are updated.
Moving and Copying Data
There are two ways to move data in Excel. One way is to drag the data from one location to another. Procedure: Position the mouse pointer over the border of a cell you want to move until the pointer changes into an arrow, press and hold the mouse button, drag to the cell and release the mouse button.
Another way you can move data in Excel is to cut the data from one location and paste it to another. Procedure: select the cell, click on the Cut button. (the marquee surrounds the selected range) click on the Paste button.
Another way to copy a text is by drag and drop method. Select the cell, click and hold the mouse button, press and hold the Ctrl key to display a mouse pointer with a plus sign, drag to cell and release the mouse button and Ctrl key.
Excel adjusts copied formulas so cell reference change according to their new locations. These “adjusting” references are known as relative references.
E.g. using the fill-handle method to see how the cell references adjust. Select a cell that contains the formula. Position the mouse pointer over the lower-right corner of the cell border until the mouse pointer change to a solid plus sign, drag to a cell you want to copy the formula and release the mouse button. As you copied the formula to each cell, Excel adjusted the formula to reflect the change in the formula’s location relative to the column into which it was copied.
One other method you can use to copy a formula is to use the Copy and Paste buttons.
Absolute References
When formulas are being copied from one cell location to another, the cell references adjust automatically. Most of the time, this adjustment is just what we want.
Sometimes, however, we may not want this adjustment to occur. To prevent this, we must write the formula by using absolute references. E.g. add a formula that will calculate sales commissions. Type= select the cells to which the formula will refer. Click on the H5, type * click on cell H9, press the Enter key. Cell I 8 display the value. Now using the handle to copy the formula to cells I 9:I12. Select cell I8. Use the fill handle method to copy the formula to the range I 9:I 12. As the formula will copy, the references will change relative to their locations. In this situation, this is not what we want, because the commission rate resides in cell only. We make a cell reference absolute, rather than relative, by inserting dollar signs ($) before the column and row designation. You should know that a cell reference can include both relative and absolute parts. We can type the dollar signs, or we select the cell reference and press the F4 key. When you press the F4 key once when a relative cell reference is selected, Excel inserts dollar signs in front of the column and row references. Press the Enter key. Use the fill handle method to copy the formulas to the range. Once the percentage value is change the formulas in the commission cells recalculate because they all refer to the %cell.
Regards,
BEM T. CELESTINE
Social Plugin