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.
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
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.
A great big thumbs up for this one. I've never actually come across any of these behaviours, but it has given me an understanding of things to look out for. Thank you.
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.
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!
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)
... 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.
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!
@@ExcelOffTheGrid As a Computer Science major it turns out the IEEE Floating Point Standard makes it impossible to store very specific decimal numbers properly. 0.1 is one of them. Essentially these work as a sort of binary scientific notation and you end up running out of bits because you never quite reach the proper fraction.
@@PixelOutlaw I'd argue that it's not that it's not the storing of specific decimal numbers that is the issue. It's calculating specific decimal numbers that creates problems. When you give the software 0.1 there is exactly 1 way that it will store that number internally. But if you instead try to calculate 0.1 using other floating point values, that's when you end up with different internal representations of what should be the same value.
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.
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!
Actually, you don't even need to copy sheet with named ranges. Unfortunately, you can expect the same result if you just copy sheet from the workbook that has workbook-level scope named ranges :-(
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.
I had this exact problem as well! Changing ranges broke for me at around 200-250 rows**. Like you, I got around it by using named ranges in the graph, and that's been working at least into the 600-ish row size range. **Changing ranges also broke for me in another scenario. My dynamic ranges pulled data from a table which is fed by power query. Refreshing the query (and/or reopening the file) seemed to "lock in" the range references on my charts even with smaller ranges. It seemed inconsistent and was very weird! Using named ranges definitely seems to be the safer option, especially for dashboards and whatnot where you want longevity.
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.
You are correct. Excel is an application instance. It holds the undo buffer for all the workbooks you have open. This ensures that if you cut & paste data from one workbook to another when you undo the change is rolled back to the original state on both workbooks. Ensuring you don't lose data. I'm sure there are situations where what you expect makes sense. In that case, the workaround is to save the other workbooks you are happy with. (optionally close the other workbooks) Rollback all the changes. Reopen the saved workbooks that you didn't want to roll back.
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.
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)
Issues such as #2 are reasons why I often include a hidden row at the (typically top) end of a range, so that _technically_ I am inserting a new row "inside" the range (formula updates) instead of "outside" it (formula does not). It also helps with applying initial formatting to the inserted row. (This also applies to LibreOffice, whose default setting is to NOT expand a formula range when inserting rows/columns adjacent to it) Any time I need to perform a "multi value" lookup I will generally set up a dedicated index column (typically containing just "=ROW()") then call a MAXIFS() on that column with whatever other columns I need to actually check the values of. I can then use that index value in basically any other regular LOOKUP() function.
A similar situation occurs to secret #4 with Word and Excel when setting up mailing addresses or mailing labels. If you change the names, addresses, etc. in the associated Excel spreadsheet you need to reselect the spreadsheet in the Word mailing/labels menu or you will continue to get the old values.
A pet peeve of mine about Excel is when you select view->new window to open a 2nd instance of the currently open workbook (useful to have 2 sheets of the same workbook on display at the same time). The original workbook has a "-1" after the name, the new copy now has a "-2", and so on if you open more and more this way. However all duplicates now no longer have freeze panes set up, and all tabs are set at 100% zoom. If you save with more than 1 workbook open, it will reopen all those workbook copies. If you accidentally close the original workbook (the "-1" version), leaving the "-2" still open, and then you save it, then Excel will overwrite the file with no freeze panes and all tabs set at 100% zoom. Excel should open all duplicates with the precise same freeze panes and zoom level.
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!
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.
Long-time fan. Been modeling for VCs since I was raising my baby brontosaurus. Instead of XLOOKUP, suggest a simple SUMPRODUCT. Keep up the amazing videos!!!
SUMPRODUCT instead of XLOOKUP… no way. SUMPRODUCT is an aggregation function which means: - No spilling without LAMBDA helpers - Can’t lookup text - Risk of calculating the wrong value if data not unique. I think lookuo and aggregation have fundamentally different calculation behaviours, so I don’t want to confuse the two.
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.
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!
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)
Thank you much for that issue I never knew. There must be 50 to 100 Excel oddities that cause hidden errors. Many of our database projects take clients out of Excel, and by conversion alone our database finds such errors for them. Some are so esoteric that they take me 30 minutes or more to resolve WHY Excel coughed up a lung. For example, an accounting firm sending a SS to one of their clients with simple SUMs incorrect. The reason was that there were a few numbers with leading sticky spaces. They did not appear any different from regular numbers, and only by re-entering the values one-by-one was I able to see that happened. Typical guesses like converting the column to number did not help because sticky spaces are treated differently. But since I work with a lot of math, I will keep the -A1**2 issue in mind.
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
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.
These can be nice features. Just pay attention that juggling with the more subtle features of Excel may become problematic if you do not test carefully, and from a certain level, ask yourself if Excel is the solution - maybe a professional accounting or data analysis software would be more reliable. You can still use Excel just for exporting or publishing of the results for external parties - I used to do this with SQL Server Reporting Services and Analysis Services.
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.
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.
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
…. 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?
@@ExcelOffTheGrid UA-cam 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.
One thing that I thought of is the Excel Date. Excel has a leapyear error in 1900, which isn't a leap year, but according to early versions of Excel it was. The date values for every subsequent days are one too much. They won't fix it, because of backward compatibility. You can get some weird results, if your calculations is between dates before and after the non existent 29. February 1900
@@ExcelOffTheGrid I've never been impacted by it. However, there is a different yet related issue that I've been impacted by, and that's counting hours across shift between daylight saving time and normal time, as well as across time-zones, and combinations of both. However, if I were calculating with older days, I would rather consider using LibreOffice Calc instead, as it will display dates before 1900 as well. But then their problem would be that they are all Gregorian, ignoring the transition from Julian to Gregorian starting late 1500s, and mostly over by the mid 1700s. I like how LibreOffice at leas allow you to go back to very late 1700s, without much of an issue, but not how it seemingly work before that, without consideration of the applicable calendars at that time.
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?
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
You can also add rounding to the lookup array in the formula, rather than the individual cell ranges. However, if your Lookup value in E4 is converted into a Data Validation object (dropdown box), this method no longer works. Would like to see that logic expanded upon.
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😮
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.
Oh man this is a way better way of searching VIA multiple parameters. I made a convoluted formula, that checks range1 for the item, and transforms it in to 0 and 1, does the same with range 2 and the second item and then multiplies the results. Then i searched for those "1" left after the multiplication of both lists and used the resulting line as a base for the index function to get the correct row.
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.
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 😫
1:26 This is why I use ROUND() in most of my cells, even though I don't use XLOOKUP() on numbers. 2:28 Great minds think alike, I see! 😀 7:37 That's stunningly Evil. 10:50 When creating the named range, can you pin it to the original cell with a $?
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.
Thanks for the video, but I'm not keen on your multi column lookup solution, as your spacer character could exist as a value in one of the lookups when concatenated, and match unintentionally, especially if it's a large or third-party dataset with many (unknown) values. A better solution is to use sumproduct(), with each lookup described as an array evaluation. e.g. Sumproduct((E1=A1:A20)*(F1=B1:B20)*(C1:C20)). This does expect unique lookup values though, but so do lookup functions for different reasons. The array lookups return arrays of Booleans, only where each Boolean array's position value is True for all the conditional arrays will the product of the Booleans be 1 (as True resolves to 1). This multiplied by the final lookup array returns its value.
SUMPRODUCT is definitely not a better option. Aggregation and lookup are fundamentally different types of calculation. Using SUMPRODUCT: - Can’t return text - Can’t spill without LAMBDA helper functions - Can’t work for lookup if data contains duplicate values. If you want to use Boolean logic then why not apply that inside XLOOKUP and maintain all the benefits for a lookup function?
Yet Excel is the most popular electronic spreadsheet program with a Gazillion users and a Eazillion developers! And these basic stuff are only the surface of how the software works. As to the Named Range, I thought at times Excel will ask you if you want to use the local Named Range, which is a good thing.
I don't think they an fix it. Billions of billions of spreadsheets use the existing calculations. If they change them, some of those spreadsheets will give a different result.
#2 -- I know how that happened. I have had to help people fix the counter problem. I.e. the range did not extend and they couldn't figure out why the sums were not correct. Forgive me father for I have sinned, I just forgave Microsoft for something. DAMNIT.. i.e people are likely to insert a row.. A row in a sum is usually part of the sum... The enter KEY is proof though. Hitting enter suggests you were adding rows rather than doing something else.
I know this one. It is a bit odd. The minus at the start of -3^2 is negation (i.e. it is negative 3 to the power of 2). But, the minus in 0-3^2 subtraction (it's 0 subtract 3 to the power of 2). They appear in different places in the calculation chain. Which is why they are different results. If you use 0+-3^2 the minus is negation once again and you get the same result as -3^2 Mind-blowing... right!!! support.microsoft.com/en-gb/office/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8
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 🙄
That 3rd one was sort of obvious. What idiot decided to use A1 as an Area when A1 would also be an Area and Zone concatenated. That’s not Excel’s fault, that’s user error.
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.
I’m not sure it’s that simple. Lotus 1-2-3 was the dominant spreadsheet package, and Excel had to be compatible with Lotus. To guarantee backwards compatibility various bugs have to remain otherwise it could change previous results. Which would be unpalatable to everybody. Excel uses the IEEE 754 standard for calculation, as do many other software tools. It’s an issue with Binary vs Decimal rather than an Excel issue.
@ : it's not difficult to use a precise mathematics library. COBOL uses it, as do many other software. it is cheaper to use imprecise floating point hardware. imagine if banks used such cruddy computing, lol
I think comparing an end user software like Excel to a banking system is pushing it a bit. It’s not difficult to make a car that can go 200 miles per hour. But it is cheaper to build cars which drive a bit faster than the speed limit (which is what most of us need).
@ : now you just making stuff up. it's compiled programs. Binary Coded Decimal for precise maths or floating point for sometimes imprecise maths. Same hardware can do both. Excel introduced calculation bugs that Lotus 123 never had. They made Excel compatible witj Lotus 123 to get users onto Excel when Lotus 123 was dominant spreadsheet program on PC.
@ : point was that it's not inevitable that binary representation of decimal + using floating point maths on computers = computers must get some calculations wrong.
Thank you for giving me 5 new reasons NOT TO USE THAT CRAP PROGRAM. WHY the heck did the developers of this trash write the code to make it work THAT EAY??? 😂😂😂 😢😢😢 TERRIBLE, but very Micro$oft.
You're right, if a tool doesn't do exactly what people want (and people don't take the time to understand it), then it's probably best to blame to tool. 😁
I discovered that if you inadvertently enter a number as text, you can perform most formulas on it as usual but in some situations, the SUM of a column derived from the text returns zero. I was using Power Query to grab values from a PDF, and forgot to change a column to a number format and it ended up coming in as text that looks suspiciously like a number. If you try to change it to the correct number format in excel, nothing happens. You have to overwrite the number to fix it, or trash the query and start all over again.
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 ua-cam.com/video/vbBXa3DcgyI/v-deo.htmlsi=qn0cGMU5QBfKey9M&t=651
An excel video with 5 tips that I've never heard of but that are also useful? That is a first. Well done!
Why, thank you. I'm glad it was helpful. 😁
I love your detailed/in depth approach. That is what differentiate this channel from many others, keep up the good work.
Thank you - I really appreciate that feedback. That's made my day 😁
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.
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
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.
Yes, lots of problems caused by named ranges being used incorrectly.
They are easy to set-up, but a nightmare to maintain.
@@ExcelOffTheGrid yes it is a worksheet named range on each worksheet. I did not know that =! trick. Awesome!
A great big thumbs up for this one. I've never actually come across any of these behaviours, but it has given me an understanding of things to look out for. Thank you.
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.
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.
I also often set up an "index" column (often just column A, containing only "=ROW()") specifically to perform multi-value lookups.
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!
"Most of them I've come to expect" - that is the voice of somebody who Excel has caught out too many times. 😁
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!
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)
@@ExcelOffTheGrid ooooh thanks! I will try that 😁
Thank you so much, Mark, for the valuable information you are providing to us all time 🎉 ....
My pleasure! Just trying to share what I know or what I find out.
Congratulations. Optimal presentation. Greetings from Alachua, Florida.
... 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.
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!
@@ExcelOffTheGrid
As a Computer Science major it turns out the IEEE Floating Point Standard makes it impossible to store very specific decimal numbers properly. 0.1 is one of them. Essentially these work as a sort of binary scientific notation and you end up running out of bits because you never quite reach the proper fraction.
@@PixelOutlaw I'd argue that it's not that it's not the storing of specific decimal numbers that is the issue. It's calculating specific decimal numbers that creates problems.
When you give the software 0.1 there is exactly 1 way that it will store that number internally. But if you instead try to calculate 0.1 using other floating point values, that's when you end up with different internal representations of what should be the same value.
@@phiefer3 sure .
1 + .1 + .1 doesn't equal .3.
But .1 of course is .1
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.
Thanks, I'm glad it was helpful. Excel is always trying to catch us out in one way or another.
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!
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!
Actually, you don't even need to copy sheet with named ranges. Unfortunately, you can expect the same result if you just copy sheet from the workbook that has workbook-level scope named ranges :-(
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.
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.
@@ExcelOffTheGrid let me know if you want any help!
I had this exact problem as well! Changing ranges broke for me at around 200-250 rows**. Like you, I got around it by using named ranges in the graph, and that's been working at least into the 600-ish row size range.
**Changing ranges also broke for me in another scenario. My dynamic ranges pulled data from a table which is fed by power query. Refreshing the query (and/or reopening the file) seemed to "lock in" the range references on my charts even with smaller ranges. It seemed inconsistent and was very weird!
Using named ranges definitely seems to be the safer option, especially for dashboards and whatnot where you want longevity.
Excellent work Mark
Loved all of them!!!!. Thanks from South Africa.
Thanks Chris 😁
Brilliant! I never knew any of these!
Now you know, try not to get caught out by it.
Thanks - brilliant tips. 👍
Glad it was helpful!
Well explained. You earnt my sub.
Great news. Thank you. 😁
Awesome! 👍👍, thanks Mark
Thanks Kebin.
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.
You are correct. Excel is an application instance. It holds the undo buffer for all the workbooks you have open. This ensures that if you cut & paste data from one workbook to another when you undo the change is rolled back to the original state on both workbooks. Ensuring you don't lose data.
I'm sure there are situations where what you expect makes sense. In that case, the workaround is to save the other workbooks you are happy with. (optionally close the other workbooks) Rollback all the changes. Reopen the saved workbooks that you didn't want to roll back.
@@davidlean8674 Good point. It makes sense when you talk about cut & paste. I was doing independent edits on each. Thanks.
Good reminders! That floating point issue caught me the other day.
Useful tips. The first one baffled me 1.30000001.3000001
Yet the True function does work 🤔!
I know, that's a bit of a tough one. That 17th significant digit is all to blame.
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.
Helpful. Thanks for sharing. 👍
You're welcome, I'm glad it was helpful.
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)
Yes - you are correct PQ will show all the digits.
Awesome Mark!
Thanks Chris. 😁
So interesting- thank you!
Glad you enjoyed it!
Basic but important! Thanks
Great stuff❤
Thank you! 😁
Issues such as #2 are reasons why I often include a hidden row at the (typically top) end of a range, so that _technically_ I am inserting a new row "inside" the range (formula updates) instead of "outside" it (formula does not). It also helps with applying initial formatting to the inserted row.
(This also applies to LibreOffice, whose default setting is to NOT expand a formula range when inserting rows/columns adjacent to it)
Any time I need to perform a "multi value" lookup I will generally set up a dedicated index column (typically containing just "=ROW()") then call a MAXIFS() on that column with whatever other columns I need to actually check the values of. I can then use that index value in basically any other regular LOOKUP() function.
A similar situation occurs to secret #4 with Word and Excel when setting up mailing addresses or mailing labels. If you change the names, addresses, etc. in the associated Excel spreadsheet you need to reselect the spreadsheet in the Word mailing/labels menu or you will continue to get the old values.
A pet peeve of mine about Excel is when you select view->new window to open a 2nd instance of the currently open workbook (useful to have 2 sheets of the same workbook on display at the same time). The original workbook has a "-1" after the name, the new copy now has a "-2", and so on if you open more and more this way. However all duplicates now no longer have freeze panes set up, and all tabs are set at 100% zoom. If you save with more than 1 workbook open, it will reopen all those workbook copies. If you accidentally close the original workbook (the "-1" version), leaving the "-2" still open, and then you save it, then Excel will overwrite the file with no freeze panes and all tabs set at 100% zoom. Excel should open all duplicates with the precise same freeze panes and zoom level.
I hear you on this one. That is super annoying. 💯
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!
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.
@ make sense… thanks! :)
Long-time fan. Been modeling for VCs since I was raising my baby brontosaurus. Instead of XLOOKUP, suggest a simple SUMPRODUCT. Keep up the amazing videos!!!
SUMPRODUCT instead of XLOOKUP… no way.
SUMPRODUCT is an aggregation function which means:
- No spilling without LAMBDA helpers
- Can’t lookup text
- Risk of calculating the wrong value if data not unique.
I think lookuo and aggregation have fundamentally different calculation behaviours, so I don’t want to confuse the two.
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.
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!
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)
Thank you much for that issue I never knew. There must be 50 to 100 Excel oddities that cause hidden errors. Many of our database projects take clients out of Excel, and by conversion alone our database finds such errors for them.
Some are so esoteric that they take me 30 minutes or more to resolve WHY Excel coughed up a lung. For example, an accounting firm sending a SS to one of their clients with simple SUMs incorrect. The reason was that there were a few numbers with leading sticky spaces. They did not appear any different from regular numbers, and only by re-entering the values one-by-one was I able to see that happened. Typical guesses like converting the column to number did not help because sticky spaces are treated differently.
But since I work with a lot of math, I will keep the -A1**2 issue in mind.
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
❤ Thank you
You're welcome.
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.
These can be nice features. Just pay attention that juggling with the more subtle features of Excel may become problematic if you do not test carefully, and from a certain level, ask yourself if Excel is the solution - maybe a professional accounting or data analysis software would be more reliable. You can still use Excel just for exporting or publishing of the results for external parties - I used to do this with SQL Server Reporting Services and Analysis Services.
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.
GREAT ONE😃
Thanks 😁
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.
As soon as you started with "I use Excel Mac"... I knew you were in trouble. 😂
Amazing ❤❤❤❤❤❤❤❤
Thank you. 😁
This should be a video series
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
If it were, I think nobody would trust Excel ever again. 🤣
great!!
Thanks.
WIth VisiCalc we used decimal arithmetic to avoid these rounding errors, but people didn't understand so went to binary.
…. 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?
@@ExcelOffTheGrid UA-cam 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.
One thing that I thought of is the Excel Date.
Excel has a leapyear error in 1900, which isn't a leap year, but according to early versions of Excel it was.
The date values for every subsequent days are one too much.
They won't fix it, because of backward compatibility.
You can get some weird results, if your calculations is between dates before and after the non existent 29. February 1900
Ah - Yes, the Lotus 1-2-3 leap year bug. Have you ever been impacted by it?
@@ExcelOffTheGrid I've never been impacted by it.
However, there is a different yet related issue that I've been impacted by, and that's counting hours across shift between daylight saving time and normal time, as well as across time-zones, and combinations of both.
However, if I were calculating with older days, I would rather consider using LibreOffice Calc instead, as it will display dates before 1900 as well.
But then their problem would be that they are all Gregorian, ignoring the transition from Julian to Gregorian starting late 1500s, and mostly over by the mid 1700s.
I like how LibreOffice at leas allow you to go back to very late 1700s, without much of an issue, but not how it seemingly work before that, without consideration of the applicable calendars at that time.
Cant thank you enough!
I notice when I use a formula to set conditional formatting sometime it changes the range in the formula.
Did you ever find out what caused it?
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?
MARK MARK MARK....awesome video of dark side Excel oddities.
Thanks, I'm glad you liked it.
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
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.
You can also add rounding to the lookup array in the formula, rather than the individual cell ranges. However, if your Lookup value in E4 is converted into a Data Validation object (dropdown box), this method no longer works. Would like to see that logic expanded upon.
Deep thanksful
You're welcome.
*mind blown*
🤯
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😮
I don't have a better solution - other than learning not create workbook scoped named ranges on sheets you might copy.
Excellent, thanks Paul
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.
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.
Oh man this is a way better way of searching VIA multiple parameters. I made a convoluted formula, that checks range1 for the item, and transforms it in to 0 and 1, does the same with range 2 and the second item and then multiplies the results. Then i searched for those "1" left after the multiplication of both lists and used the resulting line as a base for the index function to get the correct row.
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.
And 6 years on, and it's still seared into you memory. Why is rounding so difficult 😫
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.
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 😫
1:26 This is why I use ROUND() in most of my cells, even though I don't use XLOOKUP() on numbers.
2:28 Great minds think alike, I see! 😀
7:37 That's stunningly Evil.
10:50 When creating the named range, can you pin it to the original cell with a $?
If you haven’t done a video on all the issues with non-printing characters when pasting from a web page to Excel, you should.
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
The text vs number problems... That's a good shout. That can catch us all out if we're not careful.
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.
Thanks for the video, but I'm not keen on your multi column lookup solution, as your spacer character could exist as a value in one of the lookups when concatenated, and match unintentionally, especially if it's a large or third-party dataset with many (unknown) values. A better solution is to use sumproduct(), with each lookup described as an array evaluation. e.g. Sumproduct((E1=A1:A20)*(F1=B1:B20)*(C1:C20)). This does expect unique lookup values though, but so do lookup functions for different reasons. The array lookups return arrays of Booleans, only where each Boolean array's position value is True for all the conditional arrays will the product of the Booleans be 1 (as True resolves to 1). This multiplied by the final lookup array returns its value.
SUMPRODUCT is definitely not a better option.
Aggregation and lookup are fundamentally different types of calculation.
Using SUMPRODUCT:
- Can’t return text
- Can’t spill without LAMBDA helper functions
- Can’t work for lookup if data contains duplicate values.
If you want to use Boolean logic then why not apply that inside XLOOKUP and maintain all the benefits for a lookup function?
How do you see the underlying code behind a formula like on the first scenario?
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.
Can you see that big, silly grin on my face, Mark?
Ha, ha, ha - I'm glad you found it useful
Multi-Column Lookup can be done much simpler with FILTER:
=FILTER(C4:C19,(A4:A19=F4)*(B4:B19=G4),"")
True… but can it spill the results for multiple lookups at the same time?
@@ExcelOffTheGrid Yes, but if you don't want that:
=TAKE(FILTER(C4:C19,(A4:A19=F4)*(B4:B19=G4),""),1)
The one that got me is angles are in radians
Yet Excel is the most popular electronic spreadsheet program with a Gazillion users and a Eazillion developers! And these basic stuff are only the surface of how the software works. As to the Named Range, I thought at times Excel will ask you if you want to use the local Named Range, which is a good thing.
So, will MS fix this programming incompetence? Or will we get nice new pretty desktop icons?
I don't think they an fix it.
Billions of billions of spreadsheets use the existing calculations. If they change them, some of those spreadsheets will give a different result.
How did you get the .xml version of the .xlsx file?
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.
That named range thing caused me some grief in the past.
MS doesn't support calling macros with parameters from a worksheet, but with a trick it is possible and very useful!
'X "_3", "_4"'
How can I extract Excel code into HTML code?
What do you mean by Excel code? You can save a workbook in a HTML format through the Save As options.
I'm guessing that the version you are using no longer has the built-in "flight simulator"
I no longer us "Lookup" functions : I use "match" and "index" instead.
MATCH is a lookup function. It looks up the value and returns the position.
You will get exactly the same issues with MATCH and XMATCH.
@@ExcelOffTheGrid many thanks for an excellent video and a prompt reply to my comment. I will adjust my use of "match" accordingly.
😎
For the first one I thought, this is going to be an IEEE thing, innit?
Guess what, it was.
Yes, it's an IEEE thing. When it comes to programming & rounding it nearly always is.
#2 -- I know how that happened. I have had to help people fix the counter problem. I.e. the range did not extend and they couldn't figure out why the sums were not correct.
Forgive me father for I have sinned, I just forgave Microsoft for something. DAMNIT..
i.e people are likely to insert a row.. A row in a sum is usually part of the sum... The enter KEY is proof though. Hitting enter suggests you were adding rows rather than doing something else.
Meanwhile here's me learning from this that I can just type "X&Y" instead of (concatenate(X,Y)) every damn time...
Yes, CONCATENATE is pretty much obsolete…. Sorry.
-3^2 = 9, but 0-3^2 = -9. because we need more arbitrary rules I guess...
I know this one. It is a bit odd.
The minus at the start of -3^2 is negation (i.e. it is negative 3 to the power of 2).
But, the minus in 0-3^2 subtraction (it's 0 subtract 3 to the power of 2).
They appear in different places in the calculation chain. Which is why they are different results.
If you use 0+-3^2 the minus is negation once again and you get the same result as -3^2
Mind-blowing... right!!!
support.microsoft.com/en-gb/office/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8
When I find a problem with Excel I use a calculator and input the number where it belongs. Love Excel. Hate Excel.
Don't get me started on how it rounds numbers.
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 🙄
vad blir nästa avsnitt? Så här öppnar du en dörr.
That 3rd one was sort of obvious. What idiot decided to use A1 as an Area when A1 would also be an Area and Zone concatenated. That’s not Excel’s fault, that’s user error.
It's all user error. These are all documented... but guess how many people read the documentation 🤔
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.
I’m not sure it’s that simple.
Lotus 1-2-3 was the dominant spreadsheet package, and Excel had to be compatible with Lotus. To guarantee backwards compatibility various bugs have to remain otherwise it could change previous results. Which would be unpalatable to everybody.
Excel uses the IEEE 754 standard for calculation, as do many other software tools. It’s an issue with Binary vs Decimal rather than an Excel issue.
@ : it's not difficult to use a precise mathematics library. COBOL uses it, as do many other software. it is cheaper to use imprecise floating point hardware.
imagine if banks used such cruddy computing, lol
I think comparing an end user software like Excel to a banking system is pushing it a bit.
It’s not difficult to make a car that can go 200 miles per hour. But it is cheaper to build cars which drive a bit faster than the speed limit (which is what most of us need).
@ : now you just making stuff up. it's compiled programs. Binary Coded Decimal for precise maths or floating point for sometimes imprecise maths. Same hardware can do both.
Excel introduced calculation bugs that Lotus 123 never had. They made Excel compatible witj Lotus 123 to get users onto Excel when Lotus 123 was dominant spreadsheet program on PC.
@ : point was that it's not inevitable that binary representation of decimal + using floating point maths on computers = computers must get some calculations wrong.
So THIS is what Ellen is up to these days. Excel. Did not see that coming.
You are doing it wrong, so you get the wrong answer.
Thank you for giving me 5 new reasons NOT TO USE THAT CRAP PROGRAM.
WHY the heck did the developers of this trash write the code to make it work THAT EAY???
😂😂😂
😢😢😢
TERRIBLE, but very Micro$oft.
My take on that: DO NOT USE MICROSOFT SOFTWARE.
Excel is the most power, most helpful, and most dangerous software there is. Far far FAR too many things can go wrong, and there are rarely warnings.
You're right, if a tool doesn't do exactly what people want (and people don't take the time to understand it), then it's probably best to blame to tool. 😁
Is Excel the problem, or the users?
@@ExcelOffTheGrid The company is the problem.
Don't use Excel for any (serious) data analysis....
Problem solved...!
Never mix data and analysis: keep them separate!
Glad I stopped using excel
But… you’re still watching Excel video 🤔
@ExcelOffTheGrid yes was comparing it to libre office
Ah… that makes sense now.👍
How did you make that Custom format group on Home tab?🤔
It's an Add-in that I built a long time ago. We will be adding it to our membership program soon.
I discovered that if you inadvertently enter a number as text, you can perform most formulas on it as usual but in some situations, the SUM of a column derived from the text returns zero. I was using Power Query to grab values from a PDF, and forgot to change a column to a number format and it ended up coming in as text that looks suspiciously like a number. If you try to change it to the correct number format in excel, nothing happens. You have to overwrite the number to fix it, or trash the query and start all over again.
Yeah - data types are a real pain. That can cause a lot of issues.
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
ua-cam.com/video/vbBXa3DcgyI/v-deo.htmlsi=qn0cGMU5QBfKey9M&t=651