If there are interested parties I will consider selling or leasing the domain.

Please contact me at: info{squiggly-thingy}ooocalc.com

I was curious whether Calc had the same problems as Excel, of whether it’s graph functionality was better.

Unfortunately I find that Calc is worse than Excel in this field. To demonstrate I created a simple chart in both Calc (left) and Excel (right) and compared them:

1. the graph-line in Calc is — in my opinion — less ’smooth’
2. on the x-axis every data-point gets its own ‘dash’ in Calc. I can’t find an option to change this
3. I can’t find a way to exactly align a graph to a cell corner. In Excel you can press <alt> while repositioning or resizing a graph and it will align perfectly to the cell. This is particularly important when you want to use multiple graphs, neatly aligned.
4. Although not part of these spreadsheets, I heavily use dynamic ranges to ‘automatically update’ graph data. I briefly tried this out in Calc, but so far I can’t get it to work.
5. I find selecting and changing parts of graphs less intuitive than in Excel. Admittedly, this might be a result of my lack of experience with Calc.

Maybe I need to work a bit more with graphs in Calc, but so far I find that Calc is less versatile than Excel.

Sample spreadsheets
simple graph.ods
simple graph.xls

In List of regular expressions you can find the wildcards you can use in Calc.

Download Calc Default Shortcuts Keys

Source

• I change header and footer information to be able to identify printed pages.
• I change the basic font to Verdana (just a personal preference).
• I add a documentation sheet to my spreadsheet. Since I keep forgetting — I know I’m not the only one — to document my spreadsheets.

The poking of my C64 couldn’t be automated (hence I still remember the codes), but, with the use of spreadsheets, it is very easy to change the defaults in any Calc spreadsheet.

1. Open the Standard spreadsheet template spreadsheet (or a blank one)
2. Make all the changes you like to have
3. go to File -> Templates -> Save
4. Select a name and a category for your template
5. click ok
6. done!

If you want to make the template your default one:

1. go to File -> Templates -> Organize
2. select the template (within the category)
3. right-click the template and select ’set as default template’
4. done!

I´ve found it hard to decide whether to show only results from the websites I´ve selected or to have Google emphasize the selected Calc websites. In the end I´ve decided to go for the second option. This means that for certain search terms which relate to other spreadsheets, such as Excel, you will get results from Excel websites.

If you have any suggestions for the search engine, I would love to hear them from you.

The printing calculator spreadsheet is the replacement I use for the old trusted calculator with the paper strip. I find it far easier to use than the ‘original’ calculator, especially when trying te find errors in an addition.

How does it work? Enter numbers in the yellow cells and the SUM formula at the start of the columns and rows adds the numbers (I told you it’s not very complicated). The worksheet is formatted to be printed on one page, so if need be, you can also print the numbers.

Download: Printing Calculator.ods

The solution (as you might have guessed from the title of this post) is the use of SUBTOTAL which will only count or average the selected cells.

You can download an example using SUBTOTAL and autofilter here: Population of countries.ods.

Also check out the SUBTOTAL article.

With information from the excellent le Tableur Calc par l’exemple (French) I put the functions in seven languages (English, German, French, Dutch, Spanish, Italian, Polish) side by side in a Calc reference sheet.

Download Calc Multilingual Functions.ods

3 examples: sort order = 0 / sort order = 1 / 3. IF(ISNA())

VLOOKUP() offers the possibility to lookup criteria in an array and then return corresponding values from that array.

Syntax
=VLOOKUP(Search criterion;array;index;Sort order)

Search criterion is the value searched for in the first column of the array.

• array is the reference, which is to comprise at least two columns.
• index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
• Sort order is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

Sort order = FALSE (0) (screenshot 2) I usually set sort order = FALSE since I want to know when there is no exact match with the search column. When there is no exact match you will get the error message #N/A. This usually means that a row needs to be added to the array.

IF(ISNA()) (screenshot 3)The problem with using sort order = FALSE is that it gives the error message #N/A which is ugly, but — more importantly — will break other functions that reference the cell with the error message. The solution is to use the IF(ISNA()) function to look for the #N/A message to give a different result (i.e. “”) when VLOOKUP() returns an error.