CCNC/CCNC Module 4/Working with and in cells/Making changes to contents of cells

From WikiEducator
Jump to: navigation, search
Tutorial.png Presentation Basics 

Selection in a worksheet | Inserting within a worksheet | Deleting rows and columns in a worksheet | Modifying column widths and row heights | Entering data in cells | Making changes to the contents of cells | Shifting cells | Using the Search and Replace Command | Sorting data | Show Me Xine.png | Summary & FAQs

Making changes to the contents of cells

In the previous sub-sections, we focused on operations related to cells, rows and columns taken as a whole. In this subsection, we will be going inside the cells to perform a series of operations all related to the contents of cells. Specifically we will edit, replace, duplicate, delete and search and replace the contents of cells, that is the data contained in cells. At this point it is worth reminding ourselves that data in a spreadsheet may be of two types: the numerical and the alphabetical. We will be encountering both as we perform the various operations listed above.

Now do it


Edit existing content of a cell

You perform this task to add, insert or delete text to or from the existing contents of a cell. You do this in the Input line of the Formula Bar. Suppose you identify A8 as the cell in which you want to add additional text. A8 is selected and its contents appear in the Input line. You then proceed as follows:

  • Click at the end of the text in the Input line.
  • Enter the additional text
  • Press Enter, Tab or an arrow key when you are done.

Using this procedure you can also insert or delete text in the Input line.

Replace contents of a cell

You may wish to completely remove the existing content of a cell and replace with new text. You perform this task within the cell itself. Note that you execute this command within the cell itself, while you used the Input line of the Formula bar to edit the contents of a cell.

  • Click on the cell whose content you wish to replace.
  • Type your new content. When you do this, the existing content of the cell will automatically be replaced by your new entry.

Duplicate the contents of a cell or cell range

This operation is essentially the same as copy and paste that you have already done in Write. Here we will be duplicating the content of a single cell as well as of a cell range. Further we will be executing the operation in a single worksheet, between worksheets and between spreadsheets. The procedure throughout is as follows:

  • Select the cell(s) whose contents are to be duplicated
  • Copy them to the clipboard, that is Edit->Copy
  • Select the target, that is the cell(s) to which the content is to be copied.
  • Edit->Paste. The data from the previous cell(s) will now appear in the target cell(s).

Note that when you paste data, the contents of the target cells will be overwritten.

Duplicate within a single worksheet

SCREENSHOT

Suppose you wish to insert another set of headings above Group 11, which is now in Row 14 in the worksheet shown above. These headings are now in the cell range B3:K3

  • Insert blank row at row 14. Group 11 has now shifted downwards to Row 15.
  • Select cell range B3:K3
  • Press Ctrl-C, OR Edit->Copy OR Click the Copy Icon to copy the selected cells
  • Position the cursor at the start of the blank Row 14
  • Press Ctrl-V, OR Edit->Paste OR Click the Paste Icon to paste the copied cells into Row 14.

Duplicate between worksheets

Recall that each spreadsheet contains three worksheets by default. You can move between worksheets of an open spreadsheet by clicking on the appropriate tab at the bottom of the work area.

SCREENSHOT

Suppose you wish to copy A3:K3 of Worksheet #1 to the cell range A3:K3 of Worksheet #3.

  • Select cells A3:K3 of Sheet #1.
  • Edit->Copy
  • Click on the Cheet #3 tab
  • Position the cursor at the start of the target range
  • Edit->Paste.

Duplicate between open spreadsheets

It is possible to have several spreadsheets open at the same time. Note that spreadsheets are stored in different files whereas the worksheets of a particular spreadsheet are all stored in the same file. You can switch between spreadsheets using the Window menu item. Suppose you have two spreadsheets open. The first is called ObservationbyGroupSector and the second Obs2. You now wish to copy the cell range A3:K10 of the first into the second starting at B5.

SCREENSHOT

  • Click on Window
  • Click on ObservationbyGroupSector.sxc. This will display this spreadsheet.
  • Select cells A3:K10
  • Edit->Copy
  • Window->Obs2
  • Select cell B5
  • Edit->Paste

Move the contents of a cell or cell range

The procedure for moving the contents of cell(s) is exactly the same as that for duplicating the contents of cell(s) except that instead of the copy function you use the cut function followed by paste. The contents may be moved within the same worksheet, from one worksheet to the next within the same spreadsheet, or between different spreadsheets.

Now do it

  • Select the cell(s) whose content is to be moved.
  • Press CTRL-X OR Edit->Cut OR Click the cut Icon to cut the selected cells
  • Position the cursor at the start of the target range
  • Press CTRL-V OR Edit->Paste OR Click the Paste Icon to paste the cut cells at the new location.

Delete the contents of a cell or cell range

To delete the contents of a single cell or a range of cells, you use the Delete key. The outcome of that operation is that the contents of the selected cell(s) are emptied. The cells themselves are not moved.

Now do it

  • Select cell(s)
  • Press Delete Key

Increment data entries in successive cells

In some instances you may want to enter data in a range of adjacent cells, based on a specific value between the contents of one cell and the next. Thus you may wish to insert a series of numbers or other type of data that can be arranged progressively in a series across several cells. To do this, Calc uses the autofill tool, which enables it to complete the series following a specific pattern, based on the values in the first two cells.

SCREENSHOT

Now do it

  • Open a new spreadsheet
  • Type 1 in A1 and 2 in B1
  • Select A1 and B1. A fill handle will appear in the bottom right hand corner of A2 (see screenshot above)
  • Drag the highlight across the other cells of Row 1
  • Deselect the cells by clicking elsewhere in the spreadsheet. The cells are no longer highlighted, but CALC has completed a series of numbers across all the cells of the row based on the values in the first two cells.

SCREENSHOT

  • Now type 100 in A2 and 101 in B2
  • Drag the highlight (fill handle) across the other cells of Row 2.
  • Deselect the cells. Note the series of numbers across the entire row.
  • Now type 5 in A3 and 8 in B3
  • Drag the fill across the other cells.
  • Deselect. Note that the numbers in the successive cells increase incrementally by 3, based on the values of the entries in A3 and B3.
  • Now type Jan. in A4 and Feb. in B4.
  • Drag the fill across the adjacent cells.
  • Deselect. CALC has completed the series using the abbreviated names of all the months.
  • Now type January in A5 and February in B5.
  • Drag the fill across the adjacent cells.
  • Deselect. CALC has completed the series, using the whole names of the months.

SCREENSHOT


Create your own sort lists to increment data entries

The data that we used for the series just created are the standard type of spreadsheet data and are built in to the Calc software in the form of sort lists, ready to be activated when you need to increment data entries as we have just done. However there will be times when you will want to create a series based on data that is not already available as sort lists in Calc. Here you will create your own original sort list.

Now do it

  • Tools->Options->Spreadsheets->Sort lists
  • Click the New Button
  • Type the entries for your sort list into the Entries Window as shown on the screen below. You will need to Press Enter after each entry. The entries that we are including are the letters of the Greek alphabet.
  • Click Add when complete.
  • Now type Alpha in A10 and Beta in B10 of the worksheet you are working in.
  • Drag the fill across all the cells in the row.
  • Deselect. The series extends across the entire row.

SCREENSHOT