Calculating subtotals in a spreadsheet is very common.

When calculating the Total value of several subtotals usually the subtotals are added with a formula similar to: =C7+C14. This works flawlessly unless you add subtotals; then you must remember to add the new subtotal cell to your ‘Total’ formula.

The more elegant solution is to use SUBTOTAL(). This formula gives you the possibility to sum several values, without summing any values calculated with SUBTOTAL(). This means you can use SUBTOTAL() to calculate subtotals and the Total value. For Total value, you simply select the entire range, including subtotals.

Syntax

SUBTOTAL(function; range)

function is a number that stands for one of the following functions:

1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

range is the range of included cells

Pay attention: if you hide rows or colums included in SUBTOTAL() the cells will be counted. Excel gives the option to ignore hidden rows by using function 109 instead of 9.

I’m not trying to emulate Excel when using Calc, but I find certain ingrained habits hard to kick. Two of the shotcuts I constantly used in Excel are fill-down (ctrl-d) and fill-right (ctrl-r).

While fill-down and fill-right can be easily found in Calc (Edit -> Fill -> down / right / up / left) they don’t have the habitual shortcuts attached to them.

However, this can easily be changed:

• Tools -> Customize
• select tab ‘keyboard’
• select shortcut key Ctrl+D
• select category ‘Edit’
• select Function ‘Fill Down’
• click ‘Modify’

repeat for fill-right and any other shortcuts you might be missing.

One great feature of Calc is the possibility to ‘paste-special’ using checkboxes. Excel uses radiobuttons which only gives you the opportunity to select one option at a time. I often found myself wanting to copy the numbers AND the formatting of a cell. This meant I had to paste-special twice. Now in Calc I can simply check the appropriate boxes. Much more effective.