Google sheets formulas

Here are some useful formulas for Google sheets. They may help you to automate some tasks. Get the number of days left until a given date:

=DAYS("2022-10-10"; NOW())

Get life data about stocks:

=GOOGLEFINANCE("BA"; "price")
=GOOGLEFINANCE("BA"; "name")

Find more here: https://support.google.com/docs/answer/3093281?hl=en The following is useful to sum tables where the data is added offten and have a header row. Sum all values in the current column above the current cell excluding the first row:

=SUM(INDIRECT(ADDRESS(1;COLUMN())&":"&ADDRESS(ROW()-1;COLUMN())))

The following is useful to automate monthly events with the same value. Add 100 units on 15th every month:

=(DATEDIF("15.05.2019"; TODAY(); "M")+1)*100

Get the value of the "C" column in the same row:

=INDIRECT("C"&ROW())

If you need to lock specific cell when dragging a formila:

$A$1 // locks both Column and Row
$A1 // locks only the Column
A$1 // locks only the Row

Get current timestamp:

=(NOW() - DATE(1970;1;1))*86400

First day of the month:

=eomonth(today();-1)+1

First day of the month as a timestamp:

=((eomonth(today();-1)+1) - DATE(1970;1;1))*86400

Hyperlink with cell values as parameters

= hyperlink( "https://www.google.com/search?q=" & B7 & "&var2=" & INDIRECT("F"&ROW()) )

The Google sheets documentation on how to set custom cell color depending on a condition: https://support.google.com/docs/answer/78413?co=GENIE.Platform%3DDesktop&hl=en Google sheets new line:


CTRL + OPT + ENTER

Google sheets add second hyperlink to cell:

CMD + K