Writing Formulas in Excel Will Never Be the Same (Free File)

No video

Writing Formulas in Excel Will Never Be the Same (Free File)

Рет қаралды 49,670

Ай бұрын

If you struggle with writing Excel functions, this tool might be the solution.
👩‍🏫 Advanced Excel Formulas course: bit.ly/labs24f...
⬇️ Download the example file here and follow along: bit.ly/labs24file
Excel’s formula bar is difficult to work in with its limited space, lack of formatting, debugging and real time error detection, it makes writing anything more than basic formulas tedious.
In this video, I'll introduce you to a free tool built by Microsoft and available for Excel 2019 onward that will transform how you write, debug, and manage your formulas in Excel. Trust me, by the end of this video, you’ll wonder how you ever managed without it.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetr...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetr...
#Excel #ExcelTips #ExcelTools

Пікірлер: 70

@queutaih Ай бұрын

You can click in the grid to insert cell references! F4 should let you enter "Cell-select mode". I appreciate that's not very understandable, given that F2 does that in the formula bar, but unfortunately F2 is used for "rename" in AFE.

@MyOnlineTrainingHub Ай бұрын

WHAT! 🤯 This should be way more obvious. Thanks for sharing. Pinning comment for others.

@CeliaAlvesSolveExcel Ай бұрын

Thank you! Hitting [ is another option. Will test to see the difference between the two methods.

@hellopsp180 Ай бұрын

For those looking to install AFE go to 5:24 :) It would have been nice if you showed us how to get Excel Labs AFE to work before just going straight in to use it. I know its been Slotted at the end of the video but for those who have not installed it yet, it would have been better placed at the start of the video rather than the end.

@MyOnlineTrainingHub Ай бұрын

Thanks for the feedback 👍

@datingdave1310 Ай бұрын

The worst thing about this feature is, it's buried in something called Excel Labs instead of being a button available directly from the 'ribbon - typical of Microsoft! Thank goodness you explained how/where to find it, otherwise...

@notesfromleisa-land Ай бұрын

Mynda does the heavy lifting so we don't have to.

@MyOnlineTrainingHub Ай бұрын

@datingdave1310 Glad you can make use of it 😊 @notesfromieisa-land happy to help! 😉

@MyOnlineTrainingHub Ай бұрын

❓What’s the most complex formula you've ever managed in Excel? Advanced Excel Formulas course: bit.ly/labs24formula

@ivanbork4175 Ай бұрын

Hi Mynda As allways, very good performance - thank you Answer to the question ”what´s the most complex” I´m really not sure, because everything new is at least puzzling, but after a while it’s just normal. What I try to get a grip on for now is the use of Lambda

@samaruti9446 Ай бұрын

Great video! Could have used this when I created my last Lambda/Let. Now Microsoft needs to add comments to Lambda/Let so we can have others understand our logic

@MyOnlineTrainingHub Ай бұрын

The AFE was designed for LAMBDAs, so it should help make it easier to get to grips with them. You can get started with my LAMBDA video here: kzfaq.info/get/bejne/ma2onbqAvp7MaWw.html

@RichardJones73 Ай бұрын

Looks really useful. Its a pity that Microsoft didn't build it into Excel in the first place and my IT dept haven't enabled any addins to be added so all I can do is just dream about these things (or change job to a company who are a bit more advanced!)

@MyOnlineTrainingHub Ай бұрын

Yeah, I think that's the longer term plan, but with a 35+ year old code base, major changes like this don't happen easily, so having it in a task pane is the next best thing.

@therealshakespeare9243 23 күн бұрын

@@MyOnlineTrainingHubthe first ever spreadsheet application (for an IBM mainframe) that I programmed in 1974 for ICI Chemicals, had 270 lines available for writing a formula. This was 5/6 years before VisiCalc and even before IBM PC’s.

@DJPGB Ай бұрын

If you're using the macOS Excel (and you installed the Excel Labs add-in a while ago), then you'll find its button in the Formulas [sic] ribbon. When you click on its button, you'll be offered to update the add=in. When you do, the add=in will be updated, and its button will be moved to the Home ribbon.

@MyOnlineTrainingHub Ай бұрын

Thanks for sharing that info.

@alexanderbaranov5418 Ай бұрын

Great tool. Thank you. Hope they keep on developing it

@olivierissaverdens6916 Ай бұрын

Thank you, once more, Mynda! I'm definitely going to use it! 👍

@MyOnlineTrainingHub Ай бұрын

Wonderful!

@frankocarroll5093 Ай бұрын

The most complicated formula that I have ever had to deal with and indeed still deal with is in a spreadsheet that does a number of complex look up on various other sheets it's a kind of a database application is in excel. The formula when saved as a text file is 2 kB long. and then this formula is repeated every cell of a long table and then of course very similar formula that do something slightly different but basically have the same pattern also copied into thousands or hundreds of other cells throughout the spreadsheet.

@MyOnlineTrainingHub Ай бұрын

Yikes! Sounds like time for Power Query 😅

@ovaneeden Ай бұрын

Now there's a nice start to something closer to a even more mature coding environment!

@MyOnlineTrainingHub Ай бұрын

Glad you like it!

@chrism9037 Ай бұрын

Excellent Mynda, I’m going to start using it!

@MyOnlineTrainingHub Ай бұрын

Awesome, Chris. Have fun with it!

@eduardosandoval2144 Ай бұрын

This is so helpful! Thank you for sharing😊

@MyOnlineTrainingHub Ай бұрын

You're so welcome! 😉

@JackKirr Ай бұрын

This is great Thank you! I’m getting an error “Maximum number of cells in debugger exceeded” for a relatively simple formula - what might be happening?

@MyOnlineTrainingHub Ай бұрын

The formula might be simple, but if it references a lot of cells, then the debugger can't display them all. Try referencing a smaller subset to test and debug, and then expand to the full range once you are confident it's returning the correct results.

@SRKKM 29 күн бұрын

Hi Mynda! At 06:14 you're pointing to your left, but the link pops up to your right. Thought you might want to edit that.

@MyOnlineTrainingHub 29 күн бұрын

Thanks for letting me know 🤭

@mogarrett3045 Ай бұрын

so awesome thank you

@MyOnlineTrainingHub Ай бұрын

Glad you liked it!

@notesfromleisa-land Ай бұрын

AFE to eliminate WTF. (Nothing worse than going back to a laborious formula and scratching head--seemed so clear at the time). (A readme tab helps). Mynda, thanks for introducing us to this. I've got it up and ready. Now, it would be great if it would go one step further and allow annotations inside the formula--like you can do in PQ to give context.

@MyOnlineTrainingHub Ай бұрын

Yes, annotations would be great. You can sometimes use the N function to insert a note in your formulas: www.myonlinetraininghub.com/microsoft-excel-n-function Not as good as proper annotations, but better than nothing. alternatively, write your formulas as named formulas and use the Description field in the AFE/Comment Field in the Name Manager.

@notesfromleisa-land Ай бұрын

@@MyOnlineTrainingHub Brilliant. Thank you.

@RicardinhoL_5 Ай бұрын

The only drawback I find with the add-in is that you cannot use the mouse, which makes the work a bit more laborious. Possibly we could create the formula in the usual way and, in case of error, fix it with Excel Labs.

@MyOnlineTrainingHub Ай бұрын

Yeah, it takes a bit of getting used to but I expect that functionality is coming.

@LyubomirRusanov Ай бұрын

You can use the mouse and make selections by pressing F4. After you finished selecting in the sheet, you can click the small pen icon in the AFE. Or press F4 again, but the mouse pointer has to be in the AFE. Not the most intuitive way, but it works.

@RicardinhoL_5 Ай бұрын

@@LyubomirRusanov I understand, but I'm more used to using mainly Excel with the keyboard.

@LyubomirRusanov Ай бұрын

@@RicardinhoL_5 yes, me too. But it is some kind of workaround. I usually mostly used named ranges and LET() to make "names" for cells that will be used in the calculations. After this you use only variable names, more like programming.

@HachiAdachi Ай бұрын

Been using AFE for a while, and can't imagine being without it... I guess I can, but really don't want to.

@MyOnlineTrainingHub Ай бұрын

Great to hear!

@marksandsmith6778 Ай бұрын

The Excel Lab facility dumps the unformatted formula in the cell. But You can use tabs and soft returns to make formulae a lot eadier to read. M

@MyOnlineTrainingHub Ай бұрын

Yeah, it's a shame the formula bar doesn't retain the format. You used to be able to insert the AFE formatted formula in the formula bar, but it stopped doing it for some reason 🤷‍♀️

@dispirted8 Ай бұрын

Interesting - any idea if this is likely to become available without needing an add-in, in future? Our IT department has disabled add-ins, and I don’t want to pester them about making an exception for me.

@MyOnlineTrainingHub Ай бұрын

I'd start pestering. I don't expect it to be built-in anytime soon.

@rudiklein Ай бұрын

This is really great. I wish I had that many years ago. However, using very complex formulas is, in my view, not the best way to go about it. In many cases, I find it best practice to split your calculation in multiple columns. It prevents errors, makes it easier to troubleshoot, and read them back later.

@MyOnlineTrainingHub Ай бұрын

Absolutely. Good advice regarding avoiding overly complex formulas.

@ThreeDigitIQ Ай бұрын

5:25

@keithward2240 Ай бұрын

I have Excel 2021 with AI-aided-formula-editor plus 20 other add-ins in the available list, but no Advance Formula Environment in the list and a Search doesn't find it. Where and how can I get AFE?

@Kingleer69 Ай бұрын

Not sure if this AFE feature is backward compatible for 2016 version, but if you want to try this you can do so in the Office Online environment. Once you have logged into your online Office a/c, just follow the steps Mynda lists from 5:25 onwards.

@keithward2240 Ай бұрын

@@Kingleer69 Sorry, I have Excel 2021 Pro, it's build 16.0.etc which confused me

@MyOnlineTrainingHub Ай бұрын

@keithward2240 you search for "Excel Labs" in the add-ins library. The AFE is a tool inside Excel Labs.

@eliaskass1860 Ай бұрын

Is there any way to export your formulas so they're easier to transfer to another file? I know you can import, but how do you export?

@MyOnlineTrainingHub Ай бұрын

Place them in Modules, then you can access them from any file.

@solimbinanas8717 Ай бұрын

Hi ma'am

@MyOnlineTrainingHub Ай бұрын

Thanks for watching!

@humayungul2120 Ай бұрын

I installed but it didn’t work. The Grid is not showing any slots etc to enter formula.

@AussieFaraday2024 Ай бұрын

I would have preferred you told me how to install first. Add ins are banned in many corporate environments. Can't use this, sorry. Please advise at the start of a video so we don't waste time. Yes it's a great feature, but totally useless to those in big corporates that will not allow us to use it.

@shizziebizz Ай бұрын

Just install on your personal machine.

@AussieFaraday2024 Ай бұрын

@@shizziebizz Thank you for such an insightful observation. I happen to be a Microsoft Insider who does use the beta features on my personal computer. However this does not resolve the issue of work, where personal devices are banned and work that is done with these features is not compatible with the version of Excel that is approved on our work devices. The joys of working for a global organisation with strict IT policy. Also, these videos that are banging on about beta features and not saying they are not yet available to the regular subscribers are just acting in a trollish fashion.

@rosemaryng7994 Ай бұрын

Agree. I d like to know this information upfront not at the end of the video