You’ve all read them:
“10 Excel tips to boost your productivity”
Great! But how can you use those formulas to create a tool?
Learn to build your 1st Google Sheets app in under 10 minutes! 🧵
Daily Google Sheets Cheat💡
Standard deviation is a measure of variability.
It’s used often in:
💰 Finance
📊 Business
💊 Medicine
=STDEV(C3:C13)
• C3:C13 - data to get standard deviation from
It’s easy to calculate in Sheets!👇
Daily Google Sheets Cheat💡
2023 resolution: improve my statistical background 🤓
Variance is used to measure deviation in a data set 📊
=VAR(C3:C13)
• C3:C13 - values to get variance from
Check it out👇
Daily Google Sheets Cheat💡
Statistics made easy 📊
With Sheets, you can return many statistical values without calculating it yourself!
=QUARTILE(C3:C13,1)
• C3:C13 - data to get quartile from
• 1 - retrieves first quartile
It works like this👇
Daily Google Sheets Cheat💡
Sheets recently added a new functionality! 🆕
Dropdown chips ☑️
Make better to do lists and track your progress!
It’s really easy👇
Daily Google Sheets Cheat💡
You can calculate many averages in Sheets:
- mean
- mode
- median
But did you know you can also calculate the weighted average? ⚖️
=AVERAGE.WEIGHTED(C3:C4,D3:D4)
• C3:C4 - values to get average from
• D3:D4 - weight of values
Check it out👇
Daily Google Sheets Cheat💡
This trick will surely impress your colleagues 😯
Learn to quickly apply 1 formula to a whole array! ✔️
=ARRAYFORMULA(C3:C8*D3:D8)
• C3:C8*D3:D8 - arrayformula
The formula multiplies C3 & D3, C4 & D4, etcetera.
Example👇
Daily Google Sheets Cheat💡
Who’s the best salesperson? 💰
Let’s rank them!🥇
=RANK(C3,C3:C13)
• C3 - value to rank
• C3:C13 - data to rank value by
Check it out👇
Daily Google Sheets Cheat💡
Need a data set to practice your spreadsheet skills?
It’s easy to create some fake sales numbers! 🔢
=RANDBETWEEN(15000,20000)
• 15000 - minimum
• 20000 - maximum
Drag the formula over the cells, then copy and paste values only ✅
Example👇
Daily Google Sheets Cheat💡
Limit your dataset! ✂️
=ARRAY_CONSTRAIN(B2:D10,9,2)
• B2:D10 - array/range to constrain
• 9 - number of rows to return
• 2 - number of columns to return
Check it out 👇
Daily Google Sheets Cheat💡
There’s no people chart in Google Sheets! 😦
We can solve this with the REPT formula 👫
=REPT(“|”,C3)
• “|” - text to repeat
• C3 - number of times to repeat
It works like this👇
Daily Google Sheets Cheat💡
Yesterday we detected languages 🔍
Let’s translate them today! 🔡
=GOOGLETRANSLATE(B3,C3,“en”)
• B3 - text to translate
• C3 - language to translate from
• “en” - language to translate to
Check it out👇
Daily Google Sheets Cheat💡
Google Sheets meets Google Translate 🤝
Don’t know what language you’re looking at? Sheets will tell you! ✅
=DETECTLANGUAGE(B3)
• B3 - text to consider
Too easy!👇
Daily Google Sheets Cheat💡
Improve your decision-making ☑️
Never be surprised again, forecast your progression 📈
=FORECAST(B9,C3:C8,B3:B8)
• B9 - new x data to forecast
• C3:C8 - known y data
• B3:B8 - known x data
Check it out👇
Daily Google Sheets Cheat💡
Stop copy pasting ❌
Import data with a single formula! ✅
=IMPORTHTML(url,“table”,1,“en_US”)
• url - url of page to examine
• “table” - data to import (table or list)
• 1 - index of table to import
• “en_US” - language (optional)
Example👇