A collection of miscellaneous tips for building production office spreadsheets.

See these formulas in action in this Google Sheet

Formulas

Date and Time formulas

Formula to calculate next Friday, Saturday, and Sunday’s date:

next Friday:
=today() - weekday( today() ) + 6

next Saturday:
=today() - weekday( today() ) + 7

next Sunday:
=today() - weekday( today() ) + 1

Formula to add time based on a cell:

Adding 1 hour, 6 minutes, and 0 seconds to the time shown in cell A1:
=A1 + time( 1,6,0 )

Formula to convert time in hours & minutes to decimal:

=A1 * 24

note: the calculated cell must be formatted to a number format with 1 decimal.

Formula to calculate the last day of the month:

=eomonth( date , 0 )

Formula to show the date / time it is now:

today's date
=today()

current time
=now()

note: these formulas recalculate every time a change is made in the spreadsheet.

Useful formulas when dealing with strings

Formula to join multiple emails stored in a vertical array:

=CONCATENATE( ARRAYFORMULA( A1:A2 & ", " ) )

Formula to split a multi-word string into two columns:

splitting words separated by a space
=split( A1 , " " )

splitting words separated by a dash
=split( A1, "-" )

Formula to capitalize each word in a string:

=proper( A1 )

Formula to remove unnecessary spaces:

=trim( A1 )

Conditional formatting formula to highlight duplicate values:

=countif( A:A , A1 ) > 1

Mathematical formulas

Formula to skip hidden rows when performing math operations:

sum of values in a range
=subtotal( 109 , range )

count of values in a range
=subtotal( 103 , range )

average of values in a range
=subtotal( 101, range )

Formula to round numbers to the 4th decimal for calculation purposes, regardless of the reference cell’s format:

=ROUND( A1 , 4 )

Miscellaneous formulas

IF statement to trigger an action if cell A1 is not blank:

=IF( A1<>"" , value_if_true , value_if_false )

Function to prevent any errors from being displayed:

=IFERROR( A1 )

Hyperlink to a specific cell:

=HYPERLINK( "#gid=0&range=A1" , "link label" )

note: The ‘gid’ value is located in the spreadsheet’s URL and is based on the active tab

Formula to switch rows to columns or columns to rows:

=transpose ( range )

Odd Syntaxes

Syntax to use a cell reference with the query() function:

=QUERY( data , "SELECT * WHERE A = '"&A1&"' " )

Syntax to use dates with a query() function:

using a date written as a string
=QUERY( data , "SELECT * WHERE A = date 'yyyy-mm-dd' " )

using a cell reference wrapped in a text() function
=QUERY( data , "SELECT * WHERE A = date '"& TEXT(A1,"yyyy-mm-dd") &"' " )

Syntax to use the ‘or’ operator with a filter() function:

=FILTER( range, (condition) + (condition) )

Syntax to use the ‘or’ operation with a countifs() function:

=SUM( ARRAYFORMULA( COUNTIFS( A1:A , {"criterion 1" , "criterion 2" } ) ) )

How to stack multiple arrays:

horizontally
={ A1:B1 , A2:B2 }

vertically
={ A1:B1 ; A2:B2 }

Other

Formula to retrieve the exchange rate to U.S. Dollars:

exchange rate from Euros to U.S. Dollars
=GOOGLEFINANCE( "CURRENCY:EURUSD" )

using a cell reference:
=GOOGLEFINANCE( "CURRENCY:" &A1& "USD" )

Handy googlefinance() currency codes:

Region – Currency	Code
Euro Member Countries – Euro	EUR
Canada – Dollar	CAD
Australia – Dollar	AUD
New Zealand – Dollar	NZD
South Africa – Rand	ZAR
Singapore – Dollar	SGD
Malaysia – Ringgit	MYR
Thailand – Baht	THB
China – Yuan	CNY
United Arab Emirates – Dirham	AED

Values for weather unicode characters (char() function):

Char() value	Description	Result
127780	sun with small cloud	?
127781	sun behind cloud	?
127782	sun behind cloud with rain	?
127783	cloud with rain	?
127784	cloud with snow	?
127785	cloud with lightning	?
127762	waxing crescent moon	?

Thumbnail designed by slidesgo / Freepik