In esCalc, apart from editing bands to change the cellset’s structure and editing records to add or delete data, you can perform cell editing, which includes structural operations such as adding and deleting cells, as well as data copying and pasting. This article will deal with how to edit cells.
1. Editing cells and homocells
In an esCalc sheet, you can select a cell and choose any operation from the Cell item on the context menu:
There is a shortcut key for every operation on the item list.
Among cell editing operations, those about the master cell have been discussed in esCalc Structure: Homologous Relationship. Among other operations, Clear cell value, Clear cell formula and Clear homocell value just remove data or formulas from cells without changing the band’s structure, and you can perform the three operations from the Edit on the menu bar as well; Insert cell, Append cell, Delete cell and Delete previous cell will change the structure of homo-rows, and they can be performed from Structure on the menu bar.
You can also perform copy/paste operations on the cells from the context menu, including Cut, Copy, Homocell copy, Paste, Homocell paste and Band paste. Or you can perform them through Edit>Copy & paste on the menu bar.
For homo-cells, esCalc provides copy and delete operations, including Select homocell all, Select homocell above and Select homocell below.
In addition to Homocell copy and Homocell paste, there are more operations dedicated to editing homo-cells under Homocell item on the context menu. These include Homocell insert, Homocell delete, Copy homocell up/down and Copy to all homocell up/down, as well as the move operation like Move cell left/right, and so on:
2. Adding and deleting cells
Among cell editing operations, Insert cell, Append cell, Delete cell and Delete previous cell will change the structure of a band in a cellset. Below is an employee table:
Insert cell inserts an empty cell in the position of the selected cell, pushing each of the cells on its right side in the current row one position rightward; Append cell adds an empty cell on the right side of the selected cell, pushing cells following the newly-added one to the right. These move operations performed on a current row will trigger the synchronous operations in all its homo-rows. If there is value in the last cell of one of these homo-rows, an empty column will first be appended to the end of the cellset. You can use shortcut keys Ctrl+Insert and Alt+Insert to insert and append cells.
Now you might want to add a column after Birthday, or between C and D columns in the original table, to calculate ages of employees. To do this you can use the Insert cell by selecting cell D3, or perform Append cell by selecting C3. Both can give you the same result. This is the table after insertion or appending:
Unlike Insert column and Append column introduced in esCalc Spreadsheet Editing: Bands, Insert cell or Append cell only affects the homo-rows of the current row. So data in rows 1, 2 and 5 remains unaffected. Since column E – the last column – had been empty before adding the cells, no extra column needed to be appended.
If you proceed to perform Append cell on D3 or Insert cell on E3, you can add another empty cell after D3, as shown below:
At this point, when trying to add yet another empty cell, a column will be appended to the end before cells start to move since there is data in the leftmost column. The width of the new column uses the default value.
On a selected cell, performing Delete cell could remove this cell; and performing Delete previous cell could delete the cell on its left side. The deletion of a cell deletes its homo-cells too. In all the homo-rows that are within this sphere of influence, cells on the right side of the deleted one move left to fill the vacancy while an empty cell is appended to the end of the current row and every homo-row; other rows, however, remain unchanged. The execution of Delete cell and Delete previous cell will not change the number of columns in a cellset. You can perform the two operations using the shortcut keys Ctrl+Delete and Ctrl+Backspace respectively. For instance, you’ll get the same result by performing Delete cell on A3 and Delete previous cell on B3 with the shortcut keys. Here is the result you’ll get:
Different from Delete column you learned from esCalc Spreadsheet Editing: Bands, deleting a cell in a row will only impact its homo-rows. The operation won’t change the properties, numbers and width of the columns.
3. Deleting cell values or formulas
Clear cell value performed on a selected cell makes a null value, while keeping the formula. Clear homocell value will clear all the homo-cells of their formulas. Clear cell formula removes the formula from the selected cell, as well as the formulas from its homo-cells, because these cells use the same formula. The shortcut keys corresponding to the three operations are respectively Delete, Alt+Shift+Delete, and Alt+Delete.
In the following employee table, column C calculates employee age, in which C3’s formula is =age(B3):
Select C4 and press Delete to delete its value, you’ll get result as follows:
C4’s value has been deleted, and its homo-cells keep their values. Click cells C3, C4, C6, C7 and C8, and you may find that their formulas remain unchanged. For instance, C4’s formula =age(B4) is still there. Then select C6 and press Alt+Shift+Delete to remove values from homo-cells, the result would be like this:
Now the value of D6, as well as values of all its homo-cells, has been removed. But the formulas in D3, D4, D6, D7 and D8 are still there. If you select D4 again and press Alt+Delete, the formulas in these homo-cells will all be deleted.
4. Copying and pasting
You can copy or paste cells by choosing corresponding item from the menu bar, or the context menu, but a more common approach is to use the shortcut keys. Select a cell or cells and press Ctrl+C to copy, or press Ctrl+X to cut, and then press Ctrl+V on the target cell(s) to paste. The cut operation will clear a selected cell of its value and initialize its appearance properties.
The copy operation will copy the value and the appearance properties of a selected cell to the targeted cell. If there is a formula in the selected cell, it will be copied intelligently according to the position and meaning of the cell referenced by it.
In the employee table below, column C calculates employee age, in which C3’s formula is =age(B3), column E calculates the years of service, in which E3’s formula is =age(D3), and C5 calculates the average age of the current department with the formula round({C3}.avg(),2), whose result is required to be rounded off to two decimal places. After formulas are entered, you’ll get the following result:
You can see that C10, the homo-cell of C5, has also got the average age of its department. About the smart copy of formulas among homo-cells, please refer to esCalc Structure: The Smart Copying of Formulas.
Now copy C5 and paste it to E5, C11 and E11, you’ll get this result:
You may first observe that C5’s appearance properties, such as background, foreground, font and alignment, have been copied to E5, C11 and E11. Check E5 and you’ll see the formula is =round({E3}.avg(),2). The C3 in the original formula has been intelligently copied as E3, which is in the same column and band as the targeted cell, ensuring that the result is the correct average service year of the employees in the current department. Then look at C11’s formula =round({C3}.avg(),2), it calculates the average age of all the employees. Similarly, E11’s formula round({E3}.avg(),2) calculates the average service year of all the employees.
So you can see that when you copy and paste formulas, the referenced cell will transform itself automatically. It changes between columns based on the relative position of the original cell and the targeted cell; between rows in a way similar to the smart copy of formulas among homo-cells.
Besides the normal copying and pasting, esCalc specifically provides operations for copying and pasting homo-cells. These include Homocell copy, Homocell paste and Band paste, which correspond to shortcut keys Ctrl+Alt+C, Ctrl+Alt+V and Ctrl+Alt+B respectively.
Look at the following table:
Select C6 and perform Homocell copy, and then select D3 and perform Homoell paste, you’ll get the following result:
As can be seen from the result, Homocell copy has pasted only the value, but it refuses the properties. The operation will copy the values of all homo-cells no matter which one of them you select. Now select C6 again and perform Homocell copy, but select D4 to perform the Homocell paste, then you’ll get result as follows:
This result further explains the characters of homo-cell copying. When there are more data to be copied than the number of homo-cells that can hold the copies, a new homo-row for pasting the extra data will be added after the last row under these homo-rows’ parent row. There are no values in the other columns of the new homo-row.
Homocell copy allows selecting some of the homo-cells to paste. For instance, you might select C4, choose Select homocell above and press Select homocell above to copy the cell. In this case only C3 and C4 will be pasted. Now select E4 and perform Band paste, you’ll get the following result:
The result shows that Band paste has pasted data into the newly-inserted records before the selected cell, whose number is determined according to the number of homo-cells the column where the selected cell resides has within the current band. The pasting starts from the selected cell and proceeds in sequence.
esCalc allows copying and pasting data from the same sheet, or from a different type of data file, such as a txt file and Excel file, or from a database browser.
5. Operations on homo-cells
With some actual practice you may find that, different from other normal operations on cells, Homocell copy and Homocell paste are only valid for homo-cells. So are other operations on homo-cells, such as inserting/deleting homo-cells, copying data to homo-cells and moving homo-cells.
Below is an employee table:
Select A4 and B4 together and perform Homocell insert, then you’ll get the following result:
It can be seen that in the positions of the original A4 and B4, empty cells have been inserted; and from the original 4th row on, data in both A and B columns of A4’s homo-rows moves one position down. Data in other columns stays the same. A new homo-row has appended to the end of the homo-rows to hold the data in the first two columns of the last record among those that are moving down.
In the above cellset, select A7 and B7 together and perform Homocell delete, then here is the result you’ll get:
It shows that values in A and B columns of the homo-rows after the 7th row move one position up while values in other columns remain unchanged.
There are four operations for copying value of the current cell to its homo-cells: Copy homocell up/down and Copy to all homocell up/down. Suppose you select B6 and perform Copy homocell up to copy current cell value to homo-cells above, you would get the following result:
B6’s value has been copied to its homo-cell B4 above.
Suppose you select C6 and perform Copy to all homocell down, C6’s value will be copied to all the homo-cells below, as shown in the result:
You can also exchange the data between two neighboring columns by moving cell left or right. For instance, you might select B4 in the above table and perform Move cell left so that B4 and its homo-cells would move left to exchange values with column A. The result would be like this:
Suppose you select A4 to perform Move cell right, you would get the same result.