15 Spreadsheet Formulas Working Professionals Should Know!

👨🏻‍💻 Having worked in roles that required me to use Excel and Google Sheets extensively over the past 7 years, I thought it might be good to share 15 Google Sheets Formulas that all Working Professionals Should Know! Whether you're a beginner who have never come across Google Sheets functions, or a power user who feel comfortable combining multiple Google Sheets formulas, I hope there's something for you in this Google Sheets / Excel formula video!

For simple formulas that most people use such as VLOOKUP, there are things you want to watch out for, for example when to use "TRUE" vs. when to use "FALSE." We also cover more advanced formulas such as IMPORTRANGE, ARRAYFORMULA, CONCATENATE, and combined functions such as =IF(SEARCH())

If you're a fresh graduate who just landed a first full-time job, or a working professional who has been working for 1-5 years, you probably will come across situations where you would need to use some (if not all) of the formulas mentioned in this video, whether you work with Microsoft Excel or Google Sheets, so why not give yourself a head start by practicing today? 😉

TIMESTAMPS
00:00 DETECTLANGUAGE
00:46 VLOOKUP mistakes
01:25 Wildcard Asterisk Character
01:50 TODAY
02:15 IMPORTRANGE
03:45 COUNTIF
04:08 COUNTA
04:48 SPLIT
05:43 LEFT
05:55 RIGHT
07:02 ISEMAIL and SUBSTITUTE
08:06 ISURL
08:31 ARRAYFORMULA
09:10 CONCATENATE
10:11 &
10:37 IF and SEARCH
11:49 IFERROR
12:22 SUMIF
13:45 TRIM, UPPER, LOWER, and PROPER

RESOURCES I MENTION IN THE VIDEO

MY FAVORITE GEAR

MY FAVORITE SOFTWARE

BE MY FRIEND:

👨🏻‍💻 WHO AM I:
I'm Jeff, a full time Product Marketer. In my spare time I like to tinker with tools and create systems that help me get things done faster - or as one of my friends puts it: "Get better at being lazy" 😏

PS: Some of the links in this description are affiliate links I get a kickback from 😇

Disclaimer: My opinions are my own and may not reflect that of my employer

#formulas #functions #googlesheets

[JeffSu]

What's an "uncommon" Excel or Google Sheets formula you use all the time? 🔢

TIMESTAMPS
00:00 DETECTLANGUAGE
00:46 VLOOKUP mistakes
01:25 Wildcard Asterisk Character
01:50 TODAY
02:15 IMPORTRANGE
03:45 COUNTIF
04:08 COUNTA
04:48 SPLIT
05:43 LEFT
05:55 RIGHT
07:02 ISEMAIL and SUBSTITUTE
08:06 ISURL
08:31 ARRAYFORMULA
09:10 CONCATENATE
10:11 &
10:37 IF and SEARCH
11:49 IFERROR
12:22 SUMIF
13:45 TRIM, UPPER, LOWER, and PROPER

[aprudencio]

A couple of tips: Instead of using the left/right formulas, use the month(), year() formulas respectively.

Also, Google Sheets accepts rages like A1:A or A:A so use =counta(A1:A) so that way you do not need to update the range if more data is added in that range.

[prio]

24 seconds in the video and Jeff has already me feel special. You are the sweetest Jeff! And I appreciate you too!

[DemetriPanici]

*Once I figured out what CONCAT was at my job. Oh boy did my life change for the better 😂*

[DataProfessor]

Lovin’ those Excel formulas, didn’t know a lot of these functions and they’re extremely helpful for quickly processing data. And congrats on 100K Jeff! 😊

[kevtiew]

Hi Jeff, I was impressed by your presentation skill and fell in love after I watched the first video from you very recently. I am a new fan of your channel! I hope to catch up all with all your past videos very soon. 😁

By the way, instead of using left() and right() function to split the month and year, I find the formulas below useful when dealing with dates.

=month(date) ➡️ To convert date into month number, eg. Jan = 1, Feb = 2, Mar = 3...

=year(date) ➡️ To convert date into year, eg. 2020, 2021

=text(date, "format") ➡️ to convert into your preferred format, eg. text(date, "yyyy") for 2021,
text(date, "mmm yyyy") for Jan 2021,
text(date, "yyyymmdd") for 20210101,
text(date, "mmm") for Jan,
text(date, "mmmm") for January

Take care and wishing a great day ahead!

[mikeolsen_me]

Jeff, recently found your channel. Just a thank you for your content, style and editing. Your info and presentations are EXTREMELY helpful.
Thanks again.

[jjy]

Just discovered your channel. Super useful tips for a speadsheet newbie, love your tone and positivity. I will share this with so many people ! Thanks a lot

[missysmithy]

I appreciate this video so much, Jeff! I’m going to dedicate some time perfecting this. I want to be a spreadsheet nerd, too!

[calmontoya]

Great video, man... I like how we use the same kind of "setting" to manage the sheets, as in 'reduce the size of the first and last column' (so the content has "margins" or some space - for me, it helps a little bit while designing some formats and stuff), and also, I usually delete every row and column that I don't need, so the sheets are always clean and tidy!... now for the way you use the formulas, you taught me a couple of tricks I didn't now, so a big thank you man! Greetings from Colombia.

[yasminebouziane]

Great things to share, and I loved the way you explained them! I use most of them at my work, probably a few that I don't use a lot, but it was a good reminder to me, and the DETECTLANGUAGE one was new to me because I don't use it at all. I wish I knew something uncommon, I would be very happy to share. But I guess most formulas that I use are common in my field.

[attorneydoy]

I decided to watch this randomly earlier. And right after i watched it I received a work email that immediately put the sumif function to use! Haha such a time saver. If didnt watched this i would have done it manually more than 700 data! To answer your question, i track my expenses using google sheets, after awhile I randomly realized that it would be nice if i can automatically sum up cells having same colors, for example all red would signify all unpaid bills and if colored it green it will be considered paid. so i made some research and was able to accomplish it. Not sure if i can do it again haha ive been using it for close to 3 years. it has been my favorite custom sheet function. i labeled it “sumcoloredcells”.

[sumanjdomala]

New to your channel. Still going through all the videos. But boy this was a good video. Specially the split formula is a life saver for me.
Thanks Jeff🙏🏻

[ThiruMahal]

Great video! Thanks for highlighting the most common functions!

[iamkodenusi]

I love the cyber/infosec tip at 9:42. Always great to see and learn ways of protecting customer data! Big W, Jeff. BIG W. Would love a cheatsheet.

[leatherneck]

I managed to 90% automate an order sheet by stringing together like 6 IF functions. The order sheet had a few formulas running before i started but it was flawed. Basically id have to manually uncheck anything that we still had in stock but didnt want more of, change the quantity for items that were ordered in bulk, highlight items that had been out of stock for a considerable time as those were priority, and a heap of other things. This assortment of functions automated all of that and turned an easy 90 minute job into about 10 minutes of actual manual work.

[davidjohnsmith]

Thanks Jeff, Love your videos and your sense of humour...keep it up they are really helpful!

[willrodriguez]

Great video! I love your energy and enthusiasm for the subject matter.

[cassioalexan]

Thanks for sharing those tips, some of them I haven't used (nor known) before. Your channel is always helpful.

[specserge]

Live examples are the best! Thanks man! You're cool 😎