12 Google Sheets formulas guaranteed to increase your productivity: ๐
ARRAYFORMULA
Why enter 3 formulas when 1 will do the trick?
Array formulas allow you to process data in a "batch" rather than individual calculations.
Here we pull the monthly sales for each "widget" & quickly calculate the Q1 total.
Formula:=ARRAYFORMULA(C3:C5+C7:C9+C11:C13)
Why enter 3 formulas when 1 will do the trick?
Array formulas allow you to process data in a "batch" rather than individual calculations.
Here we pull the monthly sales for each "widget" & quickly calculate the Q1 total.
Formula:=ARRAYFORMULA(C3:C5+C7:C9+C11:C13)
SPLIT
Split text from one cell into multiple.
Formula: =SPLIT(B3,โ โ)
This will take the contents of B3 and separate it into C3 & D3 based on the delimiter you place inside " ".
In this case, we'll separate the First & Last names based on the "space" in between.
Split text from one cell into multiple.
Formula: =SPLIT(B3,โ โ)
This will take the contents of B3 and separate it into C3 & D3 based on the delimiter you place inside " ".
In this case, we'll separate the First & Last names based on the "space" in between.
CONCATENATE
Combine the contents of multiple cells into a single cell.
Formula: =CONCATENATE (B3,โ โ,C3)
Using the same sheet from our example above, let's try it in reverse.
We'll now take the data in cells C3 + D3 and combine it to populate B3 with a single clean entry.
Combine the contents of multiple cells into a single cell.
Formula: =CONCATENATE (B3,โ โ,C3)
Using the same sheet from our example above, let's try it in reverse.
We'll now take the data in cells C3 + D3 and combine it to populate B3 with a single clean entry.
QUERY
Search large data sets to find & extract items that meet specified criteria.
Formula:=QUERY(B3:C14, "select B where C>50000")
In this example, we'll pull out the names from column B with over 50K followers in column C.
This is only the beginning of what QUERY can do!
Search large data sets to find & extract items that meet specified criteria.
Formula:=QUERY(B3:C14, "select B where C>50000")
In this example, we'll pull out the names from column B with over 50K followers in column C.
This is only the beginning of what QUERY can do!
IMPORTRANGE
This one takes practice, but once you figure it outโโit's worth it.
Automatically pull data from one tab (or spreadsheet) into another.
The data in the receiving sheet (a dashboard for example) updates dynamically.
=IMPORTRANGE(spreadsheet_url, range_string)
This one takes practice, but once you figure it outโโit's worth it.
Automatically pull data from one tab (or spreadsheet) into another.
The data in the receiving sheet (a dashboard for example) updates dynamically.
=IMPORTRANGE(spreadsheet_url, range_string)
VLOOKUP
Searches for a certain value in a columnโโwithin a given range.
Then returns a value from a different column in the same row.
Formula:=VLOOKUP(B8,A3:C6,2,false)
โข B8 = Search value
โข A3:C6 = Range
โข 2 = Column to return value from
โข False = Exact match
Searches for a certain value in a columnโโwithin a given range.
Then returns a value from a different column in the same row.
Formula:=VLOOKUP(B8,A3:C6,2,false)
โข B8 = Search value
โข A3:C6 = Range
โข 2 = Column to return value from
โข False = Exact match
QR CODES
It seems everyone has a smartphone these days...
Generating an easily scannable image to drive traffic to your site seems like a no-brainer.
โขCopy + paste formula below
โขChange "A1" to the cell with your URL
โข๐ฅ Free QR code
=IMAGE("chart.googleapis.com"&A1&"")
It seems everyone has a smartphone these days...
Generating an easily scannable image to drive traffic to your site seems like a no-brainer.
โขCopy + paste formula below
โขChange "A1" to the cell with your URL
โข๐ฅ Free QR code
=IMAGE("chart.googleapis.com"&A1&"")
SUMIF
Perform "conditional calculations."
Formula:=SUMIF(C3:C12, "Home Decor", D3:D12)
In our example, we have a several products spread across multiple categories.
SUMIF lets us easily see the total for all widgets in only the specified group. "Home Decor"
Perform "conditional calculations."
Formula:=SUMIF(C3:C12, "Home Decor", D3:D12)
In our example, we have a several products spread across multiple categories.
SUMIF lets us easily see the total for all widgets in only the specified group. "Home Decor"
DETECTLANGUAGE / TRANSLATE
A simple set of formulas you can use to:
โข Detect what language is being used
๐๐ป
โข Translate it into another language of your choice
Formulas:
=DETECT(E2)
=GOOGLETRANSLATE(E2,F2,"en")
Check it out!
A simple set of formulas you can use to:
โข Detect what language is being used
๐๐ป
โข Translate it into another language of your choice
Formulas:
=DETECT(E2)
=GOOGLETRANSLATE(E2,F2,"en")
Check it out!
SPARKLINES/Progress Bars
What good are goals if you can't track against them?
Good newsโโSheets make it simple to see a visual representation of your progress using SPARKLINES.
Easily embed a miniature bar chart inside any cell.
=SPARKLINE(B2:C2,{"charttype","bar";"max",C2})
What good are goals if you can't track against them?
Good newsโโSheets make it simple to see a visual representation of your progress using SPARKLINES.
Easily embed a miniature bar chart inside any cell.
=SPARKLINE(B2:C2,{"charttype","bar";"max",C2})
PIVOT TABLES
OKโโthis one isn't technically a formula, but...
If you're looking to quickly summarize large groups of data, Pivot Tables are the way to go.
Easily label, sort, and organize based on your preference and how you want to present the information.
Watch & Learn ๐๐ป
OKโโthis one isn't technically a formula, but...
If you're looking to quickly summarize large groups of data, Pivot Tables are the way to go.
Easily label, sort, and organize based on your preference and how you want to present the information.
Watch & Learn ๐๐ป
GOOGLEFINANCE - Bonus
Check out this free investment tracker template from @googlefinance:
โขMonitor Trends
โขView historical data
โขBuild your own watch list & more!
Link to the free sheet here:
docs.google.com
Check out this free investment tracker template from @googlefinance:
โขMonitor Trends
โขView historical data
โขBuild your own watch list & more!
Link to the free sheet here:
docs.google.com
That's all for today!
If you enjoy learning new things about Excel, Google Sheets, and productivity in general, follow me @blakeaburge.
Also, join 20K+ people and check out my newsletter:
NERD ALERT ๐จ
3 Tech Tips | Every Sunday | 5 Minutes
blakeburge.com
If you enjoy learning new things about Excel, Google Sheets, and productivity in general, follow me @blakeaburge.
Also, join 20K+ people and check out my newsletter:
NERD ALERT ๐จ
3 Tech Tips | Every Sunday | 5 Minutes
blakeburge.com
Loading suggestions...