Calc

Prepared by Colin Charles

byte@aeon.com.my

http://www.bytebot.net/

Creative Commons License

Attribution

Noncommercial

Share Alike

Attribution-NonCommercial-ShareAlike 1.0

You must give the original author credit.

You may not use this work for commercial purposes, without the explicit permission

of the author.

If you alter, transform, or build upon this work, you may distribute the resulting

work only under a license identical to this one.

Full legal license: http://creativecommons.org/licenses/by-nc-sa/1.0/legalcode

http://creativecommons.org/licenses/by-nc-sa/1.0/

If you would like to use this for commercial purposes, please write to Colin Charles, at

byte@aeon.com.my or at PO BOX 8188, Monash University LPO, Clayton, Victoria, 3168, Australia.

Quick Start

File -> New -> Spreadsheet

Tools -> Options

configure Spreadsheet & Chart

measurement units, what the Enter key does (move/switch to Edit mode), calculation accuracy, sort lists, change control, grid setup

Tools -> Configure

what's on the status/function bar

Entering Text & Numbers

If text is wider, re-align it

Right-click cell -> Format Cells

Alignment -> Line Break

Hyphenation as an option

Filling

exactly the same data

incremented data

Before applying line break

After applying line break

Use mouse to

drag downwards

Selection Lists

Displays all text entered within a column

Right-click

here

Columns & Rows

Resizing rows/columns are done via clicking & dragging

Insertion/Deletion of rows/columns happen by right-clicking the cell

Click to select

entire column

Click to select

Entire Row

Click to select

entire sheet

AutoFormat

Select the area, Format -> AutoFormat

Select an

AutoFormat

that you'd

like to use

Cell Formatting

Set the Font Color

Set the Background Color

Set Borders around cell

Quick number formatting to

cells can be applied by clicking

on the Currency or Percent

icons on the Object bar.

Currency

Percentage

Decimal control

to add/remove

decimal places

Page Setup & Breaks

Format -> Page

Page options (like A4 size), etc...

View -> Page Break Preview

Non-scrolling horizontal/vertical regions

Select area, Window -> Freeze

Splitting views

Select area, Window -> Split

Styles

Exists in Calc too

Cell Styles – all elements, cell formatting included

Page Styles – margins, borders, headers/footers

Themes can be easily applied

Calculating & Manipulating Data

Dmaths - http://www.dmaths.com/

Writing formulas & vectors simplified

Remember, left-to right calculations, with algebraic ordering rules

=5+15*2-12/3+4 gives 15 or 35?

Functions are powerful

=A1+A2+A3+A4 -> =SUM(AI:A4)

Click the Sum icon too

Sum

Function AutoPilot

Wizard to help build formulas

AutoPilot

The actual

formula

being built

Enter values

here or use

the shrink

icon

Sorting Data

Select cells you want sorted

Data -> Sort

Set sort options, Sort!

Can sort by one option, then by another (depending on how complicated the sort is)

Apply the sorting

by Amount, and

use Ascending

amounts in this

example

Filtering Data

AutoFilter – displays only the data you want to see

Select cells, click the AutoFilter icon, then click the drop down heading

Select an option, and the data is filtered

Conditional Formatting

Format -> Conditional Formatting

Remember to set Styles for the formatting to work

Conditional Formatting has

been applied with the

Heading 1 style, and the

text has changed.

The

condition

What to do?

Combining cells

Simple concatenation:

=”Age: “&B1

More “advanced” usage includes:

=CONCATENATE("He is ";B1;" now, and will be ";SUM(B1+1);" next year.")

When would you merge cells?

A call to SUM() was performed.

Charts

Select cells, click Insert Chart

Give it a position; follow on-screen instructions

Double-click chart to go into Edit mode

Right-click on chart

Chart wall & area

AutoFormat Chart Wizard

Protecting cells

Tools -> Protect Document -> Sheet/Document

Enter a password (and remember it)

Right-click on the cell(s), Format Cells, and in the Cell Protection tab, tick “Protect”

Scenarios

Select cells

Tools -> Scenarios

Give it a name

Click OK

Value can now be changed within the sheet

Repeat to add more scenarios

Scenarios are viewable in the Navigator

Goal Seek

Finds unknown variables

Tools -> Goal Seek

Setup

the formula

Enter expected

target value

When it runs, it

finds an appropriate

figure if successful

Other stuff...

Revision control is like Writer

Inserting Notes

Click cell, Insert -> Note

Type away

Help -> Tips must be enabled

Else note is not visible

Red dot

denotes

note

On mouse over,

the note is

displayed

Printing

Many options to control

Repeating rows across pages

select row to repeat

Insert -> Names -> Define

give it a name, and select “Repeat row”

Format -> Print Ranges -> Edit

under the “Rows to repeat” section, choose the name provided earlier

Thank You!

Colin Charles

byte@aeon.com.my

http://www.bytebot.net/

Screenshot © Erwin Tenhumberg,erwin.tenhumberg@Sun.COM

You are free:

to copy, distribute, display, and perform the work

to make derivative works

And...

For any reuse or distribution, you must make clear to others the license terms of this work.

Any of these conditions can be waived if you get permission from the author.

Keyboard Shortcuts

Backspace – deletes contents of cell

F9 – recalculates the spreadsheet (Tools -> Cell Contents -> AutoCalculate)

Home – move to first cell in row

End – move to last cell in row, corresponding to the last column

Shift+F4 – absolute & relative cell references

Shift+F5/F6 – trace dependents/precendents

Ctrl+PgUp/PgDown – move to previous/next sheet

File-format Compatibility

Microsoft Excel files, Lotus 1-2-3, CSV based files, HTML based files as well as dBase files can be opened.

Saving to all the formats listed above is allowed, except for Lotus 1-2-3.

When importing CSV/Tab-separated files, its a little different in comparison to Excel (in terms of the delimiters used).

As long as pre-requisite knowledge exists about spreadsheet usage, inserting into spreadsheets is not a new idea.

Filling in exactly the same data, you just need to Control+click the cell, and drag in the direction you want to fill it in.

If filling by incrementing data is the aim, click the bottom right-hand corner of the cell, and drag it down till you want the incremented data to stop.

Selection Lists display all text entered within a column, for re-use. Right-click a cell, and choose Selection List. Now, you can input text that's already been inputted earlier (great for repetitive text).

Just type your numbers/text

into an appropriate row/column

Relative & Absolute Cell References

A1 – relative reference, which is good for automatic fill across rows.

$A$1 - absolute reference to the column and row, good for referencing cells that will never change location.

$A1 – absolute reference to the column and relative reference to the row.

AutoFormat only works when you have more than three rows and three columns. Anything less than that, and AutoFormat is disabled in the menu.

Clicking More, shows the Formatting overrides that will be applied to the spreadsheet. If you feel that keeping the Font similar, un-tick the Font option, so that your fonts stay the same and will not get over-written by the AutoFormat.

Exercise: Create your own AutoFormat

Start by formatting a spreadsheet the way you want it to look. A tip is that no data needs to be included, just row, column and cell formatting (well, having data helps visualize it).

Select the formatted area, choose Format -> AutoFormat. Click on Add, give it a name, and click OK. You will now have a new AutoFormat to use.

Select cells, right click them, and use the Format Cells option. Here you can control the cell attributes, like fonts, font effects, alignment, borders, and so on.

An option to enable cell protection exists as well.

Splitting views can be handy in a large spreadsheet, or on a monitor with a very low resolution.

Re-sizing the split window is performed by moving the cursor over the split line until it changes shape.

The button for themes ( ) can be found at the main toolbar.

Some basic operators:

= - all formula must begin with this (=2+4)

^ - exponent for power raising (=10^2)

* - multiply

/ - divide

+ - add

- - subtract

: - range of included cells (=sum(A1:A5), means cells A1, A2, A3, A4, and A5)

; - non-consecutive range of cells (=sum(A1;A4;A7), means sum of cell A1+A4+A7)

Remembering BODMAS (control via parentheses) is handy.

Some Boolean operators:

< - less than

> - greater than (=if(A5>B5;”This is going well”;”Happy I am!”)

<= - less than or equal to

>= - greater than or equal to

<> - not equal to

Control + click to include non-adjacent cells while you're making use of the Sum icon.

Great for prototyping functions, as the results are shown in the Function result field.

The Functions tab lists all available functions, while the Structure tab shows the structure of the formula in question.

The shrink icon is allows you to select cells via the mouse, rather than typing them in manually.

The Options tab has plenty of settings. Default sorts are case insensitive – turning this on if need be is an option.

Changing the direction of sorting is also a useful option.

If the AutoFilter icon doesn't exist, Data -> Filter -> AutoFilter will perform exactly the same action.

In the above example, the entire Whom column was selected, the AutoFilter was applied, upon choosing, the entry “Colin” was chosen, and the subsequently displayed data was “Colin 19 FOSSAP”. All the other data gets “hidden”, when the AutoFilter is in use.

Concatenation is one of the functions that Calc offers.

Exercise: Merging cells

Input some data, that you concatenated before – using the example above is fine.

Select the cells, Format -> Merge Cells -> Define. Notice now that the value becomes text?

Un-merging via Format -> Merge Cells -> Remove will not bring the formula back for you!

This is a warning to show that merging cells where there are active formulas is probably a bad idea.

When inserting a chart, the process is rather simple. First give it a position, by clicking anywhere in the spreadsheet (it can be in another Sheet as well). The AutoFormat chart pop-up appears, showing the selected range. You might want to make sure the First column as label and the First row as label options are ticked (so you get a “nice” legend”). Select the type of chart (scatter, pie, bar, etc...), select where grid lines should be, give it a title, and voila! you have just created a chart.

The toolbar for Edit mode is displayed in the left hand side.

The AutoFormat Chart Wizard is very useful – you can make extensive changes to the chart here, including changing the type of chart, backgrounds, and others.

Used when trying to achieve a goal. In the example provided, with an expected value from the lots you own, to make a profit, the formula is setup in F2. Since the aim is to make a profit, you need to know the value to dump the lots at – so this is the variable cell. The target value is what you hope to achieve (profit).

Remember, to put all data in a separate cell.

The logical break up of information in spreadsheets is essential.

If calculated values are incorrect, check all entered values correctly; if it involves formulas, make sure you check the parentheses usage.

Use Value Highlighting to see different sets of elements within a spreadsheet – View -> Value Highlighting

Things you can change include:

Page style window – Format -> Page Style

Print Options window

Cell Attributes window (like keeping protected cells from being printed)

Print Ranges – Format -> Print Ranges

Page breaks

Colin Charles

2004-02-22T21:28:58

Colin Charles