15 Spreadsheet Formulas Working Professionals Should Know!

Рет қаралды 119,947

Күн бұрын

👨🏻‍💻 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
📧 Subscribe to my Productivity newsletter - www.jeffsu.org/productivity-p...
MY FAVORITE GEAR
🎥 My KZfaq Gear - geni.us/youtube-gear
🎒 What's In My Bag - geni.us/mybag
💻 What's On My Desk - geni.us/mydesk
🛩 What I Travel With - geni.us/mytravel
MY FAVORITE SOFTWARE
✍️ Skillshare - geni.us/skillshare-jeff
🎨 Canva - partner.canva.com/jeffsu
BE MY FRIEND:
📧 Subscribe to my Productivity newsletter - www.jeffsu.org/productivity-p...
📸 Instagram - / j.sushie
🤝 LinkedIn - / jsu05
👋🏻 Clubhouse - www.joinclubhouse.com/@jsushie
👨🏻‍💻 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" 😏
If you'd like to talk, I'd love to hear from you. Messaging me on Instagram (@j.sushie) directly will be the quickest way to get a response!
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

Пікірлер: 313

@JeffSu 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

@iPH oh wow that sounds interesting. 3 common formulas put together in an uncommon way 😂

@markloughtonUK 2 жыл бұрын

I have a Google spreadsheet that contains a price list. I then have various tabs that pull the data in but convert with a GOOGLEFINANCE formula and then I embed the whole workbook in a web page so the customer sees a real time price list and can click between currencies. I use the IMPORTRANGE to pull the base price list in to another workbook that converts to trade prices. All works a treat :)

@JeffSu 2 жыл бұрын

@@markloughtonUK Oh wow! That actually made a lot of sense over text 😂, I can imagine those tabs in my head right now. That's awesome Mark!

@RandolphAbelardo 2 жыл бұрын

Query(). Works like ImportRange(), but with added versatility in manipulating data.

@JeffSu 2 жыл бұрын

@@RandolphAbelardo oh nice! I haven’t used that one to be honest I should check it out

@aprudencio 2 жыл бұрын

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.

@JeffSu 2 жыл бұрын

That makes a lot of sense!! Thanks for sharing!

@garrytreymendeziii5650 Жыл бұрын

Plus, wouldn’t using the LEFT function include every entry from January, October, November and December in the count since the leftmost character for dates in all four of those months is 1?

@dakshbhatnagar 11 күн бұрын

@@garrytreymendeziii5650 Months could be 10,11,12. There the formula would also break. A better way he can do is using =REGEXEXTRACT(cell_reference, "\d{1,2}")*1 to fetch the month and =REGEXEXTRACT(cell_reference, "\d{4}")*1 for the year values and these will be numerical since we would multiply the result by 1

@prio2483 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Awwww anytime 😉

@DataProfessor 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Thank you my friend!! Appreciate it!

@tskali9686 2 жыл бұрын

this man is a legend, thank you Jeff

@JeffSu 2 жыл бұрын

Woohoo thank you Tskali!

@DemetriPanici 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Hahahaha I think I felt the same way about Pivot Tables at first 😅

@kevtiew2989 2 жыл бұрын

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!

@alisonnjackson9120 2 жыл бұрын

Thanks for adding the text. Super helpful.

@JeffSu 2 жыл бұрын

Oh that's a GREAT point, I didn't know that Kev! Thank you so much for sharing this is great!!

@TheOfficialABHS 2 жыл бұрын

also using a length of 1 means it only works for part of the year, it won't work for October through December

@milianmilianmilian Жыл бұрын

@@TheOfficialABHS Great solutioon. Would've been my question seeing the video at 6:00. =month(date) makes sense then

@mikeolsen_me 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Happy to hear it Mike, thanks so much for the kind comment 😁

@missysmithy25 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Wooohooo welcome to the club :)

@ThiruMahal 2 жыл бұрын

Great video! Thanks for highlighting the most common functions!

@JeffSu 2 жыл бұрын

Thank you Thiru!!

@jjy9319 Жыл бұрын

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

@JeffSu Жыл бұрын

Woohooo thanks Jonathan 😁

@calmontoya 2 жыл бұрын

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.

@JeffSu 2 жыл бұрын

Glad to hear I'm not the only one who does that! Thanks so much Carlos 😁

@willrodriguez1224 Жыл бұрын

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

@JeffSu Жыл бұрын

Thank you so much Will! 😁

@iamkodenusi 2 жыл бұрын

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.

@JeffSu 2 жыл бұрын

Thank you Kolawole! I'm a big proponent of data privacy, thank you 😁

@sumanjdomala9671 2 жыл бұрын

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🙏🏻

@JeffSu 2 жыл бұрын

Welcome to the fam Sumanj!! Happy to have you here 😁

@yasminebouziane8843 2 жыл бұрын

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.

@JeffSu 2 жыл бұрын

No worries Yasmine! Thanks for letting me know 😁

@specserge 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Glad to hear it Sergey!! Thank you 😁

@lukev730 2 ай бұрын

Amazing work

@JeffSu 2 ай бұрын

Thanks 😁

@davidjohnsmith5417 Жыл бұрын

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

@JeffSu Жыл бұрын

Glad to hear it David! Will do 😁

@cassioalexan 2 жыл бұрын

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

@JeffSu 2 жыл бұрын

Glad to hear it Cassio! Thank you 😁

@evag3232 2 жыл бұрын

I have been waiting for this soooooooo long !!! Thanks Jeff

@JeffSu 2 жыл бұрын

Glad you don't have to wait any longer 😁

@Mr_Versano 2 жыл бұрын

Awesome as always. Thanks Jeff!

@JeffSu 2 жыл бұрын

Thanks Kobi!! Glad to hear it!

@kanavgupta4930 2 жыл бұрын

jeff is legend , soo much amazing formulas to ease our work

@JeffSu 2 жыл бұрын

Wooohooo! Love to hear that, thanks Kanav!

@yokichuchen 2 жыл бұрын

Can't thank you enough!!! Wish your channel will soon grow into 1 mil!!

@JeffSu 2 жыл бұрын

Why stop there 😉

@eguchiyuuki Жыл бұрын

wonderful!

@JeffSu Жыл бұрын

Thank you!! 😁

@lillogadget 2 жыл бұрын

That intro was 🔥awesome stuff Jeff!

@JeffSu 2 жыл бұрын

Thank you Billie, hope you found it entertaining 😂

@hashedalnasef6593 10 ай бұрын

Thanks Jeff

@JeffSu 10 ай бұрын

You're very welcome 😁

@Virak_mao 2 жыл бұрын

Thank you so much for your creative sharing brother.

@JeffSu 2 жыл бұрын

You're very very welcome!

@SherilynHanson Жыл бұрын

yay! thank you! i finally found the exact formula i was looking for!

@JeffSu Жыл бұрын

That's awesome to hear Sherilyn! 😁

@phasomyr 9 ай бұрын

This was unbelievably helpful. Thank you!

@JeffSu 9 ай бұрын

You're very welcome!

@sandychong3037 2 жыл бұрын

" //chuckles, You are a bold one" But seriously thank you for another amazing video Jeff!!

@JeffSu 2 жыл бұрын

Hahaha glad to hear it Sandy!

@properpropertiesmanagement7910 2 жыл бұрын

I love your channel so much. Keep up the great work 👍🏼

@JeffSu 2 жыл бұрын

Thank you!! Will do 😁

@markloughtonUK 2 жыл бұрын

I had a CSV given to me yesterday with names and addresses in ready to import into a new system except it wasn't because some names had extra spaces and lower case letters where they should be capitalised and I combined some of these formulas to tidy it right up. Lovely Jubley :)

@JeffSu 2 жыл бұрын

Nice! That's a perfect example of a real-world usecase!

@NormanVsNorman 2 жыл бұрын

Damn! Like, I want to use all the formulas even if I don't need them all! Super cool tips! Great content, and very well explained!

@JeffSu 2 жыл бұрын

Hahaha that's great to hear! I feel the same sometimes when I come across a new formula!

@leatherneck7778 5 ай бұрын

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.

@JeffSu 5 ай бұрын

That's awesome!

@legenderekgo 2 жыл бұрын

I actually bookmarked your video in my work laptop. Super helpful stuff!

@JeffSu 2 жыл бұрын

Awwwww, not your personal laptop?

@marcobendistinto1931 2 жыл бұрын

This is the first video I have seen from you and I immediately liked it and subscribed. After the video I went to search in your channel if you had more videos like this one. I hope you do more. Btw if you do a video like this one on add ons you use it will be super useful. Thank you for this video I'll be using all of these formulas.

@JeffSu 2 жыл бұрын

Glad to hear it Marco! And will do!

@attorneydoy 2 жыл бұрын

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”.

@JeffSu 2 жыл бұрын

Nice!! That's great to hear, thanks for sharing!

@Kytreeswerving 2 жыл бұрын

Dude I'm Salesforce Consultant and your videos are everything I need in my life.

@JeffSu 2 жыл бұрын

Glad to hear it Arthur 😂

@eightfoldsg 2 жыл бұрын

Thanks Jeff!!

@JeffSu 2 жыл бұрын

You're welcome Kelvin!

@akshaynitinvaidya Жыл бұрын

Goddd very helpful! thank you so much!

@JeffSu Жыл бұрын

You're very welcome 😁

@increator1 Жыл бұрын

=D Have a great one. You really come across like Guy from the movie Free Guy. Thank you for the video. I really got some value out of this. I find some of the formulas really useful for my budget analysis.

@JeffSu Жыл бұрын

Thanks for the compliment Henri 😁

@kakalkairuchi495 Жыл бұрын

Very cool video! Liked your way of teaching...

@JeffSu Жыл бұрын

You're very welcome! Glad you think so 😁

@Andrea_Dong 2 жыл бұрын

Watching this video has made me realize just how much of an Excel noob I am 😂 Thanks for the tips, Jeff! I’ll definitely refer back to this video if I find it applicable ^-^

@JeffSu 2 жыл бұрын

Hahaha we've all been there Andrea!

@loklok9603 2 жыл бұрын

Thanks Jeff, this is really cool

@JeffSu 2 жыл бұрын

Glad to hear it! Thank you lok lok!

@casadesapa 7 ай бұрын

Really enjoyed this ❤ thank youuuu!!!! I was engaged the whole time and learned a lot

@JeffSu 7 ай бұрын

Glad to hear it!!

@gustavocampos805 2 жыл бұрын

your content is amazing! Thanks

@JeffSu 2 жыл бұрын

Thank you Gustavo! My viewers are amazing as well 😁

@jakefairhall3832 2 жыл бұрын

Love it Jeff !

@JeffSu 2 жыл бұрын

Thank you Jake!! :)

@amitshreechandan 2 жыл бұрын

please make a video on Notion Workspace Setup for beginners

@JeffSu 2 жыл бұрын

Thanks for the idea Amit!

@onetwothree7625 2 жыл бұрын

The accent of "started" 😆! This is such an amazing video

@JeffSu 2 жыл бұрын

Haha thank you!

@Ousman2ray 2 жыл бұрын

Jeff, I’m here again man. Call me O. I called you my favorite KZfaqr today. And your intros- legendary! 👊🏾

@JeffSu 2 жыл бұрын

Hi O! Thank you my friend!! 😁

@TechnoBoomer 2 жыл бұрын

Hi Jeff and many thanks for this very useful video. On my side, I usually use regex but I have to admit G Sheet seems very powerful. For sure, this is one of your best videos ! Cheers. François

@JeffSu 2 жыл бұрын

Hey François! Did you change your channel name? Or is this a new channel? I love the new name regardless! Thank you as usual!!

@TechnoBoomer 2 жыл бұрын

@@JeffSu I changed my channel name because I am working on a new video project ;-)

@JeffSu 2 жыл бұрын

@@TechnoBoomer nice! Best of luck!!

@benchia 2 жыл бұрын

This is some S tier content!

@JeffSu 2 жыл бұрын

Can't argue with you there Ben 😉

@chansterray 2 жыл бұрын

Thanks Jeff!

@JeffSu 2 жыл бұрын

You're welcome Ray!

@alex-coelho 2 жыл бұрын

why can't I like this twice!?

@JeffSu 2 жыл бұрын

You can show your support by liking all the other videos 😂

@zagfat 11 ай бұрын

Well presented video. Im interested in google sheet, but yet to understand why people use it for work. It is so basic.

@JeffSu 11 ай бұрын

Some people (like myself) likes to keep things basic 😁

@beatrizgonzalez5247 Жыл бұрын

Thank you for that language translator tip. I do work with information from different countries and need to consolidate them in English

@JeffSu Жыл бұрын

That's great to hear! Glad you found a useful tip from this video 😁

@dark-eye4727 2 жыл бұрын

Amazing .. thank’s a Lot 😍

@JeffSu 2 жыл бұрын

You're very very welcome 😁

@vaskard905 Жыл бұрын

excellent !

@JeffSu Жыл бұрын

Thanks Vaskar 😁

@kambleji 2 жыл бұрын

Hi jeff, loved your video. Might be useful for me in the near future. Keep posting such videos. I'd be delighted if you post video on some tips to get a desired job for freshers or people with no experience.

@JeffSu 2 жыл бұрын

Will do, thank you my friend :) For people with absolutely no experience, my networking videos are definitely very relevant!

@kambleji 2 жыл бұрын

@@JeffSu Sure, I'll check them out. Thanks btw

@JeffSu 2 жыл бұрын

@@kambleji np

@josephhetherwick715 Жыл бұрын

“Hello there.” was general kenobi’s line. ButGeneralGrievisesaidAhh,General Kenobi. Delimiter separate that. Ps. I love your videos. The practical content is worth my time, the humor keeps it enjoyable.- Sales Department. Lol, But no really.

@JeffSu Жыл бұрын

Hahahahahahahahahahaha good one, thank you Joseph!

@adarshlawhare7771 2 жыл бұрын

I really loved ur dressing sense 👔 It's really awesome ✨

@JeffSu 2 жыл бұрын

Thank you Adarsh! I have my fashionable friends to thank for that 😂

@adarshlawhare7771 2 жыл бұрын

@@JeffSu Thanks for the reply Sir. We are also waiting for the Vlog ✨

@hanaazqazi6261 2 жыл бұрын

banger of an intro

@JeffSu 2 жыл бұрын

Thank you Hanaaz!! :)

@ruchapande892 2 жыл бұрын

@jeff su jeffyyyyyy perfectly timed video.. I needed to learn these formulea.. Yet to watch the video but couldn't resist commenting 😅

@JeffSu 2 жыл бұрын

Hahaha well I hope you still liked it after watching it!

@Bolly811 Жыл бұрын

Thnx for the video!

@JeffSu Жыл бұрын

You're very welcome Bolly 😁

@irfanilyeas7059 2 жыл бұрын

Heyy Jeff Thanks for cool vid But maybe a special request is that if you could share the sheets file/link in the description So your viewers could practice with you I personally learn better when I’m doing these practices with the instructor :D

@JeffSu 2 жыл бұрын

That's actually a really really good point, I'll bear that in mind next time!

@lophamdac8377 2 жыл бұрын

That great!

@JeffSu 2 жыл бұрын

Thank you 😁😁

@yanranchen1390 2 жыл бұрын

Hi Jeff, I am a new subscriber of your channel, and found some of your interview tips super useful! I am currently looking to change my job and was asked by a headhunter about my current salary and expected salary range etc, I do not want to disclose my current salary as I think this will put me in a more vulnerable position, How should I reply back focusing on the expected salary instead of the current salary? Sorry to ask an irrelevant question to this video!

@JeffSu 2 жыл бұрын

Hi Yanran - No problem. Do some research (for example on glassdoor) for your new job's salary range. Choose the top 75% and focus on that instead. For example in your reply "I did some research and noticed that this role generally pays $123,000, I'd like to start there and talk about benefits as well..." etc. best of luck!

@nadchallenge 2 жыл бұрын

this is so cool video . Thanks..

@JeffSu 2 жыл бұрын

Glad to hear it!! Thank you!

@nadchallenge 2 жыл бұрын

@@JeffSu I am very excited to create a video like yours.. best quality and better presentation style..

@wowwoahwee Жыл бұрын

oh no...I most certainly got to this video too early. lol! be back when at the least i know how to press enter without kicking myself out of typing mode , or if thats even possible. lmao good stuff. even i can follow . its just not my main focus atm. 5 STARS to Jeff! Make that 14 now buddy. lol

@JeffSu Жыл бұрын

Thank you Rory! 😁

@dhrushilzaveri 2 жыл бұрын

=int(cell name), but this is only for excel. It can remove the ' that sometime comes. Also the meaning is give back the raw form of the data. Hope this is useful

@JeffSu 2 жыл бұрын

Got it, thanks Dhrushil!!

@mcastillo-rama8031 2 жыл бұрын

Incredible video, thanks ! Jeff, I keep asking this question and no-one can answer it. Regarding importrange. If I have a "total or summary" sheet where I've importranged from other sheets, I can't seem to edit this sheet (for example if I want to let one person know I have completed an item in a specific field). Whenever I write something the data disappears. Is there any way around this ? Thanks in advance for your time

@JeffSu 2 жыл бұрын

Add a new column not related to the importrange :) 😁

@tobias2081 2 жыл бұрын

Look at Jeff responding to every single comment 🔥

@JeffSu 2 жыл бұрын

Oh heckkkk yes 😁

@Wolfie_Desu Жыл бұрын

One that I usually use would be the combinations of the IFS and AND functions for a value inside a rage for example since the IFS function works like this IFS(condition 1, value 1, condition 2, value 2), I could do IFS(A1>75%, "above 75%", AND(A150%), "Above 50%") for it to give a range. Although its longer than the nested if formula of IF(A150%), "Above 50%", " "), you would not need to write in order and its also easier to make since you can copy and pate the AND formula and make small edits.

@JeffSu Жыл бұрын

Great tip! Thanks for sharing!!

@lpanebr 2 жыл бұрын

If the column with date is an actual date it's better to use the month() and yeah() functions as they work regardless of the date format.

@JeffSu 2 жыл бұрын

That's a great point Luciano, thanks for sharing!

@seanwright2819 Жыл бұрын

Spreadsheets often need a column with an incrementing number and rather than typing out "1, 2, 3, etc." and/or dragging these numbers down, there is a formula that can number rows & adjust the numbering automatically when new items are added. Assume the list starts at B5, you can use the following: "=ROW()-ROW($B$5)+1" Paste this in B5 and drag it down the column and it will number the items in each row. When you insert a new row in the middle it will automatically update all the numbering. A simple formula but useful. I use this a lot when linking a sheet as an exhibit in a memo as I can go into detail about each item in a numbered list and it is clear which item I am discussing since the exhibit is numbered as well.

@JeffSu Жыл бұрын

Oh this is awesome, thanks for sharing Sean!!

@seanwright2819 Жыл бұрын

@@JeffSu I realized after posting this and working in the spreadsheet a bit more that it can be improved. My original method will update existing numbers but does not automatically fill in the number on the inserted row. So my new method is to create a formula in the header that fills all of that in with an array. Assuming your list of items is in C5:C17 use the following formula in the header of your number column: ={"No.";SEQUENCE(COUNTA(C5:C18)-1)} You can replace "No." with whatever you want your number column to be labeled. Notice that the range in the formula extends one row past the range of actual items. This is so you can insert new items in the last row and it will still automatically number. Also, the reason for using the header row to make the array is that you can insert items in the first row and it will still number automatically. So all possible locations where you can insert new items will be automatically numbered. Note that you will have to fill in the item string before the numbers will correct themselves. As a bonus, I noticed we've had errors related to SUM functions not updating their range when items are added, so I came up with the following to fix that. Assuming your list of numbers to be summed starts in D5 and continues down the column to just above the sum: =SUM(INDIRECT("$D$5:D"&ROW()-1)) Now new numbers can be inserted and summed without having to fix the SUM formula every time.

@JeffSu Жыл бұрын

@@seanwright2819 Ok wow you're amazing Sean!!!

@seanwright2819 Жыл бұрын

@@JeffSu Thanks! I love spreadsheets, they're like a big puzzle!

@jorge-tutor 2 жыл бұрын

Great examples! Is there any way to define a formula per column so if I add a new row in the middle automatically inherits the formulas?

@JeffSu 2 жыл бұрын

Hm.....that's a good question. I don't know to be honest. I usually just copy paste that formula down....

@aneeshsrivastava1562 2 жыл бұрын

Dope !

@JeffSu 2 жыл бұрын

Thank you Aneesh 😁

@akirakongouji Жыл бұрын

Randomly stumpled upon this video, but two formulas I used jointly a LOT at work is query/importrange (probably more uncommon), and index/match/match for a matrix lookup.

@JeffSu Жыл бұрын

Nice! Thanks for sharing Tom! Well for what it's worth I'm glad you stumbled onto this video haha

@collingriff 2 жыл бұрын

Great video! One that I find that I use a lot in my media job is the Query function to aggregate and sort different cuts of data. I find that it works particularly well in combination with and Importrange as I am typically working with large sets of data that includes more information that just what me and my team work on. For example, using some of the inputs you had in this video it could be a Query(Importrange(),"Select * Where Col1 contains 'ORGANIC'").

@JeffSu 2 жыл бұрын

Oh nice! I haven't really used QUERY() before so thank you for bringing that up! I should definitely experiment with it :)

@louiesumalinog7542 11 ай бұрын

Arrayformula and Arrayconstraint

@JeffSu 11 ай бұрын

Those are pretty awesome! 😁

@collator 2 жыл бұрын

It’s physically impossible to make a mistake but you can make a #VALUE!

@JeffSu 2 жыл бұрын

That's a good point! 😂, but at least (in my opinion), that's better than making a calculation error and NOT knowing haha

@natancmacedo 2 жыл бұрын

The functions: query and import html. And I sometimes use Google script as well

@JeffSu 2 жыл бұрын

Nice! thanks for sharing Natan! I (embarrassingly) do not know how to use Google script

@natancmacedo 2 жыл бұрын

@@JeffSu I'm a programmer, so... :-p we use a lot in the company I work on

@JeffSu 2 жыл бұрын

@@natancmacedo Time for me to check it out 😅

@johnnyhooker4098 Жыл бұрын

As a nonprofit, we build a lot of budgets using foreign currency. However, it would be awesome if I could, by selecting the currency I wanted to view the budget in, to look at the same page recalculating the budget in USD. Our work around right now is to build a copy of the budget on a second tab and use a formula to recalculate the foreign currency budget in USD. Is there a simpler, perhaps more savvy way?

@JeffSu Жыл бұрын

I honestly feel like there's a way, for example change the formula to show the new currency depending on the currency symbol you input in a cell. But I don't know the formula off the top of my head 😅. Sorry!

@rudrajaiswara9851 2 жыл бұрын

Hi jeff I just love your video I want to say that can u make a video on Microsoft Edge because recently i started noticing that my Google Chrome is crashing and I have planned to use Edge as my primary Browser thank u

@JeffSu 2 жыл бұрын

Oh you just reminded me I DO NEED to make a video on Edge! Thanks for the reminder!

@rudrajaiswara9851 2 жыл бұрын

Your welcome

@denisamokhvalov 2 жыл бұрын

Jeff, I have a question. Is there a way to use conditional formatting for isemail and isurl functions? If false, then the cell is to be painted red.

@JeffSu 2 жыл бұрын

Yup, you would just need to add conditional formatting on top of the column/rows!

@denisamokhvalov 2 жыл бұрын

@@JeffSu Thanks, it works. We can even use this formulas directly without creating additional TRUE/FALSE cells. For example: Select range we want to check > Conditional formatting > Format rules: Custom formula is: ISEMAIL(firstcellinthelist).

@JeffSu 2 жыл бұрын

@@denisamokhvalov oh nice!! Thanks for letting us know Denis!

@AlexRamsbey Жыл бұрын

Hey Jeff, Could you create a video on how I could import multiple ranges into a single list? This would be between the same sheet but append each range. (the goal here is to have a master list of tasks and each time something is added on one of the referenced ranges, it shows up in this master list.

@JeffSu Жыл бұрын

Not sure I understand the question Alex....

@AlexRamsbey Жыл бұрын

@@JeffSu My goal is to combine several ranges into a master range, if that makes sense. This would be something like in SQL a JOIN statement (but I'm looking to join rows, not columns). I'm creating an audit document within Excel, and any recommended changes I have a range that I'm using for that information. So, ideally, at the end of all the sheets, I want to combine all the recommended changes into a master check list. Hopefully that makes sense in context?

@arpanmajumdar617 2 жыл бұрын

Jeff is just one step short of using Google Sheets as a full-blown programming language.

@JeffSu 2 жыл бұрын

😂 I'll take that as a compliment!

@arpanmajumdar617 2 жыл бұрын

@@JeffSu Woahh didn't know I will actually get a reply. Big fan of you.

@JeffSu 2 жыл бұрын

@@arpanmajumdar617 Of course Arpan :)

@dayashankarsuresh57 2 жыл бұрын

Hey, I was wondering if you would make a skillshare class on excel 🤔🤔🤔

@JeffSu 2 жыл бұрын

That's actually a pretty good idea, thanks for sharing Dayashankar!

@MicrowaveHateMachine 2 жыл бұрын

Why does arrayformula not work with split?

@JeffSu 2 жыл бұрын

Because I think it splits it twice 😅

@MicrowaveHateMachine 2 жыл бұрын

Index(split(cell, " "),1,1) to split but only show the first part.

@JeffSu 2 жыл бұрын

Oh nice! Thanks for that tip!!

@joaopaulopitta Жыл бұрын

👏🏻👏🏻👏🏻

@JeffSu Жыл бұрын

Thanks João 😁

@Razel0s 2 жыл бұрын

100k Wow!

@JeffSu 2 жыл бұрын

Thanks Panos!

@willfs88 2 жыл бұрын

Does ARRAYFORMULA make easier to loading a page full of formula?

@JeffSu 2 жыл бұрын

Technically it does but it depends on the use case! What are you trying to achieve?

@Landnar_Van_Gogh Жыл бұрын

How can I quick change small parts of formula, like if I have a lot of A2:10 in my formula and I want to change all the 4 to C2:C10 , there's a way of doing it? I pretty sure python has something like this and I wonder if Sheets also have.

@JeffSu Жыл бұрын

Hm..interesting. Off the top of my head I don't know the answer since I would just edit it manually 😂

@ZestyVA 2 жыл бұрын

Off topic for your channel... what do you all do about antivirus for your windows computers? I'm a small business owner and I'm struggling with who to trust. @Jeff Su, I trust you! Do you have any recommendations?

@JeffSu 2 жыл бұрын

I believe Windows Defender does a pretty decent job now :)

@ZestyVA 2 жыл бұрын

@@JeffSu Thank you! You make my life so much easier! Many a video I have passed onto my contractors for professional development :()

@billblanchette3604 2 жыл бұрын

You did 1 digit for the splitting the month out, what happens with October or December?

@JeffSu 2 жыл бұрын

Yup I only realized that after the fact. Other viewers have already corrected me in the comments! 😁

@gnawlej9163 2 жыл бұрын

I know it's a teaching example, but using LEFT with 1 character length to extract month won't work for October-December. Sheets does have a MONTH formula that would work though.

@JeffSu 2 жыл бұрын

Good point Drew!! Thank you so much for pointing that out!

@astudio777 Жыл бұрын

How can I convert a quantity ordered into rows in google sheets? Ex: A2 = Item being ordered and B2 equals the quantity ordered I want to take the quantity in B2 and add that many rows.

@JeffSu Жыл бұрын

Not sure what you mean there... 😅

@astudio777 Жыл бұрын

@@JeffSu Say I have an order come in for an item and they want quantity of (10). I want Google Sheets to automatically populate (10) lines(rows) with that same item that was ordered. Ex. Cell A2 is the item or product being ordered. B2 is going to be my quantity cell. When the quantity wanted is input into cell B2, I want it to automatically populate that many lines(rows).