8 Awesome New Excel Formulas for 2024 | Do you know them?

[@KenjiExplains]

🔥 Take our Excel Course: www.careerprinciples.com/courses/excel-for-business-finance

[@anshika6143]

Excellent Kenjii!!! i would really appreaciate if you could make one video on google sheets as well😀

[@XLLearner_Courses]

Great Functions

[@KenjiExplains]

Thank you for all your nice comments!

[@DavePickens]

Take is from the Top-Left ... Drop is from the Bottom-Right. You can, infact, find the bottom 3 in the table by "=TAKE(B3:E15,-3,-2)" where you're instructing TAKE to show you the last three (because the '-3' in rows), and the last two columns as you showed how to do using the '-2' in columns.

[@arte7sol]

thank you! same question for you. Is this meant for smaller evaluations? what if there is 1k lines. then what? or if the data is not sorted.

[@abbottkatz8830]

You can expand the range without EXPAND, by entering =C5:D16 in G5. If you don't like the zeroes that appear in the currently unpopulated cells, write instead =IF(C5:D16=0,"N/A",C5:D16)

[@aquagrunty101]

Why not just “” to create a blank so it doesn’t look so covered in N/As instead of “N/A”. Same thing better look. At least for my job.

[@shakushki]

I personally change formatting to 0;-0; (leaving 0 blank, also replace with what ever fancy number formatting you want

[@KODAKKADANANILKUMAR]

wonderful...no dragging... to the point.... awaiting for new videos

[@akashshukla1248]

Great video Kenji 🙂

[@shivsharanrmadachane3217]

Thanks 🎉🎉

[@Edsubri2]

Questions: Would it be quicker to use Flash Fill instead of the TEXT functions? I know they both return the same information but can you explain the differences? Your video is very informative and I appreciate you for sharing your knowledge :)

[@edwardgarner7498]

Hi there. First , great video clean and easy to follow. Second I tried on Microsoft Office Pro Plus 2016, and doesn't work with the images. Do I have to change my Office version number please?

[@DiptaGhossan]

if the source is already table, and the summary is filled with formula linked to that table copied all the way down, it would still get updated with new entry without Expand formula isnt it?

[@mindmywordsbymeghna.5325]

Thank you kenji . You are doing amazing job

[@KenjiExplains]

Thanks for watching!

[@graceyeoh2575]

Thank you for sharing it’s quite useful.😊

[@joukenienhuis6888]

Great explanation! I knew a lot of them, but that is because i try to keep my knowledge up to date. I only have a problem with why you should use the drop function. It is good to get the last data, but then you have to know the length of your table (no of rows), so if you don’t know that, it takes an extra function to count the rows. I do like the expand function, which is really handy if you keep updating a table

[@paladin21aa]

Actually the DROP function is used to get rid of the number of rows and columns listed as parameteres. The 2 he used as a parameter meant that the first 2 columns had to be discarded, but since his original table had exactly four rows, it seemed that he was taking the last two rows. To get the bottom 3 records, you should enter -3 as the row parameter in the TAKE function just as he did with the columns.

[@2kute2luv]

Thank you for sharing! Very useful new formulas.

[@annalisamongio4208]

I found these video so entertaining and educational. Thank you so much, you have made my life so much easier. I have a couple of questions if I may. When I type a number and I want to drag that number all the way down to repet in the tabs below, the number changes in the succession.For example: if I want to type the same number ie. 5 in the 4 tabs below, it shows as 6;7;8 etc. How do I make it stay at 5? I am not sure if I haven't been clear.

[@Random905]

Your video is very interesting 👍

[@IsaacAkowe-l5o]

I am a big fan.. Thank you so much ..

[@arte7sol]

hi Kenji, thank you for the tutorials. Question for "drop", what if there is 1k lines. then what? or if the data is not sorted.

[@camlex6310]

I only knew 3!! The others seem super helpful, cheers

[@KenjiExplains]

Awesome!

[@AlexanderFilatov-yk8ey]

Very good content. And quite applicable for BIG4 as well!

[@chakralamurali]

Excellent sir

[@anitahui5479]

Great video ! Thanks for sharing! Clear explanation too!

[@impicker3027]

These are some great formulas. I get a lot of people who gives me an image of table.

[@joukenienhuis6888]

Yes that is a great solution. I only wonder if it will also work with non excel tables🧐🤔

[@TheTechnicalAccountant]

This is awesome stuff. Kudos!

[@christybrown2088]

Great video for those quick steps I never learned😃

[@manlikeMrA]

Hey Kenji, I think the DROP() function is quite intuitive. You asked it to "drop" the first 10 rows and first 2 columns and it did just that and returned the rest of the array. In my opinion, Excel functions are the easiest to understand. Great content as always. Thank you!

[@ralph_]

I would have picked Take() with a minus value. That way it truly takes the last rows, rather than dropping prior rows. That way it's also more dynamic/independent on number of rows.

[@sumeetpatel4961]

Knowledgeable video! Just wanted to know what is the benefit of using =take, drop and expand function instead of just copy pasting?

[@epsilon7878]

take, drop, and expand are resilient to data changing - if you copied and pasted then if the original data changes the copied and pasted data will not update.

[@peterakeredolu3346]

Thanks for the amazing video. I recently had the opportunity to join the procurement team of my School, the procurement team has traditionally struggled with the arrangement and compilation of evaluation sheets from different panels within Excel. The current practice of cutting and pasting data is cumbersome, prone to errors, and time-consuming. I believe that there must be a more efficient method to populate the outcomes of the panel evaluations into the Excel worksheet. I kindly request your advice on specific Excel functions or techniques that could be employed to make this process faster and more streamlined.

[@bayanabulkhair7552]

Can i lock a cell in a gannt table .. for tasks that should be start unless we complete other one ?

[@Think4real78]

You d best

[@muhammadkhalil5712]

Excellent 👍 Are you using office365..? Because in my Excel =textsplit function is not available...☹️

[@willzinner8813]

text split is very nice

[@KenjiExplains]

Yes sir!

[@alecosavvas3361]

Hey Kenji it would be great if you could show how to use index, match and goal functions to solve for more investment banking like exercises. Maybe looking at earnings estimates based on different PE multiples or structuring complex tables with different asset allocations and possibly using conditional formatting to highlight those asset classes that are either underweight, equal weight or overweight

[@D4taNrrtv]

Do you have videos where you can validate report if pulling correct items in the source file like cubes?

[@ServrHax]

Hi Kenji, I have a quick question. Obviously you are aware that when using excell you can press tab to fill in the cell you are currently with information from an above cell if what you are typing is the same. My question is, is there a way to make a list of everything that I am expecting to type so It can be filled in whatever excel sheet I open? For example I do accounting for my small business and since i have many re ocurring clients I would like to steamline my invoice process so instead of having to clear an excel sheet every time i want to make a new invoice, I just begin typing in the letters of a clients name and then press tab to auto fill in the rest of the info from the list I spoke about earlier. Please tell me if this is possible! Thank you!

[@Nick-eo8uv]

hey buddy, in my opinion, you could create a small data base of the client details you require and then make a unique and simple "client code" for for each row (e.g. Google = GG, Faceback =FB) and so on so now when you need to add the details to your new sheet, you can make a new code and type just the client code and have a lookup function on the next cell to extract the full client name from your database sheet if you're using a table format then the formula on the next cell will automatically get dragged down once you type in the client code hope this helps h

[@jalaristv7512]

8:42 You’re using =drop incorrectly. You just use =take and for rows you put -3. No need to use =drop.

[@joukenienhuis6888]

If you know another use for the drop function, please do explain!

[@jalaristv7512]

@@joukenienhuis6888 Hi Jouken, you can use the =DROP function to remove redundant header rows, misc rows of data or unneeded columns from an array. Example: =DROP(range,1,0) would remove the headers of the range. =DROP(range,0,-1) would keep all the rows but remove the last column. It's also worth noting that you can wrap array formulas nicely as the result of your initial formula becomes a new "Range". So I could wrap =DROP functions to remove multiple columns Example: =DROP(range,0,-2) My initial formula to drop the 2nd to last column of data =DROP(DROP(range,0,-2),0,-4) Wrapping the above result and removing the 4th to last column of data from the result. If my range had 10 columns headed 1,2,3,4,5,6,7,8,9,10. I would be removing 8 which would then give me 1,2,3,4,5,6,7,9,10 and then I would be removing 6 to give me 1,2,3,4,5,7,9,10. This concept applies to all formulas but can be especially powerful when working with array formulas like =TAKE and =DROP. You can also manipulate the rows and columns values using a function like XMATCH or an IF statement to give more control on what you're dropping. Note there are more advanced applications of DROP, so it's good to play around with any existing spreadsheets you have and see what you can do with it! Cheers, Jalaris

[@Vishnuu007]

Please reply which free version of Excel is best for partice professional level

[@yohannaagbanaji8012]

Im eagerly waiting for the next videos

[@fabiGBOtown]

This is really cool. Does it work on Google sheets as well?

[@michaelquach6655]

Great work Kenji!

[@abbottkatz8830]

You can also write =TEXTAFTER(E3:E19," ")

[@chaset7680]

For getting flags for country’s: Select the list of countries Data Data types Automatic Select list of countries Click data import above list Click Flags

[@riziprince]

which version u use?i have 2019 but i dont have these formulas

[@nishanth4323]

You forget to tell which version of Excel will have this formulas...Sensai😲

[@rafayhussain4171]

Microsoft 365

[@KenjiExplains]

I’m using office 365 but not sure about which version has what 🙏

[@rafayhussain4171]

This formula should work on google sheet

[@yohannaagbanaji8012]

I think you need 2021 version to get these formulas

[@nishanth4323]

@@KenjiExplains 😯oohhh👍

[@kellog89]

Do these adv functions work in Google sheets too?

[@EllenSP]

Need help looking for a value in another sheet when there are multiple criterias. Example- Fist sheet - I have an invoice column and a Bill of lading column, the second sheet only have one column with either invoice or Bill of lading. How do I look them up to return in on cell instead of looking it up in two separates cell for each column?

[@yonip8661212]

For which excel version these function() work?

[@srisridaran05]

Hi Kenji, Just want to know why i cant see any of these functions in my excel? Is that i have to upgrade or i need to change any option in excel to make it visible? Please guide me. Vstack, Taxsplit, Textbefore,Textafter etc.

[@KenjiExplains]

Hi not 100% sure but likely due to your Excel version. Are you using an older version of excel? I was using office 365 here

[@srisridaran05]

@@KenjiExplains Am using MS office 2016? Is that i have to upgrade to have this new functions?

[@tijesuniogunrombi9456]

Yes, you have to upgrade since they are recently released Functions

[@srisridaran05]

@@tijesuniogunrombi9456 I Have updated to 2021 excel version even though i cant use this functions?

[@tamerhegab1513]

Cool

[@bikeny]

What versions of Excel does this work on? They don't appear in my Office 2019. thx.

[@lovish2728]

Will take and drop function work if data is not sorted ?

[@sylviahinkle5727]

Hi there, Im copying/pasting information onto my columns but trying to find a way to automatically move from my last cell back to the first column without having to keep moving my mouse

[@money_excel]

Certainly! Explaining VLOOKUP, XLOOKUP, and INDEX in a video sounds like a great idea. I can provide a brief overview here to give you an idea: 1. **VLOOKUP (Vertical Lookup)**: - VLOOKUP searches for a value in the first column of a table and returns a corresponding value in the same row from a specified column. - Syntax: `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` 2. **XLOOKUP**: - XLOOKUP is a newer and more powerful function that can perform both vertical and horizontal lookups. - Syntax: `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` 3. **INDEX**: - INDEX returns a value from a specific row and column of a given range. - Syntax: `INDEX(array, row_num, [column_num])` In the video, I'll elaborate on how to use these functions, their syntax, common use cases, and provide examples to demonstrate their functionalities. Stay tuned for the detailed explanations! 😊

[@sitirohana2346]

Is this applicable for excel 2016?

[@suchismitamukherjee3364]

Hi, why can't I seem to find Vstack and Hstack function in my Office 365 version? Also could you please tell how do I call a table in another workbook instead of copy pasting it.

[@samar1218]

You’re so great! Great mind great face! So handsome! I love you ❤🥰😘 hope to see you when I go to the UK. 😘

[@Isfandiyor-k2e]

watched

[@wallywechs4694]

Hello, what can I do because these formulas are not in my Word 2019?

[@alexargott3391]

I am having trouble inserting an image. Error saying i can not use more than 225 Characters. im not sure how to use Concatenate

[@deutschnacharabischemgeschmack]

I have Microsoft 365 Suite Excel included but all new functions are not there and don't get automatically updated. Any advice on how to get the latest functions in Excel ?

[@TheZeroCoolest]

No sure if I am doing something wrong here or not, but trying to use vstack in this scenario. I have a workbook with customers and the services they use. I have a tab for Service A and Service B. Some customers have only Service A, some only have Service B, and some have both A and B. In both tabs I have a unique customer identifier. If I do a vstack(ServiceACustID, ServiceBCustID) in a new tab I get a #Num error. Am I doing something wrong or do the arrays have to be on the same tab?

[@tijesuniogunrombi9456]

When dealing with arrays and stacking in Excel, the arrays usually have to be of the same size I suppose.

[@smirdaadouch2182]

Whats the difference in using vstack or tocol??

[@vikramadityarathore97]

Awesome Kenji,if you could explain vlookup,xlookup and index in the next video it would be great.

[@KenjiExplains]

Got a few videos on that already check them out in my profile :)

[@KenjiExplains]

But can consider a specific one just on those thanks!

[@vikramadityarathore97]

​@@KenjiExplainsyes you must have covered them before I am sure

[@vikramadityarathore97]

​@@KenjiExplainsbut yes I meant specifically covering these topics

[@ΗλιαςΜπαλ]

I have an excel file that I use for work, specifically car parks. The data I use are the following, license plate number, time of entry, time of exit, duration of stay, and based on these the price is calculated. Sometimes I mistakenly click on the classification, as a result of which the numbers get confused, i.e. they get mixed up. If I don't understand it right away to reverse it with undo, what options do I have?

[@phunkee22]

hi, pls help. i cant use formula to compute age . always #VALUE is appearing

[@1yyymmmddd]

Your can select all the table at once by just clicking Ctrl - * .

[@luongmxnh]

Kenji, on which device do you use Excel? I have a Mac and it doesn't work ... I've watched multiple Excel videos from you and not 1 formula worked.... a bit frustrated tbh

[@KenjiExplains]

Hey Martin sorry to hear about that. I use office 365 on a windows. That’s frustrating, I wasn’t aware it doesn’t work on all computers. Thanks for watching my videos hopefully some more luck next week!

[@luongmxnh]

@@KenjiExplains Thanks for your understanding, Kenji. I'll try to look further into it. Maybe Excel on Mac compared to Windows is different. Could you make a video about your Equipment? If I don’t enjoy Excel on Mac, I'll see if I buy a Windows PC 🙄

[@a.rakeshpatro]

While using Image fuction getting error as Block

[@AkshayHegdeSH]

Hey Kenji, I have a doubt. In Expand function, you took an example where you knew that there are 12 months in a year. What to do if we are unsure how many entries are coming up next?

[@jacksonalbert2310]

You could try using a count equation, maybe. Something like =expand(Table1[[EU Sales]:[US Sales]],counta(F:F)-2,,"N/A") could work? The -2 would be to remove the month and summary cells from evaluation.

[@alessandrobozzini8144]

when you use the drop function you chose 10, but why didn't the function drop values starting from row 11 then instead of 13?

[@tijesuniogunrombi9456]

I guess it's because actual data entry starts on row 3 of the sheet. So that's 2 rows down. This ends up being the 13th row if you count appropriately

[@riziprince]

send me link from where to download ms office 2023 bcoz i dont get from google

[@OmaidullahHelmand]

this formula dose not work in our excel how to include this or need update?

[@janashiffert7184]

I am trying to do the =image and keep getting the error #NAME?

[@Nihalp033]

Why these formulas not working on my excel..?

[@providencewilliams8064]

Thanks for this awesome video. for the TEXTSPLIT FUNCTION, Please How can one split a name column that is not symmetrical? example JAMES BIDEN JAMES BIDEN THOMAS PHILIP MARK SMITH WILLIAMS PETER CHRIS JASON How can one split this into two column of last name and first name? last name first name James Biden James Biden Thomas

[@llewellynevans8018]

Lets say this is your desired result where the text split is indicated by "*_*" Here is your Raw Name: and after these two formulas: Raw: JAMES BIDEN /After Formulas: JAMES*_*BIDEN Raw: JAMES BIDEN THOMAS /After Formulas: JAMES BIDEN*_*THOMAS Raw: PHILIP MARK SMITH WILLIAMS /After Formulas: PHILIP MARK SMITH*_*WILLIAMS Raw: PETER CHRIS JASON /After Formulas: PETER CHRIS*_*JASON It cant be done with one formula but instead should be done with two columns: Formula one - =LEFT(E2, FIND("@", SUBSTITUTE(E2, " ", "@", LEN(E2)-LEN(SUBSTITUTE(E2, " ", ""))))-1) Formula two- =RIGHT(E2, LEN(E2) - FIND("@", SUBSTITUTE(E2, " ", "@", LEN(E2)-LEN(SUBSTITUTE(E2, " ", ""))))) Hope this helps.

[@ali8bilal]

❤

[@KenjiExplains]

Cheers!

[@mrityunjay39gtc64]

not a single formula is available in excel 2021 , which version are you using?

[@GhostGaming-iz5df]

unfortunately in my excel version there is no similar formula its 2021 ms office

[@Sunflower_729]

vstack isn't available in my excel, what to do

[@Sventa-x6g]

🍀🍀🍀🍀🍀🍀🍀🌲💖💫

[@nicholaspetre1]

Image() does not work - it says: #BLOCKED

[@kellizsa3662]

he is cute

[@yasincomps2056]

I think it's called functions not formulas

[@Josephchiadikaobi]

I am tired of setting all keyboard

[@tiagosilva856]

They are not awesome at all! I would say useless.

[@Josephchiadikaobi]

HELLO I USE MACBOOK PRO and I’m finding it difficult to get all this formulas. its very annoying 😭😭😭. Hstack, Vstack, F4 , TEXTSPLIT, TEXTIMAGE, OTHERS

[@yohannaagbanaji8012]

❤