FIRST TIME USER – CALC

This tutorial donated by Wayne Tschirhart

Introduction

Welcome to OpenOffice.org Calc. Calc is a spreadsheet program like Excel, Lotus 1-2-3 or Quattro. The purpose of this tutorial is to teach basic spreadsheet skills to someone who has never used spreadsheet software. Intermediate and advanced topics will be covered in other tutorials.

This tutorial assumes that you already have basic computer skills. If not, you should do the "No Computer Experience" tutorial. You will find the tutorial at www.tutorialsforopenoffice.org The path is: Home Page > Standard Tutorials > Writer > No Computer Experience

What is a 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 sheet to get a complete financial overview. Early computerized spreadsheets were almost exclusively used by businesses for financial record keeping. Today, spreadsheet programs like Lotus 1-2-3, Excel Quattro and Calc are used in almost every profession to calculate, graph, analyze and store information.

What are Spreadsheets used for?

A spreadsheet is, more or less, a over-sized calculator. People use spreadsheets for tracking personal investments, budgeting, invoices, inventory tracking, statistical analysis, numerical modeling, etc.

Getting Started

Start OpenOffice.org and click File > New > Spreadsheet. Take a moment to look the screen over. You should see four toolbars at the top of the screen and one toolbar along the left side of the screen. If you don't see four:

1. Click View > Toolbars

2. Make sure the first three items are checked

The topmost toolbar is the standard Graphical User Interface (GUI) Menu Bar that gives you access to all of the commands in Calc.

Immediately below the Menu Bar you'll find the Function Bar. The Function Bar contains icons (pictures) to provide quick access to commands like New, Open, Print, Copy, Paste, etc. There is also a drop-down menu that let's you open previously-used documents quickly. If you place your mouse cursor over any of the elements of a toolbar, the tooltip tells you what command the icon represents. If you aren't seeing the tooltips, then click Help > Tips.

The Object Bar is located below the Function Bar. The Object Bar icons and drop-down menus are used to select a font, font color, alignments, number formats, border options and background colors.

Below the Object Bar is the Formula Bar. The Formula Bar contains the Sheet Area drop-down menu and a long white box called the Input Line.

The toolbar on the left is the Main toolbar. The Main toolbar contains a mixed batch of commands. Use the tooltips to see what they are for.

The rest of the window contains the worksheet. The worksheet is divided into rows and columns. In Calc, rows are numbered from 1 to 32000. Columns are labeled with letters from A to IV. Scroll bars on the right and bottom are used to move around the worksheet.

If you look along the bottom left edge of the worksheet you will see three tabs labeled Sheet1, Sheet2 and Sheet3. These are other worksheets. A workbook contains multiple worksheets.

Now that you know where everything is located, let's begin.

Entering Data

At first, all the blank space is a bit intimidating, but that will soon pass. 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 cell at the very top left

2. Notice the gray highlighted area. The A and the 1 are highlighted

3. Type Hello World and press Enter

4. The active cell is now A2

5. Click on A1 again

6. Press the Delete key

7. Check the Delete all box and click OK (Make a note of the delete options)

When you type something in a cell and press Enter, Tab, click the Left Mouse Button (LMB) or use one of the arrow keys, whatever you typed is entered into that cell.

Selecting cells

There are many ways to select cells in a worksheet. The primary ones are listed below:

1. Click a single cell with the LMB

2. Click the LMB and drag the cursor over many cells (Row, column or block)

3. Click on a column label to select a column. (Click on the gray A column label)

4. Click on a row label to select a row. (Click on the gray 1 row label)

5. Click on the gray box above row label 1 and left of the column label A (This selects all cells in the worksheet)

Using Formulas

Using Formulas

Calculations are performed with formulas. Formula is another word for equation. To enter a formula:

1. Click on A1 to make it the active cell

2. Type 12.5 and press Enter

3. Type 23 and press Enter (A2 is the active cell)

4. Type 1000 and press Enter (A3 is the active cell)

5. Type = (A4 is the active cell)

6. Click on A1

7. Type +

8. Click on A2

9. Type +

10. Click on A3

11. Press Enter (Answer: 1035.5)

You just entered a formula by typing it into the cell; however, there will be times when this is not possible because of the length of the formula. Try entering the formula this way:

1. Make A4 the active cell

2. Press Delete (Make sure the Delete all box is checked)

3. Press OK

4. Click on the Input Line (The white box above the column titles.)

5. Type =A1+A2+A3

6. Click on the green check mark

The formula you just used is ok for adding three cells, but what about adding 200 cells?! Would you like to try? That's where functions come in. Functions are computer routines, or mini programs, that simplify operations. Let's try one:

1. Click on A4

2. Type =SUM(

3. Click on A1 and drag down to cell A3 (hold the LMB down)

4. Type ) and press Enter

An even easier way of using the SUM function is:

1. Click on A4

2. Click on the Σ just left of the Input Line (The mathematics symbol for sum.)

3. Click the green check mark

The Σ button will automatically use all of the cells above the active cell.

The SUM function is probably the most-used function in spreadsheets. The A1:A3 in the parenthesis is called a cell range. It is shorthand for "from A1 to A3". There are hundreds of functions available to help you. Take a look:

1. Click Help > Contents (The on-line help window opens)

2. Click the Contents tab

3. Double-click the Spreadsheets book icon

4. Double-click the Functions Types and Operators book icon

5. Double-click Mathematical Functions

6. Click on the SUM link in the list at top

You are looking at a detailed explanation of the SUM function. When you get a chance, browse through the different types of functions listed on the left. The on-line help is a great resource for learning the functions available in Calc. Close the help window when you're done.

Spreadsheet Math

You may be asking, "What if I want to subtract or multiply?" That's a good question, so let's briefly look at mathematical operators. The table below shows the common math operations and their spreadsheet equivalents.

Addition 5 + 5 =5+5

Subtraction 10 - 5 =10-5

Multiplication 5 x 5 =5*5

Division 10I2 =10/2

Exponentiation 10² =10^2

Computers are not very bright, so you have to watch how you type in formulas. The order of operations is law. The order is: exponentiation, multiplication, division, addition, then subtraction. Let's pretend we want to perform a calculation using the equation:

                                                                             x + 10                                                                             -------                                                                               y + 10

and suppose we entered it into Calc as follows:

1. Select the A column. (Click on the letter A)

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

7. Type the formula =A1+10/B1 + 20 (You should be in cell C1)

8. Press Enter (Answer will be 41)

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/20) + 20

which equals 40.5 and rounds up to 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)

4. Click the green check mark (The answer is now 1)

There, now we have the correct answer.

A Simple Application

Now to something a bit more challenging. Almost everyone has seen a check register and is familiar with its components: check number, payee, item description, withdrawal, deposit and balance. We're going to make one. Start by labeling the data columns to define where the data goes:

1. Select all cells as described in Step 5 of "Selecting Cells".

2. Press Delete

3. Click OK

4. Make A1 the active cell

5. Type Check No.

6. Press the Tab key and type Paid To (active cell is B2)

7. Press Tab and type Description (active cell is C2)

8. Press Tab and type Deposit (active cell is D2)

9. Press Tab and type Withdrawal (active cell is E2)

10. Press Tab and type Balance (active cell is F2)

11. Press Enter

Hmmm, something is missing. I guess it would be nice to know when a transaction took place, so we need a date column. We could add it after the balance column, but that would be lazy. The date is usually the first item, so add a column:

1. Select column A. (Click on the A. The column turns black.)

2. Click Insert > Columns (Everything will shift to the right.)

3. Select A1

4. Type Date and press Enter

We have all the columns we need for our check register, but it isn't very pretty. Lets make the data labels more attractive:

1. Select A1:G1 (Select all data labels)

2. Go to the Object Bar (That's where you change the font)

3. Hold the mouse cursor over the icons in the toolbar until you find the Align Center Horizontally button, then click it

4. While the cells are still selected, move to the left and click the Bold (B) button, then move over to the Font Color button and pick Light Blue

Of course, you can also format columns and text the long way:

1. Click Format Cells...

2. Click on the Alignment tab to do change centering and orientation

3. Click on the Font tab to change the font

4. Click on the Font Effects tab to change color, etc.

That's much better! You can select any color, but I like blue. Data labels are a good spreadsheet practice. Use labels that clearly describe the data in the column below. Be creative and use color, different fonts, bold, etc., but make them stand out.

Helpful Hint: If you are using data that has units like inches, feet, square miles, etc., enter the unit in the cell below the label. There have been many times when I've opened a spreadsheet and the units weren't labeled. This makes the data meaningless unless you can figure it out what you're dealing with, which takes time and energy you may not have.

Now enter an initial balance:

1. Select A2

2. Enter a date. (Enter the date in the mm/dd/yy format. Example: 02/12/05)

3. Select G2

4. Enter 5000

The Deposit, Withdrawal and Balance columns will all contain values that represent currency, so we let's format them accordingly:

1. Select column E

2. Hold down the Shift key

3. Select column G (All three columns turn black)

4. Click Format > Cells... (The Format Cells dialog box appears)

5. Click the Numbers tab

6. In the Category box, click Currency (Left side)

7. 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.

8. Click OK

Those three columns will show the $ when they have numbers in them. That's how you format cells. We can start entering checks now:

1. Enter a date in the Date column (A3)

2. Enter a check number of 205 (B3)

3. Enter Energetic Electric in the Paid To column (C3)

4. Enter Monthly electricity bill in the Description column (D3)

5. Enter 250 in the Withdrawal column (F3)

Look at the text you entered in the Paid To and Description columns. One is cut off and the other extends into the Deposit column. Let's fix it so we can read them:

1. Select column C

2. Click Format > Column > Optimal Width... (use the default value)

3. Click OK

4. Repeat steps 1-3 for column D

You can also resize cells manually:

1. Place your cursor over the column dividing line between the letters C and D

2. When you see the symbol with two arrows, click the LMB and drag.

3. Size the column like you want it and release the LMB

Now the fun starts! 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

To enter this into the spreadsheet:

1. Select G3

2. Type =

3. Click on G2

4. Type +

5. Click on E3

6. Type -

7. Click on F3

8. Press Enter

Tada! You have everything you need to use your check register! Enter another check on row 4. For this check, put a withdrawal amount bigger than your balance. (Ladies & Gentlemen - Don't try this at home!)

1. Enter a date (A4)

2. Enter a check number of 206 (B4)

3. Enter Fast Cars, Inc in the Paid To column (C4)

4. Enter New Car in the Description column (D4)

5. Enter 7000 in the Withdrawal column (F4)

Now we need to copy the formula in the 3^rd row to the 4^th row:

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 box. When you see a +, click on it and drag it down to cell G4

You now have a negative balance and the font turned red. Dragging the little black box (I call it drag-copy) is the shortcut for copying the contents of one cell to a neighboring cell. You can also use the Copy and Paste commands in the menu. The number turned red because the currency format was set up that way. Cool, huh?

When you copy formulas, the cells referenced in the formula will change. Activate cell G3 and look at the Input Line. You will see the formula for that cell. Click G4 and you'll see the cell addresses change. That's because spreadsheet programs use relative addressing. In other words, the program doesn't store the actual cell address; rather, it stores something like, "the active cell = one cell up + two cells to the left – one cell to the left." You need to keep that in mind when you copy formulas.

Now, pretend we forgot to enter an earlier deposit. In that case we would:

1. Select A4:G4 (or click row label 4)

2. Click Insert > Cells... (A dialog box opens)

3. Select Shift cells down

4. Click OK

5. Enter an appropriate date

6. Tab over to the Description column

7. Enter Paycheck and press Tab

8. Enter 20000 for an amount (Wouldn't that be great!)

9. Drag-copy the formula from G3 down to G5

Note: If you tried to insert the cells on row 3 and drag-copied up, you will find that the technique doesn't work correctly. To remedy the problem, edit the formula in G3 and replace G1 with G2, then drag-copy G3 to G5.

Whew! At least we're out of debt now! I encourage you to enter more checks and practice copying formulas to get more comfortable with the information presented so far.

Before we continue, change the name of Sheet1 and save your work:

1. Click Format > Sheet... > Rename...

2. Name it Checking

3. Click File > Save As...

4. Type a name like CalcIntro in the File name box and click Save

Borders and Shading

Before we move on to charts, let's look at 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:

1. Select A1

2. Hold down the Shift key

3. Click on cell G25 (Another way to select a block of cells)

4. Click Format > Cells...

5. Click on the Borders tab

6. Find the User-defined box

7. See the four gray boxes?

8. Click to the left of the top left box (Toward the middle of the box side)

9. Click between the top two boxes (Toward the middle of the box sides)

10. Click to the right of the top right box (You should have 3 vertical lines)

11. Click OK

12. Select A1:G1

13. Find the Borders icon on the Object toolbar

14. Click it

15. Click on the second box from the left on the second row

16. Click Format > Cells...

17. Click the Background tab

18. Click on Gray 20% (Use tool tips to find)

19. While you're there, click the Borders tab

20. Click on the bottom horizontal line in the User-defined box

21. Click the 2.5 pt line weight in the Style box

22. Click OK

As you can see, adding borders and shading is pretty straight-forward. A lot depends on individual tastes, so play around with the settings and see what you come up with!

Charts

I could do a whole tutorial on charts alone, but I'm only going to give you the basics to get you started. Charting data is really an art form. You have to have an intimate knowledge of the data, the best way to visualize the data and a sense of what people respond to.

The first thing you need is data that shows some kind relationship. Examples include mathematical functions, stock market prices over time, rainfall over time, statistical divisions of a population, or divisions of an income that make up a budget. Since math is something that anyone can duplicate, we'll use the sine function:

1. Go to the bottom of your workbook and click on Sheet2.

2. Type X in A1

3. Type Sine(X) in B1

4. Enter 0 in A2 (That's a zero)

5. Select A2

6. Drag-copy it down until you see the tool tip number read 90 (You should see the numbers auto-increment)

7. Go back to the top and Select B2 (Ctrl-up arrow is a quick way to move up)

8. Enter =SIN(A2)

9. Drag-copy the formula all the way down to the 90 in the A column.

Don't worry if you don't know what the numbers mean; we aren't concerned with that.

The order of the columns matters. Spreadsheet programs typically assume that the column on the left is the variable that is plotted on the horizontal (x) axis of the chart, and the column on the right is the variable that is plotted on the vertical (y) axis. With that in mind:

1. Select columns A and B

2. Click Insert > Chart...

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>>>

9. Give the chart a title 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 and 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.

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 and select None

3. Click OK

4. Place the cursor over the data plot line and double-click (smooth Purple line)

5. Click the Line tab

6. Change the Color to Sea Blue and set the Width to .02

7. Click OK

8. Select Format > Grid > All Axis Grids...

9. Change the Color to Gray 40% (You have to scroll down the palette)

10. Click OK

11. The chart still seems busy. Select Format > Axis > X Axis

12. Click the Scale tab

13. Clear the Maximum check box and replace 90 with 45

14. Click OK

15. Change the main title text to Sine Function by double-clicking on it and editing it

16. When you are done, click somewhere else on the chart to accept the changes.

17. Click on a worksheet cell

18. Rename the sheet Sine Graph

19. Save your work

Ahh, much better! You just used most of the chart editing commands. The point to carry away from this section is that you can change every aspect of the chart in some fashion. To get good at it, you'll have to experiment with the settings and develop your own style.

Printing

Printing is pretty much the same as printing in any other software application; however, there are settings that are unique to Calc.

I guess the best place to start is the print preview, so:

1. Bring up the Checking worksheet

2. Click File > Page Preview

This view lets you see what Calc is going to print. If you look on the left end of the third toolbar you will see two book icons with arrows. These let you view each page that would go to the printer.

1. Click Close Preview

Look at the worksheet. You should see lines that are darker than the rest. These are the page breaks based on the current page settings.

Let's start with the chart:

1. Bring up the Sine Graph worksheet.

2. Click on the chart

3. Push it up into the upper left corner

4. Resize it to fit the dark gray page break lines.

5. Click File > Page Preview

You will see a header and footer above and below the chart. 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

2. Click the Header tab

You can turn them off by unchecking the Header checkbox. You can also format the margins and spacing. To edit the header:

1. Click Edit...

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

2. Uncheck the Header box

3. Look through the other tabs

4. Close the dialog box

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.

Removing the header and footer will change the chart size, so you may have to resize it.

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.

1. Bring up the Checking worksheet

2. Select A1:G25

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.

Summary

Well, that wasn't so bad, was it? There are many more features that will help you in your spreadsheet work, so explore the menu and try them out! By completing this tutorial, you have experienced, and hopefully learned, the basic tasks to enable you to begin using spreadsheets. We began with the layout of the screen; then worked through entering data, using formulas and functions, creating charts, using basic format tools on charts and worksheets, and printing spreadsheets and charts. More advanced topics will be covered in future tutorials. I hope you enjoyed it. Good Luck!

NOTE

TUTORIALS ARE IMPROVED BY INPUT FROM USERS. WE SOLICIT YOUR CONSTRUCTIVE CRITICISM.

CLICK HERE TO E-MAIL SUGGESTIONS AND COMMENTS