Excel's dirty little secrets - 5 things it does you don't expect!

[@iankr]

Many thanks, Mark. The Named Range issue when you copy a sheet is a terrible gotcha! If you copy a sheet containing named ranges to another workbook, it drags across the names with it, but referring back to the original workbook, rather than the new one. A major pain, if you're not aware!

[@ExcelOffTheGrid]

That is such a pain, and if you don't manage it correctly, you have have 1,000 of broken named ranges. I've been through that pain!

[@IvanCortinas_ES]

Mark in lab mode. I love it. The "Named ranges change scope!" point is very important, as it can cause a lot of headaches if you don't know it. Thank you very much for sharing all these secrets.

[@jeffstevens9729]

I have a workbook that has worksheet named ranges for updating formulas (I call it CellAbove) so that it auto updates if new rows are added below a range used for a formula

[@ExcelOffTheGrid]

That's a nice technique. And you have that as a worksheet named range? Did you know you can create that as a workbook named range? Select cell A2, in the name manager enter =!A1 as the formula. Because no workbook is named, it works on every sheet in the workbook.

[@ExcelOffTheGrid]

Yes, lots of problems caused by named ranges being used incorrectly. They are easy to set-up, but a nightmare to maintain.

[@jeffstevens9729]

@@ExcelOffTheGrid yes it is a worksheet named range on each worksheet. I did not know that =! trick. Awesome!

[@sevetp]

Thanks Mark for this video :-) I usually add junk columns to perform xlookup on unique values; now you showed me a clean way to do it :-). For Tip1, I would suggest to check this option : File menu / Options / Advanced options / Set precision as displayed.

[@ExcelOffTheGrid]

Thanks, I'm glad you liked it. I'm not sure about Precision as displayed though, that can cause a whole world of pain in a different way.

[@DataVisualisation]

... thanks for the interesting dirty secrets. Your first example has been following me for a long time. As my math teacher always said, 1 is not equal to 1 as long as I don't know the original source of the number. Most people don't think about that and the implications it can have. Here it's only a function that doesn't work.

[@ExcelOffTheGrid]

That math teacher sounds like they knew their stuff! As an accountant, I know that zero is not equal to zero... even if they are both zero. $1,000,000 error one way, ($1,000,000) error the other way. The difference might be zero, but it's certainly not right!

[@emregursel589]

Thanks Mark for this detailed and informative work. I have another situation for INDIRECT and OFFSET functions. Trace Dependens could not find the formulas if the formula is at another sheet, even if the reference is not in the INDIRECT and/or OFFSET functions directly. That is, if the formula in Sheet2!E8 "=OFFSET(Sheet1!E6,1,1)+Sheet1!E7" and you ask for the dependents of Sheet1!E6 and/or Sheet1!E7, Excel could not find Sheet2!E8. But if you ask the opposite direction which is the precedents of Sheet2!E8, it gives the cells Sheet1!E6 and Sheet1!E7. This problem could not occur if the references and the formula are in the same sheet.

[@WMcCullough-l1p]

Another one: order of operations. Excel’s order is P -sign EMDAS. In algebra, -x^2 is always a negative number. In Excel, with a number in cell A1, the formula =-A1**2 is always positive because the minus sign takes precedence over exponentiation. Confronted with this behavior, Microsoft called it a feature. You have to protect the exponentiation with parentheses to get PEMDAS result: =-(A1**2)

[@TheMrMishutka]

Thanks Mark. Another funny i have come across is if you use a dynamic range in a graph (eg A1#), it will show you a fixed range if you look at it in the chart design tab, but it changes if the size of the dynamic range changes. However, if the dynamic range gets too large (and I havent tested how large), the changing range breaks and it gets set at a fixed range, even if you subsequently go below the size of range that broke it. This happened for me when I had a graph of monthly values by day - if I changed the logic to "year to date" at some point in the year, it started breaking as the number of days in the year to date exceeded some number. It seems to work if you set a named range to the dynamic range and then use that named range in the graph.

[@ExcelOffTheGrid]

That's interesting. I wasn't aware there was a maximum size. Or maybe there isn't a maximum size and there is a bug. I might do some testing.

[@TheMrMishutka]

@@ExcelOffTheGrid let me know if you want any help!

[@leonhardmay3569]

I love your detailed/in depth approach. That is what differentiate this channel from many others, keep up the good work.

[@ExcelOffTheGrid]

Thank you - I really appreciate that feedback. That's made my day 😁

[@sledgehammer-productions]

Most of them I've come to expect - after scratching my head too often as to why the result is not as expected. But they're tricky! Thanks for putting them together!

[@ExcelOffTheGrid]

"Most of them I've come to expect" - that is the voice of somebody who Excel has caught out too many times. 😁

[@juliusbiliran]

So many things I didn’t know before which I might have made the exact same errors and not even aware of it. Ugh!! Super helpful!!! Thanks for this video. I can now be more careful.

[@ExcelOffTheGrid]

Thanks, I'm glad it was helpful. Excel is always trying to catch us out in one way or another.

[@leebecker8255]

Thanks Mark! I would definitely be stumped by these! The cached data one is 😮😮. What a cool trick that you can go in and have a look at it though!

[@ExcelOffTheGrid]

Thanks Lee. It's interesting to have a look around the xml code, you can even change it and do some interesting things (like removing passwords protection)

[@leebecker8255]

@@ExcelOffTheGrid ooooh thanks! I will try that 😁

[@mohammedelsakally540]

Thank you so much, Mark, for the valuable information you are providing to us all time 🎉 ....

[@ExcelOffTheGrid]

My pleasure! Just trying to share what I know or what I find out.

[@ksumar]

Useful tips. The first one baffled me 1.30000001.3000001 Yet the True function does work 🤔!

[@GeertDelmulle]

Mark, in my experience if you remove the locally defined names, Excel will use the global ones. BTW: thanks for the video - very humbling to see these limitations.

[@ExcelOffTheGrid]

Yes - deleting the wrong named range will point it to the correct one. It's been a long time since I've had that issue. I've learnt my lesson... don't create workbook scoped named ranges on sheets you might copy! Also, I discovered that if we have both the workbook and local named ranges. When trying to use the Workbook named range, the IntelliSense generated sheet reference doesn't work!

[@huseyinburaktasci1638]

Thanks Mark!! Very inspirational video as usual! A short question regarding the first tip of Xlookup... wouldn't it work if you we select Match Mode as 1 or -1... However, rounding is also a great solution. In general, there are many advanced tips. Thanks again!

[@ExcelOffTheGrid]

1 or -1 could be worse. It might match against 1.2 or 1.4 instead. So, rather than error, it gives completely the wrong result without us even knowing it.

[@huseyinburaktasci1638]

@ make sense… thanks! :)

[@JJ_TheGreat]

Brilliant! I never knew any of these!

[@ExcelOffTheGrid]

Now you know, try not to get caught out by it.

[@ziggle314]

Good reminders! That floating point issue caught me the other day.

[@ExcelWithChris]

Loved all of them!!!!. Thanks from South Africa.

[@ExcelOffTheGrid]

Thanks Chris 😁

[@slaydesaid8741]

Thanks - brilliant tips. 👍

[@ExcelOffTheGrid]

Glad it was helpful!

[@kebincui]

Awesome! 👍👍, thanks Mark

[@ExcelOffTheGrid]

Thanks Kebin.

[@naveedyousaf1657]

Helpful. Thanks for sharing. 👍

[@ExcelOffTheGrid]

You're welcome, I'm glad it was helpful.

[@ivanmamchych5802]

Thanks for the video. From time to time I encounter with the following "stranger thing" in Excel: Let's say we have a table on a 7th row of a blank worksheet. Select upper 6 empty rows and group them (Data->Outline->Group->Group Rows). Then collapse these grouped 6 rows (button +). Then freeze the table's header (View->Freeze Panes) on the row 8. After that try to expand hidden 6 grouped rows (button -). The rows won't be shown until you make unfreezing panes. This situation is really annoying, but fortunately rare

[@chrism9037]

Awesome Mark!

[@ExcelOffTheGrid]

Thanks Chris. 😁

[@lvtutorials3039]

Great stuff❤

[@ExcelOffTheGrid]

Thank you! 😁

[@JodoKast1221]

I notice when I use a formula to set conditional formatting sometime it changes the range in the formula.

[@ExcelOffTheGrid]

Did you ever find out what caused it?

[@t1mmmmo]

Thank you, interesting video. For item 2 when you inserted a row above the total but below the data it also inserted the row above the total line. Whenever I insert a line there it always inserts it below the total line which is irritating. How did you get it to automatically insert the line above the total line please?

[@jackgrayson9567]

An unrelated question to the video, on your Home tab you have a custom formats section. I have something similar on a customer ribbon tab i made years ago, they are all macro's in my personal workbook. Is that what yours are? Or is there another way to do it without having to create macro's that i didn't know about? Thanks.

[@ExcelOffTheGrid]

Mine is an Excel Add-in that I built years ago (built in VBA). We used to sell if for a while. Now we give it away as part of our membership program.

[@DinoDelight]

Great video, the Hidden formula explains alot, The cache trick, would that store the data name of someone else in the workbook. For example, every time someone opens a certain WB and someone is using it, the warning always says "user 1" Even though "user 1" isn't, it could be "user 45"/"user 10" etc.

[@alexb9312]

An alternative to opening the zip file to see the 17 digits in problem figures is to use Power Query. While Excel is restricted to 15 digits PQ will go to 17 digits. You will need to access the same workbook while it is closed though and then click on the amount and the full 17 digits will display in the details screen at the bottom. (this needs to be using the raw or decimal data type)

[@ExcelOffTheGrid]

Yes - you are correct PQ will show all the digits.

[@Trucpq]

Basic but important! Thanks

[@SteveGroom]

I use Excel Mac, often with PowerQueries. Sometimes I copy a formula that returns a dynamic area, but when pasted the formula is shown instead. If I retype the formula it usually works.

[@ExcelOffTheGrid]

As soon as you started with "I use Excel Mac"... I knew you were in trouble. 😂

[@gandhisunil3]

Hello Mark The case presented in last example is something which bothers me a lot, then we go for deleting locally created names, do tou have any better solution for same😮

[@ExcelOffTheGrid]

I don't have a better solution - other than learning not create workbook scoped named ranges on sheets you might copy.

[@tazguy371]

A suggestion for when you need to look up multiple values: put a character, such as an underscore or other delimiter between the values and use that to look up the result.

[@RichardJones73]

One thing that excel does that gets on my goat, is power query deciding it doesn't like mouse clicks on drop down areas sometimes. Yes you can use the keyboard on some of them, but there are some when you just can't select so requires a reboot of excel

[@ExcelOffTheGrid]

Oooh - interesting. I'm not sure I've experienced that. But it certainly be annoying - let's hope I don't get that bug now.

[@87CVH]

This should be a video series

[@poposhkov]

Indeed, I could think of atleast 2 similar issues on conditional formatting formula referencing when row number jumps, or when we use ut in a pivot table and nothing works there consistently

[@ExcelOffTheGrid]

If it were, I think nobody would trust Excel ever again. 🤣

[@johnburgess2084]

Thanks for the info. Here's my pet peeve: It seems like Excel has a single undo buffer shared among workbooks. Suppose I have workbook A and have been working on it for a while. Now I open (or create) workbook B and do stuff there. Finally I get back in workbook A and make some more edits, say, 3 edits. Now I do undo 3 times, all while workbook A is active. Lastly, still while workbook A is active, I do undo a few more times. What should happen? I'd EXPECT that the previous edits in workbook A would be undone, since A is still active. But no, the subsequent undos will be applied to workbook B until all my edits done to B are undone, then, for subsequent undos, it will undo the previous edits to A. Thus, my reasoning is that Excel has a single undo buffer such that my EXPECTED, and DESIRED, behavior does not work. I think Excel should treat workbook A and workbook B independently.

[@wmcnabb]

MARK MARK MARK....awesome video of dark side Excel oddities.

[@ExcelOffTheGrid]

Thanks, I'm glad you liked it.

[@tomekja3034]

I had reversed 1st issue one day, sum.ifs summed too many cells, cells were different at 37 place (it was text), i found out it by F9 in formula bar

[@ExcelOffTheGrid]

The text vs number problems... That's a good shout. That can catch us all out if we're not careful.

[@GameAGuy]

GREAT ONE😃

[@ExcelOffTheGrid]

Thanks 😁

[@Cherry_Memory]

great!!

[@ExcelOffTheGrid]

Thanks.

[@entrustedhub]

❤ Thank you

[@ExcelOffTheGrid]

You're welcome.

[@ChiranthakaSampathJayakody]

How did you get the .xml version of the .xlsx file?

[@ExcelOffTheGrid]

Use the same method as shown at 8:11 to rename the .xlsx to .zip. Then navigate into the xl/worksheets/ folder and they will all be in there.

[@ennykraft]

The external cache is the reason why I advise people to store their external lookup data in a table. Then you will get a #REF! error if the external file isn't open. It's a bit pesky to always having to open both files but that's better than incorrect results. The named range error is seriously annoying and Microsoft should change that behavior. Duplicates should be named something like Discount2 or - even better - Excel should ask us how we want to handle the problem. Having multiple ranges with the exact same name is insane.

[@ExcelOffTheGrid]

I would advise no external data links at all; it's just too risky. If you need to move data between workbooks, use Power Query. For named ranges, if we copy in a worksheet from another workbook, it asks what to do with duplicate named ranges. So why can't it do the same for internally copied sheets 😫

[@danp6101]

Hello. I have a question for you. I created a new Excel workbook and I use VBA to create some named ranges. Everything works fine but when I look in the Name Manager I see Other named ranges from another Excel workbook. Why do I see those named ranges. The workbooks are not linked. Plus if I delete them from the new workbook will it delete them from the workbook that they are from.

[@nazarkamal8831]

Amazing ❤❤❤❤❤❤❤❤

[@ExcelOffTheGrid]

Thank you. 😁

[@mattnyman9933]

I stumbled onto 4.08 - 4.07 .01 if you go to 16 decimal places. It was somehow signifcant to something I was doing 6 years ago.

[@ExcelOffTheGrid]

And 6 years on, and it's still seared into you memory. Why is rounding so difficult 😫

[@paramveerssachdeva]

Cant thank you enough!

[@amneetbola6733]

How do you see the underlying code behind a formula like on the first scenario?

[@ExcelOffTheGrid]

Use the same method as shown at 8:11 to rename the .xlsx to .zip. Then navigate into the xl/worksheets/ folder and they will all be in there.

[@mohammadalbizri2013]

Deep thanksful

[@ExcelOffTheGrid]

You're welcome.

[@kenritops1927]

How can I extract Excel code into HTML code?

[@ExcelOffTheGrid]

What do you mean by Excel code? You can save a workbook in a HTML format through the Save As options.

[@roderickmose4691]

Can you see that big, silly grin on my face, Mark?

[@ExcelOffTheGrid]

Ha, ha, ha - I'm glad you found it useful

[@muhammadasad729]

*mind blown*

[@ExcelOffTheGrid]

🤯

[@BobFrTube]

WIth VisiCalc we used decimal arithmetic to avoid these rounding errors, but people didn't understand so went to binary.

[@ExcelOffTheGrid]

…. wait a minute. Are you THE Bob Frankston? And you’ve just watched my video? 🤯 When you say “people didn’t understand”, what was it they struggled with? Was it that programming element which was too difficult. It was it users who struggled?

[@BobFrTube]

@@ExcelOffTheGrid KZbin operates in mysterious ways in what it chooses to present. The point of using decimal arithmetic is to match the roundoff and other errors an accountant would expect. Doing binary arithmetic through the lens of a decimal interface leads to just the problem you cite. The idea that Excel second-guesses the precision is simply perverse.

[@LEXIXON]

MS doesn't support calling macros with parameters from a worksheet, but with a trick it is possible and very useful! 'X "_3", "_4"'

[@k.chriscaldwell4141]

That named range thing caused me some grief in the past.

[@Chris-op7yt]

accuracy was never a goal of microsoft and engineers. speed and adding loads of cruddy features made it winner in office suite wars. so now you have a number cruncher that doesnt add up basic things right. other spreadsheets dont sacrifice accuracy for 5 milliseconds faster on most real world small spreadsheets. as long as they lock you into their product suite, that's all that matters for microsoft.

[@larmondoflairallen4705]

Don't get me started on how it rounds numbers.

[@ExcelOffTheGrid]

OK, in which case, I certainly won't mention that Power Query and VBA both use Bankers rounding which gives a different result to Excel's ROUND function 🙄

[@Rice0987]

How did you make that Custom format group on Home tab?🤔

[@ExcelOffTheGrid]

It's an Add-in that I built a long time ago. We will be adding it to our membership program soon.

[@tommy2cents492]

Don't use Excel for any (serious) data analysis.... Problem solved...! Never mix data and analysis: keep them separate!

[@EricHartwigExcelConsulting]

Very awesome video with some great examples Mark! Thank you creating & posting it! Wyn posted a work around for the last problem you are having and I thought it was a very clever solution, I would like to get your thoughts on it. Excel Table Traps and a few tips kzbin.info/www/bejne/rJOliZRpeciar6ssi=qn0cGMU5QBfKey9M&t=651