Excel

From Torben's Wiki

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":

=NA()

Weeknumber / Kalenderwoche

=IF(A1<WEEKNUM(TODAY());COUNT(A3:A55);#NV)

Join Strings

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

Substrings

=RIGHT(A1;3)
=LEFT(A1;SEARCH("/";A1)-1)

Search & Replace

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

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)

or

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

or

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

=SUMIFS(B:B;A:A;">=01.01.2018";A:A;"<01.01.2019")
better:
=SUMPRODUCT(--(YEAR(A:A)=2018);B:B)

Check if C in A

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

=VERGLEICH(C1;A:A;0)
 0 3rd parameter tells Excel to check for exact matches
 if found the row nr is returned
 else #nv
D1 : =VERGLEICH(C1;A:A;0)
E1 : =WENN(ISTFEHLER(D1);"";D1)

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

Data: in A and C

D1 : =VERGLEICH(C1;A:A;0)
E1 : =INDIREKT(ADRESSE(D1;2))
 2 -> column B
F1 : =WENN(ISTFEHLER(E1);"";E1)

Find corresponding X-value to max(Y)

EN: =INDEX(A:A;MATCH(MAX(B:B);B:B;0))
DE: =INDEX(A:A;VERGLEICH(MAX(B:B);B:B;0))