COMP 1001/1004 - Introduction to Computers for the Arts and Social Sciences
2005-2007

 SpreadSheets - Quattro Pro 1

1.  What is a Spreadsheet
2.  Creating a Spreadsheet
3.  Quatro Pro Basics
4.  Data Entering
5.  Copying and Moving Data
6.  Formatting a Sheet


1.  What is a Spreadsheet?

A spreadsheet is


An electronic spreadsheet allows the user to:
Electronic spreadsheets are used heavily by:
They are also extensively employed by many others...
Some advantages and capabilites of electronic spreadsheets are:
Here is a screen snapshot of a Quattro Pro spreadsheet:



2.  Creating a Spreadsheet

The creation of a spreadsheet is a bit more involved than a typical essay-style document. 
Quattro Pro has numerous formatting and layout options.
Good planning is required in order to create a workable spreadsheet. 

Attention must be given to:

The creation of a spreadsheet can be divided into three stages (ideally):


3.  Quattro Pro Basics

A.  Quattro Pro Files

The Quattro Pro (or Excel) program is used to create spreadsheet files. 
A spreadsheet file is called a notebook in Quattro Pro. (called a workbook in Excel)

A notebook may contain:

Every sheet:
Rows are labelled by numbers:

1, 2, ..., 1,000,000

Columns are labelled by letters:

A, B, C, ..., Z, AA, AB, AC, ..., ZZ, AAA, AAB, ... ZZZ (stops at IV in Excel)
 

A B C D E
F
1            
2            
3            
4            
5            
6            
7            
8            
9            

A cell is a box at the intersection of a row and a column (see red square above)
It is identified by the column label and the row label
(called the address, or reference, or coordinate, of the cell). 
For instance, the cell at the intersection of column D and row 5 is the D5 cell (see above). 
The address of a cell may also contain the sheet label such as PAYROLL:D5
This mean cell D5 on sheet PAYROLL.  

Note that the : character is used to separate the sheet label from the cell. ( ! in Excel )

When a notebook is open, one sheet, the active sheet, is shown. 
In the active sheet, there is, similarly, an active cell
Any action that the user takes, such as...
...will be on this active cell. 
The user may change the active sheet or the active cell by selection actions.

 

B.  Starting Quattro Pro

To start Quattro Pro...
      1. Click on the Start button in the taskbar.
      2. Point to All Programs.
      3. Find Office Suites and look for...
      4. Quattro Pro

C.  The Quattro Pro Window

The Quattro Pro window contains the following components.
              Title bar
              It contains the program name and the name of the current notebook
              Menu bar
              It contains a list of menus that contain Quattro Pro commands.
              Toolbar
              It provides easy access to the more common Quattro Pro commands
              Property Bar
              It provides easy access to the more common text layout features (like the property bar of WordPerfect).
              Input line
              It allows you to enter data into the active cell (and displays the active cell address).
              Status bar
              It displays information about the current state of Quattro Pro.


D.  Spreadsheet window

The main area of the spreadsheet displays a sheet of a notebook shown as a grid of columns and rows
The window only shows a small portion of the available columns and rows. 
The user may use scroll bars to move to other parts of the sheet. 
In Quattro Pro, you may open more than one notebook at once.
From the Window menu, you may arrange the spreadsheet windows in different ways as was done with WordPerfect (e.g., cascading, tiled).

There are some terms which you should know relating to the selection of cells:

 Selector: 

It is the black outline that indicates the active cell:

Blocks: 
A block is a rectangular group of one or more cells.
Identified by the cell in the upper left and the lower right.

For example:
C1..C1 is a block with a single cell (C1:C1 in Excel)
B8..E13 specifies the block which is highlighted in the following figure (B8:E13 in Excel). 

Notice the use of two dots between the cell references (colon in Excel):
When you select a block of numbers, you get some statistical information shown to you in the status bar
(as seen in the image above) (only Sum in Excel):


E.  Moving Around in Quattro Pro (and Excel)

Here is how to move around within a notebook and within its sheets:

KEYS
QUATTRO PRO
EXCEL
Tab one cell to the right Same
Shift-Tab one cell to the left Same
Right arrow one cell to the right Same
Left arrow one cell to the left Same
Up arrow one cell up Same
Down arrow one cell down Same
Home go to cell A1 in the current sheet Excel: go to column A in the current row
Ctrl-Home go to cell A1 in sheet A Excel: go to A1 in the current worksheet
PgUp move one window-full up Same
PgDn move one window-full down Same
Ctrl-left arrow  move one window-full to the left Excel: Jump an entire block of empty / populated cells
Ctrl-right arrow  move one window-full to the right Excel: Jump an entire block of empty / populated cells
Ctrl-up arrow
nothing
Excel: Jump an entire block of empty / populated cells
Ctrl-down arrow
nothing
Excel: Jump an entire block of empty / populated cells
F5 shows a dialog box to specify the cell you want to go to Same
Ctrl-PgUp move to the previous sheet in the notebook Same
Ctrl-PgDn move to the next sheet in the notebook Same
Tab Scroll Controls
(see diagram)
navigate through the sheets
Same
Click on Sheet Tabs move to the sheet specified by the tab Same
Scroll Bars move up and down or left and right within a sheet Same


F.  Selection and Naming of Cells, Blocks and Sheets

You can select cells, blocks, rows, columns and various combinations thereof...

You can give names to blocks to simplify calculations. 
Thus, in complicated expressions (as we'll see later) we can use the block name instead of the cryptic row/column specifications. 
There are two ways to set a name. 
First, however, you must select the block to be named. 
Then...
In either case, a dialog box appears in which you should type in a name, then click close. (click OK in Excel)

You can also give names to individual sheets.  
By default, the names are merely letters (Sheet1, Sheet2... etc in Excel)
You can pick more meaningful names by double-clicking on the sheet tab and then typing in the new name (Same in Excel).


4.  Entering Data

The data entered from the keyboard will be stored in the active cell
You may enter a label, or a value, or a formula (defined below). 

When a label or a value is entered, it will be shown in the active cell as well as in the input line (Same in Excel

When a formula is entered
Quattro Pro recognizes the type of entry automatically

After the data is entered in a cell...
 +   add 
 -   subtract 
 *   multiply 
 /   divide
 ^   raise to7 the power of (exponent) 
&  concatenate (i.e., join)
          QuickFill

Deleting and Modifying Data:

Importing Data From a Text File:

Text data can be imported into Quattro Pro (or Excel). 

We will look here at importing ASCII text data. 

Text data should contain labels and values that are to be transfered into a speadsheet.
Often a comma character is used to separate the data into columns. 
Data that is to appear in a new row is usually given as another line in the text file (i.e., separated by a carriage return or new line). 
The commas and carriage returns are called delimeters

Here is an example of a text file that can be imported (notice that commas separate the data within a row and new lines separate the data on different rows):
Employee Name,Pay Rate,Hours Worked
Joe E.,6,30
Mary K.,7,50.5
Mike V.,9,25
Jane D.,2,35
Once loaded into the spreadsheet, the data will be arranged like this:
 
Employee Name  Pay Rate  Hours Worked 
Joe E. 6
30
Mary K.
7
50.5
Mike V.
9
25
Jane D.
2
35

To import text data, follow these steps:

For more information, use the Quattro Pro help to look up importing of text data. 

As an exercise, try importing the file Example1.txt such that it ends up looking something like this...
(won't work in Excel - need to first replace "$" with Return)


5.  Copying and Moving Data

To copy or cut a block of cells...
  1. Select the block
  2. Click on the cut or copy button in the tool bar
To paste data from the clipboard...
  1. Select the top left corner of the block that you want the data to appear in
  2. Click on the paste button in the tool bar
You may also use CTRL+c, CTRL+x, and CTRL+v, to do copy, cut and paste (respectively).

You may also click-and-drag a block to move it to another place. 

To do so...

  1. Select a cell or a block
  2. Point to the boundary of the block
  3. When the pointer changes to a cross of four arrows...
      • Drag the block and drop it someplace else  (same steps in Excel)

For example, the block F3..M7 was selected in the image below
Then was dragged to H4. 
Note that the yellow/blue outline indicates (while you are dragging) where the block contents will be written.

When a formula is copied...

The references to the cells used in the calculation will change according to the relation between the reference to the cell where the formula orginally was, and the reference of where the formula is copied to. 
Sounds confusing, but with a bit of practice, it's not...
In formulas, Quattro Pro (and Excel) "thinks" of cell references as being:
So many cells over, and
So many cells up/down
..even though they're displaying the exact cell address.

Generally, this makes things easier.
You usually don't have to worry about re-entering formulas. 
You may not know it yet, but this is how you want copy-paste to work most of the time.

However, you should always make sure that nothing has gotten fouled up by your cutting-and-pasting. 

As an example of how the references change...

Suppose a formula +A1+C1 is in cell D1
Suppose it is copied to D2
The formula (in D2) becomes +A2+C2
When it is copied to cell E3...
The formula become +B3+D3

Please make sure that you understand this!  Try it yourself to be sure.

These changing references are known as relative references

If you do not want a reference in a formula to change when it is copied, we may specify this reference as an absolute reference

To specify a reference as an absolute reference...

Add a $ in front of the row and/or the column label. 

For instance:

$A1 will make the column label absolute
A$1 will make the row label absolute
$A$1 will make both absolute

Suppose formula +A1+C1 is in cell D1

If we entered it as +$A1+C1, then,
When it is copied to E3,
We end up with +A3+D3

If it is entered as +A$1+C1, then,
When it is copied to E3,
The formula becomes +B1+D3

If it is entered as +$A$1+C1, then,
When it is copied into E3,
The formula becomes +A1+D3.


6.  Formatting a Spreadsheet

Many format actions can be done using the active cell dialog box,
Right click on a cell
Select Cell Properties
or
Choose Selection from the Format menu.

A.  Adjusting the Column Width and Row Height:

Sometimes a column has data that does not fit because the width is too small
Sometimes as well the data is very small and the column seems too wide
The width of a column can be adjusted in a few different ways:
  • Drag the column border between column labels until it is the desired width.
or
  • Select the column and then click the QuickFit button  on the tool bar.  
  • The column width will changed to fit the longest entry in this column.
or
  • Click on any cell in the column and then right click to get the pop up menu.  
  • Select Cell Properties... from the menu and the Active Cells dialog box (shown below) will appear.  
  • Click on the Row/Column tab of the dialog box.  
  • You may then choose the width of the column and/or the height of the row.  
  • Finally click OK.

B.  Inserting or Deleting Rows or Columns:

To insert a row (or a column):
To delete a row (or a column):

C.  Choosing the Font Type, Size, Appearance, and Color of the Text:

Font, size, and appearance can be changed the same way as in WordPerfect
or
  • Right-click on the text
  • Select Cell Properties... from the menu. 
  • Click on the Cell Font tab. 

D.  Formating Values:

Values in cells may have different formats
To choose a format select the Numeric Format tab from the active cell dialog box

You may choose one of these formats:
Here is a snapshot showing the various settings for currency.  
Note that it gives you a preview:

E.  Using Color:

To colour cells
You can choose the foreground and background colours
You can choose the pattern to be used to fill the background.
 

F.  Adding Borders and Lines:

or


G.  Adding a Title Centered Accross a Block:

Follow these steps:
          1. Determine the set of cells (consecutive, and in the same row) that you want the text to be centered accross
          2. Click the leftmost cell in this set.
          3. Enter the text in the cell there (see "Restaurant ratings" below).
          1. Select all the cells from step 1.
          2. Choose the alignment option Center Across Block from the justification button 

H.  Using QuickFormat and SpeedFormat:

QuickFormat

Allows formats to be copied to other cells quickly
It is similar to QuickFormat in WordPerfect.
SpeedFormat

Provides some predefined formats
This includes coloring, border lines, font attributes etc...

I.  Sort the data in a sheet.

Items are sorted by some unique indicator called a key
Items can be sorted in increasing or decreasing order. 
Note that you should ALWAYS save your work BEFORE sorting!
There are times when the program will "hang", or became unresponsive while sorting. 

To do a simple sort
If you want to sort a block using...
More than one key column
or
A key column that is not the leftmost or rightmost column