NINJA Level Excel Formula Tips Most Don't Know

Рет қаралды 37,879

Күн бұрын

Formulas are THE MOST IMPORTANT EXCEL SKILL you can master. In this lesson I cover some little known Excel formula hacks and tools that’ll give you ninja level skills and enable you to decipher any formula. Even ones you haven’t written yourself.
Download the quick reference Excel guide here: www.myonlinetraininghub.com/p...
Learn Excel Named Ranges: www.myonlinetraininghub.com/e...
Learn Excel Dynamic Named Ranges: www.myonlinetraininghub.com/e...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 Excel Formula Writing Tips
0:22 Cell Reference Best Practice
2:55 Formula Structure
4:55 Formula Constants
6:30 Formula Navigating tips
8:06 Formula Evaluating Shortcut
8:54 Evaluate Formula Tool

Пікірлер: 104

@LosoIAm 10 ай бұрын

The ability to evaluate functions like in VBA where we can step through the code line by line will make life in the office much more pleasant - many thanks for the tips!

@MyOnlineTrainingHub 10 ай бұрын

My pleasure!

@chrism9037 Жыл бұрын

Thanks Mynda, great tips. I always use tables now, it makes things much easier. I liked the formula navigating tips too!

@MyOnlineTrainingHub Жыл бұрын

Great to hear, Chris 🙏

@nsanch0181 Жыл бұрын

Thank you Mynda for the good formula tips. You showed me that formula editor add-in. I never used an add in but I was browsing through that list and there is a lot that lot to check out. Thank you.

@MyOnlineTrainingHub Жыл бұрын

Glad it was helpful!

@IvanCortinas_ES Жыл бұрын

Excellent tips. Little by little it seems that the use of F9 Formula Evaluating is being introduced to more and more users. Thank you Mynda!!!

@MyOnlineTrainingHub Жыл бұрын

Glad you liked them, Ivan!

@wayneedmondson1065 Жыл бұрын

Great tips Mynda! Thanks!!

@MyOnlineTrainingHub Жыл бұрын

Glad you liked them 😊

@andriikulishov3371 Жыл бұрын

Mynda, thank you very much!

@MyOnlineTrainingHub Жыл бұрын

You are most welcome!

@endthefed9937 Жыл бұрын

Another great video, thank you!

@MyOnlineTrainingHub Жыл бұрын

Glad you enjoyed it!

@darrylmorgan Жыл бұрын

Hi Mynda!Great Tutorial,Really Cool Tips...Thank You :)

@MyOnlineTrainingHub Жыл бұрын

Cheers, Darryl!

@moisestatis5418 Жыл бұрын

Great tips, thank you!

@MyOnlineTrainingHub Жыл бұрын

Glad it was helpful!

@sjdyt Жыл бұрын

Some useful tips there. I was guilty of creating some beasts of nested formulae in previous jobs which I then struggled to understand when things went wrong. F9 to evaluate within formula is something I didn’t know about so that will help me in future. When I get back to work again I will be embracing tables though along with power query.

@MyOnlineTrainingHub Жыл бұрын

Glad it was useful 😊

@therealjeffg Жыл бұрын

Another awesome video thank you.

@MyOnlineTrainingHub Жыл бұрын

Glad you enjoyed it 🙏

@ivanbork4175 Жыл бұрын

Yet another very good tutorial Even when the topics is “known”, there’s always something new learn e.i. formula evalueting

@MyOnlineTrainingHub Жыл бұрын

Great to hear, Ivan!

@GeorgeAJululian Жыл бұрын

Always your videos are very helpful and useful thank you

@MyOnlineTrainingHub Жыл бұрын

Glad you like them!

@notesfromleisa-land Жыл бұрын

I am an old dog, and there are so many new tricks. O heck I remember that spreadsheets when you had to manually make all of the changes that Excel does so easily now. (and when a Dell desktop cost 16K). So many upgrades in Excel now, that I've not really had to learn as some of my 'stuff' has been in place for a while. But the new functionality is something that I have been able to leverage lately--particularly Power Query. Thanks to your and other's videos, I've been able to harness the power of the new stuff (and forget the qualifiers of you need to have 365--any person viewing these videos needs to invest the paltry $'s to get 365 to get the functionality that will save hours of time and frustration). Thanks for all that you do.

@MyOnlineTrainingHub Жыл бұрын

Thanks so much 🙏 well said regarding investing in 365.

@MiRofek Жыл бұрын

Very Useful!

@MyOnlineTrainingHub Жыл бұрын

Glad to hear that!

@rmmccarthy1240 Жыл бұрын

Thank you, Mynda! I have downloaded the add-in as per your tutorial. Any viewers interested in the "Advanced Formula Environment" add-in must query "Excel Labs" (it appears that MS has folded the AFE into a collection and named it Excel Labs). Again, thanks for your hard work and incisive tutorials!

@MyOnlineTrainingHub Жыл бұрын

Yes, good catch. They have renamed it to Excel Labs. Knowing Microsoft, this probably isn’t the last name change 🙄

@rmmccarthy1240 Жыл бұрын

@@MyOnlineTrainingHub👍

@sedratioussama7582 Жыл бұрын

Your are Always the best

@MyOnlineTrainingHub Жыл бұрын

Thanks so much!

@iankr Жыл бұрын

Thanks, Mynda 😊

@MyOnlineTrainingHub Жыл бұрын

My pleasure, Ian!

@barttrudeau9237 Жыл бұрын

I did not know about the Advanced Formula Environment, thank you, great tip!

@MyOnlineTrainingHub Жыл бұрын

Glad it'll be useful 😊

@peterbartholomew7409 Жыл бұрын

@@MyOnlineTrainingHub Interesting that you feel able to recommend the AFE to non-365 users. Jack Williams (MS Research) should be pleased.

@silverfunnel6819 Жыл бұрын

Super useful🥰

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@HachiAdachi Жыл бұрын

Thank you, Mynda! ...Of course, AFE **just** recently got a major update (ver1.1) which places the button in the FORMULA tab, and, more importantly, an ability to directly edit the formula in any cell (in "GRID" tab). Among other changes is the newly added "Named Function Editor" which is basically a LAMBDA wizard. It's awesome.

@MyOnlineTrainingHub Жыл бұрын

Yes, I have a video dedicated to the AFE with the new features coming out soon. It's way more useful now 😊

@sunnybaggu785 Жыл бұрын

@@MyOnlineTrainingHub looking forward to learn from your content. Your videos are very helpful and give clear explanations. Will wait for AFE

@alexanderbaranov5418 Жыл бұрын

Like by default. Thanks, Mynda

@MyOnlineTrainingHub Жыл бұрын

Thanks for your support!

@newbritishlady Жыл бұрын

It would be fantastic if you could consider making a video on SPC charts in excel. Maybe even including them in dashboards? Your videos are great thank you!

@MyOnlineTrainingHub Жыл бұрын

Great idea 🙏

@newbritishlady Жыл бұрын

@@MyOnlineTrainingHub I will await impatiently then! SPC charts are great statistical tool (in my humble opinion) they are easy to digest for non finance individuals yet nicely tell a story and highlight issues with data. I do a basic version I would say. Learning to integrate them into a dashboard (if possible) would be fantastic 🧐

@markus_aurelius_888 Жыл бұрын

@@newbritishlady me too 👍

@winchish Жыл бұрын

AFE did not appear in my search results just now. There’s a few AI Editors, but they seem to do a different task? Any advice? Great content, thank you. Clear, comprehensive and succinct. After 30+ years of faffing I am finally starting to use Excel properly.

@MyOnlineTrainingHub Жыл бұрын

Try searching for Excel Labs. It has recently been renamed. Although, I can still find it when searching for 'Advanced Formula Environment'.

@HenricAndersen 3 күн бұрын

Great video 🙂

@MyOnlineTrainingHub 3 күн бұрын

Glad you enjoyed it 🙏

@vijayarjunwadkar Жыл бұрын

Thank you Mynda for yet another useful video! I feel happy, when I receive the notification of your new video, which always teaches something more than expected! 🙂 BTW, the link to Excel file in this video, appears to be broken, as it throws Page Not Found error:-(

@MyOnlineTrainingHub Жыл бұрын

Aw, that's so lovely to hear, Vijay! Thank you 🙏 the download page will be up later today. I didn't mean to post this video now, which is why the page isn't ready 🙈

@MyOnlineTrainingHub Жыл бұрын

The link should be working now!

@mahendraroliya8695 4 ай бұрын

thanku

@MyOnlineTrainingHub 4 ай бұрын

My pleasure!

@tone3765 Жыл бұрын

Do you do assisted Dash builds? I am attempting to build one based on your videos, but wondering if you offer any services to develop the framework with the provided data?

@MyOnlineTrainingHub Жыл бұрын

I don't have time to do Excel consulting, sorry. However, if you reach out via email I can recommend someone who can help: website at MyOnlineTrainingHub.com

@phandongexcel 4 ай бұрын

I from Vietnam. I like this video ❤❤❤❤❤

@MyOnlineTrainingHub 4 ай бұрын

Welcome 😊glad you liked it!

@JCEurovisionFan1996 Жыл бұрын

Ma'am Mynda, could you do a tutorial on how to make diverging bar charts like these Likert scale charts?

@MyOnlineTrainingHub Жыл бұрын

Please see this tutorial: www.myonlinetraininghub.com/segmented-survey-data-chart

@GrahamClarkson Жыл бұрын

Thanks Mynda for another excellent tutorial. I am trying to simplify a spreadsheet showing member dues month by month. Each month is a new sheet and it references the previous month on several cells. Is it possible to use the techniques described to do this? Thank you.

@MyOnlineTrainingHub Жыл бұрын

Hi Graham, having your data spread over multiple sheets will make Excel life very difficult. It is best practice to put your data on one sheet and add a column for the month it relates to. This way you can use the built in tools like PivotTables and functions the way they were intended. More on correct data formats here: www.myonlinetraininghub.com/excel-tabular-data-format If you have further questions, please post your question and sample Excel file on our forum where we can help you further as I won't see follow up responses to this thread due to the volume of comments I get: www.myonlinetraininghub.com/excel-forum

@GrahamClarkson Жыл бұрын

@@MyOnlineTrainingHub Hi Mynda Thank you very much for your reply. I did not know about the forum, but will post there in future. Kind regards Graham

@yogeshsali Жыл бұрын

There is always something new to learn from your videos. Can you suggest any formula for unrealised profit loss calculation of partial sell in stock trading excel sheet?

@MyOnlineTrainingHub Жыл бұрын

Great to hear! In regards to your stocks, you'd have to split the transactions onto separate lines for the sold vs retained stock so you can ignore the sold stock from your unrealised P&L calculation.

@yogeshsali Жыл бұрын

@@MyOnlineTrainingHub Thank you 😊

@office4u791 Жыл бұрын

first commenter here 😁😁 Thanks

@MyOnlineTrainingHub Жыл бұрын

My pleasure!

@eplay2121 4 ай бұрын

Weird question I know but I was curious to know if you use those notebooks on your desk for looking up certain formulas or functions

@MyOnlineTrainingHub 4 ай бұрын

Those note books are blank pages for taking notes 😉

@hdpugal6732 Жыл бұрын

Nice one

@MyOnlineTrainingHub Жыл бұрын

Thanks for watching 😊

@jagannathan4089 9 ай бұрын

10 shipping bills containing pkgs and weight. loaded onto vessel on different shifts. only total pkgs and weights loaded during each shift are known. how to tally the shipping bill and the loaded cargo for each shift

@skimpylemon8034 Жыл бұрын

Thanks for the helpful tips! I think Microsoft really needs to improve the user interface of the "Evaluate Formula Dialog" box so users can see it better. Is there anyway to write to them about this issue?

@MyOnlineTrainingHub Жыл бұрын

I agree. We MVPs are telling them all the time! Did you see my latest video on the new Advanced Formula Environment: NEW Advanced Formula Environment - Finally the formula editor we've been waiting for! kzfaq.info/get/bejne/r69xn5SJx9u6qok.html

@GalaxyCookies76 Жыл бұрын

i have one column with two values which are A and B, I display those values by applying filters on this column. another column K having the dates of issue resolved duration against the value A and B the format of dates are days,hours and mins. what I want is the take an average of the K column and display the output of an average of K column and display output in cell E10 I want if the user selects A the date's average aginst A should be appeared and if user selects B its value appeared in E10 cell and If user selects both A and B then the Average of all the K21 column calculated and display different out put for A and B

@MyOnlineTrainingHub Жыл бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@shoppersdream 3 ай бұрын

Nice, thanks! It is so insane that on Mac we don't even have this Advanced Formula Environment. Paying so much money to Microsoft for Office 365 but half of the features are not even available for us.

@MyOnlineTrainingHub 3 ай бұрын

Yeah, it’s a shame but they are working hard at bringing Mac into line with Windows versions.

@shoppersdream 3 ай бұрын

@@MyOnlineTrainingHub There is no way you can talk to even a person at Microsoft. Online Agents ask the same question 10 times because they are chatting with 10 people at one time. I just wasted my $70 for renewing the subscription. Sorry, I am so angry at Microsoft right now.

@labonnes Жыл бұрын

I would suggest also adding spaces in you formulas. It's quick and easy and makes reading the formula more readable. We do it between words in sentences, why not do the same with formulas. Another tip would be to keep your formulas short and instead segment long formulas in intermediary calculations. It will be much easier to review after. I had never noticed the step-in/step-out portion of the evaluate formula tool working until today, thanks

@MyOnlineTrainingHub Жыл бұрын

Great ideas! Thanks for sharing.

@JustMe-sh8nd Жыл бұрын

How would you add spaces? Excel automaticly remove spaces in formulas, at least mine does... ;-)

@labonnes Жыл бұрын

@@JustMe-sh8nd it does remove spaces if you don't have all your closing parenthesis and you let Excel complete it for you. Try =A1 + A2 and I'm sure it will work

@idjles Жыл бұрын

At 9:59 does it actually recalculate C7 or does it still know and reuse the value from 9:24?

@MyOnlineTrainingHub Жыл бұрын

The evaluate formula window doesn't write anything back to the worksheet. It's just a window into how Excel is calculating in the background. So, yes, it still references C7.

@jonathanjackson4983 Жыл бұрын

Thanks for this post, Mynda - very useful as always. So, who are you and your husband supporting in the QATAR 2022 World Cup, Australia or England? 😀⚽

@MyOnlineTrainingHub Жыл бұрын

Great to hear! We're supporting Australia (which will be short lived), England and Phil is supporting Denmark! My grandmother was Danish and Phil is Irish, so he'll never support England 😁

@thestitchmenagerie 9 ай бұрын

I'm lost! I have Excel 365 but I don't have the Add-in's button on the insert tab. Where can I find this.

@MyOnlineTrainingHub 9 ай бұрын

They moved the add-ins button to the home tab.

@nadermounir8228 Жыл бұрын

The thing I don't like about excel tables is that if we remove data, the table ranges doesn't contract unlike when u add data, the table expands. I use a range with indirect function like this : A3:INDIRECT("A"&MATCH(9.99E+307,$B$3:$B$10). The list expands and contracts much better with this formula than with excel table.

@MyOnlineTrainingHub Жыл бұрын

If you delete the cells instead of just deleting the contents, then the table will adjust. If you still prefer a dynamic range, then I would avoid INDIRECT as it's volatile and use INDEX instead. See example 4 in this post: www.myonlinetraininghub.com/excel-dynamic-named-ranges

@dominicmcnamara Жыл бұрын

@@MyOnlineTrainingHub in 99% of examples, Tables are superior. In this response, it's great to also understand the VBA of the activity, if you .ClearContents (or select cells and Del) you just clear the Table record values, however, if you .Delete you select records and Delete Table Row. Both have their purpose, but I'd guess 80-90% of time, people want to delete the record. Also, with Tables, we should encourage everyone to use record and field, not row and column. Great video.

@kenediegoldson9717 Жыл бұрын

I love using tables until I have to make absolute references. When I drag my formal across multiple columns I always have to readjust the references. So I’d rather not use tables for such cases and type my formula once

@MyOnlineTrainingHub Жыл бұрын

You can make them absolute, as explained in this tutorial: www.myonlinetraininghub.com/excel-table-absolute-structured-references

@peterbartholomew7409 Жыл бұрын

Another approach, which I do not think Mynda mentioned (sorry if I am wrong), is to use a defined name. Even though the name picks up its definition from the structured reference and is dynamic, it remains an absolute reference. It may have the advantage of being more concise too.