Chapter 5 StarOffice Calc
StarOffice Calc is a spreadsheet program that you can use to
calculate with data that contains text, numbers, date and time values, and
more. In a network environment, Calc supports multiple users working in collaboration
on the same sheet at the same time.
Opening a Sample Calc Spreadsheet
To Open a Sample Calc Spreadsheet
-
In any StarOffice program, choose File->New->Templates
and Documents
-
In the left pane, click Samples.
-
In the middle pane, double-click the Spreadsheets folder.
-
Select a sample spreadsheet and click Open.
Spreadsheet Basics
By default, a spreadsheet document consists of three sheets: Sheet1, Sheet2, and Sheet3. Each
sheet is divided into a maximum of 65536 rows and 1024 columns. Rows are labeled
with numbers, while columns are labeled with letters. The intersection of
a row and a column is called a cell.
A cell is identified by a reference that consists of the column letters
of the cell followed by the row number of the cell. For example, the reference
for a cell at the intersection of column A and row 2 is A2. The reference for the range of cells in columns A through C and rows 1 through 5 is A1:C5.
You can also include the file name and the sheet name in a reference
to a cell or to a cell range. You can assign a name to a cell or cell range
so that you can use the name instead of a column/number reference. For details,
search the application help for the term references.
Creating a Spreadsheet
To create a new spreadsheet from within any StarOffice program,
choose File->New – Spreadsheet.
Navigating in a Sheet
You can use the mouse or the keyboard to navigate in a Calc sheet or
to select items in the sheet.
To Navigate a Sheet With the Mouse
-
Use the horizontal or vertical scrollbar to move sideways or up
and down in a sheet.
-
Click the arrow on the horizontal or vertical scrollbar.
-
Click in the empty space on the scrollbar.
-
Drag the bar on the scrollbar.
Tip –
To move the cursor to a specific cell, click in the cell.
To Navigate a Sheet With the Keyboard
-
Use the following keys and key combinations to move around in
a sheet:
-
To move one cell down in a column, press the down arrow or Return.
-
To move one cell up in a column, press the up arrow.
-
To move one cell to the right, press the right arrow or Tab.
-
To move one cell to the left, press the left arrow.
Tip –
To move to the last cell that contains data in a column or row,
hold down Control when you press an arrow key. To move to
a cell by its address, enter the address in the Name Box above the column
A.
To Select Cells in a Sheet
You can use the mouse or the keyboard to select cells in a Calc sheet.
-
To select a range of cells with the mouse, click in a cell and
drag the mouse to another cell.
To select just one cell, Shift-click
the cell.
To select an entire row or column, click the label of the row or column.
To select all cells, click the button above row 1 and left of column
A.
-
To select a range of cells with the keyboard, ensure that the
cursor is in a cell, hold down Shift, and press an arrow
key.
Entering Data
The simplest way to add data to a sheet is by typing, or by copying
and pasting data from another Calc sheet or from another program.
To Enter Data in a Spreadsheet
-
Click in the cell where you want to add your data.
-
Provide the data.
Either type the data, or to paste
data from the clipboard into the cell, choose Edit->Paste.
-
Press Return.
You can also press
an arrow key to enter the data and move to the next cell in the direction
of the arrow.
Tip –
To type text on more than one line in a cell, press Control-Return at the end of each line, and then press Return when
you are done.
To Quickly Enter Consecutive Dates and Numbers
Calc provides a fill feature so that you can quickly enter a successive
series of data, such as dates, days, months, and numbers. The contents of
each successive cell in the series is incremented by one. 1 is
incremented to 2, Monday is incremented
to Tuesday, and so on.
-
Click in a cell.
-
Type the first item of the series (for example Monday),
and press Return.
-
Click in the cell and drag the fill handle until you highlight
the range of cells in which you want to enter the series.
The
fill handle is the small black box at the bottom right corner of the cell.
-
Release the mouse button.
The consecutive items in
the series are automatically added to the highlighted cells.
Tip –
To copy without changing the values in a series, press the Control key while you drag.
Editing the Contents of Cells
You can edit the contents of a cell or cell range in a sheet.
To Edit the Contents of Cells in a Sheet
-
Click in a cell or select a range of cells.
-
To edit the contents of a single cell, double-click the cell and
make the changes that you want.
Tip –
You can also click in the cell, type your changes in the Input
line box of the Formula bar, and then click the green check mark icon. However,
if you need multiline text with defined line breaks, you must double-click
the cell and edit the contents inside the cell. Press Control-Return for
a line break. You cannot enter line breaks in the Input line box.
Calc converts your input to what seems most appropriate. If your input
looks like a number, Calc formats it as a number. The same is true for input
that looks like a date and time. Start your input with a leading single quote
' if you do not want the automatic conversion to a number or date.
-
Press Return.
-
To delete the contents of the cell or the cell range, press Backspace or Delete.
Backspace deletes
immediately, Delete first shows a dialog box.
Formatting Spreadsheets
You can format your sheet manually or by using styles. Manual formatting
is only applied to the cells that you select. Style formatting is applied
wherever the style is used in your spreadsheet document.
Formatting Cells Using AutoFormat
The easiest way to format a range of cells is to use the Calc AutoFormat
feature.
To Apply Automatic Formatting to a Range of Cells
-
Select the range of cells that you want to format.
Select
at least a range of 3 x 3 cells.
-
Choose Format->AutoFormat.
The AutoFormat dialog
box appears.
-
In the list of formats, click the format that you want to use,
and then click OK.
Formatting Cells Manually
To apply simple formatting to the contents of a cell, such as changing
the text size, use the icons on the Formatting toolbar.
To Format Cells With the Formatting Toolbar
-
Select the cell or cell ranges that you want to format.
-
On the Formatting toolbar, click the icon that corresponds to
the formatting that you want to apply, or select an option from the Font Name
or Font Size drop-down lists.
To Apply Manual Formatting With the Format Cells Dialog
Box
If you need more formatting options than the Calc Formatting toolbar
provides, use the Format Cells dialog box.
-
Select the cell or the range of cells that you want to format,
and then choose Format->Cells.
The Format Cells dialog box
appears.
-
Select one of the tabs and choose your formatting options.
- Numbers tab
-
Provides options to change the formatting of numbers in cells,
such as changing the number of decimal places that are displayed
- Font tab
-
Provides options to change the font, font size, and typeface
used in the cell
- Font Effects tab
-
Provides options to change the color of the font, and the
underline, strikethrough, or embossed effects of the text
- Alignment tab
-
Provides options to change the text alignment and text orientation
within cells
- Borders tab
-
Provides options to change the border of cells
- Background tab
-
Provides options to change the background fill of cells
- Cell Protection tab
-
Provides options to protects cell contents inside protected
sheets.
-
Click OK.
Formatting Cells and Sheets With Styles
In Calc, the default formatting of cells and sheets is done with styles.
A style is a set of formatting options, that defines the appearance of the
cell contents as well as the layout of a sheet. When you change the formatting
of a style, the changes are applied wherever the style is used in your spreadsheet.
To Apply Formatting With the Styles and Formatting
Window
-
Choose Format->Styles and Formatting.
-
To change the formatting of cells: Click in a cell or select a
range of cells.
-
Click the Cell Styles icon at the top of the Styles and Formatting
window.
-
Double-click a style in the list
-
To change the layout of a sheet: Click anywhere on the sheet.
-
Click the Page Styles icon at the top of the Styles and Formatting
window.
-
Double-click a style in the list.
Using Formulas and Functions
You can insert formulas in a spreadsheet to perform calculations for
you.
If the formula contains references to cells, the result is automatically
updated whenever you change the content of the cells. You can also use one
of the many predefined formulas or functions that Calc provides to perform
your calculations.
Creating Formulas
A formula starts with an equal sign (=) and can contain values, cell
references, operators, functions, and constants.
To Create a Formula
-
Click in the cell where you want to display the result of the
formula.
-
Type =, and then type the formula.
For example, if you want to add the contents of cell A1 to
the contents of cell A2, type =A1+A2 in
another cell.
Tip –
You can also start with the + or - key on the number keypad.
-
Press Return.
Using Operators
You can use the following operators in your formulas.
Table 5–1 Operators for Calc Formulas
|
Operator
|
Name
|
Example
|
Result
|
|
+
|
Addition
|
=1+1
|
2
|
|
-
|
Subtraction
|
=2–1
|
1
|
|
*
|
Multiplication
|
=7*9
|
63
|
|
/
|
Division
|
=10/2
|
5
|
|
%
|
Percent
|
=15%
|
15.00%
|
|
^
|
Exponentiation
|
=3^2
|
9
|
|
=
|
Equal
|
=8=8
|
TRUE
|
|
>
|
Greater than
|
=7>9
|
FALSE
|
|
<
|
Less than
|
=5<6
|
TRUE
|
|
>=
|
Greater than or equal to
|
=3>=4
|
FALSE
|
|
<=
|
Less than or equal to
|
=42<=63
|
TRUE
|
|
<>
|
Not equal to
|
=6<>4
|
TRUE
|
|
&
|
Text concatenation
|
=“Sun” & “day”
|
Sunday
|
Example 5–1 Sample Calc Formulas
- =A1+15
-
Displays the result of adding 15 to the contents of cell A1
- =A1*20%
-
Displays 20 percent of the contents of cell A1
- =A1*A2
-
Displays the result of multiplying the contents of cells A1 and A2
Using Parentheses
Calc follows the order of operations when it calculates a formula. Multiplication
and division is done before addition or subtraction, regardless of where these
operators appear in the formula. For example, for the formula =2+5+5*2, Calc
returns the value of 17, and not 24.
Editing a Formula
A cell that contains a formula only displays the result of the formula.
The formula is displayed in the Input line box.
To Edit a Formula
-
Click in a cell that contains a formula.
Alternatively,
double-click a cell to edit the cell content directly.
The formula is displayed in the Input line of the Formula Bar.
-
Click in the Input line and make your changes.
To
delete part of the formula press Delete or Backspace.
-
Press Return or click 
on
the Formula Bar to accept the changes.
To reject the changes that
you made press Escape or click 
on
the Formula Bar.
Using Functions
Calc comes with many predefined formulas or functions. For example,
instead of typing =A2+A3+A4+A5, you can type =SUM(A2:A5). The Function Wizard assists in entering formulas with functions.
To Enter a Function
-
Click the cell where you want to add a function.
-
Choose Insert->Function.
The Function Wizard dialog
box opens.
-
In the Category box, select the category that contains the type
of function that you want to use.
-
In the Functions list, select the function that you want to use.
-
Click Next.
-
Type any required values, or click the cells in the spreadsheet
that contain the values that you want to use.
-
Click OK.
Tip –
For more information on Calc functions, including examples, see
the Calc help.
Using Charts
Charts can help you visualize patterns and trends in your numerical
data. StarOffice 9 provides several chart styles that you can use to represent
your numbers.
You can create a chart from numerical data in table cells of Calc or
Writer. When the values in the cells change, the chart also changes automatically.
Note –
Charts are not restricted to spreadsheets. You can also insert
a chart by choosing Insert->Object->Chart in the other StarOffice programs.
These charts are inserted with some default data, and you can then provide
your own data.
To Create a Chart
-
Select the cells, including the headings, that contain the data
for the chart.
For a contiguous data area, you need only click
into the area. No manual selection is necessary.
-
Choose Insert->Chart.
The Chart Wizard appears,
showing a live preview of the chart using the current settings.
-
Select a chart type.
-
Click Finish.
Editing Charts
After you create a chart, you can change, move, resize, or delete the
chart.
To Resize, Move, or Delete a Chart
-
Select the chart.
If the chart is not selected, click
the chart once, so that you see eight big handles around the chart. If the
chart is in edit mode, first click outside the chart, then click the chart.
-
Resize, move, or delete the chart.
-
To resize the chart, move your mouse pointer over one of the big
handles, hold down the mouse button, and drag the mouse.
Calc
displays a dotted outline of the new chart size as you drag.
-
To move the chart, move your mouse pointer inside the chart, hold
down the mouse button, and drag the mouse to a new location.
-
To delete the chart, press Delete.
To Change the Appearance of a Chart
You can use the context menu inside a chart to change the properties
of your chart.
-
Double-click a chart to put it in edit mode.
-
Right-click any chart element.
A context menu is
displayed.
-
Choose a command to change the element.
Download this book in PDF (2072 KB)