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)
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.
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.
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!
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)
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
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/chart?chs=200x…"&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"
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!
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})
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 👇🏻
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/spreadsheets/d…
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
Share this Scrolly Tale with your friends.
A Scrolly Tale is a new way to read Twitter threads with a more visually immersive experience.
Discover more beautiful Scrolly Tales like this.