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

If you struggle with writing Excel functions, this tool might be the solution.

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 #ExcelTips #ExcelTools

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

[hellopsp]

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.

[datingdave]

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

[MyOnlineTrainingHub]

❓What’s the most complex formula you've ever managed in Excel?

[RichardJones]

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

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

[frankocarroll]

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.

[alexanderbaranov]

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

[ovaneeden]

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

[olivierissaverdens]

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

[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?

[chrism]

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

[eduardosandoval]

This is so helpful! Thank you for sharing😊

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

[SRKKM]

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.

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

[RicardinhoL_]

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.

[HachiAdachi]

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

[marksandsmith]

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

[dispirted]

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.