From Torben's Wiki
Jump to navigationJump to search

See LibreOffice Calc as well

Tips for working faster

from [1]

  • 5. Use a table to enter formulas automatically
  • 9. Insert function argument placeholders with a shortcut
    if you type "=DATE(" and then use Control + Shift + A, Excel will give you "=DATE(year,month,day)". You can then double-click each argument (or use the Function tip window to select each argument) and change it to the value you want.
  • 11. Toggle the display all formulas at once via CTRL + ~
  • 14. Use Paste Special to adjust values in place
    to convert a set of dates in place to one week later, do this: add the number 7 to any cell in the worksheet, then copy it to the clipboard. Next, select all of the dates you want to change. Then, use Paste Special > Operations > Add. When you click OK, Excel will add the number 7 to the dates you've selected
  • 15. Use named ranges to make formulas more readable (B3 * $A$1 -> B3 * const1)
  • 16. Apply names of named ranges to existing formulas (Formulas -> Define Name -> Apply Name)
  • 19. Use (Win: F4 / Mac: Ctrl + T) to toggle relative and absolute references
  • 22. Formula debugging (Windows Evaluate Formula from the formula menu / Mac: Formula builder)
  • 24. Use named ranges like variables (store text snippets for reuse in named cells)
  • 26. Add line breaks to nested IFs to make them easier to read
  • 29. Enter the same formula in multiple cells at once (1. select, 2 write in first cell, 3 CTRL + Enter)

Basic Operations

TO exclude calculated nullvalues from chart plot, replace them with "not available":


Weeknumber / Kalenderwoche


Join Strings

    ="Some String" & B8
DE: =VERKETTEN("Some String" ; B8)
EN: =CONCATENATE("Some String" ; B8)


  SEARCH(" / ";Table4[@[MyCol2]])

Search & Replace

EN: =SUBSTITUDE(D2;".";"")
DE: =WECHSELN  (D2;".";"")

Find value of last numeric cell of column

# corresponding row number

Comparing Cells

filtered Sum

Data: in A and B Goal: Filter column A and give the sum oft the corresponding B values

EN: =SUMIF(A:A; "foo";B:B)
DE: =SUMMEWENN(A:A; "foo";B:B)

for is empty / isblank



EN: =SUMIFS(B:B;"foo";A:A)
(allows several filters)


EN: =SUMPRODUCT(--(A:A="foo");(B:B))
DE: =SUMMENPRODUKT(--(A:A="foo");(B:B))
-- converts boolean values to 0 and 1

Sum of a year


Using date and today() function


Check if C in A

Data: in A and C Goal: Check each value of C if it is in A

 0 3rd parameter tells Excel to check for exact matches
 if found the row nr is returned
 else #nv

Check if C in A and if so return the value of B correspondig to A

Data: in A and C

 2 -> column B

Find corresponding X-value to max(Y)