Theme 4 Exercise help 5: MANIPULATING CELLS (I)
Exercise 1: Fruit Sales IV.
In part 2 we are asked to copy a range without using the clipboard.
1 Select the range to copy C5:H5 .
2 Situate yourself on the border of the selection and press and maintain the CTRL tab, clic the mouse button and drag till cell H12.
In part 3 we are asked to undo the previous operation.
1 Go to the Edit menu and select the Undo option.
We can also use the button on the toolbar.
In part 4, in order to copy without copying the format we need to use the paste special:
1 Select the range to copy C5:H5 and go to the Edit menu, then the Copy option.
2 Select the range C6:H12 that we want to copy to and go to the Edit menu, then the Paste special.. option.
3 Select the Formulas option and then OK.
Observe how the formulas have been copied but not the format.
We could have used the button on the toolbar. To try this, we will undo the operation and do it again:
1 Go to the Edit menu and select Undo.
We can also use the button.
2 Select the range to copy C5:H5 and clic on the copy button on the toolbar.
3 Select the range that we wnt to copy to C6:H12 and clic on the triangle of the paste button on the toolbar, a drop down list unfolds where we select the Formulas option.
In part 5 we are asked to do the same as in part 4.
1 Select the range to copy J5:L5 and clic on Copy or use the copy option of the menu.
2 Select the range that we want to copy to J6:L12 and select the Paste special... option from the menu bar.
3 Select the Formulas option (the All except borders option will also work..), and clic on OK.
For part 6 we need to use absolute references, we put B14 in cell K5 as an absolute reference so that it does not change when changing the cell:
1 Situate yourself in the cell to modify, K5.
2 Add the $ sign in the B14 reference in such a way that the formula is =J5*$B$14 .
The $ sign can be added by writing it or by the cursor being in the reference to modify, that is, B14 within the formula, press the F4 tab. Observe how the cell reference passes from relative to absolute.
Every time we press the F4 tab the type of reference will change.
3 Press ENTER to update the formula.
After this we need to copy K5 again:
1 Select the range to copy K5 and select the Copy option, after this select the range where we want to copy to K6:K12 and paste it.
It has pasted with the format and so we have lost the origonal format. To solve this problem:
1 Click on the paste button that has appeared and select the Coincide with destination format option,
2 To select a cell you need to clic on it.
3 To select a range of cells you firstly need to clic and maintain on the first cell (G4) of the range, and drag till the last cell of the range.
4 To select a complete column clic on its name (C).
5 To select a row clic on its name (4).
6 To select from row 5 to row 16, clic on the row 5 name , and without releasing drag the mouse to row 16.
7 To select jointly cell A5 and cell A9, clic on cell A5, press the CTRL tab, and clic on cell A9.
8 To select a complete sheet clic on the top left corner.
9 To select jointly row 4 and column A, clic on the row 4 name, press the CTRL tab, and clic on the column A name.
10 Click on cell A1, and whilst pressing the CAPS LOCK tab clic on cell F16.
If you have any doubts as to how to resolve this exercise we advise you to revise the theme.
To finish the model:
1 Copy the range H5:I5 for all the months of the year. Be careful that we only paste the formulas and not the format.
2 Copy the range B18:B23 for the various cities. The same as before - only the formulas.
We will not explain how to do this as you can refer back to the previous exercise and use the method you prefer most.
To finish the model:
1 Copy the range G6:H6 for all the rest of the employees. Only the formulas.
2 Copy the range C12:C14 for all the months. Only the formulas.
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.