Excel

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

See LibreOffice Calc as well

Shortcuts

Insert Date / Datum einfügen CTRL+SHIFT+;

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)
=IF(
 ISNUMBER(
  SEARCH(" / ";Table4[@[MyCol2]])
 );
 RIGHT(Table4[@[MyCol2]];3);
 ""
)

Search & Replace

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

Find value of last numeric cell of column

=LOOKUP(2;1/(ISNUMBER(T2:T9999));T2:T9999)
# corresponding row number
=LOOKUP(2;1/(ISNUMBER(T3:T10000));ROW(T3:T10000))

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

=SUMIF(A:A;"<>"&"";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)

Using date and today() function

=SUMIFS($C:$C;$A:$A;">="&today())

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))

Statistics

Weighted Average

from https://blog.hubspot.com/marketing/weighted-average-excel

for A: values, B: weight
EN: =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)

Generate Sequence of Dates

="01.10.2022"+SEQUENCE(1+TODAY()-"01.10.2022";;0)

VLookup

Source data

A: Dates with gaps
B: Corresponding values

Target

D: Sequence of dates, see above
E: Value of B, that has the nearest lower value A to D 
=VLOOKUP(D2;A:B;2;TRUE)