Select File → Open from the Main menu, choose the desired file in the dialog window, and click the Open button.

Alternatively, you can open this dialog window more quickly from the keyboard by pressing Ctrl+O.

The third way to open a spreadsheet file—either a StarCalc file or a Microsoft Excel file—is simply to click on the file icon. StarCalc launches with the file open and is ready to go.

To save the current file to its current folder location, simply select File → Save from the Main menu.

Alternatively, from the keyboard, simply press Ctrl+S.

If you need to select a new folder into which to save the file (such as when saving a new file for the first time), select File → Save As . . . from the Main menu. Choose the folder location and the filename in the dialog and press the Save button. (See Figure 8-1.)

Figure 8-1. The Save As dialog

To export the current spreadsheet into the Printable Document Format (PDF), select File → Export as PDF . . . from the Main menu. Optionally, select the target destination folder in the Export dialog, enter or change the filename if you choose, and press the Export button.

Alternatively, you can export directly as PDF, using the dedicated Export to PDF icon on the toolbar. Clicking the icon opens the same Export dialog as the procedure just described.

This procedure generates a PDF file from the current document and stores it in the designated file on your filesystem. This document is viewable with the PDF Document Viewer on your system (Adobe Acrobat Reader 5.0 for Linux) and by any other user with Adobe Acrobat Reader 5.0 and above.

StarCalc offers convenient ways to send spreadsheets attached to an email message.

Send as PDF

To send the current file attached to an email message as a PDF file, select File → Send → Document as PDF Attachment . . . from the Main menu. This opens the PDF Options window (Figure 8-2), where you can select a page range and compression amount, or leave the options alone and just press the Export button. This action calls up the Email and Calendar program and simultaneously opens a new message window with the current spreadsheet file attached. Enter an address, subject, and body and send it as you would a typical email message.

Figure 8-2. The PDF Options window

Entering numbers into a cell is straightforward. Simply place the cursor in the target cell, type the desired number, and press the Enter key.

Note that numbers appear flush-right in a cell by default.

To enter a label in a cell—that is, a word rather than a number—place the cursor in the target cell, begin the character sequence with a ' (single quote) character, finish typing the rest of the characters, and press the Enter key.

For example, the column heading “Sales” would be entered as 'Sales, followed by the Enter key.

Labels appear flush-left in a cell by default.

This feature is designed to speed up common repetitive tasks. Autofill permits you, in one stroke, to fill in either a column or row of numbers that automatically increase by one. This is often useful when first creating a new table or spreadsheet. In the case of labels, Autofill simply repeats the label across the cell range you specify.

For instance, after entering the year 2004 (as a number and not as a label) in cell B-3, simply highlight the cell by clicking it once. At this point, you will see a small black square at the bottom-right corner of the cell. Grab the square with a left mouse click and, with the left mouse button pressed, drag the square across the cells you want to fill with numbers. Release the mouse, and the numbers will fill in consecutively. Figure 8-3 shows the result of this autofill procedure, in which the default action is to automatically increase the numbers by one in sequence. The result is similar when autofilling a column of numbers.

Figure 8-3. Autofill saves time-consuming repetition

Formulas begin with an equal sign (=). To calculate the result of 1+1, for example, type =1 + 1 and press Enter.

To calculate a result based on other cells, type = in the cell where you want the result to appear, then click on the first cell in the formula. This highlights the cell in a red outline. Type an operator such as + and click on the second cell. This highlights that cell in a red outline. You can keep entering as many operators, followed by cells or other values, as needed. Finally, press Enter and the result appears in the target cell.

Using the example in Figure 8-4, with the cursor in cell B9, type =. Next, click on cell B5, and type -. Next, click on cell B7 and press the Enter key. The result appears in cell B9.

Figure 8-4. Formulas begin with the equal sign

Note that the Formula Field in the above figure contains the formula we just described, =B5-B7. The alternative way of creating the same formula is to simply type it directly into the Formula Field. First, click once on cell B9. Then click once on the empty Formula bar and type =B5-B7. Press the Enter key. The result is the same.

To quickly sum an existing column of numbers, highlight the target result cell with a single click. Then, click the Sigma icon on the Formula bar. This automatically highlights in blue the most likely nearby column of numbers to be summed. (See Figure 8-5.)

Figure 8-5. Clicking upon the Sigma icon

If the highlighted group is appropriate, press the Enter key and the result appears in the target cell. (See Figure 8-6.) If you find you have not chosen the set of cells you wanted, you can grab the small blue square at the bottom right of the highlighted column and adjust the grouping to the precise numbers you want to sum. Then press the Enter key.

Figure 8-6. The Sigma is for summing

It’s easier to move a range of cells in StarCalc than it is to move a single cell entry. This task is the one that gives the most people trouble when they are adjusting to the new environment of StarCalc, but it is quite simple once you’ve done it once or twice.

To move a range of cells, simply highlight the range by left-clicking in one cell at an extreme corner of the range, and, while holding the left mouse key down, drag the mouse pointer across the rest of the cells in the range. When the whole range is blackened, release the left mouse key. Go back with the mouse pointer and make a single mouse click anywhere in the blackened range to grab the range and move it to its new location. Drop the range of cells in its new location by simply releasing the left mouse button.

Moving a single cell entry requires the same procedure, but highlighting a single cell usually proves troublesome for new users. That’s because the highlight motion with the left mouse button requires the user to left-click on the cell, move the mouse pointer outside the cell and back, release the mouse button, and then go back to grab and move the highlighted cell.

To change the width of a column, bring the mouse pointer up into the grid’s column headings, labeled A, B, C, etc. Note how the mouse pointer changes to a double horizontal arrow when it rolls over any column divider.

While the arrow is visible, simply move it to the right or left to increase or decrease the width of the column immediately to the left of the divider.

To put a column back to its default width, right-click on the column heading to call up the Column Width dialog. Check the empty box labeled “Default value” and press the OK button. The column now snaps back to its default width (0.89 inches).

To adjust the height of a row, apply the procedure shown earlier for adjusting column width, but with the mouse cursor on the tops or bottoms of a row heading, at the left edge of the page.

To restore a row’s default height, apply the procedure shown earlier for restoring the default column width, but at the left edge of the page on the desired row heading.

To merge multiple cells together, first highlight the group of cells you want to merge, then select Format → Merge Cells → Define from the Main menu. This creates one cell that contains the contents of the cells in the range you highlighted. StarCalc’s recognition of data can be quite sophisticated. For instance, if one column contains Jun and another contains 3, the date 06/03, followed by the current year, appears in the merged cell.

Most spreadsheet users find that a few cell-formatting commands carry them through most of their work.

The Object Bar

The quickest way to format numbers and labels in the cells of a spreadsheet is to use the formatting buttons across the Object bar, shown in Figure 8-7. Most office tool users are familiar with the Bold, Italic, and Underline buttons, as well as the justification and simple number formatting buttons.

Figure 8-7. The Object bar

If the formatting choices offered by the Object Bar prove too limiting, apply more customized formats through the Format → Cells . . . path off the Main menu. This displays the Format Cells dialog box, with a bewildering range of formatting options, just a few of which are discussed in the following sections.

Underlining a cell

To underline an entire cell or range of cells, the Borders palette is the best tool. Highlight the range you wish to underline, then click on the Borders icon on the formatting toolbar to open the Borders palette, and click on the underline button in the palette, as illustrated in Figure 8-8.

Figure 8-8. Borders palette is the tool for underlining cells

Tip

Note in Figure 8-8 how the activated underline icon is highlighted on the Object bar and has a dark outline around it (the outline is blue on your screen). This visual cue is used consistently across the Object bar to indicate that formatting features are active wherever the cursor presently rests.

Underlining a cell label

Underlining a cell label is different from using Borders to underline the entire cell. To underline a cell label, highlight the label you want to underline and click the underline icon on the formatting toolbar (just as with typical word processors). (See Figure 8-9.)

Figure 8-9. Underlining a label

Cell background color

Perhaps you’d like to dress up a simple table of numbers or make the bottom line you’re trying to emphasize stand out by giving the cell backgrounds a little color. This is easily done by invoking the Format Cells dialog. First, highlight the range of cells you’d like to color. From the Main menu select Format → Cells . . . and click the Background tab, shown in Figure 8-10. Choose a color on the color palette with a single click and click the OK button.

Figure 8-10. Format Cells dialog; Background tab selected

Figure 8-12 shows our table fully dressed with cell backgrounds, set according to our taste. (Sorry you have to see it in gray, in the printed book, instead of in riotous color.)

Formatting numbers

When you first enter numbers in a cell, they have no formatting. To indicate a unit such as dollars ($12), or dollars and cents ($12.43), you need to apply currency formatting or another type of number formatting. You can also conform to various local standards such as formatting data with commas to separate thousands.

To format some of the data in thousands, first highlight the desired range to format, then from the Main menu, select Format → Cells . . . . This opens the Format Cells dialog, where you should select the Numbers tab by clicking on it. Figure 8-11 shows the variety of formatting options available to you.

Figure 8-11. Number formatting

Select Number in the Category list at left, then choose your desired number format from the Format list in the center. For our purposes, we want -1,234. That done, click the OK button, and the numbers in your designated range appear with the new formatting.

Tip

Note that in Figure 8-12 we chose to vary the number formatting only in the Gross Margin row. Using currency formatting sparingly can make the table easier to read by removing clutter.

Figure 8-12. Varying number formats is communicative

You can see by browsing through the Format Cells dialog box that there are many facilities for applying custom formatting to cells and characters in them. The dialog box offers the following tabs, which are also visible in Figures Figure 8-10 and Figure 8-11:

• Numbers

• Fonts

• Font Effects

• Alignment

• Borders

• Background

• Cell Protection

It’s hard to navigate through larger spreadsheets because the column and row headings disappear out of view. The Window → Freeze and Window → Split commands permit you to lock column and row headings into place, while scrolling to view other sections of the spreadsheet.

To lock down your column and row headings, click on the cell where you want the freeze to take effect and select Window → Freeze from the Main menu. This will put a check mark on the Freeze item on the drop-down menu and lock the columns to the left of the highlighted cell, as well as the rows above the cell. The spreadsheet initially shows just lines to outline the frozen cells, as can be seen in Figure 8-13.

Figure 8-13. Freezing the column and row headings

Now you can move down and to the right. Note in Figure 8-14 how Row 1, with all column headings, stays fixed and visible as you move down the spreadsheet. A similar effect takes place with the Column B row headings at the left if you scroll through the spreadsheet at the right.

Figure 8-14. Row 1 is frozen

Another interesting way to leave parts of your spreadsheet visible is to choose Windows → Split, instead of Window → Freeze. Now you can click on any pane and scroll it. The pane you clicked on moves, along with one of the panes next to it, depending on whether you scroll up and down, or right and left. The other two panes stay still.

To remove the Freeze or Split settings, simply click the checked selection on the drop-down menu, and the freeze or split lines go away.

Page Break View offers a detailed view of the current spreadsheet’s page breaks for printing. To turn on Page Break View, select View from the Main menu and click on Page Break View in the drop-down menu. This sets a check mark at the selection. To turn off Page Break View, uncheck this selection on the drop-down menu.

Figure 8-15 shows a spreadsheet in normal view, just as Page Break View is about to be checked. Figure 8-16 shows the same spreadsheet with Page Break View turned on. Each page’s number appears as a light gray watermark at the center of each page as it comes out of the printer.

Figure 8-15. Spreadsheet in normal view

Figure 8-16. Spreadsheet in page break view

You can quickly set or adjust page breaks by dragging the outside blue lines to cover the desired range, and just as easily move the page-dividing lines to include the desired columns and rows on the proper printed page. Page Break View also offers a way to view and navigate larger spreadsheets from a higher perspective.

When you create a new spreadsheet from scratch, it has no print range set. Such a spreadsheet appears gray when in Page Break View. To set a print range for your spreadsheet, make sure Page Break View is turned on, then highlight the full area you want to print by clicking on the cell in one corner and dragging the mouse pointer across the entire range. Finally, select Format → Print Ranges → Define. Any spreadsheet content that’s outside the range you set this way is not printed.

If there is a print range already defined and you need to adjust it, simply grab the corner of the blue outline (or just grab a side) with the mouse and stretch it to include all the desired cells of your new print range.

To “grab,” first move the mouse pointer over the blue outline of the print range and you see the mouse pointer turn to a bidirectional arrow. The arrow permits you to drag the print range blue outline to a different place, simply by clicking and dragging the line to the desired location.

StarCalc has a full array of function types, including:

StarCalc’s functions, their syntax, and their required formats are well documented in the Help drop-down menu off the Main menu. Select Help → Contents and the Help window opens up. Then, in the Index tab at the “Search term” field, type functions, and press the Enter key. Here, you can double-click on the name of a function in the left pane to view the information about that function. Figure 8-17 illustrates the Help Index and information on the financial function called PV, which calculates the present value of a stream of regular payments or cash flows. PV is a spreadsheet function that’s understandably popular with MBAs and bankers.

Figure 8-17. The PV (present value) Function

When entering a function into a cell, always remember to precede the entry with an equal sign (=). The example, offered in Figure 8-18, indicates in the formula field what the PV function formula looks like when it is correctly typed into a cell, and the necessary information for the function is properly cell-referenced:

               =PV(B1;B2;B3)  

Figure 8-18. A common mortgage problem, solved

It is possible also to enter numbers as well as cell references into the body of a function. In the Formula Field, this looks like:

               =PV(.0042;360;-1500)

However, using cell references leaves room for easily trying alternative inputs or for generating a sensitivity analysis, using a range of choices for one variable.

In overall quality, StarCalc’s ability to create and render graphs needs improvement, but its functionality is adequate for the simplest types of graphs. The example, shown in Figures 8-19 through 8-23, demonstrates the creation of a simple bar graph from a table of numbers.

In Figure 8-19, we create a graph based on a simple table with a column of labels (the different operating systems employed by users of OpenOffice.org) and a column of numbers (the number of responses indicating use of the respective operating system). We want to show the relative scale of responses for each category, so it’s clear that a bar graph is suitable for this purpose. Generally, the graph type you select is dictated by the kind and amount of data you are portraying and by the most important point(s) you need to communicate. We exclude the totals line, in this case, because its inclusion distorts the scale and interferes with the graph’s main purpose of the comparison of category responses. Totals information, if relevant to an audience, may be included in a textual footnote to such a graph.

Figure 8-19. Creating a graph from a table of numbers

First, highlight the range of data to be included in the table. Figure 8-19 illustrates the desired data range already highlighted. For this particular graph, it’s necessary to include the column headings as labels but exclude the totals row at bottom of the table. We are excluding the totals line for this type of graph because its inclusion interferes with the primary message of comparison of responses for each category. It also changes the scale of the graph and seriously disarranges the presentation of information.

Next, from the Main menu, select Insert → Chart. This opens the AutoFormat Chart wizard. (See Figure 8-20.)

Figure 8-20. The AutoFormat Chart wizard

In this particular case, it’s necessary to check the two check boxes, labeled “First row as label” and “First column as label” to enable the wizard to reference the proper axis labels automatically. Note that in the Range field of the AutoFormat Chart wizard, your predefined range has been picked up by the wizard, so you don’t need to enter or adjust it. Click the Next button.

Here, make a few additional settings to allow the AutoFormat Chart wizard to create an accurate and informative chart. This particular table offers data series in rows (it’s a series of one, so don’t be confused), so the proper setting, in this case, is to click the radio button for “Data series in: Rows,” as shown in Figure 8-21. Also notice in Figure 8-21 that we have checked the box for “Show text elements in preview.” This provides a WYSIWYG (What-You-See-Is-What-You-Get) view of the bar graph in progress, visible over on the left side of the AutoFormat Chart dialog in Figure 8-21. If the graph or text looks incorrect at this juncture, click the back button to adjust some settings or pick a different chart type, if necessary. Click the Next button.

Figure 8-21. Additional chart settings

This takes you to the dialog for labeling the main chart title and the axes, and provides a check box to include or exclude the legend. (See Figure 8-22.)

Figure 8-22. Entering the chart title and labels

In this case, we chose to show the legend (the Legend box is checked) because the colors in the legend indicate which platform is represented by each bar in the graph. Figure 8-22 shows the dialog after the main title, “OpenOffice.org User Platforms,” has been written into the Main Title field. (See Figure 8-23.)

Figure 8-23. The bar graph is complete

Finally, click the Create button at the bottom right corner of the dialog, and the graph appears in the live worksheet. You can now adjust the size or placement of the graph by grabbing one of the gray edges to move one of the black squares .

One StarCalc spreadsheet file (sometimes called a “Workbook”) contains three sheets by default but can hold up to 256 sheets in total.

Figure 8-24 shows the three sheets of a standard, default spreadsheet file. In the figure, note from the white coloration of the sheet tab that sheet 1 is live, or current. The gray coloration of sheets 2 and 3 indicate they are present but not visible.

Figure 8-24. Three sheets to the wind

To move among sheets, simply click on a sheet tab, and it becomes the live sheet.

Adding a new sheet

To add a new sheet, right-click on the sheet area, or any one of the sheet tabs, to call up the menu, shown in Figure 8-25.

Figure 8-25. The menu for manipulating sheets

Then select Insert Sheet . . . from the menu, and the Insert Sheet dialog box appears. Here, you can designate the names, positions, and number of the new sheets. Note that you can add multiple sheets. You can also bring in sheets from another file; after you browse and select a file, the names of its sheets are displayed for you to choose from.

Navigating among many sheets

If you have a spreadsheet with many sheets, like the one illustrated in Figure 8-26, not all the tabs are visible at the bottom. To make a tab visible so you can select its sheet, you need to use the sheet navigation arrow buttons at left of the sheet tabs.

Figure 8-26. A file with 17 sheets

By clicking the arrow buttons, you can move the sheet tabs over one by one. The buttons that have arrows and thin vertical bars allow you to jump all the way to the first or last tab. The arrow buttons change according to context: when you are on the left-most tab, the arrows to move right are highlighted and available. The converse is true when you are on a sheet toward the right extreme.

To sort a list or chart of numerical or textual information, first highlight the full range to be sorted (including labels, but excluding unwanted data such as totals) and then select Data → Sort from the Main menu. This launches the Sort dialog box, where you can designate the sorting order among other parameters.

In the case, as illustrated in Figure 8-27, we want to reorder the data to put the largest responses at the top. Therefore, in the Sort dialog, we select to sort by the “Responses per Platform” column (where the numbers are) and set the radio button at the right to Descending. Then we press the OK button. Notice how rearranging the order of the source chart automatically registers the new order in the bar graph that was previously generated. (See Figure 8-28.)

Figure 8-27. Sorting a simple table

Figure 8-28. Table (and graph) successfully sorted

Instead of having its own database format, StarCalc is designed to interact with many different varieties of external databases. Data Sources is the name for StarCalc’s powerful feature set for interacting with databases and for linking forms and reports to information contained in databases. StarCalc offers a variety of ways to link to a MySQL or Adabas D database, for example, or many other data sources—including MS Outlook, Outlook Express, Mozilla, and your JDS address book in the Email and Calendar program.

In StarCalc, call up the Data Source View by choosing Tools → Data Sources . . . from the Main menu, or simply by pressing the function key, F4. Press F4 again to close the Data Source View.

Having made such a promising introduction, it’s regrettable to say that Data Sources is outside the scope of this book. It’s unfortunate because interacting with databases is becoming more relevant to the web-enabled desktop user. Furthermore, StarOffice’s database interactivity is a hot focus of development activity and promises to get stronger as well as easier for the average user to handle, with each progressive release of the StarOffice software.

To learn more about Data Sources in StarCalc and across all modules of StarOffice, look here: http://docs-pdf.sun.com/816-5405/816-5405.pdf.

Creating or handling macros in StarCalc is not within the scope of this book. However, we can offer some general information that may be useful to macro users. Macros could come into play for all the different modules of StarOffice (and MS Office), but here we deal strictly with their relevance to StarCalc (and MS Excel).

StarCalc uses its own macro-scripting language called StarOffice Basic (or “StarBasic”). This is a different macro language from the one used by Microsoft in MS Office, which is called Visual Basic (or “VBA”).

VBA macros are not able to run in StarCalc, creating a significant barrier for migration from MS Excel to StarCalc for users who have many large or significant VBA macros within their spreadsheets. VBA macros that come with MS Excel files currently must be rewritten in StarBasic for these files to be fully useful in StarCalc.

Sometime “early next year [2005],” Sun Microsystems promises to release a new Visual Basic-to-Star Basic macro conversion tool to facilitate the automatic conversion of VBA macros to StarBasic macros.

Meanwhile, StarCalc is set by default to save VBA macros to be available and written back, whenever a StarCalc spreadsheet file is saved again in the MS Excel file format. This offers three options:

Because VBA macros do not run in StarCalc, the viruses associated with them pose no threat, as long as you use StarCalc. If you want to leave off the macros (for security reasons or because you just don’t want them) when importing Excel files, turn off the default in Tools → Options → Load/Save → VBA Properties.

If you are interested in macros, feel free to consult the StarOffice Basic Programmer’s Guide at http://docs.sun.com/db/doc/817-1826?q=star+basic.