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