This chapter is devoted to the hours of ninth week. By having some information and experience about the document preparation for publishing or for web sites of Internet it is now time to mention about the spreadsheets. We emphasize on the spreadsheet facilities of StarOffice which is in a good compatibility with the Microsoft Office Suite. Some screen snapshots are also included inside the document.

Spreadsheet

     A spreadsheet is composed of rows and columns such that the rows are usually numbered while the columns are assigned alphabetical letters. The elemantary units which are positioned at the intersections of rows and columns are called cells. Each cell has a distinct and unique cell adress like A1, B4, or D15. You can place a value which is a number, or a label which is a heading or explanatory text. The value may also be a hidden formula that performs a calculation. A formula can involve constants like 3 +5 however the most useful formulas contains cell references like C10+E23. The placement of formulas into the cells enables us to create even a complex network of links among the parts of a spreadsheet. After embedding the formulas it is possible to adjust constants and to see how the bottom line changes. The concepts of spreadsheet comes from accountant's worksheet and includes generally a graphic representation. Hence, a spreadsheet usually requires a graphic interface like Windows or X Window. In computer science terminology, a spreadsheet program simulates an accountant's worksheet on screen and has the capability of the performing hidden formulas.

     The paper spreadsheets were used for business data and its process long before the invention of the computers. The beginning of the computer age facilitated the creation of these structures. The first computer spreadsheet was called VisiCalc. It was used on Apple II computer at the and of 1970s. The most widely used spreadsheet have been Lotus I - II - III and Excel since they were available for the abundance of personal computers although more powerful spreadsheets became available for Unix and recently Linux operating systems.

     Now we can itemize the main features about spreadsheets even by repeating some statements above.

1. The data of the spreadsheet is arranged in rows and columns. Columns and rows are labeled by letters and numbers respectively. Each data location in the spreadsheet is called cell. Numbers or letters can be entered in a cell as below.

2. When a formula entered the computer automatically evaluates the value defined by the formula and displays it in the cell where the formula was entered. For example we can evaluate the revenue of the sales of cereal in the above spreadsheet by entering the formula +B2*C2 where + specifies the entered thing as a formula while * denotes multiplication in cell D2. The resulting display is as follows.

     When a formula is entered the result of the formula will be displayed by the spreadsheet instead of the formula itself. However it is possible to see the content of the formula at the top of the screen when the cell pointer into the cell where the formula entered. This also enables us to edit the formula if necessary.

3. You can copy formula in one cell to the other cells. For example, the formula in cell D2 in the above spreadsheet can be copied to cells D3 and D4. This results in the following spreadsheet display.

     When a formula is copied from a cell to other one it is automatically readjusted by the spreadsheet according to the new cell address. For example, in the above example, the formula becomes +B3*C3 when it is copied from the cell D2 to the cell D3.

4. A formula may contain some built-in functions. For example the function SUM can be used to sum all cells within a range specified in the command.

5. The spreadsheets are important because the formulas are recalculated when a new value is given for one of the numbers.

6. The graphics can be automatically created for a given data by the spreadsheet.

7. MACROs can be used to combine a series of keystrokes into one command. There is possibility of constructing advanced macros which are themselves computer programs to establish interaction between the user and the spreadsheet and to process data.

8. The spreadsheets were originally two dimensional whereas the new versions now in- volves the three dimensional graphic capabilities.

     In the following sections we deal with the StarCalc which is a publicly free spreadsheet under StarOffice Suite. It is very similar to MSExcel and covers almost all features of MSExcel and in good agreement with it. Hence, here we emphasize only on StarCalc. The user which gets an increasing experience on StarCalc will be able to equivalently use the MSExcel.

StarCalc

CREATING SPREADSHEETS WITH STARCALC

Opening A New StarCalc Spreadsheet

     The easiest way to open a new spreadsheet is to click on the start menu and then choose spreadsheet for the pop-up menu. The other way is to choose New from the File menu and then spreadsheet from the submenu. Also on the desktop there is an icon for New Spreadsheet. Double-clicking on that icon will open a new spreadsheet.

     In StarOffice there are a collection of templates for things such as: yearly/monthly calendar, loan calculation, amortization schedule, inventory, timetable, schedule. When using templates you just enter the data and print, no need for formatting. You can find templates under the File menu/ New. From New you can choose the template you want to use. You can also click on More in this menu to see more about the templates.

Opening An Existing StarCalc Spreadsheet

There are several ways you can access an existing document:

* Choose Documents from the Start Menu and select a document from the list

* Choose Open from the File Menu and select a StarCalc spreadsheet file choose OK.

* If you have stored your spreadsheet on the StarOffice desktop double-click on its icon.

* Use the Explorer window to browse your Workplace or the Work Folder. Find your

spreadsheet and double-click on it.

Saving Your Spreadsheet

     To save your spreadsheet, choose Save As from the File Menu. Select a directory using the file/directory browsing window and enter a filename for the spreadsheet. Thenpress OK and the file will be saved. After this when you want to save again you can use Ctrl+S or you can use Save from the File Menu. You can also click on the disk icon onthe Function toolbar.

Printing Your Spreadsheet

Setting the Print Area

     When you have several screens of data, you might want to just print aportion of the whole sheet. You can select an area of your spreadsheet as the Print Area. To define a Print Area:

- Select a block of cells within the spreadsheet; Click and drag the mouse to select the desired area OR, Hold down the Shift key and use the arrows to enlarge and select an area.

- Choose Print Range from the Format Menu.

- Choose Set from the Print Range menu.

     After this procedure if you select print only the selected portion will be printed. If you want to print the entire sheet you can choose Clear in the Print Range menu. If you want to explore Print Area options select Edit from the Print Range menu.

Using the Fit-to-Page Feature

     When you prepare a spreadsheet, you want to print itonto a certain number of pages not ending up with pages with a single column or a row. To set how many pages a spreadsheet is to print on:

Choose page from the format menu,

Choose Sheet tab in the Page dialog box;

In the Scale area of the dialog box, click on the maximum pages option;

Enter the number of pages on which to print;

Select whether you want to print the pages from top to bottom or from right to left.

Turning Grid Lines On or Off

The following options can be selected in the Page dialog box, in the sheet tab:

* Column and Row Headers : Include labels on columns.

* Grid : The grid in the spreadsheet is made up of vertical and horizontal lines that separate cells.

* Formulas : Prints the formulas that are used instead of just the results.

* Notes : You can add a note to any cell in the spreadsheet ( Choose Note from the Insert Menu ). Notes are added to your pages as footnotes.

* Charts : Charts can add a lot visually.

* Zeros : This option enables you to have StarCalc indicate in your printed pages the cells in which a zero value occurs. ( Itis important to distinguish between cells that have zero value and the cells that are just empty ).

Choosing the Page Format

You need to decide the overall page layout youwill be using. You can decide on landscape or portrait depending on the layout of the spreadsheet. To set these options:

Choose Page from the Format Menu

Click on the Page tab in the Page dialog box.

In the Paper format part of the dialog box, check that the page size drop-down list displays Letter.

To the right of the paper size field, click on the Landscape or Portrait buttons to change the print orientation.

Review other options such as page margins and page numbering

Choosing OK will apply your changes.

SETTING SPREADSHEET OPTIONS

To view or set the spreadsheet options, select Options from the Tools Menu and choose Spreadsheet from the submenu.

Choosing What to Display on Screen

    A spreadsheet contains a lot of information behind what you see on screen. From the Contents tab of the Spreadsheet Options dialog box, you can select parts of the spreadsheet to be displayed onscreen. You can also select some display options from the Layout tab of the Spreadsheet Options dialog box. The layout options define the display and color or gridlines, and whether screen elements such as the scroll bars, multisheet tabs, and page break lines are displayed.

Setting Input Options

    In the Input tab of the Spreadsheet Options dialog box, you can set preferences for how you use the keyboard to enter data in your spreadsheet. When you are entering a lotof data, it can help to set where the focus goes when you press Enter or whether pressing Enter in a cell starts Edit mode for the contents of that cell or just moves to a new cell. All these items can be set with the check boxes in the Input tab.

Adding a Grid to a Spreadsheet

     In the Grid tab of the Spreadsheet Options dialog box, you can choose to have grids as lines or as closely spaced dots or even no grids at all. To use no grids, select The Visible grid check box.

Setting Calculation Preferences

     The Calculation tab of the Spreadsheet Options dialog box enables you to set how formulas in your spreadsheet are to be calculated. If you want toallow iterative formulas, check the Iteration check box. This enables self-referring formulas that reach a final value by a process of refining the cell value.

     The Decimal places field is the default number of decimal places to display in a cell.

     The check boxes in the lower half of the dialog box are used for sorting and searching operations.

ENTERING SPREADSHEET DATA AND NAVIGATION IN STARCALC

Moving with the Mouse or Keyboard

    The spreadsheet usually has a certain cell that has focus. The cell with the focus has a heavy black outline around it and it is the active cell where you can enter data. You can move around the focus by using the mouse and clicking on the cell you want to focus, or you can do it by using the four arrow keys on the keyboard.

Understanding Cell References

    A single cell reference consists of the column and row. Ex: D13. If you move things around StarOffice will update this cell reference, but if you use $ signs like $D$13, these signs make the cell reference an absolute reference. It means that StarOffice will not updateif things are moved around.

Naming a Cell or Range

    If you are working with a large spreadsheet, by naming a cell or a range of cells you can jump to a precise location in your spreadsheet with one click. Tonamea cell or block of cells and then jump to them at any time: Select a cell or a group of cells to be the named point to which to jump. Select Names from the Insert menu and choose Define from the submenu. In the Name field enter a name for the area to which you want to jump. Choose OK. Now youhave defined a name for the cell or the group of cells.

Selecting a Range of Cells

    As you work with a spreadsheet, youwill often want to select a large block of cells in order to print it, give it a name, format it or perform some other operations on it. The easiest way to select a block of cells is to click on one corner of it with the mouse and drag the mouse to the other corner. You can also use the keyboard to select a block of cells. Hold down the Shift key while using other keys like: all four arrow keys, PageUp and PageDown, Alt+PageUp and Alt+PageDown.

Selecting Rows or Columns

     You can select an entire row by clicking on the row label. Also you can select an entire column by clicking on the column label. Any action that you take ( formatting, changing width etc. ) apply to every cell in the row or column that youhave selected.

WORKING WITH SHEETS

    You can split your information across several sheets that are part of a single spreadsheet. Also you can use features such as hiding sheets to arrange and protect the information on each sheet.

Naming a Sheet

    When you start a new spreadsheet, the three sheets are named Sheet1, Sheet 2 and Sheet3. If you want to change the name of a sheet:

    Go to sheet that you want to rename by clicking on its tab or pressing Ctrl+PageDown.

    From the Format Menu choose Sheet.

    From the Sheet submenu, choose Rename. Rename dialog box appears.

    Enter the new name you want to use

    Choose OK

    The name that you enter appears on the sheet tab at the bottom of the spreadsheet.

Adding Another Sheet

    To add another sheet to the spreadsheet:

    Choose Sheet from the Insert Menu.

    Enter a name for the new sheet in the Name field.

    Choose OK

Protecting a Sheet

    You can protect information in your spreadsheet in several ways:

     Hiding a sheet from view without password protection, Protecting a sheet from being altered Protecting specific cells in a sheet from being altered Preventing certain cells from being printed when the spreadsheet is printed To hide a sheet choose Sheet from the Format Menu and then choose Hide from the Sheet submenu. To see that sheet again choose Show from the submenu and select the sheet you hid.

     All the cells in a StarOffice spreadsheet are marked as read-only by default. If you turn Sheet protection on, you cannot modify any cells in the spreadsheet. To unprotect a few of the cells in your spreadsheet so that they can be modified even when the Sheet Protection is on:

     Start by entering information in several cells of your spreadsheet

     Select the cells that you want to modify when Sheet Protection is turned on

    Choose Cells from the Format Menu and select the Cell Protection tab

    Uncheck the Protected checkbox

    Choose OK

    Hiding Cells

    From the Cell Protection tab of the Cell Attributes dialog box under the Format Menu, you can select the Hide All checkbox to make cells invisible.

ENTERING DATA IN YOUR SPREADSHEET

Entering Numbers, Dates and Text

   Here are a few rules that youll see as you enter data:

    If you enter text StarOffice capitalizes the first letter and uses left justification within the cell

    If you enter a number StarOffice displays it right-justified with the default two digits after the decimal

    If you enter a date StarOffice displays it in a standard format and marks the cell as containing a date.

    When entering numbers although the display formatting constrains how numbers are shown in the spreadsheet, the cell actually stores the full number that you enter.

Cutting, Pasting and Moving

    Information in spreadsheet cells can be deleted, copied and pasted.

    When youare working in a spreadsheet, you can use several ways to delete things.

    When you have ablock of cells full of information, you can delete the contents of the cells: By using the Delete key or the Delete Contents item on the Edit menu. You can also use the Undo item from the Edit menu to reverse a delete operation. By pressing Backspace key.

    And place them in the Paste buer. Use the Cut item on the Edit menu or press Ctrl+X. So that other cells move up or over to fill the space left by deletion. Use the Delete Cells option on the Edit menu. If you want to copy information use Copy from the Edit menu, or press Ctrl+C. This is similar to Cut operation but the information that is copied is not deleted.

    When you paste cells that you have cut or copied, you can decide which pieces of the structured information you want to place in the new location. Select Paste Special from the Edit menu. In the Paste Special dialog box you can select which types of information are to be pasted. If you use the regular Paste item on the Edit menu or Ctrl+V everything is pasted.

USING FORMULAS, FUNCTIONS AND NAMES LABELS AND FORMULAS

Every cell in a spreadsheet can contain a number, a text or a formula as its data. Formulas are entered into a cell to make StarOffice calculate something.

Entering Cell References

Cells in a spreadsheet are defined as a letter-number combination that refers to a row and column. 11 Absolute references can also be used. ( Ex: D13 ) A range of cells that are referenced are defined as follows: ( D13: R55 )

Entering Numbers and Labels

    Open a new spreadsheet and enter some information to see how all this works: Click on a cell to place the focus on that cell. Type a word as a title and press Enter. When you press enter the cursor moves to the below cell. Now type numbers as your data.

Editing a Cell

    Sometimes you will need to return to a cell and make some changes on the data. If you move the focus to a cell and start typing, you will erase the contents of that cell and replace them with what you are typing. If you want to edit the contents but not erase, press F2 key. You can also double-click on the cell for editing.

ENTERING FORMULAS

    A formula cell is any cell in which StarOffice must do some calculations before displaying a numeric or text value. To place a formula in a cell, start your entry with an equal sign (=). To make a formula useful, another cell is usually referred to. ( Ex: 4*C55or B6+B7+B8 )

USING FUNCTIONS

Spreadsheets also provide a set of tools called functions that make calculations a lot

easier. A function takes a cell or a set of cells as a parameter and does some calculations

on those cells to produce a value.

Using Functions to Simplify Formulas

Sometimes a formula can be too long to write and instead of using a formula you

can use functions. For example there is a formula like =B6+B7+B8+B9+B10. You can

shorten this formula using a function: =sum (B6: B10).

12

Finding Functions

By using the StarOffice Function AutoPilot, you can review a list of functions by

category and what the function does and what parameters to use.

Editing a Function

You already saw how you can press F2 or double-click on a cell to edit the contents of

that cell, including cells that contain a formula you have entered. The Function AutoPilot

has a partner dialog box called the Edit Function dialog box that can help you edit any

function. StarOffice detects if the cell you choose already contains a function. The Function

AutoPilot dialog box is opened and the function is displayed.

Using the Mouse to Enter Cell References

Instead of typing in cell references or using the Function AutoPilot dialog box, you

can also use the mouse to drag across an area of cells that you want to use in a function.

For example if you want to use the function B11=sum (B6:B10) click on cell B11. Type

sum ( and do not press enter. Click and hold the mouse button on the cell B10 and drag

the mouse pointer up to the top to B6. Release the mouse button and the operation by

pressing ) to close the sum ( ) and press enter. You can use this technique for any function

that uses a cell as a parameter. ( You can also use the keyboard arrows with Shift to select

a block of cells while entering a formula. )

Recalculating Your Spreadsheet

StarOffice automatically recalculates everything in your spreadsheet as you enter new

or revised numbers in cells, but your spreadsheet is a large one it slows down your work.

To turn o the auto-calculate, select Cell Contents from the Tools menu and then choose AutoCalculate from the submenu. Uncheck this feature. Pressing F9 any time will recalculate the spreadsheet. You can give the recalculate command by choosing Recalculate from the Cell Contents submenu.

FORMATTING YOUR SPREADSHEET CHANGING FONTS AND SIZES

    When you are viewing a spreadsheet, most of the text formatting options you will use are included in the Object Bar: Font name, Font size, Bold/Italic/Underline, Justification (center, left, right), Vertical Alignment (top, center, bottom), Number Format, Border type and background colors, Decimal places. All these options can also be set using menu items in the Format menu.

    To set a font and size for example for the column headings you can follow these steps:

    Select the entire block of column headings using the mouse. In the Format menu choose Cells. From the Cell Attributes dialog box choose Font tab.

    Select a font from the list under the Font field in the dialog box.

    Select a style from the list under the Style field.

    Select a size from the list under the Size field.

    Choose OK to apply your choices to the selected cells.

    It is usually easier to use the Object toolbar:

    Select a font from the drop-down list. Select a font size from the drop-down list. Set a font as bold, italic or underlined by clicking on the corresponding button. Set the justification for the selected cells to make themcentered, right- , left- or fulljustified. You can also make them top, vertical center or bottom aligned.

SETTING NUMBER FORMATS IN CELLS

     Formatting also determines how numbers in cells are arranged according to the information they represent. Setting a number format for a cell determines how the number entered in that cell is displayed.

Using the Object Bar to Set Number Formats

    There are two important buttons important for number formatting. One of them is the Currency button which puts dollar signs before the number and two decimal places at the end. The other button next to the Currency button formats numbers as percentages.

Adjusting Decimal Places

    You can adjust how many decimal places are displayed. Two buttons on the Object Bar, Increasing decimal and Decreasing decimal button, increase or decrease the number of decimal places on all selected cells. These buttons do not just cut o the number, but they round the number.

ADJUSTING AND INSERTING ROWS AND COLUMNS

    Setting row height and column width enables you to fit the spreadsheet to the data that you have entered. Inserting and removing rows and columns enables you to add andremove unused space to make your data fit the spreadsheet.

Adjusting Row Height and Column Width

   Row height adjusts automatically to the tallest characters on the row. But the column width has to be adjusted manually to accommodate longer numbers or text labels. The easy method of adjusting the column width is to use the mouse: Move the mouse pointer to the right edge of the column letter label. When the mouse pointer changes to a side-by-side double-headed arrow, click the mouse button and move it to the right for a wider column or to the left for a narrower column. Then release the mouse button. Row height can also be set in the same way. Also you can use the sizing dialog boxes under the Format menu. Choose Row or Column and then Height (for rows) or Width (for columns). In these dialog boxes you can reset the size to the default value or you can enter a specific numeric value.

Inserting and Deleting Rows

   You can use copy and paste functions to move data around but sometimes the easiest way is to insert a row or column rather than trying to move everything to a new location. To insert additional rows or columns: Click on the row or column label Drag the mouse downward or rightward until the wanted number of rows are selected. Choose Rows or Columns from the Insert menu. The bottom part of the table moves down and three rows appear. To delete a complete row or column: Select a complete row by clicking on the label for that row or column. Choose Delete Cells from the Edit menu.

ADDING CHARTS AND GRAPHICS TO SPREADSHEETS

Importing a Graphic File

To add a graphic to a spreadsheet:

Move to the cell in which the upper left corner of the graphic image is to be positioned.

Choose Picture from the Insert menu. Then choose From File from the submenu. The

Insert dialog box appears.

Browse through the filesystem until you find the graphic you want to insert.

Select the Preview checkbox and click on a listed filename once to view it in the

Preview window; double-click the filename to choose and load it.

The graphic is loaded into the spreadsheet and appears as a selected item.

Editing an Imported Graphic

    After you load a graphic you can move and edit it in many ways. To select a graphic, click onceanywhere on the image. To move the graphic click and drag it to a new location. To resize the image, select it then click and drag on a green corner handle.

CREATING A CHART

    StarOffice has the capability to create charts from the information you enter in the spreadsheet cells. It provides many types of charts with many options.

Selecting a Data Set

    Use the mouse to click and drag over a block of cells to select the data that you want to graph. (Do not forget to include labels.) Answer the questions that appear in a series of dialog boxes. You can edit the chart after it is created.

Starting a Chart

    With a data area selected choose Chart from the Insert menu. The AutoFormat Chart dialog box appears. First part of the dialog box enables you to indicate the location of the chart. The options in the first part are: E The Area field: Uses cell reference notation to describe the block of cells that you selected before. The sheet name and corner cells define the block of information being charted. The Location where the chart will be placed: Select the sheet in which to place the chart. Label checkboxes: Check if the labels of the columns and rows are selected correctly.

Selecting a Chart Type

    Select Next and move to the second part of the dialog box. From this dialog box choose the type of chart you want to use. Chart types are: lines (horizontal), areas (horizontal lines filled in underneath), columns (vertical bar graph), bars (horizontal bar graph), pies (pie chart), XY diagram, net, lines (3D), areas (3D), bars (3D), columns (3D), pies (3D).

Labeling Your Chart

    If you want to include a title at the top of your chart, leave the Chart Title checkbox selected and enter a name of the chart in the text entry field. If you want to include a legend on your chart, leave the Yes button selected in the Add legend section. If you want titles on the axes, select the X axis or Y axis checkbox. Also you can select a checkbox to indicate whether the data series that you are graphing are in rows or in columns.

MODIFYING AND FORMATTING A CHART

Selecting a Chart to Modify

    When you click on a chart, it becomes a selected object. When a chart is selected you can use the handles to resize the chart, or click and drag in the middle of the chart to move it anywhere in the spreadsheet. To modify the chart you must have the chart in edit mode. For this, double-click on a chart.

Reviewing the Edit Options on a Chart

    You can access a piece of the chart to format it in one of these ways:

    Double-click on a part of the chart when it is in edit mode. Click on a part of the chart to select it and then click the right mouse button to select from a submenu of editing options.

    Click on one of the buttons on the Main toolbar to use a chart editing tool.

    The following items can all be edited by double-clicking on the chart or using a toolbar button:

    Scale text in the chart

    Edit the chart's title

    Change the grid layout

    Edit a data point for color or labels

    Set the color of the chart area

    Select rows or columns as the data series

    Change the cells that make upthe chart

    Turn the vertical or horizontal grid on or off

    Turn axis labels on or off

    Turn the legend on or off

    Turn the title on or off

    Set the color, position, and character type of the legend

    Turn on labels for individual data points

    Set the scale, number style, and line and color style for each axis.

Changing a Color in the Chart

    To alter the colors used for a data set:

    Select the chart for editing.

    Double-click in the top color box within the legend.

    The Data Row dialog box appears. Choose the Area tab.

    From the transparence drop-down list choose an option. The Preview area shows the changes in color.

    Choose OK to apply your changes.

Screen Snapshots