If you've been following along with this series, you know that we're now in the trenches of OpenOffice.org and that I'm no longer a newbie. In fact, I'm feeling pretty comfortable in the world of OpenOffice. But, if you're new to OpenOffice.org or just curious what it's like to explore this compelling and arguably superior alternative to Microsoft Office, you might want to back up and get your bearings. (See Background Basics.)
Having already explored the basic features of Writer and Impress, I'm now moving into the spreadsheet world with Calc. But, since I was never an accountant and prefer to balance my checkbook with a pencil, paper and a calculator, my exposure to any spreadsheet program, including OpenOffice.org Calc has been minimal. So, before we get into the bigger issues of functions and formatting, I first want to explore this strange new world of cells and sums.
> At First Glance
Like most casual spreadsheet users, the spreadsheet program I've used is OpenOffice.org's Microsoft counterpart, Excel. In fact, in some ways, I've even gotten used to the way Excel works. I know a few shortcut keys and I know how to use simple functions and formatting, such as freezing cells. So, when I first opened up OpenOffice.org Calc, I wasn't surprised to learn that Calc looked very similar to Excel. In fact, most OpenOffice.org Calc users (besides accountants or other spreadsheet power users) say Calc is the most similar to its Microsoft reflection; however, those who are used to using Excel might find a few things are different. Here's an outline of some of the differences you might encounter.
While you're looking in the help files for functions and formatting you used in Excel, it might help to know where Calc terminology differs. In Calc, the entire document is called a spreadsheet, not a workbook, and a single tabbed sheet is called a sheet, not a worksheet. The callouts you can edit and attach to a cell that are called comments in Excel are called notes in Calc. And the parts of functions and formulas that are required to be computed are called parameters, not arguments as they are in Excel. And if you get really deep into the terminology, functions that are called array functions in Excel are called matrix functions in Calc.
> Getting Around
Calc is divided into numbered rows and lettered columns and each section of the sheet is called a cell. You can use the input line or type directly into one of these cells to input data just like other spreadsheet programs. In fact, most of the shortcut keys Excel users use will still work, as will copy and paste functionality, freezing panes, and other common commands; however, as I began working in Calc, I did notice a few differences in how you get around in Calc.
Dragging and Dropping
To drag and drop a cell to move its contents in Excel, you select the cell or series of cells and then place your cursor carefully over the cell until you get a handle that will let you drag and drop its contents. But when I tried this functionality in Calc, I never got the drag and drop handle to move the contents. Instead, to drag and drop a selection in Calc, I had to select at least one cell outside of the cell I wanted to move and then move back to select only the first selected cell. Once I did this, I got the option to drag and drop the cell where I needed it.
Although most common shortcut keys work the same way in Calc as they do in Excel, some are very different. To change from relative to absolute references (you know, that shortcut that changes regular cell references, A1, to references with dollar signs, $A$1), you have to press Shift+F4 instead of just F4. And Shift+F2 doesn't work to edit notes, instead, use Ctrl+F1. Also, I found as I tried to fill down or right, Ctrl+D and Ctrl+R don't work. Instead, I found the command in the Edit menu, Edit | Fill. Regardless of the shortcut keys that are default in Calc, you can remap any keystroke to be your old familiar keystrokes by going into Tools | Customize | Keyboards.
Interested in the short path to familiarity with OpenOffice.org? Read the previous articles in this series, which cover the following topics:
- October 2006, OpenOffice.org: One Suite Alternative novell.com/connectionmagazine/2006/10/tech_talk_3.html
Explains why you should care and continue to read about OpenOffice.org.
- November 2006, First Look at OpenOffice.org Writer novell.com/connectionmagazine/2006/11/tech_talk_4.html
Introduces the suite’s word processing component, OpenOffice Writer.
- December 2006, Canary Report: I Tawt I Taw A Puddy Tat novell.com/connectionmagazine/2006/q4/tech_talk_8.html
Shares my attempts to format, export as .pdf, and create a template for my community newsletter, The ABC View, using OpenOffice Writer.
- March 2007, Office Party novell.com/connectionmagazine/2007/q1/tech_talk_9.html
Describes my experience creating mailing labels to distribute The ABC View using the Mail Merge Wizard in OpenOffice Writer and a Microsoft Excel file, which I converted to OpenOffice Calc.
- May 2007, Dialing it Up novell.com/connectionmagazine/2007/q2/tech_talk_9.html
Shows how I used OpenOffice Writer to create a form for my church's children's organization.
- June 2007, First Impressions novell.com/connectionmagazine/2007/06/tech_talk_2.html
Illustrates how I created a presentation with OpenOffice Impress.
- August 2007, Impressive novell.com/connectionmagazine/2007/q3/tech_talk_10.html#background
My experience converting a Microsoft PowerPoint presentation into OpenOffice.org Impress.
One of the most different discoveries I came across while familiarizing myself with Calc came when I pressed Delete. Because pressing Delete in Excel deletes the contents of the selected cell, I expected it to do the same thing in Calc, but instead of automatically deleting the contents, it instead brought up a dialog that asked me what I wanted to delete. (See Figure 1.) The dialog gives you the options of deleting everything in the cell or choosing among strings, numbers, date and time, formulas, notes, formats or objects. By default, the strings, numbers, date and time, formulas and notes boxes were checked. I pressed Enter to delete the checked items and then moved on, storing that piece of delete information for later use. I then decided to select another cell and press Backspace to see if it would do the same thing. This time there was no dialog. It just erased the contents of the cell like I expected it to.
I also noticed as I was moving content around in my spreadsheet that the fill handle in Calc works a little differently than the fill handle in Excel. And, I think I like the fill handle in Calc a little better. In Excel, when you drag using the fill handle, the cell's contents are copied into each subsequent cell, whether the cell contains a number or text. So, the fill handle in Excel is basically a quick way to copy information. When I tried the fill handle in Calc, it worked the same way for text, but I got a surprise when I used it with numbers. Instead of copying the number, it moved the number up in increments of 1 for the space where I filled. So, when I entered a 5 in the first cell, the next cell was a 6, the next a 7, and the next an 8, and so on. Even with a combination of letters and numbers, the fill handle still incremented the number portion of the cell by one each time. (For example, Room 101 became Room 102, Room 103, and so on.)
Note: Excel has a similar pattern when you begin a pattern of numbers and select the first two cells of the pattern. For instance, if you entered 2 in the first cell and 4 in the next and then selected those two cells and used the fill handle, the even number pattern would fill in. The difference between Calc's autofill feature and Excel's autofill feature is that Calc will automatically increment any number.