Just to review, spreadsheets developed from large green ledger pages (no, green is not important) that accountants used to list expenses or revenues, or assets, or …. and then to categorise them. Every expense, for instance, was listed in column A, and if it was a material purchase, it was also listed in Column D, a labour paycheque was listed in column E, and an office overhead was listed in column W (for aWFUL - ok sorry…). Then each of the columns could be added up, and you had totals for materials, labour, overheads… This was called - spreading the expenses. Accountants are pretty simple folk, they could have called it categorising, and then we would have to call the ledger pages categorisheets…
Anyway, sometimes the columns contained, not the expense amount, but an amount of tax to be paid on the list of invoices. So sometimes, the columns had calculated amounts based on some other column. Now, calculating these types of columns was very time-consuming, so accountants were very happy people when the desktop computer permitted them to create their spreadsheets on a computer, and use formulas instead of all of these laborious calculations. That is why accountants in the early days of desktops whined and whined until their bosses bought them these pretty shiny Macintosh computers. Funny, there was always some spare money for accountants toys, but not for new operating equipment for the plant…
So, now you know…
Using Calc
Well, we were going to do an extensive series on how to use the spreadsheet program Calc, but, after looking around at all of the work that has been done by other people, we are just going to send you to those sites. At least, we will as soon as we are sure that you have called up the program. After you look at the guides at other sites, we have a few additional tips to pass along from our own experience that you might bear in mind.
You installed Open Office, right ? If not, have a look at our last post. When it installs, Open Office usually does not put shortcuts on your desktop. You access Calc, by navigating the Start menu with the sequence Start>Programs>OpenOffice.org>OpenOffice.org Calc. Away you go. That’s it.
Now, there are a number of basic tasks. Lets assume that you already have a file that you want to work on - a good friend gave you a spreadsheet already to get you started… (Like this one…). So the tasks are, more or less
Open a file
• From the menu bar—Click File and then select Open.
• From the toolbar—Click the Open button on the Standard toolbar.
• From the keyboard—Use the key combination Control+O.
Each of these options displays the Open dialog, where you can locate the spreadsheet that you want to open
Input some data
- Numbers are the most common input data to a spreadsheet
- Select the cell and type in the number using either the top row of the keyboard or the numeric keypad.
- To enter a negative number, type a minus (–) sign in front of it or enclose it in brackets ( ).
By default numbers are right-aligned and negative numbers have a leading minus symbol. Text is input in the same fashion. However, if the text is a number, precede it with an apostrophe ‘ so that Calc will not treat it as a number. - Date can be input in many formats. You can use this one for starters 10-Dec-07.The day followed by dash, the first three letters of the month, followed by a dash, followed by the two (or four) digits of the year.
Modify a Formula
A formula is a series of cell references and numbers, combined with arithmetic operators (+ - * / ^ ). If you are inserting a new formula, simply select the cell, type an “=” sign and start picking cell references and operators - don’t forget to use brackets ( ) to group expressions together to make sure they are evaluated in the order you need. On the other hand, if you are modifying an existing formula, start by selecting the cell. You can then either press F2 for edit, or click on the cell edit box at the top of the spreadsheet. Use the arrow keys to move to the point in the formula that you wish to change and use backspace or delete to remove what you do not want. Feel free to type in replacement formula components as needed. On the third hand, you are responsible for those formulas now - you changed it, you own it sort’a thing.
Save the file and your work
Eventually, you will tire of all of this fun that you are having, and you will want to get a drink - coffee right? So, you need to save your work. If the file is already named, you can either navigate to File>Save, or use the small blue diskette icon towards the right of the toolbar (we all remember diskettes…?). However, if the file has no name, you will need to do a file Save As. While we are on the subject, we recommend that you consider versioning your work while you go. By this we mean, change the filename by adding a version number periodically. There is nothing worse than having done 4 hours of intense work only to find that an error crept in somewhere. Trying to undo all of the changes you have made is a true exer5cise in frustration. If you save regularly with fresh version numbers, it is quite easy to back up to the last good version. Consider it at least.
File save is reached by navigating as File>Save_As - not so tough. Simply change the name in the filename entry box on the pop-up dialogue.
Print some material from the file
Despite the idea of the paperless office, there is no such thing. Everyone prints. Printing in spreadsheets can be a cumbersome thing, since so many spreadsheets do not fit in to convenient single page widths that are several pages long. Often, the printouts are several pages wide as well. Having said that, this will get you started. Calc is a little different than Excel in navigating through the printing effort. For one thing there is no print>set area. For another, the Page Setup is actually on the Print Preview screen.
Start by Selecting a range to print using click and hold. Then navigate with Format>Print_ranges>Define.
Then navigate to File>Print Preview
And lastly, select Format Page from the second line of the toolbar. This will let you set the page type, orientation, margins, headers and footers, compression, and many other items. When you are done, click on OK to close the dialogue box, and then either select the small printer icon to immediately print the pages(s), or close the preview and navigate using File>Print.
Close the file
Well, assuming that you have saved (or have chosen to discard your work, closing the file is pretty simple. Navigate using File>Close. Umm, that’s it.
Exit the application
Again, pretty simple. Simply navigate File>Exit, or click on the red “X” at the top right hand of the Calc window.
Resources for Learning Calc
The quick and dirty instructions above are simply to demonstrate that you can access the program and start using it immediately. Obviously, there is an enormous range of capability to the platform, and few people use more than 30 or 40% of its functionality. Even fewer know more than 60% of its potential. Nevertheless, you certainly will want to know more than you do now. Here are some resources.
The Getting Started guide can be accessed here, and you might want to spend the time reviewing it. A more complete guide in chapter sections can be located here. We recommend particularly chapters 5 (Printing), and 11 (Revision Control) .
There is a FAQ for Calc with some items in it that are difficult to find in the manuals.
There is a series of pictorial introductory guides that are helpful for creating formulas and formatting pages, amongst other things. More pictorial tutorials are available as well. There are 3 animated video tutorials available that cover the basics of navigation, data input and formulas, and using the split screen layout for convenience.
There a number of blog postings with some more advanced topics like lookups, conditional formatting, and cell protection at OpenOffice Calc Tips.
Then of course, there are the forums and mailing lists for the truly unusual or arcane tricks - ask your peers there, since one of them has probably already wrestled with a similar problem. Nothing new under the sun and all that…
A Last Tip from Us
From time to time, we have run into a conversion problem in taking an Excel spreadsheet into Calc. In particular, several functions do not travel well, because they use slightly different syntax in the function. Amongst these are INFO(), and SUM(INDIRECT():INDIRECT()).
The use of INDIRECT( ) has a slightly different format in Calc. In Excel, each of the arguments are indirected, with the standard punctuation between them- :. So we use instead of A1:A6 as the argmuent for sum, indirect( ) : indirect( ). In Calc, the entire argument is nested within a single indirect, with the appropriate punctuation. So we use SUM(INDIRECT(A1 & “:” & A6). Here the ampersand & is the concatenation for a text string. Three text strings are joined then - the two cell refences on either side and the punctuation in the middle. Calc interprets this as A1:A6 in text string form. It then applies the INDIRECT function to the whole string, which resolves into the referenced cell range, which it then SUMs.
This means that any use of this function in Excel must be edited when converted to a Calc spreadsheet, and vice-versa. Note that this form of volatile or dynamic function structure is often used where calculating summary reports from data whose boundaries might shift, as when report dates change. The Harbour Forum summary statemetns use this form of structure.
We will update shortly on the conversion edit that INFO() requires.
Post a Comment