Quattro Pro Tutorial

Working with Cells

A spreadsheet is simply a 2D array of cells that contain your data and formulas. These cells, up to 18 billion in each spreadsheet, are organized in rows numbered 1 through 999,999 and columns numbered, using letters, A through IV. These contain the data and formulas and they can be formatted to enhance their appearance, resized to accommodate larger fonts, joined, etc. Cell contents can also be moved, copied, and new rows or columns of cells can be inserted or deleted. Cells are the fundamental building block of the spreadsheet, so being able to work with cells is essential.

Contents


Addressing Cells

Each of the 18 billion cells in each spreadsheet in the notebook has a unique address. Not only does this make it easier for you to find a particular cell, but formulas and other objects to refer to the data in cells by their addresses.

Cell addresses consist of a column and row address and optionally a sheet address. The format is:

{Sheet name}:{column address, letter}{row address, number}

For example, the cell at address A:A1 is the top (row=1) left (column=A) cell in sheet A.

Addressing Blocks of Cells - Often one must write the address for a block of cells. To do this simply type the address of the cell in one corner of the block, followed by “..”, and finally the address of the cell in the opposite corner of the block. For example, E11..G23 would address all cells in the rectangular block where the corners are the cells at E11 to G23.  See the figure below.

Block selection of cells
An illustration of selecting a block of cells. The darkened cells have been selected.

Copying cells is a very common procedure, but what happens to the cell addresses in your formulas when the cell is copied to another location? The short answer is, they change.  Cell, as well as sheet, addresses are relative, meaning that if you copy a cell down ten rows and across ten rows, the cell addresses in your formula are adjusted accordingly. The wonderful thing about relative addressing is that you can do something like set up a column of numbers (column A in the figure below), enter a formula in cell B1, then copy that formula down the column and the formula will calculate the results using the adjacent values in column A. 

The numbers in column B were calculated by entering a single formula (=1/A1) into cell B1, then copying it to cells B2 through B9. The relative addressing of cell A1 in the original formula meant the form of the formula was copied, while the cell addresses were adjusted so that it always referred to the cell in the same row, but in the column immediately to the left.

Cell addresses, and even partial cell addresses, can be made absolute, meaning that when the cell is copied the addresses in its formulas do not change. You might want to use absolute addressing when copying a formula that uses a fixed constant, say, Avogadro's number, which you have entered near the top of the sheet, or maybe it is listed along with other constants on another sheet.

To make an address, or part of the address, absolute, all you have to do is enter a dollar sign ($) in front of the part of the address that you want to be absolute. For example, if you write an address as $A20, the column address would be absolute (unchanging) while the row address would still be relative. The table below shows a number of variations on this.

Address Sheet Cell Copy/Paste Behavior
C10 Current C10 The sheet, row, and column addresses are relative
$D$10 Current D10 The sheet, row, and column addresses are absolute
E$54 Current E54 The sheet and column addresses are relative, the row address is absolute
A:A1 A A1 The sheet, row, and column addresses are relative
Summary:G20 Summary G20 The sheet, row, and column addresses are relative
$G:$A$1 G A1 The sheet, row, and column addresses are absolute
$Charts:D3 Chart D3 The sheet address is absolute, but the row and column addresses are relative

A common situation in which you'd need to use partial relative and absolute addresses are when you want to perform a calculation that utilizes values listed along the top and side of a table, like the one shown in the figure below. The formula entered into cell C4, then copied to fill the table, was =$B4*C$3. This ensured that only the numbers in the blue cells, located along the top and side of the table, were used in the calculations.

This is an example of where you'd use a combination of absolute and relative addressing. To perform the simply multiplication calculations in this table the formula =$B4*C$3 was entered at cell C4, then copied to fill the rest of the table.
Back to top

Naming Cells

There are times when using cell addresses can become awkward and tedious. To make this a bit easier one can name a cell and then refer to the cell using its name instead of its address. For example, you might prefer to name cell B3, which contains the value of Boltzmann’s constant, Boltz. To do this right-click this cell and select Name Cells from the popup menu, or simply type CTRL-F3, and enter the name.

There are some restrictions on the names you can use. You can use any alpha-numeric character, spaces, and some punctuation marks and symbols. Upper and lower case letters are allowed and the names are not case-sensitive. In general, avoid using characters that Quattro Pro uses as math operators or for other purposes, and of course the cell name cannot be a number.

Back to top

Manipulating Cells

Selecting Cells - To select a single cell simply use the mouse to click that cell or use keyboard arrow key to move to that cell. Once selected you can enter or edit the data and formulas, or you can return to the dialog or formula that needed the cell’s address.

Selecting Blocks of Cells - Many operations involve a contiguous block of cells. For example, matrix operations only work on blocks of cells. Or, you might want to copy, paste, delete or format a block of cells all at one time. To select a block of cells using the mouse simply select a cell at one of the corners of the block, press and hold the left mouse button, and drag it to the opposite corner of the block and release the mouse button. The whole block of cells, except the top left cell, are highlighted. To select a block of cells using the keyboard simply select a cell at one of the block’s corners, press and hold the shift key, and use the arrow keys to move to the opposite corner, and then release the shift key. One can also use the PgUp, PgDn, End and Home keys during this operation.

Inserting and Deleting Cells - One can insert or delete individual cells, whole or partial rows and columns, even groups of rows and columns. The insert and delete commands are accessed through the popup menu (select, then right-click a cell in the column or row), using buttons on the notebook toolbar, or in the main menu under the Insert and Edit menus.

Whenever a row or column are deleted the remaining rows and columns move up or left to take their place. The cell address scheme, however, does not change. Deleting row 5, for instance, doesn’t mean there is no longer a row 5. Instead rows 6 through 999,999 shift up by one row and the cell references made in all formulas are adjusted accordingly. The exception to this rule is macros and cells in which the reference to a cell is entered as text. In these cases these macro commands and data must be edited manually.

Editing Cell Contents - To enter data or formulas into an empty cell simply select that cell and start typing. To replace the data or formula in a cell the same procedure is used. But if you want to change the data or formula in a cell without retyping the whole thing there are two methods available:

  1. Select the cell, press function key F2, and edit the cell contents.
  2. Select the cell, edit the cell contents in the edit line just above the spreadsheet. See the toolbars figure on the Quattro Pro concepts page to see this edit line.

When done editing the cell contents press the Enter key or select another cell.

Cell Editing and Navigation Shortcuts

The following shortcuts make editing and selecting cells much quicker and easier than constantly working the mouse or track ball.

F2 Begin editing the current cell.
PgDn Move down the sheet one page.
PgUp Move down the sheet one page.
End, arrow key If the current cell is not empty, moves to the last cell a column of data. If the current cell is empty, moves to the next non-empty cell. (Press the End key, release it, then press the arrow key)
Home Move to the top-left cell on the current sheet, to cell A1.

Note - if the SHIFT key is held during any of the above moves then all of the cells traversed during the move will be selected.

Back to top

Copying, Pasting, Deleting and Moving Cells

After entering data or a formula into a cell you may find you need to move it, duplicate it, or delete it. These procedures are easily done and for the most part follow standard Windows conventions, particularly copying and pasting cell contents. The Edit menu in the main menu contains most of the copy/paste commands. A quick look at this menu shows the standard Cut, Copy, and Paste command, and also the Delete, Paste Special, and others.

Cut/Delete - To delete the data or formula in a cell simply select that cell and press the Delete key. This will not, however, delete the cell’s format setting (color, font, fill, border, etc.). To delete the cell contents and formatting use the Cut command using Edit|Cut, or ALT+E+T, or CTRL+X, or Shift+Delete. Note that the Shift+Delete command also copies the cell contents and formatting to the clipboard.

Copy/Paste - To copy the contents of a cell or any other object to the clipboard use the standard Edit menu command or their keyboard equivalents. The keyboard short cuts, CTRL+C ("C" for copy) and CTRL+V ("V", as in wedge the selection into the spot you chose), have been used as copy and paste shortcuts since the old DOS days (a time before Windows). Other long standing copy and paste keyboard sequences are Shift+Delete and Shift+Insert.

Note that when copying and deleting cells that the data, formula and cell’s format are also transferred to and from the clipboard. Items in the clipboard can be copied to other applications.

The copy and paste commands work for cells, graphs, images and any other object, and as just mentioned you can copy and paste to and from other applications. A good way to get a graph, for instance, from Quattro Pro to an open document in a word processor such as Word or WordPerfect, is to copy it to the clipboard and then paste it into the document. And very conveniently, copying a block of cells to a Word or WordPerfect document will insert it as a table. Copy and paste can also be used effectively to import text or data from a text (*.txt) file.

Cut/Copy/Paste Keyboard Shortcuts

Many of the keyboard shortcuts used for cell editing date back to the pre-Windows days when DOS and QuickBASIC were Microsoft’s major products and Wordstar, which ran on CP/M, was the dominant word processing program. Now, like then, copying, pasting and deleting cells, like text, are the most frequently used procedures. These keyboard shortcuts are:

Delete     Delete Deletes cell contents but not the formatting
Cut CTRL+X ALT+E+T Shift+Delete Delete cell contents and formatting and copy to clipboard.
Copy CTRL+C ALT+E+C   Copy cell contents and formatting to the clipboard.
Paste CTRL+V ALT+E+P Shift+Insert Paste cell contents and formatting into the spreadsheet.
Back to top

Changing the Appearance of Cells

Cell properties determine how the contents of the cell are displayed (font, numeric format, alignment), whether or not the user can change the contents of a cell (constraints), the size of the cell (row/column) and the color, pattern and border used to enhance the visibility of a cell. All of these properties are accessible through the Active Cells Properties dialog form. This dialog can be brought up by right-clicking the cell or group of cells and selecting Selection Properties from the pop-up menu, pressing function key F12, or through the main menu by selecting Format|Selection Properties.

This figure shows the "Alignment" page of the cell properties dialog form. This dialog allows you to change practically every aspect of the cell's, or block of cells', appearance. Pressing the F12 key will bring up this form.

 

Keyboard Shortcuts to the Properties Dialogs

The three most common spreadsheet procedures involve selecting cells, editing cell contents and editing the properties of cells and objects. You may have noticed that function key F12 is associated with the properties dialogs. Below are the four property dialog forms that the F12 key invokes:

Cell F12 ALT+R+R Properties of selected cell or block of cells, including fonts, fill, borders, numeric format, alignment, cell size. Pressing F12 will bring up the properties dialog for other objects, too, such as graphs and images.
Notebook Shift+F12 ALT+R+N Notebook properties, including scroll bars, calculation options, password protection, zoom, palette, summary and statistics.
Sheet CTRL-F12 ALT+R+H Sheet properties, including zoom, cell borders, sheet borders, cell and object protection, column width, sheet name.
Quattro Pro ALT+F12 ALT+T+E Quattro Pro settings, including simulating Excel, Lotus and other main menus, toolbar options, timed backups, default currency, date and other formats, startup macros, compatibility, etc.
Back to top

Hints and Suggestions

  • Keyboard shortcuts can be very useful. It is often easier, when already typing data and formulas, to type the shortcut instead of picking up the mouse over and over again.

  • The exercise offered in this tutorial provides an introduction to the basic features of spreadsheets. The lesson, however, doesn’t stop here. While the tutorials in the following chapters will guide you through techniques used in the Applications module there are many other interesting and useful features which you might find interesting.
Back to top