Developing a Spreadsheet

In this section, you will learn how to create a simple spreadsheet.  Spreadsheets are ideal for working with numbers and data.  Using the Gobe Productive suite, you can keep track of your checkbook, add up your expenses, and even look into the future with forecasting and trending.

This section contains the following exercises:

Launch Gobe Productive and Begin a New Document

  • Run Gobe Productive.

  • When the New Document window displays, select Spreadsheet and click OK.   This starts a new spreadsheet document.

Spreadsheet Basics

A spreadsheet document is specifically designed to work with columns and rows of information.  Each cell of data has a reference, kind of like a map.  The third cell down in column B is called B3.  If you wanted to add the value of whatever number was in cell B3, to whatever value was in B4, you could enter a formula like this:

= B3 + B4. 

Formulas make spreadsheets extremely powerful.  Rather than entering the actual numbers you want to add, you can enter references to those numbers.  If you change the numbers later, the formula automatically updates the value to reflect the new values. 

Selecting Cells

Spreadsheets work a little differently from word processors, especially when it comes to how material is selected. 

To select a range of cells, click and hold and then drag the highlight over the cells you want to select. You can also select an entire row, column, or the whole spreadsheet with a single click. 

  

Entering Data

First, you must enter some data to work with. 

  1. Click on the first cell in the spreadsheet, cell A1.

  2. Type in Auto Expenses and press [ENTER].  This is the title for the spreadsheet.

  3. Select cell A3.  Type in an expense such as gasoline and press [ENTER].   Repeat this going down the A column and entering five or six expense categories such as repairs, insurance, finance charge, etc.

  4. Once you have setup some categories, you need to create headings.  Select cell B2. 

  5. Type January and press return.  Move to cell B3 and type February. You can press the [TAB] key to confirm the data entry in the cell and move to the next cell in the row.  Repeat this step until you have entered all twelve months across the B row.
NOTE:  If you make a mistake, press [ALT] [Z] to undo, or select Undo from the Edit menu.  If you need to change the value in a cell, click in the cell then edit the text or numbers in the data entry field in the PartBar. 

Next, you need to enter some values.  Position the cursor at cell B2.  This should be the first entry under January for the expense gasoline.

  1. Enter 32.95 and press [TAB].  Don't worry about the dollar sign, we can take care of that later.  You should be in cell C3 now, enter another value.  This is the gasoline expenses for February. 

  2. Repeat this step for all the months and for all the expense categories.  When finished, your sheet should look something like this.

    ssexample.gif (9982 bytes)

Formatting Cells

The formatting bar provides most of the standard formatting features for cells.   Remember you must first select cells to format them.  For more information on formatting cells, see the Formatting Cells topic in the Spreadsheets section.

Use the text font tool to change the text font for the selected cells.
Use the text size tool to enlarge or decrease the size of the text for the selected cells.
Use the text face tool to set the typeface (bold, italic, underline, etc.) for the selected cells.
Use the text color tool to set the color for the text in the selected cells.
Use the fill color tool to change the background color for the selected cells.
Use the border tools to set the color for the borders for the selected cells.
Use the number formatting tool to set the text or number format for the selected cells.
When you have a particular set of borders, fonts, and colors setup, make a style.   Later when you want to apply the same qualities to other cells, you only need to select the style and all the formatting is done for you. 
buttonnameselection.gif (944 bytes) Named selections are an efficient way to refer to groups of information in a spreadsheet.  You can select any number of cells in the spreadsheet and assign a name to them.  That name can be used later in formulas.  For example, you could select the entire B row, assign it the name "January".  Later, rather than creating a complex formula to add up all the values in the row, enter the formula "=SUM(January)".  The "January" name is the same as referencing all the cells in row B.

See the Named Selection topic in the spreadsheet section for more information. 

Resizing Columns and Rows

There are two ways to resize a column or row. 

  1. In the row or column header, "grab" the border and move the column or row to the appropriate size.

That method is good for one column or row.  For a more precise way to resize a selection of columns or row:

  1. Select the rows or columns you want to resize. 

  2. From the Format menu select Row Height or Column Width, depending on what you want to resize.



  3. Enter the width or height (in pixels). 

  4. Check Use Default to return the row or column to the original, default size (72 pixels).

  5. Click OK.

  6. The selected rows or columns are resized. 

Number Formatting

Not only can you format the look of text and numbers in a cell, but you can define the type of information that is stored in the cell.  Using the spreadsheet created earlier, consider the following example.

  1. Select all the cells where dollar values are placed.  From the previous example, this would be cells B3 - E7. 

  2. Click the Number Formatting button on the PartBar. A dialog box is displayed.

Using this dialog box, you can select a pre-defined number or text format for the selected cells.  For our spreadsheet, we want currency values (since the data in the selected cells represents money.)

  1. Select Currency from the Categories list

  2. Select the $1,234 | -$1,234 option.

  3. Check the Use 1000 Separator option.  This instructs the application to place a comma (,) between each thousand in a number.

  4. Enter 2 in the Decimal Places field.

  5. Click OK.

Notice how the values all now appear as currency amounts.  Try typing in some negative and very large numbers.  Notice how a format is automatically applied to those numbers.

Number formatting is very useful for defining the type of information that is in a cell or range of cells.  You might want to experiment with this feature a little more to familiarize yourself with the categories and formats available.

Formulas and Functions

The most important task for a spreadsheet is calculating values.  The Gobe Productive suite includes numerous statistical, mathematical, data, and financial functions.   In this task, you will create a simple formula and use a function.  For further help with functions and formulas, please see the Function Reference topic in the Spreadsheets section. 

The most common formula is a sum formula or a total formula.  This formula merely adds up all the values in a column or row of data.

NOTE: All formulas begin with an equal sign (=).  This tells the spreadsheet that the cell contains a formula and not text or values. 
  1. Using the previous example, select the cell directly below a column of numbers.

  2. Type =SUM(

  3. Using the mouse, select the cells directly above the total cell. Notice that the range is automatically placed in the formula. 

  4. Position the cursor back in the input field (in the PartBar)

  5. Type ) to close the parenthesis. 

  6. Press the [ENTER] key.  Your formula is finished. 

Pretty easy.  Now try the same thing for the other columns until you have a total line for each column in the spreadsheet.

TIP:  Rather than type the same formula over and over again, copy the formula cell and paste it into a new column.  The spreadsheet uses relative references.  When you paste it into a new column, it automatically adjusts the references within the formula to apply to the new column. 

Next, try using a simple function.  Say you wanted to know what the average expense was for all the categories.

  1. Position the cursor in the cell below the total formula.

  2. Click the Functions button located on the PartBar. The Paste Function dialog box is displayed.

    pastefunction.gif (15596 bytes)

  3. Select the Average function and click Paste.

  4. The function and arguments are pasted into the Entry Field for the selected cell.

  5. You can either edit the function arguments directly, or use the mouse to select the range of cells you wish to average.

  6. Press [ENTER] to confirm the data entry.  The cell displays the average calculation.

You might want to try experimenting with other functions.  If you know the function name, you can type it into the cell rather than using the Paste Function dialog box.

Printing a Spreadsheet

See the Printing a Document topic for more information about how to print. 

Conclusion

In this topic, you learned how to select cells, format cells, enter formulas, select functions.  For more information about the features of Gobe Productive spreadsheets including a full function reference, see the Spreadsheet section

NOTE: Keep the example spreadsheet you designed in this topic.   You can use it in the next topic to create a chart.

Copyright © 1999, Gobe Software, Inc