I had big plans for this domain, but unfortunately I can’t find the time to pursue them.

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

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

In business spreadsheets I like to incorporate small, simple charts to quickly convey trends. Although the basic graph templates in Excel are very ‘cluttered’, you can improve them dramatically with some simple modifications.

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

Calc allows you to use regular expressions (wildcards) in filters and functions. However, if you want to use wildcards in functions, this must be enabled via Tools -> Options-> OpenOffice.org Calc-> Calculate page

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

I use shortcut keys as much as possible, so it’s very easy that OOoAuthors.org has published an appendix with the default Calc shortcut keys. I prefer to have them all together in one Calc sheet, which I added to this post.

Download Calc Default Shortcuts Keys

Source

How wonderful it is to start a new spreadsheet with a new, blank, page in front of you. However, as I have done from an early age onward, I always feel the need to immediately change some basic things. In my Commodore 64 days this used to be: poke 53280,0 / poke 53281,0 to change the background color of my computer screen to black. In spreadsheets I usually change the following:

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

Google offers the possibility to create custom search engines. I went ahead and created a custom search engine for Calc related websites. You can use this search engine from the top of every page of this website.

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.

My printing calculator spreadsheet isn’t a very complex one by any stretch of the imagination, however I use it frequently. Because of it’s lack of sophistication I wouldn’t have shared it on this blog until a colleague of mine saw me use it and wanted a copy immediately.

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

When using an autofilter (data -> filter -> autofilter) you can quickly and easily make selections in a data range. However the use of common functions such as AVERAGE or COUNT will create a problem because in these functions you will need to define a range. The defined range will be averaged or counted even if certain cells are hidden by the use of a filter.

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.

When you use Calc in several languages — I use the Dutch and English language version — you’re sometimes looking for a function name in an other language. You could open the file in the other language version of Calc to find the name to use, but it’s far easier to have a reference sheet.

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.