Cheet’s Sheets

[Cheet’s Sheets @CheetSheets]

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! 🧵

[Cheet’s Sheets @CheetSheets]

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!👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

Resolution for 2023: improved budget planning 💵

This is a great start👇

[Blake Burge @blakeaburge]

This week I built an easy-to-use "One Page Budget" planner in Google Sheets.

Here's a breakdown of how it works and a template you can use: 📊

[Cheet’s Sheets @CheetSheets]

@blakeaburge Loving the graphs in your budget planner!

I like to add slicers to make them interactive 👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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 👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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!👇

[Cheet’s Sheets @CheetSheets]

Excellent list of useful Excel skills👇

[Blake Burge @blakeaburge]

5 Excel Skills you should have learned in school (but didn't): 📊

[Cheet’s Sheets @CheetSheets]

@blakeaburge Yesssss, & is not used often enough!

JOIN is a great alternative by the way👇

[Cheet’s Sheets @CheetSheets]

Daily Google Sheets Cheat💡

Short cut time 🏃‍♂️💨

Ctrl + ; = datestamp 📅
Ctrl + Shift + ; = timestamp 🕰️

It’s easy!👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

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👇

[Cheet’s Sheets @CheetSheets]

Daily Google Sheets Cheat💡

Your data should be easily readable ✅

2 quick ways to format your data:

🎨 Alternate colors
❄️ Freeze rows

It’s easy👇