introduction to calc - img kerala · introduction to calc ... the purpose of this tutorial is to...

1

Introduction to Calc

A spreadsheet similar to Microsoft Excel with a roughly equivalent range of features. Calc provides a number of features not present in Excel, including a system which automatically defines series for graphing, based on the layout of the user’s data. Calc is also capable of writing spreadsheets directly as a PDF file.

The Word Spreadsheet

The term spreadsheet was derived from a large piece of paper that accountants used for business finances. The accountant would spread information like costs, payments, taxes, income, etc out on a single, big, oversized sheet of paper to get a complete financial overview.

Spreadsheets Today

Some examples of spreadsheet usage:

• Spreadsheets act like a calculator by automatically doing calculations. • Spreadsheets are used for tracking personal investments, budgeting, invoices, inventory

tracking, statistical analysis, numerical modeling, address books, telephone books, printing labels, etc.

• Spreadsheets are used in almost every profession to calculate, graph, analyze and store information.

Open A Spreadsheet

IF on the desktop, click > All Programs > OpenOffice.org 2.1 > OpenOffice.org Calc. IF you are in OpenOffice.org Writer, click File > New > Spreadsheet. (The spreadsheet called

appears on our screen.

The Calc Toolbars

The following four Calc Toolbars appear at the top of all Calc screens

Main Menu Toolbar

The first toolbar is the Main Menu toolbar that gives you access to many of the basic commands used in Calc.

Function Toolbar

2

The second toolbar down is the Function Toolbar. The Function Toolbar contains icons (pictures) to provide quick access to commands like New, Open, Print, Copy, Paste, etc. When you place your mouse cursor over any of the elements of a toolbar, the name of the element appears on your screen.

Move your cursor over the icon . (The word “New” appears. Clicking on opens a new spreadsheet.)

Formatting Toolbar

The third toolbar down is the Formatting Toolbar. The Formatting Toolbar has icons plus drop-down menus that allow you to select a font, font color, alignments, number formats, border options and background colors.

Formula Toolbar

The fourth toolbar down is the Formula Toolbar. The Formula Toolbar contains the Name Box drop-down menu and a long white box called the Input Line. Note: If your Toolbars look different, it is because these toolbars are in 800x600 screen resolution

and the last eight icons are not shown but are available by clicking on the on the far right of the toolbar.

The Spreadsheet Itself

3

The rest of the window contains the spreadsheet. The spreadsheet is divided into rows that have a number at the left of each row and divided into columns with letters at the top of each column.

Cells

A cell is the fundamental element of a worksheet. This is where things are added and where things are seen. A cell address in a spreadsheet identifies the location of the cell in the spreadsheet. A cell address is a combination of the column letter and the row number of a cell, such as A2 or B16.etc. When identifying a cell by its address, the column letter is always listed first followed by the row number. The cell address of the example below is A5.

Enter Data

1. Click on the A1 cell (The cell at the very top left of the spreadsheet). 2. Notice the heavy black border around the A1 cell. The heavy black border indicates that

A1 is the active cell. (A is at the top of the first column and 1 is on the first row. Both are highlighted. The highlighting also indicates that A1 is the active cell.)

3. Type Hello World and press Enter. 4. The active cell is now A2. (The words “Hello World” are in A1.)

4

5. When you type something in a cell and press Enter, what you typed is seen in that cell and the cell below becomes the next active cell.

Delete Data

1. Click on A1 again. 2. Press the Delete key. (The “Delete Contents” window appears.)

3. Check the Delete all box then click OK. (“Hello World” is deleted from A1)

Add A Column Of Numbers For The Total

1. In A1, type 5, press Enter. 2. In A2, type 5, press Enter, 3. In A3, type 5, press Enter, 4. In A4, type 5, press Enter 5. In A5, type 5, press Enter. 6. Click In A6, the last cell in the column of numbers you want to add. ("A6" has a heavy

dark border and is the "Active Cell." )

7. Click on the Sum, Σ, ("Σ" is the mathematics symbol for sum.) icon on the Formula

Toolbar just left of the Input Line The Function, , icon changes to the Accept, , icon.

8. Click the Accept, , icon (green check mark) or press Enter. The formula, =SUM(A1:A5), appears in the A6 cell.

5

9. Press Enter. The sum, 25, will appear in A6. A7 becomes the active cell.

Cell Range

In the section above, the SUM function appears as =SUM(A1:A3). The A1:A3 in the parenthesis is called a Cell Range. It is shorthand for "from A1 to A3".

Purpose

The purpose of this tutorial is to explain how to use math in spreadsheets.

Addition - The + sign is used for addition. To enter the + sign, press the Shift key and the +,

,key.

1. Click on A6. 2. Type the number 10, then press Enter. ("10" appears in "A6" and the active cell is "A7".) 3. Type the number 5, then press Enter. ("5" appears in "A7" and the active cell is "A8".)

4. Type =A6+A7 (Or click the “Function”, , icon. The “=” appears in “A8'. The “Function”,

, icon changes to the “Accept”, , icon. Then type "A6+A7".).

5. Press Enter (or click the “Accept”, , icon). The answer, 15, appears in A8.

Subtraction - The - sign is used for subtraction. To enter the – sign, press the key.

1. Click on A8. To clear cell A8, press the Delete key. (The “Delete Contents” window appears.) Check the Delete all box and click OK.

2. Type =A6-A7 (Or click the “Function”, , icon. The “=” appears in “A8" The “Function”,

, icon changes to the “Accept”, , icon. Then type "A6-A7".).

3. Press Enter (Or click the “Accept”, , icon.) The answer, 5, appears in A8.

6

Multiplication - The star ,*, sign is used for multiplication. To enter the * sign, press the Shift key

and the number 8 key.


2. Type =A6*A7 (Or click the “Function”, , icon. The “=” appears in “A8”. The “Function”,

, icon changes to the “Accept”, , icon. Then type "A6*A7").


Division-The slash, /, sign is used for division. To enter the slash, /, sign, press the key [? And /].


2. Type =A6/A7 (Or click the “Function”, , icon. The “=” appears in “A8'. The “Function”,

, icon changes to the “Accept”, , icon. Then type "A6/A7".).


Exponentiation

The number 102 is written as 10^2. To enter the ^ sign, press the Shift key and the number 6,

, key.

1. Click on A9.

2. Type =A6^A7 (Or click the “Function”, , icon. The “=” appears in "A9". The “Function”,

, icon changes to the “Accept”, , icon. Then type "A6^A7".).


Enter Formulas

When you type in formulas, the order of operations must be correct. The correct order is: exponentiation, multiplication, division, addition, then subtraction. Let's pretend we want to perform a calculation using the equation:

x + 10

y + 20

and suppose we entered it into Calc as follows:

1. Select the A column. (Click on the letter "A." The whole "A "column is selected) 2. Press Delete. (Make sure the "Delete all" box is checked.) 3. Press OK. 4. Click on A1. 5. Type 20 in A1 and press Tab. 6. Type 10 in B1 and press Tab. (Cell C1 becomes active)

7

7. Type the formula =A1+10/B1 + 20.(Or click the “Function”, , icon which changes to the

“Accept”, , icon. Type "A1+10/B1+20").

8. Press Enter (Or click the “Accept”, , icon. The answer, 41, appears in C1.)

The answer should be 1, using x = 20 and y = 10, but we got 41 for an answer! Why? Because Calc interpreted the equation as:

20 + (10/10) + 20

which equals 41. If you use formulas with equations in the numerator and denominator, you need to use parenthesis () to force the spreadsheet to calculate them properly. Make the following changes:

1. Click on C1. 2. Click on the Input Line. 3. Edit the formula to read =(A1+10)/(B1+20). (Click just before the A, then Press the ( key

[Shift-9]. Click after the 0 in 10, then press the ) key [Shift-0]. Click just before the B, then Press the ( key [Shift-9]. Click after the 0 in 20, then press the ) key [Shift-0]).

4. Click the Accept, , icon (green check mark.) The correct answer, 1, appears in C1.

Copy Formulas

You can copy a formula so that the formula itself is copied or that the answer to the formula is copied.

Copy The Formula Itself

1. Select (click on) the cell(s) that is to be copied. 2. Right-click, then click Copy. (The material is copied to the clipboard) 3. Select (click on) the cell where the material is to appear. 4. Right-click, then click Paste.

Copy The Answer of a Formula

1. Select (click on) the cell(s) that has a formula. 2. Right-click, then click Copy. (The material is copied to the clipboard) 3. Select (click on) the cell where the material is to appear. 4. Right-click, then click Paste Special. (The “Paste Special” window appears) 5. Remove the check mark at Paste all. 6. Remove the check mark at Formulas and add a check mark at Numbers. Click OK.

This tutorial explains how to use columns in spreadsheets.

A check register with columns (check number, payee, item description, withdrawal, deposit and balance) is created to help explain columns.

Additional information about columns is added at the end of this tutorial.

Open A Spreadsheet

1. IF on the desktop, click > All Programs > OpenOffice.org 2.2 > OpenOffice.org Calc.

8

2. IF you are in OpenOffice.org Writer, click File > New > Spreadsheet.

3. In either case, the spreadsheet called appears on our screen.

Add A Label To Columns

1. Make A1 the active cell. (Click in cell A1) 2. Type Check No then press the Tab key (The cursor moves to B1) 3. Type Paid To then press Tab. (The cursor moves to C1.) 4. Type Description then press Tab. 5. Type Deposit then press Tab. 6. Type Withdrawal then press Tab. 7. Type Balance then press Enter.

Insert An Additional Column

Insert a Date column in the first column.

1. Select column A. (Click on the "A" at the top of the column. The column turns black.) 2. Click Insert > Columns. (The cell contents in columns "A" through "F" shift to the right

and becomes columns "B" through "G". Column "A" cells are empty.) 3. Select A1. 4. Type Date then press Enter.

Change The Appearance of Column Labels

Center The Column Labels

Use An Icon From The Formatting Toolbar

1. Select cells A1 through G1 by dragging the mouse from cell A1 to cell G1 (Click on A1 and without releasing the click, move the cursor over B1, C1 etc until the cursor is in cell G1. Release the click)

2. On the Formatting toolbar, click the Align Center Horizontally icon. (The column labels become centered)

Select “Bold” And “Light Blue” For The Column Labels

1. While the cells are still selected, move to the left and click the Bold, , icon.

2. Move to the right to the Font Color icon and pick Light Blue. (At the "Font Color", icon, click and hold the click until a choice of colors appears. Select [click on] the "blue" square). Press Enter.

Make Entries In the Check Register

9

Enter An Initial Balance:

1. Select A2. 2. Enter a date, 07/12/07. (type 07/12/07) 3. Select G2. 4. Enter 5000.

Format Columns For Currency

The Deposit, Withdrawal and Balance columns will contain dollars with a $ appearing in front of the numbers. All three columns can be formated at the same time.

1. Use Drag to select columns “E” through “G”. Click on E. (The cursor is at the top of column “E”) Press and hold down the left mouse button. Move the mouse pointer to G by moving the mouse. Release the mouse button. (Columns “E”, “F”, and “G” are highlighted)

Continue by using either of the following two methods:

Use A Icon From The Formatting Toolbar

1. Click the Number Format Currency, , icon. (The three columns will show the $ when they have numbers in them.)

Use Format From The Main Menu

This method is more complex than using the icon.

1. Select columns E through G.

2. Click Format > Cells... (The "Format Cells" dialog box appears.) 3. Click the Numbers tab.

10

4. In the Category box, click Currency. 5. In the Options section, make sure that Decimal places is set to 2, Leading zeros is set

to 1, and both check boxes are checked. 6. Click OK.

Adjust Column Width

Use "Optimal Width"

1. Select column C. (Click on "C".) 2. Click Format > Column > Optimal Width... (The “Optimal Column Width” window

appears) 3. Click OK. 4. Repeat steps 1-3 for column D.

Resize Cells Manually

5. Place your pointer over the column dividing line between the letters C and D.

6. When your pointer changes to , click the left mouse button and drag.

3. Size the column like you want it and release the left mouse button.

Enter Current Balance

The current balance is defined as the previous balance plus any deposits, minus any withdrawals. In equation form it looks like:

Current Balance = Previous Balance + Deposits – Withdrawals In the spreadsheet, the formula is written as =G2+E3-F3.

1. Click on cell G3. 2. On your keyboard, press the = key. 3. Click on cell G2, then press the + key. 4. Click on cell E3, then press the – key. 5. Click on cell F3, then press Enter. ($4,750 appears in G3)

Copy A Formula

Drag-to-Copy is a quick way to copy the contents of one cell to a neighboring cell or into a series of neighboring cells.

1. Select G3. 2. Look at the black frame around the cell and notice the little black box on the bottom right

corner.

3. Put the cursor over the little box. When you see a , click and drag it down to cell G4. ("-$2,250.00" appears in cell G4)

11

When you copy formulas, the cells referenced in the formula will change. Click on cell G3 and look at the Input Line that is just above the columns. You will see the formula =G2+E3+F3. Click G4 and you'll see the formula =G3+E4+F4.

All spreadsheet programs use relative addressing. The program does not store the actual cell address; rather, it stores something like the following in G3:

Cell G3 equals (=) one cell up (G2) plus (+) two cells to the left (E3) minus (–) one cell to the left (F3)

The program stores something like the following in G4:

Cell G4 equals (=) one cell up (G3) plus (+) two cells to the left (E4) minus (–) one cell to the left (F4)

Enter An Additional Row

You forgot to enter an earlier deposit. Enter that deposit now.

1. Click on the 4 that is to the left of 07/20/07. Keep the cursor on the 4, then do a Right-click, then click Insert Rows. (Row 4 cell contents shift to Row 5; Row 4 has empty cells.)

2. Click A4, enter 07/19/07. 3. Click D4 enter Paycheck. 4. Click E4, enter 20,000. (Wouldn't that be great!) 5. Drag-to-Copy the formula from G3 down to G5. (Put the cursor over the box at the

bottom right corner of "G3". When you see , click on it and drag it down to cell "G5".) ($17,750.00 appears in cell G5)

Rename Sheet1 And Save

1. Click Format > Sheet... > Rename... (The “Rename Sheet” window appears) 2. In the “Name” box, type Checking then click OK. (At the botton of the screen “Checking”

appears in place of “Sheet1” 3. Click File > Save As... In the “Save in:” pull-down menu, select My Documents 4. In the “File name:” box type Check Register and click Save.

Borders And Shading

Borders can be used to separate data, mark certain cells or anything else you want. They are typically used to draw attention or separate. Add some borders to the check register worksheet:

Select A Block Of Cells

Use either of the following two methods;

Use Drag To Select A Block Of Cells

1. In A1, click and hold the click, then move the cursor onto G25.

Use The Shift Key To Select A Block Of Cells

1. Click A1. Press and hold down the Shift key then click G25.

12

Add A Border

2. Click Format > Cells... 3. Click on the Borders tab. Find the User-defined box. Notice that there are four boxes

created by inward facing triangles. 4. Click to the left of the top left box . (Toward the middle of the box side.) (1) 5. Click between the top two boxes. (Toward the middle of the box sides.) (2) 6. Click to the right of the top right box. (3) You should have 3 vertical lines. Click OK.

7. Select A1:G1. (Click "A1". Drag copy to "G1".)

8. Click the Borders, , icon on the Formatting Toolbar. 9. Click on the second box from the left on the second row.

Add A Background Color

1. Click Format > Cells... 2. Click the Background tab. 3. Click on Gray 20%. (Use tool tips to find it.) 4. Click the Borders tab. 5. Click on the bottom horizontal line in the User-defined box.

6. Click the 2.5 pt line weight in the Style box. 7. Click OK.

13

Additional Information

Other Ways Of Formating Columns And Text

You can also change formatting by selecting a cell(s) or column(s) then clicking Format > Cells and doing the following

1. IF you click on the Alignment tab, you can change centering and orientation. 2. IF you click on the Font tab, you can change the font. 3. IF you click on the Font Effects tab, you can change color, etc.

Hide Or Show A Column

A column(s) can be hidden so that the column(s) is not seen on the screen. Hidden column(s) will not appear in a printout. If any cell in the hidden column is used by a formula, the formula will still use hidden columns to produce the correct answer. At the top of the columns, a missing letter(s) tells what column(s) is hidden. (If you see columns A followed by column C, column B is hidden.)

Hide A Column

1. Select the column(s) that you want to hide. 2. Click Format > Column > Hide.

Show A Hidden Column

3. Select the columns on both side of the hidden column (If column “B” is hidden, select columns A and C so that both columns are highlighted)

4. Right-click > Show. (Column “B” will appear)

Insert Chart

1. Select columns A and B.

2. Click the Insert Chart, , icon on the Function Toolbar. (The pointer changes to . Click anywhere on the spreadsheet. Or Click Insert > Chart... The "AutoFormat Chart" window appears.)

3. Select -New Sheet- from the drop-down box labeled Chart results in worksheet.

4. Click Next>>>.

5. Select XY Chart. (Hold the cursor over the icons to find it.) 6. Click Next>>>. 7. Select Lines Only and check the X axis

and Y axis grid line boxes. ("Y" may already be checked. Don't uncheck it.)

8. Click Next>>>.

14

9. Give the chart a title, My First Chart, in the box that has Main Title in it. (Replace text.) 10. Click the X axis and Y axis check boxes. 11. Type X for X axis title, and Sine(x) for Y axis title. (Replace existing text.) 12. Click Create.

13. Look at the worksheet tabs at the bottom. 14. Click on the last tab. (Probably labeled Sheet4.) 15. Use the little boxes on the corners to resize the chart by clicking on them and dragging

them until you like the proportions.

Appearance Of Chart

Charts created by spreadsheet programs are unappealing most of the time. You have to mess with the format of the chart elements to make them look better. The first thing I notice is a jagged plot line. That is appropriate for some data, but the sine function is a smooth function, so make the following changes:

1. Double-click somewhere on the chart if you see green boxes or no boxes. 2. Click Format > Chart Type...

3. Select Cubic Spline, , from the Variants box at the bottom. 4. Click OK.

That's better, but it could still use some improvement. Try:

1. Click Format > Chart Wall.

2. Click the Area tab. In the dialog box below Fill, click the and select None. Click OK. 3. Place the cursor over the data plot line and double-click. (The smooth Purple line.) 4. Click the Line tab. In the Color dialog box, change the color to Sea Blue.

5. In the Width dialog box, change the width to .02. (Click the repeatedly or highlight the number in the dialog box and type ”.02”.) Click OK.

6. Select Format > Grid > All Axis Grids... 7. Change the Color to Gray 40%. (You have to scroll down the palette.) Click OK. 8. The chart still seems busy. Select Format > Axis > X Axis. Click the Scale tab. 9. Clear the Maximum check box and replace 90 with 45. Click OK. 10. Change the main title text to Sine Function by double-clicking on it and editing it. 11. When you are done, click somewhere else on the chart to accept the changes. 12. Click on a worksheet cell. Click Format > Sheet > Rename. (The “Rename Sheet”

window appears.) Rename the sheet, Sine Graph. 13. Save your work. (Click “File > Save”.)

Page Preview Toolbar

15

The Page Preview Toolbar allows you to see what Calc is going to print.

1. Bring up the Checking worksheet. (Use the at the bottom left of your spreadsheet.)

2. Click File > Page Preview. (The Page Preview toolbar appears.)

3. If you look on the left end of the Page Preview Toolbar (the third toolbar), you will see four book icons with arrows. These let you view each page that would go to the printer.

• The Previous Page icon is first book icon.

• The Next Page icon is the second book icon.

• The First Page icon is the third book icon.

• The Last Page icon is the fourth book icon.

4. Click on the other icons on the Page Preview Toolbar to see their functions. 5. Click Close Preview.

Headers And Footers

When printed, all pages will have a Header and a Footer on them. The header shows the name of the sheet. The footer shows the page number. All the pages have them. Here's how you can turn them off or edit them:

1. Click Page Format on the toolbar. (The "Page Style: Default" window appears. 2. Click the Header tab.

You can turn them off by unchecking the Header check box. You can also format the margins and spacing. To edit the header, click Edit... (The "Header (Page Style: Default)" window appears.) Headers and Footers are broken into three sections. You can use one of the auto-format buttons below the three boxes or type your own header.

1. Click Cancel. (The "Header (Page Style: Default)" window closes.) 2. Uncheck the Header box. Look through the other tabs. 3. Click OK. (The "Page Style: Default" window closes.)

The same procedures apply to the footer, so I won't elaborate on that. The Sheet tab gives you the capability to control the way Calc determines the page order and what features to print or scale. The remaining tabs are self-explanatory. Note: Removing the header and footer will change the chart size, so you may have to resize it.

Print Ranges

What if you don't want to print the whole worksheet? I'm glad you asked! Calc gives you the ability to define print ranges.

16

1. Bring up the Checking worksheet. 2. Select A1:G25. (Click "A1". Drag copy to "G1".) 3. Click Format > Print Ranges > Define. 4. Open the Page Preview again.

Now you only have one page. Unfortunately, there is not an equivalent for charts. The only way you can print a chart only is:

1. Click File > Print... 2. Click the Pages radio button on the bottom left. 3. Type in the page number of the worksheet the chart is on.

Sheets

When you are working with spreadsheets, there are several items that will help you manage sheets such as Select Sheets, Insert Sheets, Delete Sheets, Rename Sheets, Move/Copy Sheets, And Select All Sheets.

Spreadsheet Rows And Columns

The spreadsheet is divided into rows and columns. Rows are numbered from 1 to 32,000. Columns are labeled with letters from A to IV.

The scroll bars on the right and bottom are used to move around the worksheet. Click on the

downward facing image at the lower, right corner of your screen. (Lower parts of your spreadsheet appear on your screen and the row numbers on the left side of your screen increase.)

Select Sheets

1. If the sheet tab that you need is shown, you can click on the sheet tab with the sheet tab

number, , to select a sheet. If you have more than three sheets (sheet tabs 1, 2, 3, 4) and you are on sheet tab 4, you will only see sheet tabs 2, 3, 4. To get to sheet tab 1, you will have to use the bar.

2. The bar has to be used when you have more than three sheets in the spreadsheet. Click

on the bar at the bottom left on the spreadsheet, (The first arrowhead, , will take you to the beginning of the sheets (pages), the second and third will take you to

the previous sheet, , or to the next sheet, , the fourth arrowhead, , will take you to the last sheet.)

Insert Sheets

1. If more sheets are needed, click on the blank tab after Sheet 3 to quickly add a sheet. (The "Insert Sheet" window appears.).

17

2. Another method to insert a sheet is to right-click on one of the tabs. (A menu appears.)

3. Click on Insert Sheet... (The "Insert Sheet" window appears.). 4. Under Position, you can select Before current sheet or After current sheet. Under

Sheet, you can select New sheet, select the number of new sheets you want to add to your spreadsheet, and enter a Name for the sheet. Another selection is From file. You can use the Browse button to select a file.

5. Click OK. (The "Insert Sheet" window closes.)

Delete Sheets

1. Right-click on one of the numbered tabs. (The menu in the section, "Insert Sheets", Step 2 appears.)

2. Click Delete... (The message below appears.)

3. Click Yes to permanently delete the current sheet or No to cancel deleting the sheet. (The message closes.)

Rename Sheets

18


2. Click Rename... (The "Rename Sheet" window appears.)

3. In the dialog box under Name, name this sheet, Working Sheet. 4. Click OK.

Move/Copy Sheets


2. Click Move/Copy Sheets...(The 'Move/Copy Sheet' window appears.)

3. In the dialog box under To document, click on the , you are given the choice of Untitled 1 or -new document-.

4. In the dialog box under Insert before, you can select where you want to move or copy the sheet. Click Sheet1. ("Sheet2" appears before "Sheet1".)

Select Cells

There are many ways to select cells in a worksheet. (To unselect the cells, click anywhere on the Spreadsheet.) The primary ways are listed below:

By Dragging

1. Click a single cell with the left mouse button. 2. Click the left mouse button and drag the cursor over many cells. (over Row(s), column(s)

By Columns And Rows

19

1. Click on a column label to select a column. (Click on the gray A column label.) 2. Click on a row label to select a row. (Click on the gray 1 row label.)

By Selecting All Cells In A Spreadsheet

Click on the empty box that is above row 1 and to the left of column A.

Cell Range

The SUM function appears as SUM(A1:A3). The A1:A3 in the parenthesis is called a cell range. It is shorthand for "from A1 to A3"

Data

The rectangles you see are called cells. A cell is the fundamental element of a worksheet. This is where the action takes place. Every cell has an address that is determined by the letter of the column and the number of the row that the cell is in. Addresses are written in the form A2, B16. Click on a cell on your screen. Notice the black box and the darker gray shading on the column and row labels. The black box identifies the active cell, or the one that will receive whatever you type. A cell can contain text, numbers or formulas. Let's enter something into cell A1:

1. Click on the A1 cell (The cell at the very top left of the spreadsheet). 2. Notice the black rectangle around the A1 cell. The A and the 1 are highlighted. 3. Type Hello World and press Enter. 4. The active cell is now A2. (The words “Hello World” are in A1.) 5. When you type something in a cell and press Enter, or Tab, or click the left mouse

button or use one of the arrow keys, whatever you typed is entered into that cell.

Enter Data Into More Than One Spreadsheet At The Same Time

Method 1: Using Control Key

1. While holding down the Ctrl (Control) key, click on the numbered tabs. Sheet1, then click on Sheet2, then click on Sheet3. Release the Ctrl key. (All 3 numbered tabs now have a white background. The numbered tab on the current sheet will have the word Sheet in bold)

2. In cell A1 type the word test then press Enter. (The word test will be entered in cell A1 on all 3 of the spreadsheets)

3. To stop adding data into three Sheets, hold down the Ctrl key then click on the Sheet tab for each of the sheets.

Method 2: Using "Select All Sheets"

4. Right-click on one of the numbered tabs, Sheet1, Sheet2, etc. The Menu below appears.

20

2. Click Select All Sheets. (There is no indication that anything happened.) 3. In cell A2, type the word test2 then press Enter. (The word, test2, will be entered in cell

A2 on all the spreadsheets) 4. To stop adding data into the Sheets, hold down the Ctrl key then click on the Sheet tab

for each of the sheets.

Delete Data

1. Click on A1 again. 2. Press the Delete key. (The “Delete Contents” window appears.)

3. Check the Delete all box and click OK. (Make a note of the delete options in the “Delete Contents” window.)

Delete Data From More Than One Spreadsheet At The Same Time

Method 1: Using Control Key

4. While holding down the Ctrl (Control) key, click on the numbered tabs. Sheet1, then click on Sheet2, then click on Sheet3. Release the Ctrl key. (All 3 numbered tabs now have a white background. The numbered tab on the current sheet will have the word Sheet in bold)

5. Click on A2. 6. Press the Delete key. (The “Delete Contents” window appears.)

4. Check the Delete all box and click OK. (The word, test, is no longer on the sheets.)

Method 2: Using "Select All Sheets"

21

5. Right-click on one of the numbered tabs, Sheet1, Sheet2, etc. (The Menu below appears.)

2. Click Select All Sheets. (There is no indication that anything happened.) 3. Click on A2. 4. Press the Delete key. (The “Delete Contents” window appears.)

5. Check the Delete all box and click OK. (The word, "test2", is no longer on the sheets.)

Add A Column Of Numbers For The Total

By A Simple Entry

1. Click on A1 to make it the active cell. 2. Type 12.5 and press Enter. (A2 becomes the active cell.) 3. Type 23 and press Enter. (A3 becomes the active cell.) 4. Type 1000 and press Enter. (A4 becomes the active cell.)

5. On the Formula Toolbar, click on the Function, , icon . (The equal sign “=” appears in A4)

6. Click on A1 (A red line appears around cell A1) Press and keep holding down the Shift key then press the + key. Release the Shift key.

7. Click on A2. Press and keep holding down the Shift key then press the + key. Release the Shift key.

8. Click on A3. Press Enter. The answer, 1035.5, appears in A4. (The cursor moves down to cell A5.)

By The Input Line

9. Click on A4.

22

10. Press Delete. (The “Delete Contents” window appears. Make sure the “Delete all“ box is checked.)

11. Press OK. 12. Click on the Input Line.

13. Click on the Function, , icon on the Formula Toolbar. Type =A1+A2+A3. 14. Press Enter. The answer, 1035.5, appears in A4. (The cursor moves down to cell A5.)

By Using The Sum Command

When a very large number of cells are included in the column to be added, the Sum command is more efficient.


2. Type =SUM(. (To do this type the = key, the word, sum, and the opening parenthesis, “(“.

Click just after "SUM", then press the shift key and the number “9” key [Shift-9] to get the opening parenthesis..

3. Click on A1 and drag down to cell A3. (To drag, hold down the left mouse button and move the cursor over the desired cells “=Sum(A1;A3” appears in "A4".)

4. Type the closing parenthesis “ )” ( Click after the "A3", then press the shift key and the

number "0" key [Shift-0] to get the closing parenthesis. In “A4”, the complete command,"=SUM(A1:A3)", appears.)

5. Press Enter. The answer, 1035.5, appears in A4 (The cursor moves down to cell "A5".)

By Using The Sum, Σ , Icon


7. Click on the Sum, Σ, icon on the Formula Toolbar just left of the Input Line (Σ is the

mathematics symbol for sum. The “Function”, , icon changes to the “Accept”, , icon.)

8. Click the Accept, , icon (green check mark).

The Sum, Σ, icon will automatically use all of the cells above the active cell. Selecting rows

The easiest way to select a row is to click on the row header. To select several rows that are next to each other click and drag the mouse or you can click the row header of the first row, then click the last row while depressing the shift key.

Selecting columns

Selecting columns works the same way. Click the column header to select a single column or c lick and drag to select multiple columns that are next to each other. To select columns that are not next to each other click each column header while holding the Ctrl key.

23

Selecting cells

There are several ways to select a cell or a range of cells. To select a single cell, just click on it. You can click and drag to select a rectangular range of cells. If you want to select more cells that aren't next to each other, hold the Ctrl key and click on each of the cells you want to include.

Changing the cell background color

Let's change the background color of the first row to the “olive brown” color we just created. First, select the row by clicking on the row header to the left of the sheet, then select the format menu and choose the cells option.

The format cells screen will let you change all kinds of things related to the display and format of items in a cell, as well as the cells themselves.

To change the background of the selected cells, click on the background tab. The menu defaults to the tab you most recently used. In this case, it is defaulted to the background tab. If your menu didn't default to the background tab, click on it to display the background options screen.

Next, select the “olive brown” color created in the previous step. Click the OK button to apply the changes and you're finished.

Formatting text

There are several ways to change text formatting in OpenOffice. The quickest way is to use the font formatting controls on the toolbar. These buttons will let you specify a font, the font size, bold, italic, underline, font color and text alignment.

24

Let's change the font color from black to white. Make sure the row is selected then click the font color icon. This will display the standard color palette. Select white from the palette and we’re finished.

The formatting controls on the toolbar are convenient but they only represent a fraction of the text formatting functionality you get through OpenOffice.

To access more advanced options, select the format menu, then choose the cells... option. The font, font effects and alignment tabs contain several tools you can use to format text the way you want.

Cell alignment

Alignment lets you specify how values are placed within a cell.

Right now the values in our spreadsheet are vertically aligned to the bottom of the cells. To change the spreadsheet so that the values align to the top of the cells use the alignment function.

First, select all the cells to align. In this case we’ll choose all cells by clicking on the upper left corner of the spreadsheet. Next, right click one of the cells and select format cells from the popup menu.

The alignments options are found on the alignment tab of the format cells screen.

25

Horizontal alignment settings determine whether an item will align to the left, right or center of a cell. Vertical alignment will let you specify whether items align to the top, middle, or bottom of a cell.

Select the top option from the vertical alignment dropdown and click the OK button. Now the values in our worksheet all align to the top of the cells.

There are 4 buttons on the default toolbar that will let you change the horizontal alignment of a cell to align to the left, right, center or justify the text. To use these buttons, just select the items you want to align and click on the desired alignment button.

Rotating text

To make our list easier to use we're going to rotate the column headings for the last 11 columns of the sheet – the columns that deal with plant hardiness zones.

First, select the cells to format.

Then click the format menu and choose the cells option. If it’s not already selected, click the alignment tab.

We’ll want our rotated text to align to the bottom of the cell so we’ll change the vertical alignment to bottom.

Next, then set the text orientation to 90 degrees.

For this function to work, make sure the automatic line break checkbox is deselected and click OK.

Now the text in the selected cells has been rotated 90 degrees and aligned to the bottom of the cell.

26

Sorting data

Calc has a sort feature that will let you dynamically change the order of data in your spreadsheet.

Suppose you want to view the entire sheet ordered by category.

First, select the data to sort. Let’s sort the entire sheet by clicking on the upper left area of the grid. Next, choose the sort option from the data menu.

Before defining the sort conditions, display additional sort options by clicking the options tab.

For now, let's take a look at the “range contains column labels” checkbox. Because you selected the entire sheet, the range does contain column labels. If we leave this box checked Calc will leave the first row alone and rearrange the sort order of all the other rows in the range. In this case, that’s what we want.

You'll notice there are lots of other options on this screen. We'll take a closer look at them in the next example. For now, let's set the sort options by clicking on the sort criteria tab.

First, we'll sort the data by category. Then, we'll choose the ascending order option. Ascending order will arrange text values in alphabetical order, numeric values would be arranged from smallest to largest.

Descending order is just the opposite.

If all you want to do is to sort the data by category, you could click the OK button now and be done. Let's take it a step further and apply a second set of sort criteria. What if you wanted to first sort the data by category then sort the values in each category by another column - by item name for example. You've already set up the first sort. All that’s left is to set up the second sort. Choose “item” from the second list and select ascending order.

27

Click the 'OK' button, and you'll see that Calc has sorted the data first by category, then by the secondary sort criteria – the item name.

Filtering data

One of the toughest things about using a spreadsheet to manage your data is that it's sometimes hard to find the data you want – especially if you're working with a large spreadsheet. Fortunately, Calc offers you the ability to filter your data.

Filters let you display only the records that meet certain criteria and hide the rest. How you define that criteria is up to you.

Let's look at an example.

The easiest way to filter your data is to use the autofilter feature. To do this, you’ll need to select the cells you want to filter. Let’s click the upper left corner of the sheet to select the entire worksheet.

Next, select the data menu then choose filter, autofilter.

You may see a message stating “The range does not contain column headers. Do you want the first line to be used as column header?”

In this case, the first line of the selected range functions as a column header so you can click the yes button.

Suppose you wanted to only view items that belong to the category of deciduous shrubs.

28

Click the auto filter menu button in the category column. Then select deciduous shrubs from the drop down menu. Now only deciduous shrubs are displayed.

You can filter further if you want to. Imagine you only want to show deciduous shrubs that have a medium growth rate. Just click the autofilter dropdown button in the growth column and select medium. Now you only see deciduous shrubs with a medium growth rate.

To remove the filter, select the filter option from the data menu, then choose remove filter.

Standard filters

Another type of filter is the standard filter. To use this filter, select the range you want to filter then select “filter” from the data menu. Choose the standard filter option.

Some of the filter options are hidden by default. You can access these functions by clicking the “more” button.

The standard filter screen will let you define up to three filter conditions by specifying a field name, condition, value and an operator.

29

Suppose that you wanted to find all items that grow in full sun, have a fast growth rate and a price less than $15.00.

You would select light requirements as the filter field. Select “equals” as the condition and select full sun as the value.

Next you want to add a condition to display only the items that have a rapid growth rate. Select “and” as the operator, growth rate as the field, “equals” as the condition, and “Rapid” as the value.

The final condition is to only show items less than $15.00 in price. Again, select “and” as the operator, then select price as the field. This time the operator will be “less than” and the value will be 15.

Click the OK button and we'll see the list filtered to show only the items that require full sun, have a rapid growth rate, and cost less than 15 dollars.

Removing a filter

To remove a standard filter select the filter option from the data menu, then select remove filter.

Removing a filter

To remove a standard filter select the filter option from the data menu, then select remove filter.

Creating subtotals

The sales details sheet has lots of information in it. The reason for importing the data was to use Calc's analytical tools to better understand sales patterns and trends at the garden center.

30

Creating subtotals

One of the easiest and most useful tools Calc offers is the subtotals feature. As its name suggests, subtotals will allow you to break data down into logical groups and calculate subtotals for each of those groups. The subtotals feature also leverages Calc's outline functionality so you can analyze summary information and quickly drill down to the details upon which the summaries are built. Let's take a look at how to build a subtotal report. First, select the cells you want to subtotal. Then select the subtotals function from the data menu.

Next, select a “group by” column. Calc will create a subtotal for each distinct value in the column. In this example lets group by category. This will group all of the values together for each category in the list and create a subtotal.

The next step is to tell Calc how to calculate the sub total. In this case we’ll sum all of the entries in the “Amount” column together for each group. To do this, check the Amount field, then select sum.

Let's add a few more subtotal calculations, let’s create a sum of total cost and a sum of quantity.

31

Click the OK button and Calc will subtotal our spreadsheet grouped by category, with a subcategory calculation for each of the items we specified - amount, total cost, and quantity.