Writing Formulas in Excel Will Never Be the Same (Free File)
HTML-код
- Опубликовано: 21 авг 2024
- 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
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.
WHAT! 🤯 This should be way more obvious. Thanks for sharing. Pinning comment for others.
Thank you! Hitting [ is another option. Will test to see the difference between the two methods.
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.
Thanks for the feedback 👍
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...
Mynda does the heavy lifting so we don't have to.
@datingdave1310 Glad you can make use of it 😊
@notesfromieisa-land happy to help! 😉
❓What’s the most complex formula you've ever managed in Excel?
Advanced Excel Formulas course: bit.ly/labs24formula
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
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
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: ruclips.net/video/cGxjWOY8h98/видео.html
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!)
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.
@@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.
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.
Thanks for sharing that info.
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.
Yikes! Sounds like time for Power Query 😅
Thank you, once more, Mynda! I'm definitely going to use it! 👍
Wonderful!
Great tool. Thank you. Hope they keep on developing it
Now there's a nice start to something closer to a even more mature coding environment!
Glad you like it!
Excellent Mynda, I’m going to start using it!
Awesome, Chris. Have fun with it!
Go to 5:22 in the tut to get how to instal AFE
This is so helpful! Thank you for sharing😊
You're so welcome! 😉
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?
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.
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.
Yeah, it takes a bit of getting used to but I expect that functionality is coming.
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.
@@LyubomirRusanov I understand, but I'm more used to using mainly Excel with the keyboard.
@@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.
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.
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.
@@MyOnlineTrainingHub Brilliant. Thank you.
so awesome thank you
Glad you liked it!
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.
Thanks for letting me know 🤭
Been using AFE for a while, and can't imagine being without it... I guess I can, but really don't want to.
Great to hear!
5:25
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
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 🤷♀️
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.
I'd start pestering. I don't expect it to be built-in anytime soon.
I installed but it didn’t work. The Grid is not showing any slots etc to enter formula.
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?
Place them in Modules, then you can access them from any file.
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.
Just install on your personal machine.
@@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.
Agree. I d like to know this information upfront not at the end of the video
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.
Absolutely. Good advice regarding avoiding overly complex formulas.
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?
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.
@@Kingleer69 Sorry, I have Excel 2021 Pro, it's build 16.0.etc which confused me
@keithward2240 you search for "Excel Labs" in the add-ins library. The AFE is a tool inside Excel Labs.
Hi ma'am
Thanks for watching!
Doh, it doesn’t support the old array {} way of writing formulas. Ah well, still brilliant tool!
No, it was written for the new dynamic arrays and lambdas.
Very badly made video.
Copilot AI - no more need for formulas.
Copilot is not that effective. 😅
Copilot doesn't know its VLOOKUP from its XLOOKUP. One day maybe, but it's not there yet by a long shot.