Never used a spreadsheet before? Here's how to get started by using OpenOffice.org Calc. [This article initially appeared in TUX, issue 4.]

Many office environments make extensive use of spreadsheets. The reasons why are no mystery, as spreadsheets allow you to collect large amounts of data and, more important, allow you to try out a series of hypothetical situations to see their impact in your specific situation.

If you've been a computer user for some time, it is likely that you have used at least one type of spreadsheet application or another. For those of you that have used other spreadsheet applications in the past and simply want to become acquainted with OpenOffice.org's Calc application, you'll probably want to skip to section two, or if you're more adventurous, simply open up OpenOffice.org and start doing what you need to do.

OpenOffice.org Calc is quite similar to many of the major office spreadsheet applications. Feel free to scan the article to figure out what you need to know if you get stuck, but in my experience, the best way to learn if you are switching to OpenOffice.org Calc from another office suite spreadsheet is simply to start playing. For everyone else, I'm going to explain the general elements of a spreadsheet so that we can all start with some common ground. In my next article, we'll get into more depth on how to use OpenOffice.org Calc.

What Is a Spreadsheet Anyway?

A spreadsheet is nothing more than a table. We call single spaces used to store information cells. Rows and columns of these cells make up a spreadsheet. Take a look at Figure 1.

Figure 1. OpenOffice.org Calc's Main Screen

I'm just going to say this now. There are lots and lots of buttons. Don't let this overwhelm you. These are largely there to save you time, and they will in the long run. Using a spreadsheet application is extremely easy. I'm going to explain more than enough to get you started, and once you're comfortable with that, you'll be able to forge your own path, or read the next article and go from there.

As you can see in Figure 1, the rows are labeled with numbers, and the columns are labeled with letters. So, to reference a single cell, you'd give its coordinates by which column it resides in, and then which row. That means that the top-left cell would be called cell A1. A cell three columns to the right and four rows down would be called cell C4. I know this seems strange and useless at this point, but don't worry. It's important to be able to refer to cells with a coordinate system for formulas, which will be covered in the next article. Also, I refer to cells by their coordinates because it's much less confusing than trying to explain, “The cell four up from where you entered the last data.”

In its simplest form, a spreadsheet can keep track of your data. The tabular format allows for keeping track of financial, scientific and many other forms of numerical data. We create one of these spreadsheets in this article. Later, when you actually start utilizing the power of spreadsheets, the sheet can perform many complex calculations, and it either will take care of the calculations for you or allow you to perform complex hypothetical calculations, simply by entering a new number where the real value used to be. So, let's get started.

Getting Started with OpenOffice.org's Calc Application

The first thing to do, in any case, is to open the application. On KDE, this is usually located under a heading called Office in the K menu. This is where it's located on my SUSE Linux 9.2 system. You may have to browse your own menu to find the selection to start OpenOffice.org Calc if you are using another Linux distribution or if you are using another desktop, such as GNOME.

Figure 2. Starting OpenOffice.org

You also could navigate to Office Suite directly under Office. The Office Suite icon allows you to access all the parts of OpenOffice.org and start with a specific layout and kind of document. We simply want to start with a blank spreadsheet in this case, so go ahead and click on OpenOffice.org Calc instead of the Office Suite icon if it exists on your system. You'll be greeted with an empty spreadsheet that looks like Figure 1.

The first step we take is to begin entering data. To navigate to a cell, you can use the mouse to click on the desired cell, or if your hands are off the mouse and you need to move around, it's faster to use the arrow keys on the keyboard. Let's go ahead and create a table of data so you can get some practice entering information. Feel free to start your own spreadsheet and follow along.

We're going to create a quarterly earnings table like the one shown in Figure 3.

Figure 3. The Finished Product

Of course, the data isn't the most accurate in the world, but it'll get you used to entering data and formatting the cells to make the data look nice and easier to read.

Start with a blank spreadsheet. The first thing that we're going to do is create headings (labels) to keep the data organized. Start with the title. Click the cell D1 or use the keyboard to navigate to cell D1, type the title “Quarterly Earnings (2005)” and press Enter. Use your mouse or the arrow keys to navigate to the other cells until your spreadsheet looks like Figure 4.

Figure 4. The First Steps

If you put some information in the wrong place, or simply decide to move it, you can move it around by selecting (clicking on, or using the arrow keys to move the cursor to) the cell, and then selecting Edit→Cut, selecting the destination cell and then selecting Edit→Paste, like in many other office applications.

Now we're going to insert the numbers. Feel free to choose your own data, or use the numbers you see in Figure 5. You can type a number and then press Enter to move down to the next cell in a column. This makes data entry go more quickly. Try to avoid using the mouse. You can improve your overall speed by using the arrow keys instead, at least once you become comfortable with the application. Your spreadsheet should now look something like Figure 5.

Figure 5. Starting to Look Presentable

At this point, you've put a fair amount of work into your spreadsheet. You wouldn't want to lose that work under any circumstances. This would be a great time to save. Click on the disk icon in the toolbar, and you'll see a window that allows you to choose a filename and location for your file. In Linux, spaces and most symbols are perfectly acceptable in a filename. Give your spreadsheet a meaningful name. I chose Quarterly Earnings (2005) as is shown in Figure 6. The default extension of an OpenOffice.org Calc spreadsheet at the time of this writing is *.sxc. An extension is simply the last portion of a filename that is one way your computer can figure out how to handle the file. This extension will be changing in OpenOffice.org 2.0, but don't worry, the old format still will be supported in newer versions. Select Ok, and your work is saved to disk.

Figure 6. Save your work often!

We need to add a little bit of formatting to make the sheet easier on the eyes. First, we should make the heading larger. I'd like to point out something here. The text in cell D1 is too large to fit in that cell, so it's overflowing into cell E1. Select cell D1. If you look at the text entry bar among the toolbars, you'll notice that when you have cell D1 selected, you'll see all of the text, and in cell E1, you'll see nothing. This means that all of the text is actually in cell D1, and OpenOffice.org Calc decided it would be better to show the text on the next cell, rather than have it end at the cell border. This may seem confusing at first, but it really does make more sense once you've gotten used to things.

Now we need to touch up the title. You should have already selected cell D1. Now drop down the font size menu by clicking on the arrow where the number appears (the font size selector should be to the right of the font name selector). Select something larger. I chose size 16.

Figure 7. Choosing Font Size

Now we need to align the data labels. Select the rectangular region of cells starting with cell C3 and extend the selection to cell G4. You can do this with the mouse, but it is probably easier to do it with the keyboard. Select cell C3, and then hold down the Shift key while using the arrow keys to move to cell G4. Once you have that range of cells selected, you can change the alignment (among other things). Change the alignment to center the numbers by clicking the center button in the toolbar. The center tool button is the one where you see different lengths of horizontal lines, all centered in the button.

Figure 8. Centering the Data Labels

Now you should right-align the contents of cell A7. Select cell A7, and click the right-align button (it is immediately to the right of the center-align button). Your sheet should now look like Figure 9.

Figure 9. It's coming together!

The final touch for this spreadsheet is to add some lines on the table so that the data stands out and the values are easy to find. Select cells B4 to G4 (select the cell B4, hold down the Shift key and then use the arrow keys to navigate to G4). Next, select Format from the main menu, and then select Cells from that menu, as shown in Figure 10.

Figure 10. Adding Our Horizontal Line

This shows a new window with many options. Click on the Borders tab at the top of the window, and your screen will match Figure 11.

Figure 11. The Borders Dialog

In the section toward the left labeled User Defined, you can add your own borders however you like to the cells of the sheet. In our case, we want a single horizontal line at the bottom of the cells we've selected, so we click on the white area toward the bottom of the User Defined area. This makes a line appear where you've clicked. If the line doesn't match Figure 12, simply click again in the same place to make the line go away. Once you have a single horizontal line at the bottom of the User Defined area, click Ok.

Figure 12. Adding Our Line

Once the dialog disappears, you'll need to select a different cell to see the results of your handiwork.

Now, we just need to add the vertical line. Select cells B4 to B8 using your mouse or the keyboard technique we described in previous operations (click on the first cell, hold down the Shift key and navigate to the last cell). Select Format→Cells from the main menu again. This time, the Borders tab already should be highlighted. Click on the right side of the User Defined area, and then click Ok. Once you select another cell on the sheet, you should see the finished product. Save your finished work, and you've created a simple spreadsheet.

At this point, you really haven't even begun to see the power of what you can do with spreadsheets. In the next issue of TUX, I'll cover some of the more powerful functions of OpenOffice.org Calc.

About the Author

Kevin Brandes is 21 years old and is proud to help others join the Open Source movement. He lives in rural Oregon with his partner of three years, where he studies Software Engineering.