Feedback by UserVoice

Roy

My feedback

  1. 478 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    The 260 vs. 256 characters expl;ained:

    (from StackExchange: https://stackoverflow.com/questions/1880321/why-does-the-260-character-path-length-limit-exist-in-windows )

    (Entire Answer quoted below.)

    "Quoting this article: https://docs.microsoft.com/en-us/windows/desktop/FileIO/naming-a-file#maximum-path-length-limitation

    Maximum Path Length Limitation

    In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which is defined as 260 characters. A local path is structured in the following order: drive letter, colon, backslash, name components separated by backslashes, and a terminating null character. For example, the maximum path on drive D is "D:\some 256-character path string<NUL>" where "<NUL>" represents the invisible terminating null character for the current system codepage. (The characters < > are used here for visual clarity and cannot be part of a valid path string.)

    Now we see that it is 1+2+256+1 or [drive][:\][path][null] = 260. One could assume that 256 is a reasonable fixed string length from the DOS days. And going back to the DOS APIs we realize that the system tracked the current path per drive, and we have 26 (32 with symbols) maximum drives (and current directories).

    The INT 0x21 AH=0x47 says “This function returns the path description without the drive letter and the initial backslash.” So we see that the system stores the CWD as a pair (drive, path) and you ask for the path by specifying the drive (1=A, 2=B, …), if you specify a 0 then it assumes the path for the drive returned by INT 0x21 AH=0x15 AL=0x19. So now we know why it is 260 and not 256, because those 4 bytes are not stored in the path string."

    Roy commented  · 

    There are two separate issues stemming from a single problem.

    The first is that Excel seems to have a 260 character limit for resolving a cell reference. The apostrophes and exclamation point in the sheet name all get added in and then seven more for the old style cell references (65536 rows, 256 columns) for 41 characters max, so 260-41 = 219. One character seems to be taken somehow by the Excel system leaving 218 characters to be safe, sort of, for the rest of the path to the file that holds the cell. Literal characters, apparently. If that 260 characters is exceeded, the cell reference won't be properly handled and so things could go very wrong.

    So there's the problem, inside Excel.

    The outer difficulty is that Excel doesn't save files, it has the operating system do it. So an operating system with no such problem saves anything valid to its own rules and limitations. Windows, for example, can use as many as 32,767 characters if YOU enable it to. ("Enable Win32 long paths" in the Local Group Policy Editor — where it says the "normal" 260 character limit even though Excels support bandies about "256" even though things add up to 259...)

    Excel makes no check to see if you really have an issue, it just checks the path length against its hardcoded limit. You might be able to do a 250 character path, or 238, etc. with "the facts on the ground" but no...

    Worse yet, experimentation on my computer before enabling the long paths had various files with no cells addressed in them, just a couple cells of text, failing to save and re-open with anywhere from 168 to 190 characters vs. the 218 promised. So... there's more here somewhere. Oh, local drive C, no network paths, I didn't want the vagaries there to affect the experimentation.

    The aggravations mostly noted come from the totally outside problem that we now routinely have very long paths possible. Various programs that want you to buy them and collaborate roll their own" to enable longer paths, but they all vary, most likely due to each's own focus. A Balkanized world rather than the operating system simply providing and Excel working with it so middlemen don't have to figure it on their own.

    Worse yet, some code inserted into Excel that would read an address as text, provide an alias for the path, if needed, maintain it until the file is closed, and then take the resulting string and regard that as an address could fix it all immediately. No major rewrite, just an insertion. (Lol, I know, "just a wee little insertion..." but really, it should actually be that.)

    So even enabling the operating system only works for a program if it is "manifested" for it and wouldn't solve (doesn't solve) Excel's internal problem anyway.

    Meantime, all those programs and services have likely got a way to give a location a short alias and one could use that. Take a few hundred characters off the path that way, let Excel see the short path, allow the Save, and then the environment program would trade out the alias for the real path and present it to the file system for saving. If they can't do that, they really need some complaints too!

    But given MS's push at collaborative solutions, it would seem this is a thing that would be hugely important to them... oh wait... stuff like PowerApps doesn't have problems and would earn them a TON more money... sensible people should just pay up and everything would be great... things are much clearer now... poorer, but clearer..

    Roy commented  · 

    The upside here is that hope cannot possibly be futile in this case.

    Consider how MS wants the computing world to move forward. Things like Sharepoint are essential to it as are things like moving people to the "cloud" in its full glory: app server farms as much as storage farms. Either side of that is going to require much more complicated paths.

    Forcing everyone to use shortened naming and substitute pathways is mostly a non-starter for a litany of reasons. Result? Not just what the IRS likes to phrase as "I'm feel like I'm, feeling some pushback here when you claimed you were being cooperative" and not even outright refusal to do so but rather a simple ignoring of their desires no matter what and the folks buying things regarding a variety of products as "broken": as "not career enhancing"... and so simply no one purchasing their efforts. A flat "we're not even saying 'no', we're just buying something that works" result.

    So not only can and does Windows support a 32767 character path for anything with a path so they already have an incredible upgrade in ability available for no cost or effort at all, but also, they must do it or wither on the vine they are choosing to force us to.

    The only difficulty then is rewriting the portion of Excel that limits, on its own, how much path length a function can address. It seems functions can address slightly less than 256 characters and some of that has to be left for the file name and cell or range of cells being addressed. 31 characters seems to be the assumption, though reading the explanation they give for that made it seem like it should really be a few more... it adds to more than 31 (just the sheet tab name alone can be that long, exactly that long, so it suggests that's the real reason for the 31 and the cell or range isn't in the sum at all)... with the rest being available for the actual path name to the file. (Add a full cell range to the 31 and the 218, and one exceeds the 256 max possible: maybe there is something in that for why certain things mysteriously fail. Hmm...)

    In any case, from THEIR OWN viewpoint, this is an utter, ABSOLUTE MUST. So it is very likely they mean it when they say they intend to include it in upcoming work.

    (Of course... if, in 3½ years the ADMIN comment is 3½ years old and still no changes... feel free to question my mother's species... Bill Gates must have loved the movie Brazil.)

    Roy commented  · 

    They limit it so as to keep the files theoretically findable and openable/delvable by formulas in the program itself.

    Windows itself can allow 32767 characters if one sets it that way. Excel is interdicting the operation. Why not change it to allow any old path length? Bet it's tied up in a kludge that involves still maintaining some 8-bit limit that impacts other things too.

    Why not fix those? Well... might as well ask why some functions can reach into a closed file and obtain data and why some can't. Literally. My wager is some use the old methods associated with this problem and CAN do it, while along the way someone added a new approach and the ones that cannot reach in are written using the new approach. Or maybe the other way around makes more sense.

    Hopefully they really mean the "PLANNED' thing instead of it ending up like a couple others aging like a Hollywood starlet. Seems like this would be a fairly simple change and the only downside going forward would be that the functions that would no longer reach into files would seem to fail to do that for some unknown reason. Or maybe it's all upside, if all of them now would. Old limited paths would work fine in a larger setting, so there's no obvious downside there.

    So, one hopes...

    Roy commented  · 

    No. But thanks for thinking I might be.

    Are you an a**hole? Walk like a "duck"... talk like a "duck"... people might think so...

    Roy commented  · 

    Mr. McGuinn!

    You are a troublemaker! Me, me, me!

    OTHER people don't have this problem! (One lives in Texas, about 40 miles west of Dallas, the other lives in Idaho, sorry, no references there to be miles outside of, it's just a lot of empty.)

    If those two can handle working with the limitation, surely the other 800 million of us, you included sir, can do so as well.

    Bring back America's CAN DO attitude! No more of CAN DON'T. That doesn't even make sense! How can you "don't"??? Note, a CAN DO attitude does not apply to monopolies or big bullies. So do not judge MS, lest ye be judged! Oh, well, judged again?

    Remember the successful two who firmly believe that Apple does not have directories and structure hiding somewhere, and that Windows can work with everything in the single directory they call "root." Just put every little thing in the root directory and you can easily let SharePoint consume almost any number of characters and still save your file!

    It's so simple. I mean "simple" like in "otherwise mentally enabled" not like in "easy", of course, but still simple! No further need for "wishful thinking" or "improvements"!

    Roy supported this idea  · 
  2. 985 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!

    John, Excel

    Roy commented  · 

    (Sigh... )

    It used to be fun to poke fun at the advertising about "use Office whose programs all are built to work seamlessly with each other" failures. They never did more than Windows allowed anyway and they never will. But that's not even bruted about anymore so...

    It's still ironic though, that the lack of working together is so deeply rooted that separate instances of the same Office program can't even coordinate better than the chosen program and Jim-Bob's shoe shine app do. Usually, "ironic" usually includes "sad, so freaking sad..." and this is no exception.

    16 months... "You wait 16 months and what do you get? Another day older and... "

    Roy commented  · 

    Wikipedia says (and it fits what I've read over the years):

    https://en.wikipedia.org/wiki/Visual_Basic_for_Applications

    "... implementation of Microsoft's event-driven programming language Visual Basic 6, which was discontinued in 2008 ..."

    (VB6 being the thing discontinued, not VBA)

    "... the VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 ..."

    (apparently just added 64 bit support, though not complete support)

    "Office 2013 and Office 2016 include VBA 7.1"

    (nothing specified; just housekeeping for the new versions?)

    "... Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the .NET Framework ..."
    "... allowing even .NET-unaware applications to be scripted via .NET languages. "

    (always wondered why not when they ended VB)
    (oh, that would have been nice...)

    "However, VSA was deprecated in version 2.0 of the .NET Framework,[10] leaving no clear upgrade path ..."

    (oh... I see... )

    So, for now, I'd say our best hopes go with MS leaving the path open to other languages to write versions that, like Python to whatever extent, can replace, or work with/within VBA. That way, the onus is on others and their own development paths are not cut nastily short by decisions in other departments that they cannot really influence.

    As for this applying to our issue though, I'm thinking a laundry list of deficiencies will get made up over time since MS is NOT going to address them. With luck, they will become a list of reasons to use one language or another that might step into the void rather than staying an ever increasing list of reasons to revile MS and Excel.

    In particular, VBA was clearly never really pictured outside an MDI environment so there are definitely issues one might want to take note of in some of this Suggestion's comments. Flickering seems a small point (I know, important to the experience and some people's products) when compared to coordination issues that have been mentioned, for example.

    The bottom line is SDI creates what are in essence entirely different programs running in Windows and even though they are fellow Office programs (how much more "fellow" could they be?), it just isn't seamless and never will be, or would have been. Excel had a fantasy if they thought it could be. Drinking the "Office works seamlessly with itself" Kool-Aid does NOT make the advertising a reality.

    Evidence of that? Well, 30 years of living the dream... Of ALL the complaints in ALL the Suggestions you've all read, the sometimes complicated set-ups and uses of Excel, the writing of workflows maybe more complicated than many standalone software programs... of ALL those, when have you even one time read someone mentioning, even in passing, writing VBA code in Excel that reached out into other running Office programs to accomplish any task? Ever? On some other website? Some guru website telling you that your goal is best accompished by workign the material in Excel, perhaps, firing up Word to present it, and firing up Outlook to mail if off to 12,000 addresses. And using VBA from Excel, since it is the first program in the flow, to control it all, not just the Excel portion?

    No one even tries to suggest doing something like that. Not even MS. So just how well can we ever expect VBA to coordinate, safely and accurately, two or twelve instances of Excel, even if it is "just" instances of Excel, in order to work with all the files required by the work flow?

    Bring back MDI, allow one to set a choice, especially one available per spreadsheet rather than application-wide, and life is good for everyone.

    People who want separate instances could even have a "/switch" in the program shortcut (like the "/e" switch I just love). The complaint about moving spreadsheets between monitors was always spurious, though it is often said it was either THE reason for this, or at least the straw that broke the camel's back. Anyone desiring that could have always opened separate instances, or they could have normal-windowed Excel, drug its borders to cover the screen, then drug the appropriate one over onto the other monitor/s and moved MDI windows in the huge workspace. So even the rumored thing never needed a nuclear fix.

    It's all just sad and wretched. I'll eat a five pound pot roast with all the fixin's if it ever gets satisfactorily addressed. (I mean, I might do that anyway, but it's the thought that counts, right?)

    Roy commented  · 

    Sorry, I wrote that badly:

    Physically set the monitors only once when setting up the computer. NOT every, every day! Just the shortcut keys once a day when logging on to match what Windows presents to the screwy physicality. Lordy, not moving the monitors themselves each day!

    Roy commented  · 

    Too bad we can't rotate monitors 90°. Windows can be easily rotated too, in 90° increments, so one could just do that each day. Ctrl-Alt-(arrow of choice) and your computer matches your monitors in their "goofy" orientation. If I have to logon with a password to MY computer, I can do this without feeling put upon! (Well, one has to go to the Action Center once after buying the computer to turn on the use of those shortcut keys. Oh my, that does it!)

    When you'd need width too, you could spill the windows you tiled off the edge of whichever monitor so they cover both (the once a year I do this, I get columns A through AU). LOTS of columns too then.

    Oh well...

    Roy commented  · 

    So many of the difficulties we are listing in here could actually be addressed individually by Excel, in ways that do not depend on MDI/SDI. Even the more involved thoughts, like if you open a file from INSIDE Excel, use Excel to do it (File|Open), not Windows (by Exploring to it and clicking, say), Excel could almost certainly easily keep track of those (it does now, build 1904, in that only one instance shows in Task Manager while using Windows to open the 2nd file shows two in TM) and if you tell it to, allow presentation of the files like it did for MDI, even though they are truly SDI, and provide a single ribbon at the top. So whatever window, maximized or normal, that it starts with, ribbon at the top that keeps track of which file you are in at the moment and acts upon it, like tiled windows and MDI did.

    Literally, how hard would that be? Probably? Not very since they can keep track of the files a single instance opened, and all the pieces are simply windows anyway so they can just work with the pieces differently, and use both those things to present it AS IF it were MDI. Who cares? Real estate-wise.

    Solve half a dozen of the little things without the major switchback to real MDI, like they did with the copy/paste issue the switch created but is now solved, and I bet ¾ of us could make nice-nice with it all.

    That could be seen as "divide and conquer" but it would be "responsive to customer needs" in my book.

    Roy commented  · 

    (Sigh...)

    Remember all those years ago when MS and everyone else fell all over themselves proudly declaring they now had MDI enabled applications? Such a bright, new future we all were facing...

    It's like "1984"... OR IS IT?

    (Yes, it is.)

    Roy commented  · 

    Bummer is, the part I wanted worked for one (1) day after I "woke up to" the 1903 Build. Then back to business as usual.

    "Woke up to" Build 1904 today, no joy.

    Roy commented  · 

    Looks like 1903 is pretty up-to-date. I found an Office Insider person saying he has 1904.

    Interesting in its own way... Given your 1902 and my 1903, that may mean that 1904 will be rolled out next and contain the Spill functions, rather than it being more months.

    Maybe. But an interesting thought.

    Roy commented  · 

    I saw the same, so "real estate" issues and the all open spreadsheets in the work area still seem to not be restored. One could probably use "Normal" windows now, for something like that, though on the Windows canvas, not in the old "workspace" below the menu, etc.

    If one uses VBA much, there are macros (fairly) readily available for positioning file windows exactly, and repeatably, so one would not have to do it by hand every single time.

    Of course, that probably could be done with multiple instances as well, maybe, but maybe not as a single macro but rather a set of one each per spreadsheet, but those instances could not have shared data like in the "old days" and almost certainly could not share macro services very well.

    Now though (see how has moved from "hope it's permanent" to "it's permanent" for me?), since they are all in the same instance, it might be a workable thing to set up.

    Roy commented  · 

    @Mary:

    I seem to have: "Version 1903 (Build 11425.20228 Click-to-Run)"

    In human terms, it is Excel 2019 (I believe) installed from the Office 365 subscription the computer was set up with. The initial installation would have to have been Excel 2016, considering, and then updated over time. Other places here and elsewhere make it seem pretty solid that the version number is "19" and "03" as in 19 = 2019 and 03 might mean third update release, or March (third month) update release, or something of the kind.

    I cannot say concerning Access. My usage goes the other way, massaging data in Excel, then taking it into Access.

    Roy commented  · 

    Reporting a change in my copy of Excel:

    Today I found, quite by surprise that a large portion of what I want concerning the MDI to SDI change has suddenly appeared.

    I had been unable to open multiple files in a single instance of Excel using all the normal methods (File|Open, click on a file in Explorer or on the desktop, drag a file onto the program window, open directly from Outlook). I could work around the Explorer issue by dragging a file onto the program window, but nothing would keep a file opened from Outlook from starting a new instance.

    But now ALL normal "person" opening methods open to the single instance. (Have not tested VBA or opening from other programs.) Task Manager reports only the single instance, I can close all of them by clicking the Taskbar icon and choosing close all, and I can copy and paste between spreadsheets just like between pages (formulas paste, not results, and so on).

    Since I have no indication as to WHY, I am planning on enhjoying the thought that it was some upgrade last night and is permanent. Honestly, I don't know that to be the case, but always have hope.

    If anyone is now seeing the same thing, perhaps some of the related difficulties people face over the change are also fixed, or at least accomodated if "fixed" isn't the appropriate word.

    On the other side of my "Hope balance"... I would expect an updated comment from the Admin for the Suggestion if it is a permanent change rather than a transitory one. I can picture it being an experiment rather than a final idea. That could lead either way: many of us like it and find our biggest problems taken care of so it ends up being permanent, possibly with further adjustments, OR, well, taken away. I will say that there is a noticeable lag in a file opening when clicked on in Outlook or Explorer, say 3-5 seconds, rather than pretty much immediately, which IS suggestive of a work in progress. (Note to Excel: if that can't be improved, I promise I CAN live with it for the functionality! Don't throw the progress out for that reason!)

    Bright, shiny day!

    Ooh... I'm going to try entering a number with leading 0's next, see if they remain! Either way though, BRIGHT, SHINY DAY!

    Roy commented  · 

    Judging by some other Suggestions' comments from MS, I think Mr. Portwood's use of the word
    "last" is probably pretty spot on, though in a way we won't any of us find palatable.

    (Sigh...) Though I did see one for a different Suggestion that is less than a month old at the moment. So someone still looks at things here.

    Roy commented  · 

    (Sigh...)

    Roy commented  · 

    Enough digressing though.

    Roy commented  · 

    Yes, hence part of the desire to break the spreadsheet into parts. You are going to be one of the data sources? Then yes, you can try to mangle my work (I, um, mean the company's work) to ease your pain but you only have access to one little corner and nothing else. So even if (when) you find a way to break things, it is easy to overcome.

    In that approach, the individual users have no real choice except to request changes and live with whether and how they get them. Naturally, politics often trumps good thinking, but at least they usually have to be done by the maintainer of the spreadsheet.

    Life is definitely complicated by everyday events and workloads though, and a busy creator sometimes has to leave modifications to someone else. These ARE company owned, not creator owned...

    For less important spreadsheets, life is more complex. Not being critical, they are less cared for... chewed upon.

    But approach them as if one is (slowly) normalizing an unruly database and life with them becomes easier over time. Work smarter, not harder, and one's productivity rises.

    Yep, I remember IRC. Had a six digit ICQ ID. I do not reach back to bulletin boards, never having liked them, but started out in '79 with a TRS-80 and have used phone receiver modems. Jumping Jack Flash seemed oddly primitive until I realized "banks..." And I remember all the little Hitlers pretending to love the freedom of the internet. Until we breed different people, that's not going away, whatever a thing is called. John Calvin is around every corner it seems. Even Stack Exchanges are going that way. (Sigh...)

    Neither here nor there to this I suppose. The shame of it is that something harder to manage and maintain is be forced upon us solely for the money. BG is in rapture telling us monopolies DON'T do that, but it is not only what they are doing here, it is what they do, period. Because we aren't, apparently, succeeding at breeding vile selfishness ("Mine! Mine! It is ALL mine!") out of humanity while preserving mild selfishness ("If I don't look out for myself, who will?" "You have a point there Bob, you do. And more to the point Bob, who should if not you?").

    What I'm saying is this is the future: there will be no fixing things, 1,000 votes or 20,000 votes, just a slow deprecation of Excel to something that can only handle an inventory list at home for insurance concerns. Figuratively. We shall overcome, like we have through the decades, but the pool we overcome is shrinking down, so to speak, to a kiddee pool. The kind adults look funny in, their b*tts wet, but arms and feet dangling outside. Ahh... metaphors... There will be no fixing, no returning to old functionality. Even freshly selling Excel 2010, say, is not in the cards because these people really believe every human would have to buy 10 copies of the most recent version if there is no other choice. Indians with annual incomves of $300 would pay $500 for Office if those pesky pirates would just go away (to the h*llhole prisons they of course belong in... right?). They wouldn't eat, as whole families, for two years just to do it, gosh, if it weren't for that. That kind of "thinking" just does not lead to good news for this kind of problem.

    Roy commented  · 

    Absolutely. All sorts of flaws that have minor and sometimes major impact, but normalizing is an exceedingly useful concept, those DB people are on to something there...

    And it applies, or did, in the Excel world. For reference data, loading live data from various sources and types of sources, separating code into portions (or altogether), separating what is done with data (proprietary things especially) from sourcing the data, the list goes on.

    It all relies though, on the spreadsheets being available to each other and to VBA. Which is spotty at best in SDI.

    Of course, in a perfect world, one just puts a full spreadsheet together, makes it tick, works out the kinks and unexpected difficulties in the data feed and how people use it, and then management angels step in an hand over the cash to have programmers write an applciation that uses the essential approach, sources, people, and lessons of the spreadsheet. No boss calling you in one day and asking cold what specs such an app would need and your off-the-cuff reponses turning out to be the contracted for application. Rather a fully successful working better-than-prototype spreadsheet solution to inform the programmers every step of the way.

    Or the bosses ante up for an existing software that does the trick, after seeing how nice life is with a steady, accurate, safe, and private data flow is created and feeds your (proprietary) uses.

    So I am hardly opposed to seeing a treasured spreadsheet die by being replaced with an application. It's often the goal, really. But FORCED upon me? No, not so much... Especially when all that's really cared about is the new cash.

    And there are a million things we do that are "involved" but never rise to the "now let us write a program" level, if only because they are temporary needs (even, you know, "permanently temporary"), or fast changing, or just gradually become "involved" as one user or boss after another asks for this one little change. And "involved" means different things to different organizations depending on talents available at any given time. None of these situations cry out for writing a formal program, and the simple-minded things that are apparently PowerApps' focus are not necessarily on point.

    Additionally, managing a "normalized" spreadsheet is tough enough as the environment becomes complicated. Can you picture the management nightmare of, say, eight PowerApps apps that achieve the same-ish normalization? Managing them for results would be hard enough, but now picture managing them for not paying unneeded licensing fees.

    As to folks in forums... well... they might as well be religions, each one. "Choirs" at the very least, as in they are all "speaking to the choir." Each seems to have its set of absolute interests which must be nodded to, explicitly, and never, ever challenged. You might get lucky and post when a schism is imminent, but... those are not often. The self-validation they urgently need feels pathetic, but is in truth just sad. I avoid them mostly, have for a couple decades. Give a try when I'm optomisitc, but... it's also disgusting and I'm replacing lightbulbs with LED's that I will never have to replace again, so I don't have time to be interested in much of their cant. Good luck trying to swing any of them to a new view. It's like science: the people believing in (I, um, mean "who can prove continuous creation") continuous creation and who see the "big bang" theory (they even named it) had to die out before the big bang theory took the solid grip it has today. Overgrip actually, as there are actually some interesting explanations for some things if continuous creation is happening as well. That's physics. Imagine how that plays out in all other arenas in which opinion can have a role. Forums are almost nothing but opinion, so...

    Though if I need to know something, I'll suck it up and try again.

    Let me get in my daily thank you to MS for the wonderful world they just let us pay to live in.

    Roy commented  · 

    And looke d at more starkly in my example, if you figure Excel was worth $5 out of your $12.50 (Outlook IS pretty important), MS would now have $285 a month, vs. the $5 a month, from the things you used to do with Excel alone.

    5,700% increase in revenue.

    People get bonuses for stuff like that. Not promotions, 'cause they can NEVER repeat that success, but bonuses, sure.

    Roy commented  · 

    Here's the simple honest root of it all,no ifs, ands, or buts:

    MS wants to shift Excel away from being able to do anything much complicated or needing any real security or programming so that we all shift to PowerApps.

    Excel does a million things for a nice subscription price of $12.50 a month for a good version of Office. Well, a portion of that since you get all of Office and certainly use Outlook a lot and Word to some degree.

    PowerApps... that gets a nice subscription of $8 or so per month per application per user (including per designer).

    So you have 10 employees including yourself, all using Excel from an Office 365 subscription? That's $125 per month for "all you can use" (assuming they weren't silo-ing us into these newer, high cost applications and so made Excel useful and safe again).

    If you have five things you do, with an average of 7 users of the 10 using each one every month, they will now get $8 per month times 7 users each times 5 things = $280 per month for the five big things you used to do with Excel... PLUS the same $125 per month they STILL get because you still have to have the Office 365 subscription for not-the=five-big-things Excel uses, Outlook, and Word.

    So... by hammering us down to SDI so we cannot normalize our spreadsheets into a set for a task allowing better sfaety security as well as knowledge security, ease of use due to minimizing conflicts, reduced training by limiting a worker's interaction to what actually applies to him, and, by making each spreadsheet actually used smaller, having less load on the computers opening up he world of devices that can use the spreadsheets, MS is intending to force us into PowerAps, amongst others, raising the farming revenue they make, for no real work, by that example of $280 a months, tripling the example office's payout each month.

    Add in the required subscriptions to be able to share the app about ("publish" it) and access for maintenance of it, and you have raised the farming revenue even more.

    THAT AND NOTHING ELSE IN GOD'S WORLD is behind this SDI kick. Why do they not respond? They want such a future well-entrenched before we revolt.

    How many of you have been forced to consider the conveniently now available, oh-so-wonderful PowerApps subscription program as one of the alternatives we talk about below? How many of you are being told by bosses, who seem to be getting addressed on the subject outside of inside channels, "Look into PowerApps, it looks like something we need..."? I think Google, but worry, for good reason, they'll just drop their spreadsheet one day. They do that A LOT. No obvious reasons, just a thing is gone. But it isn't Google MS is angling at, it is PowerApps.

    PowerApps is meant to do every last little thing we keep complaining ("yapping" in MS-speech) has been lost and there is simply no coincidence to that. You sell the new bananas but people are reluctant to change? Buy some of the plantations and burn the crops. Pay other farmers to drop the crop for no rhyme or reason (Google) (yeah, that's anti-trust law there). Then begin sewing people's fingers together so they can't peel the old bananas and point out how the new bananas pop open with a mitten-paw squeeze and are oh-so-yummy. This is what monoploies do. Then they imitate Trump and simply tell some other story that also has no basis in reality and that's the life of the peons like you and me.

    Bitter? Cynical? F*ck yes it is. But it is also the absolute truth here.

    No nudge from me or from others has even bumped a comment out of MS. They're just happy "We the Sheeple" are busy forming a not in the least perfect Union. While they are opening new bank accounts as the old ones reach the physical limits of current bank software for recording balances. Sorry Ms. Gates sir, the account balance field is just a double integer field so you need several new accounts to hold the new money...

    Happy Days was a show created from a skit on Love American Style and Fonzi spent a year wearing a blue windbreaker. The story they all telll is utterly different. This is like 1984, things you did yesterday are now recorded as different than your memory.

    What we need to do is find someone who offers something like PowerApps, but works, not like the usual cr*ppy MS rival, and all go with that, smoothly leaving MS at the altar. Or talk to lawmakers. Every generation or so, that works a little.

    Roy commented  · 

    And God forbid you split a task into multiple files so each person can do his part without affecting others and without everyone handling the real collection of information and program logic.

    Would MS prefer to give us real security (something the word "granular" could apply to)? Would they prefer to also give us the ability to segment a single file so each segment could limited to particular people and also give us the ability for true communal work (i.e.: real sharing/multiple workers using the file without miserable limitations?)?

    So no? Then let us open multiple files in the same instance without having to go to trouble about it and without all the attendant issues arising when that does not work out.

    I might mention there are a number of functions that will not reach into a closed file to extract data. They regard a file open in another instance as being closed. So they have to be opened in the same instance, utterly reliably, not in a sketchy (and useless) "sometimes people don't get a tricky thing like file opening right and it all goes to H*ll" way.

    Users DON'T get everything right, and the further up the food chain, the more effort one has to apply about that (I have weight issues so it's no surprise my metaphors involve the word "food"...). We all know that: bosses boss and they regard you as making excuses if they ***** up. File opening shouldn't be tricky anyway... FILE OPENING! How should the word "tricky" EVER apply to that? But it does now. Yes, I can make the files open with VBA which WILL put them in the same instance, but part of the reason for spreadsheets at all is to not have to program as much. And I have not tested how well VBA does without the Registry fix mentioned in the comments, and if it is required for VBA's opening of files to land them in the same instance, well...

    I must say though, I don't understand the real estate point mentioned:

    1) Surely you understand you can reduce the magnification to, say, 20% or 30% and all the real estate problems just go away!

    2) Surely you understand you can just upgrade everyone in the company to nice 50" or 60" monitors, like they have, probably, in every cubicle at MS, and the real estate problems just go away!

    Sheesh... must MS bear responsibility for everything??? Use the 30% magnification for those older folks in the office that would otherwise need reading glasses. They should be retired and enjoying the golden beaches of Tahiti anyway, no company should keep them as wage slaves after 35 or so anyway! Do them a bigger favor and fire them all. So really, that 20% level should be awesome, right?

    Roy commented  · 

    @Anonymous:

    The bit at the end about the Registry entry is not correct. Many things in it are, of course, no argument. But the apparent purpose of it was to force Excel to ALWAYS operate that way, not only when the user did a special thing and by his distinctive action forced a new instance to open.

    There could easily be many reasons one might wish to do so. I myself would now and then to isolate spreadsheets from each other and sometiems in order to have more than one spreadsheet with the same name open at once. Perhaps someone would to have separate Undo stacks. Maybe they had formulas in something they were working on that would update from some second spreadsheet they had to open upon its opening, but did not wish that to happen yet had to open it anyway.

    Whatever though, it doesn't why they might've, they apparently needed it and asked about it and Excel's answer was, yes you can do that if you like, just make this Registry entry.
    Everyone else can keep on keepin' on, and you'll be happy too.

    HOW it applies to us today though is different as we represent a different part of the user base, one that wants the opposite and can't have it.

    What little it does for us is that it gives us back a few ways to get files to open in the single instance of Excel. So I can drag a file off my MRU list in Explorer and drop it on the instance of Excel I want it to open in. I could NOT do that before the fix. ONLY if I used the instance's File|Open function could I do so.

    So yes, it actually does bear on things here, and while it is no kind of argument for or against the Suggestion, traditionally folks in the Voice forum do not limit themselves to only such things, but also allow for helpful thoughts for difficulties expressed, even though that is not, of course, the actual purpose of this place. (If you like that kind of **** behavior, go to StackExchange instead...)

    I whole-heartedly agree though that there are contradictory desires expressed in here. My own comments decry SDI and cry the wonders of MDI. But as you can see, I agree with you that we should forget arguing about what's under the hood and express the functionality we need and a lack of appreciation for the functionality destroyed. We want a result and probably should evolve to discussing the results we desire without directly mentioning SDI or MDI. This would be natural since I think many of us are realizing some of what we want actually might be a bit SDI, not MDI.

    My understanding of human nature and my experience of general human intelligence says to me that there is no actual confusion about all this on Excel's part. Their people are surely fully aware of the things we want by now and capable of ignoring "yay SDI" and "boo SDI" commentary because they can realize we are perhaps thinking seemingly related changes came with that change and seem related and so mention them, but they aren't fully on point. They are curely able to catch on to that. But... counterpoint... you did not, so maybe they never, ever have in the years of reading these comments and talking to people and so on. I don't think so, no. But maybe.

    So I accuse them of "mendacity" and "neglect." I believe many others would harshen those terms and add "contempt" to the list. And it's well-deserved.

    But incredibly clear that MS misunderstood the requests? NO. Maybe for a week or two at first, but NO. They may still react ("trigger" in the modern parlance?) when anything SDI vs. NDI arises, but that's emotional, not intellectual. They're smart people and certainly as socially aware as myself, or a great deal more, and so much more than likely DO pretty much understand our points even if they trigger when seeing SDI/MDI popping up. There is 100% certainty they DO NOT 'clearly MISUNDERSTAND.' Bad feelings? Not care? Figure that we would think them meally-mouthed to explain they are bringing this or that back, but it's not really an SDI/MDI issue so...? Oh yeah, lots of that and more variants. But they simply do not "clearly" misunderstand. Maybe "somewhat" or "you folks are kind of confusing in small ways now and then" but not "clearly."

    I do think it's a good idea for us all to shift to saying the results we need, and/or the features we desire, since we may not have a full understanding of everything in the SDI/MDI thing. Just use MDI as a reference when possible.

    Roy commented  · 

    Lol, they took " s..e..x..y.. buzzwords " and made it " **** buzzwords "

    But that loser who tells them to blah-blah-blah and lards it up with the "F word" sees most of the "F bombs" post. And then reposts them 40 times. Some programmers with heads up their backsides!

    Roy commented  · 

    Two posts to get it all in since it seems there is a character limit (no, it doesn’t occur to me that maybe that means I should cut things…) Gonna post “halves” in reverse order so that it will hopefully read in the right order when seen together, though anyone following the thread will get email in which they come the wrong way. Sigh… yeah, I know trying to address two concerns at once is exactly where MS Excel’s programmers find themselves so there would seem to be some irony here…

    I should perhaps mention I hate the ribbon interface:

    1) I understand some people think "menu" is a four-letter word, in the worst sense, but the old menus worked like a charm. One thing they could have used would have been a right-click option of "Place on a quick access bar (single "line" button bar above or below the menu bar, say, with this single click of my mouse instead of the hassle I have to go through to achieve this now" so one could easily (literally, not in marketing speech-land imagination) have a bar of often needed commands. But whatever. This anti-menu thing was a religious thing, once upon a time, if you recall, with people hating to clock a menu choice then a sub-menu choice, then something else and a dialogue box opened with the real interaction... So now we have either a huge ribbon taking up real estate where one clicks a thing, not a menu, which opens more choices, and one clicks a thing, that then opens a dialogue box... but the priest says it's not what everyone hated about menus so it isn't, um, I guess...

    And now once has a hard time even learning Alt-shortcuts, when they even exist anymore. Yay religious fervor.

    2) One CAN reduce it to a menu looking thing, which I can't live without since I hate even seeing it at all nowadays (we evolve), and at least the loss of real estate is no more significant than with the menu bar it then looks like. But I'd prefer to get back the old menu interface, updated for new/newer commands and features, like Tables. With Alt-shortcuts always available and shown.

    3) I always hated the single workspace. I want the same functionality, but not that workspace bit. Talk about limiting real estate!!! Geez people:

    a) A single workspace with ALL your files sharing it? So you have four files open and somehow your real estate is greater than whole windows for files?
    b) You hate switching between windows? But switching between files that completely filled the workspace so you didn't lose real estate is different? (In concept... I know it's slightly different in some of the things that can interject themselves.)

    So whatever it takes to let me have separate windows rather than the single workspace is fine until it starts hammering and destroying other essential functionality.

    4) Just flat-out, the ribbon sucks. I hate it. Especially the huge space it takes and the very odd, in many cases, arrangement of features in it.

    I just want real, separate windows for the fact they give me a full window to work with, both sizing-wise and moving around-wise. One that interacts with the Windows Taskbar (which is the last time anyone will likely see me call it that instead of the incorrect "Start Bar" or variations on that). If SDI is required for that, then I applaud SDI.

    HOWEVER... since SDI apparently forces an incredible baggage train onto the program and utterly destroys big chunks of essential functionality, I don't want "it." Or at least, I don't want the ham-fisted, cut-the-baby-in-half approach taken to implement the single aspect of gain I see it having. I'd rather go back to full one workspace Excel than have what we have now.

    As to that Registry fix, it was apparently (based on the language at the Support site's way of putting things completely implying you wanted these things but currently did not have them, that it was written as if to an audience in that exact situation: certainly NOT the audience I am part of) meant to allow you to FORCE SDI upon your installation. So it was apparently written to ham-fistedly overcome what the audience did not like and creating the key with a value of 0, actively NOT seeking to do so could only keep the particular behaviors available from implementing the key from happening because of the key (with a value of 1).

    It, of course, was apparently not written to completely prevent those things, just not implement them because of the key... so if they were implemented in other manners, the key cannot help us. And if other programs (Outlook for instance) were updated assuming the full SDI concept in Excel, then abilities might not be available in the interaction (like how opening an Outlook attachment still opens a new instance, even with the fix).

    So... not a panacea. But better. A bit.

    Roy commented  · 

    Seocnd half of the post:

    Bad sides of SDI that we are stuck with and need to be done with include everything arising from the multiple instances thing. Almost everything. So no, I DON'T want SDI, I want the functionality of separate windows. Everything else that's been mentioned, I want the abilities offered by MDI... but NOT in the world of the LITERAL single workspace on a single screen with thing tiled or lapped over each other or maximized within the workspace so one tabs between them, etc. I want the stupid things free, physically, of that.

    But why can't Excel do that? Offer the separate windows one can arrange over twelve monitors however one likes, each with some basic command bar (my precious menu bar of the past, or the hideous ribbon interface of today, with the minimization, only covering half the screen when you are actively using it, ability) so it has full usability, BUT with EXCEL still seeing them in a single workspace, just not requiring that they be displayed to YOU that way. A program doesn't have to see a thing the way it presents it to you any more than you have to make the screen jump around following a macro's selection of cells as it works on them. In fact, that's usually not the best thing, eh?

    But the files HAVE to be utterly and fully integratable. In the sense that MDI allowed. Not just (though it's an incredibly annoying loss!) allowing something as simple as full cut and paste but macro integration and formulas reaching into other files and so on. (I have a Suggestion out there, with no interest or support for it, sigh, concerning that whole silo of issues. Why should a function that can reach into another file for data require that file to be open when other functions doing the same thing can use closed files? Anyway, in separate instances, the file does not look open to those functions so... SDI strikes again.)

    To use an analogy, I don't care about the engine, I just want the CAR. And when I turn on the radio, I want it to work IN the car I'm in, not launch a new car in which it is working fine, but that has no air conditioning... that's running in a third car it launched... so I get the worst of all worlds.

    I just want to farm my fields and plant at the right time. I don't want to have to worship a pantheon of gods just to know what time is the right time for planting each year. I know many prefer that alternative, but I just want the functionality and don't care about the trappings. Office's original marketing point (not just theirs: even the horribly limited spreadsheet in Open Office's Harmony program from 37 years ago did the same marketing, before Office existed) was that you could more easily share between its programs. They did whatever they did, but YOU lived in the world of it all can flow between as needed without worrying about how they did whatever they did. I want THAT. I don't care what the techniques are the programs use, or the marketing, or the **** buzzwords: I just want the functionality. ALL of the functionality. No more ham-fisted muck-ups.

    Roy commented  · 

    I'm looking for the following things:

    1) EVERY spreadsheet opens in the SAME instance of Excel. NO MATTER how I open them, every last method of opening them works like this and it does not matter where I open them from (including from inside other programs, Outlook being an example). ONLY if I intentionally force an new instance does one ever happen.

    2) EVERY one of them comes with a ribbon, etc., and looks and acts like it owns the world. (Better yet, bring back real menus and ditch the ribbon.) When they open, they open in their own windows which will be "normal" windows if I set it that way or maximized windows if I set it that way. There will be no tiling inside one workspace, or staggering, or whatever. They each get their own full window. If I wish to do side by side, I set that up.

    3) I have full and absolute interactivity between them. Only if I intentionally open new instances do I lose that full interactivity. That includes full value copy and paste, amongst other things, and full access via formula to any and all open files.

    4) I'd like a unique Undo stack for each but have lived 28 years without that so I can go a few days longer.

    5) Like any window, if I move it to another monitor, well, it's just on the other monitor. Nothing fancy needed, just Windows letting me move an Excel window around. I can do this because each file simply has its own window, NOT because someone chose the nuclear war option of forcing new instances. But to be explicitly clear: no returning to primitive days where the instance had a framework and a workspace and all the spreadsheets lived in the workspace... and could never be lifted out of it as their own windows.

    This is what I had using Excel 13 from an Office 365 purchase. IT couldn't make the 365 installation run on the new computer, so they dumbed it down to the Excel 13 version that could be installed instead. NOT 2010, 2013, no matter what people tell me.

    (Supposedly, I could not have had what I describe above, but I did. All with the Excel 13 available instead of the 365 installation.)

    For the time period I did try to use the suite's 365 version (it wasn't Excel that was unable to ever work safely with the computer), I experienced all the horrors I see today with Excel 16 (again from a failure of an Office 365 installation to work with a seriously plain vanilla Dell business computer). Then life returned to me. Now I'm stuck in this terrible place again.

    So... basically, it DID all work nicely as described above in the Excel 13 world which I am assured was 100% SDI.

    Which leads (finally) to the POINT:

    I don't really care about MDI vs. SDI, per se. I simply want the above, numbered, things just like I had them before when supposedly I had SDI anyway. I apparently had an SDI program, but had all the nice things about MDI which I list above. I just want the usability. MS can leave it SDI if they give me the usability I list above, back.

    I think most of us actually want precisely that and that some of the things said in order to attack and defend SDI are not really on point because apparently what is desired can be had in either situation. MS just has to return to accomodating MDI usability like they did in whatever version of Excel 13 they let one use if dumbing down from a 365 installation.

    (Not only did it tell you it was Office 13, it had formulas introduced in 13. So... Possibly there were odd files and Registry entries that hung around from the 365 installation that made it a Frankenstein monster, but a friendly, happy one.)

    As to SDI defenders, when I first came here, while using that version, I saw endless things here screaming to bring back SDI. So there is a big support for it. That's why, since it seems SDI is not, perhaps, the literal failure point here but rather removed functionality riding along with it, that there may a culture of ignoring us on the basis that we might be fervent, but don't know what we're talking about. (Thanks, MS, for giving us the benefit of believing we have the capacity to be ignorant, if that is so. Not every software provider believes that deeply in their users.) If they think that, then there's really nowhere for them to take this suggestion.

    Frighteningly, the difference I see between 13 and 16 could mean they did not remove all the MDI-like functionality with 13, but as they put 16 together, they "cleaned up" lots. And that it could get worse and worse as further releases "clean up" even more.

    All I care about is the usability I once had, and need, and apparently even when I had a full SDI version. Not about MDI vs. SDI, per se.

    Roy commented  · 

    @J ROGERS:

    Some of the MDI side of things still exists but needs a Registry entry. After someone mentioned it, and I created it, I eventually had the ability to open multiple files in one instance if I used File|Open or dragged a file from Explorer ONTO the instance of Excel that I wanted it in. I can also achieve that opening a file with VBA.

    However, most of MDI is swept away even still. For instance, find a file in Explorer and click on it? Opens a new instance. I can drag it onto the running instance of Excel, drop it, and it opens in it nicely, but click on it and it opens a new instance. And in no circumstance I've tried can I open an Outlook attachment by dragging and dropping (MDI effect): no matter what I've tried, they open a new instance of Excel. Maybe using a macro?

    Anyway, the Registry edit is from a MS Support article and does give some MDI functionality back:

    Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options

    DisableMergeInstance

    Value to give (DWORD) = 0

    The Support article is:

    https://support.microsoft.com/en-us/help/3165211/how-to-force-excel-to-open-in-a-new-instance-by-default

    (You may notice it was designed, apparently, for an MDI world in which one might want strict SDI, hence a positive value for the key forces SDI only. Hence the need for the value to be zero.)

    Again, it doesn't bring my world back to being right, but it does restore some of the MDI world to me.

    Roy commented  · 

    Folks aren't really aware of how change happens in many arenas... Science for example: the new ideas are never really accepted by most of the folks who preceded them. They have to work with them to keep their jobs, but like Einstein, NEVER accept their win and work as much as possible in their old paradigm. They move from the front line (teaching, working in labs) to administration and slowly to retirement. Then they die.

    Like it or not, this is how Big Bang theories, etc., eventually are the only thing taught, or largely so, and called "mainstream."

    LOTS of arenas, this is precisely how we (grudgingly) progress. Consider how it works when the results of experiments, and whatever stands in for experiments, are more a matter of opinion than clear and absolute (seeming) fact.

    Point of mentioning that?

    It's kind of looking like we will see one of two outcomes here:

    1) With no promises or updates, one day a new version will come out that restores MDI, whether tubing S(ucky)DI, or giving us a direct option.

    Yay! I wait the day!

    or

    2) MS just... never does do that... tosses an ADMIN comment to us every two-three years... (look at Pavlov's set of experiments: you'll be stunned at how long those dogs would salivate at the bells after the conditioning was set (or consider Cleveland Browns fans)) and just... NEVER does restore MDI.

    Figuring, of course, that like so many arenas of human life and endeavor, if they just wait it out, what? 30-60 years?, that all of us who ever knew MDI could even exist will die and then there'll be no pushback on the subject at all.

    Playing the "long game."

    I know which one I'm coming to believe it is.

    So... and I'm not there yet, but... alternatives. And as I've said before here, if I do significant work on that front, I will change and of course, never come back. I used three spreadsheets before switching to Excel and never returned to any of their predecessors after moving to each new one.

    When someone gives you an absolute "Pick from these one choices" edict, picking choice three is ALWAYS the way to go. If it weren't for Google's perfectly understandable willingness to utterly drop a whole idea the moment they determine it might not have a future, I actually would have switched already. That risk though... it's significant. "Pick from these zero choices" is even worse than what MS is offering.

    And finally (this comment, not forever, lol, seriously, right?) given how they are pushing the add-on programs like BI and the quick programming via Power Apps, I think they really want to break us from using Excel for anything other than quick and dirty databases with all the "fancier" work being done by one subscription app after another so they can add a pile of cash onto the initial purchase or basic subscription price. Honestly, breaking easy connection between sources and therefore the ability to separate a process into several spreadsheets so one interacts with only what his role needs to interact with, not fixing the Undo stack issue, ever, and giving VBA fits... at the very same time one is offering things like Power Apps and BI... it just smacks of a plan that is NOT NOT NOT going anywhere any of us want it to go.

    Ever watch the You-Tube videos of Bill Gates explaining how monopolu power allows a wonderful, beneficial organization like MS to force development of the future along just the right path instead of those nasty fits and starts and dead ends the riff-raff come up with? Ah, those wonderful idealists at MS. And Apple. And Exxon. And so on... where would we be without them and that sweet, sweet use of monopoly power?

    So... yeah... there's an old saying about sh*tt*ng in one hand and w*ish*ng in the other and see*ng wh*ch one f*lls up f*rst. MS is helping us with the "one hand"... will someone like Google step up and promise their spreadsheet will exist in twelve years? Not everyone left Eqypt in the Exodus, but enough did to keep a whole kingdom running for a few hundred years.

    Roy commented  · 

    @Frank: That might be Windows, or MS apps in general. For me, Excel opens on the correct monitor, first of the day, almost always. Maximized nicely. Open a file then close when done? Pops over to monitor #2. (By the way, glad SDI solves multiple monitor issues, right?) Put it back and it stays put. However, the many times a second instance opens, it's the same cr@pshoot you see for which monitor it opens on. And whether it's maximized or not. And even if it IS maximized, the first window it opens (the one that persists if you just close the file, not the instance), sometimes called the "gray window" by people, is often a normal window trying to get lost in the shuffle and left open.

    All s*cks. But...

    I've noticed similar problems with other MS programs. Not precisely those, but Explorer, say, sometimes opens as an odd window for no obvious reason. Word? Never a problem. Anything else MS, whether Windows or Office, yeah, one odd behavior or another vis-a-vis the window the open, sometimes.

    Most obnoxious of course, are the subsidiary windows: password entry boxes, file saving boxes, pretty much any dialogue type window, find, find and replace, sort... you get the idea... ALL OVER THE PLACE. They stay in place for days, then all of the sudden hide away in some corner of the program window.

    It was using Acrobat (X, not that sh*tty DC version), its file export and import functions in particular though that suggested something other than the intentional forcing me to use a window if only for moments instead of everything being maximized unless I had a particular need to see two programs at once, though the second monitor solved that need forever — the intentional forcing of "normal" windows upon me that it seemed like MS was doing (As in: "Oh, if only the unwashed would JUST try them, they'd understand how wonderful sh*tty little windows all overtop of each other are! Then they'd sing their praises and use Windows the way God intended it to be used!") — that something else might be happening.

    It was a glitch in Acrobat that suggested Windows, running on a computer that puts any I had before it to shame, now needs maybe 5-10 seconds to close out actions it takes involving files. I've experienced this for years now, not just the latest computer so it happens with regular hard drives and with SSD types and it happens at the computer level at home as well as with networked files at work. I just never focused on it before.

    If I gave Acrobat a 5-10 second count before the next click, its glitch went away. Completely. Not ITS glitch: it's the underlying file operations of Windows, of that I am convinced.

    With the exception of the monitor jump that ALWAYS occurs when I close the first file of the day in Excel (But not if I open a second one first using File|Open, even if I close the second one before closing the first one.) all of the things I hate vis-a-vis window sizing and location (for dialogue boxes) seem to be solved by making sure I remember to give Windows 5-10 seconds after closing a program before shifting to another or launching another. Basically, let Windows save final conditions for things, I think, before moving it on to another thing. I SHOULD have noticed this with IE, how if I re-launched it immediately (1-2 seconds) after closing it, it would abort deleting all the browing stuff it is willing to delete and turn the files over to the new instance rather than give it a set of its own while continuing to delete the old. Seems obvious in retrospect, but the Acrobat thing (not their glitch I am now sure) that opened my thinking.

    Hence my saying what you experience might be Windows jacking you with inexcusably slow file operations work that it aborts the moment you launch something new so it can concentrate on the something new. Once aborted, it doesn't seem to go back to it, especially if you're re-launching the program.

    Worse, given how MS now does the SDI thing, and you have constantly launching instances, some of which will persist (keeping a deathgrip on certain file operations) because you closed the file, but not the instance, I think you're seeing Excel launching and seeing incomplete or contradictory garbage for loading and choosing the default ideas that make sense to the 75 windows overlying each other people in Redmond. Kool-Aid drinkers doing OUR thinking and choosing for us. Yay.

    Another way of putting it is that I think it's a MUCH larger problem than just Excel, though given Excel's extreme shakiness, its frailty, due to the SDI garbage-implementation, and its presence in EVERYONE'S day, we see it there, first and a lot.

    (That frailty actually disturbs me more than any particular obnoxious expression of it does.)

    Roy commented  · 

    @Deanna... Please Deanna... broken up? Have you never seen a YouTube video of Bill Gate$ patiently explaining to us all why monopolies are incredibly good for an economy, and let's not forget, for a society?

    It seems that a monopoly can make decisions against the desires of consumers, for the better, of course, forcing them along the path from their current miserable existence to the shining future the monopolist, in his benevolence will allow them to have. I mean give to them, oops...

    'Against the desires of consumers' because they control the market and will still make tons of money regardless. The phone companies planned for us to be moving out of ISDN lines roughly now. That sort-of-128Kb transfer speed would still surely be better than today's 100Mb transfer speeds. If only we weren't so greedy, we could still be living their vision. Bad consumers!

    Roy commented  · 

    Of course, John commented nine months ago and there's no update on anything, no rumors, no experiments forced on 365 users so... it just doesn't look good.

    Roy commented  · 

    You had a window open for each file with a full set of tools. You could ALWAYS position them however you liked, move them to different monitors, tile them, cascade them, whatever you liked. Multiple monitors weren't any kind of issue if my plain vanilla equipment in XP, Windows 7, and now Windows 10 could do it.

    You could always open a whole new instance or three if you liked. Files with the same name, say.

    It wasn't like the old days when there was one window with the program interface (menus and such) and every file open had to share the real estate below that because they couldn't be drug out of the instance's basic framework window.

    So PRECISELY AND TRUTHFULLY, how was there ever an issue?

    Obviously, the Undo stack was shared by all the open files in the instance and that sucks, but that is a separate thing to fix.

    Then... SDI... Every file opened is in a whole new instance. Even with the registry fix, I can't open an attached spreadsheet from Outlook, their own directly related program, without doing it in a new instance. And others that I can open in the running instance have to be drug onto its window. I can't click to open them or... whole new instance.

    It is a nightmare. There are VBA issues. There are issues with files that depend upon each other. YOU try explaining to the boss that he now has a new level of difficulty to work with to use your nice spreadsheets. We used to be able to group spreadsheets and open them in sequence. Took that away. Very quick and handy it was. Now it's write a unique macro for every set or deal with the links that don't/can't update even though the files are open. In the context of this development, I have to think they took that away some time back because of things that led to this issue or because they chose to follow a path to here that along the way lost us this capability.

    Worse yet, the instances seem to be "owned" by the application that opened them. If I open an Outlook attachment and the new instance starts, I can keep opening Outlook attachments and they all open in that instance. That somehow has to be figuring into this as well.

    There is NO control over how the new instances open. So instead of maximized and on the monitor of my choice, they come up literally willy-nilly on their choice of monitor and always just a stupid "normal" window that I then have to maximize. Not to mention the dead one that opens and launches the new file. Sometimes it closes when I close the file, sometimes it stays. It is always present in the Task Bar, until closed.

    There is and was NO monitor issue unless it was very weird and almost certainly limited to a small slice of users. If plain vanilla has/had no issues, then there just wasn't an issue.

    I will note though, that we finally read something from John that is certainly true-ish. When I first came here, there was a steady whine, though NOT a roar, about having changed FROM SDI to MDI. Made no sense to me because it had never existed for me: My experience since the ribbon has all been what I described above, 10 years of it, until I got stuck with this version. Even 2013 was the good stuff though everyone says no it wasn't. Strange, that. But there were constant cries to return to SDI though they always petered out with a few votes. Sad that more than a few votes doesn't seem to take us back.

    Seems like it should be a choice in Options. Or at least with some registry fixes. (I'd be thrilled if the attachment opening could be fixed that way, or the dead window each new instance starts, or the window state when they open, or the whole having to drag because clicking would open a new instance thing... or all of them...)

    Hmm... maybe MS is trying to hide performance issues in Excel by forcing each file into a separate instance? Whole new set of resources... alleviating performance issues some? Fix this MS or you won't have any reputation left as we think of more reasons why you're doing this to us. And if you go the way of 123 and Quattro Pro, WE will write the story of miserable failure to consider your market instead of your own weird ideas. Right or wrong, just like those who wrote the 123 stories, we will write how it looked to us and then it, like you, would be history.

    Be nice if you chose a different fate. We DO actually like Excel, for the most part.

    Roy commented  · 

    @Doug: Sorry to hear that... I would say though, for two reasons: 1) With it in the registry and set to 0 I at first thought it had no effect (the longer story is below somewhere) but eventually realized it DID have some effects, just not a wholesale changeover back to MDI. I could, for instance, drag a file from Explorer onto the running instance of Excel and it would open in that instance where without the key, it would not even open in a new instance. I cannot drag from Outlook, and maybe other places, oddly enough. However, before the key, if I opened a file and a new instance was created, that instance persisted after closing the file while now it 90% does not. I still cannot click on a file without a new instance. Also, files opened from inside the instance, say via a hyperlink, open in the existing instance where before they started a new one. So... maybe like me at first, the changes, not change, were not noticed because we were both looking for a "change" not "some changes for the better, but still sucky-ish"? And 2) The Support article does not explicitly say so, but strongly implies, it should work for anything affected from 2013 onward. Not definitive by any stretch, but maybe worth a shot.

    But if not, I feel for you. SDI SUCKS. And weird things seem to happen. And to think I used to think it was nice I could have it when I wanted it (say, for two files with the same name... another thing MS is pathetic about given there's no way on God's green earth it should present even difficulties, much less be utterly not permitted... you can't even achieve it by accident without a fresh instance of Excel) which wasn't often, but I wish now that no one at MS had ever had the idea cross his mind.

    Sounds like MS is beginning to look at Home the way some providers look at Evaluation copies. Sad, for something they sell for a pretty penny and certainly do not state is crippled.

    Roy commented  · 

    Something I've seen as a topic here, bearing on the discontent with the subject, that I'd like to point out is NOT necessarily what people have said it to be:

    "The investment of time in switching to a new spreadsheet would be just so huge that I have to have satisfaction here, or live with suckiness forever."

    Well, for those not old enough to have gone through it, people said that about Lotus and Wordperfect once upon a time.

    More tellingly, Lotus and Wordperfeect said that about Lotus and Wordperfect.

    What are those programs? Something used in WWII to break German codes? Maybe Apollo used them for the lunar landings? No. Those programs were given to us by God as the only things that could ever be used, but they are gone now. Admittedly, a large part of that was an aspect of monopoly power, but I'd say Google, at least, is in a position to do the same thing. And guess what? Their spreadsheet is free too.

    MS had better get it right. I ditched 123 for Quatro Pro, learning curve be d*mned and then Quattro Pro for Excel because they broke them, literally, broke and not paying bills, but still learning curve be d*mned. I gave up Locascript for WordStar and then Wordstar for Word, learning curves be d*mned. All of them lesser programs, at the time of shifting, than what I had been using.

    And another for the record: the competition uses Windows, unlike MS in competition back then so no advantage to MS there. Arguably, because I hate it, but have no power to change it and it must change to this for society's sake anyway, the fact that Google has built to use the cloud (storage and app farms, to anyone who remembers Lotus and Wordperfect, "magic" to anyone who remembers Locascript and Wordstar) while MS is slumping its way there, in poorly conceived ways, and maybe not for another decade (the threatened sea changes in their engine, vis-a-vis the seven new wonder-functions, which it is hinted will make using older versions impossible as backward compatibility isn't part of the functions but rather a backward compatibility ("xlfn.") engine that is NOT mentioned despite being pretty revolutionary (and in place for a little while now) and that can be clipped out of the program in a second. With a SNIP... no more effort than that... no more backward compatibility.

    At that moment, several hundred billion spreadsheets would be dead... except that Google stands ready to help. Lotus and Wordperfect all over again.

    And in the coming move to a big iron cloud that you only interact with, seldom running things at your own desk, and having incredible implications for the cell phone market as well (important in itself, but also a thing utterly beyond MS control, which is how ideas win - Blu-Ray won the moment its capacity increased significantly as suddenly why would anyone buy a few hundred million computers with 60% of the optical storage - nothing to do with movies like the HD people aimed at - the whole second, unrelated market thing is massive in economics), Google is poised, like MS remembers itself, to go from a few percent of the market to 90% in a couple years, then enjoy 20-30 years resting on their laurels, milking it. If they make the milking choice, knowing their end would come similarly, someday.

    So a big iron cloud, with no Windows needed, and a competitor built on that model... what is MS's profit going to do under those circumstances?

    Or they can do a wee bit of work and satisfy both sides of this issue (there is an argument in here the OTHER way around, though it gets little interest as they are winning right now and not anticipating having to argue their side again). Stave off the liquidators a while longer.

    Or not. But it is not actually that hard to shift major programs. We've done it before, as a society, and we will do it again. Here? Maybe? If we have to. It's a pain, but not an unending one and makes room for a whole new generation to rise bringing new (old) ideas with them. So... whatever MS. IGNORE us. P*ss on us with mendacity (read ol' Johnny's message to us) and outright lies. See where it gets you.

    I will have a perfectly functional spreadsheet to use whether it is yours or Google's. To steal from NFL Films: "YOU make the call."

    Roy commented  · 

    Yep, that's the one.

    In the Support article it implies/makes clear that if the entry does NOT exist, you have MDI. MAYBE that is the case in a version that has MDI for a default. Hoever, my Excel 16 has SDI as a default and I was strictly limited to File|Open or everything created a new instance. After creating the entry and giving it the value of zero, rather than no entry at all, I got some capabilities back, for instance, being able to drag a file from Explorer onto the instance of Excel and it would open in that instance where before creating the entry, it would open its own instance.

    So take the "no entry at all is the same thing" that they either say or imply with several grains of salt.

    Roy commented  · 

    @Doug Reid:

    Sadly, it is no longer my most recent registry edit and it seems to have been deleted from here, a whole string of comments and comments back, 6-7-8 by different people. So I cannot say for sure.

    However, based upon memory, it was in a key in the Office structure and that key had a LOT of entries. In addition, it was a DWORD entry.

    So I will say it is the following key you want to add it to:

    Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options

    or whatever corresponds in your system. It has entries for Custom Margins through Monitor Topology Fingerprint through Use System Separators.

    The name to add is, I believe (not from memory this time, but because it seems to fit best and EVERY other one there is pretty clearly not it):

    DisableMergeInstance

    and the value to give it is zero ("0").

    (Because it is asking if you want to DISABLE merging instances which would disable all the MDI characteristics it will let you have. So 0 = False, as disbaling is NOT what you want to do, and should leave things enabled.)

    As I mentioned, it is not a return to MDI, and I went back and forth on it and deleted it too, hinking it had done nothing. Even thought it was a "ha ha" comment from a jack*ss until one of the comments had a Microsoft link for it (the real kind, one of their notes on how to work around their *****-ups, which, to be fair, though not happy, they DO come up with and offer while lots of others don't at all). Then I put it back in and suddenly caught on to the wording.

    The wording is like the way people write ballot initiatives, so a vote against turns out to be a vote for...

    Given the other stretch of comments is gone, I'm thinking this will only last so long and then be gone too. Stupid, really, given that I am less aggravated now I have this much, and one would expect others would be too, but... "beauro-think," aside from being the oxymoron of oxymorons, is a strange thing.

    Roy commented  · 

    The registry modification DOES help out a bit. Now I can drag a file from Explorer onto the instance of Excel I have open and it will open in that instance where before the modification, it just wouldn't.

    However, that's aggravating to do. Clicking on the file in Explorer still launches a new instance of Excel. Don't get me wrong, I'm not undoing the modification but it isn't MDI.

    Further, I still can't drag an attachment onto the running instance of Excel and have it fire up. Won't open it at all. And every method of actually opening it opens a new instance of Excel. So... not MDI in the least.

    Lol, boy, they sure showed all of us "How could you not have a separate Undo stack for each file" people, didn't they? With SDI, it's completely not a problem...

    Lord, the way some people apparently think...

    Roy commented  · 

    Yes, this is about "Unless I PURPOSELY choose an opening method that starts a new instance of Excel for opening some new file, I want every file I open in the same instance of Excel, NO MATTER HOW I DELIVER THE INSTRUCTION TO OPEN."

    So I open 50 spreadsheets? They are ALL in one instance of Excel. I might have used File|Open, I might have clicked on a desktop icon, I might have "Explored" for the file and clicked on it, I might be opening an attachment in an email: WHATEVER way I told it to open, it opens in that one instance of Excel.

    If that causes problems, they are my problems.

    Further, the idea this has to do with solving some issue with two monitors is either very particular to certain situations or I have, without the least effort or knowledge on my part, hit upon, utterly by chance, the better solution to it because for two years using 2013 I never experienced the issue. That reallllly suggests it is only in certain very particular situations, eh?

    And I can't even get the functionality by being careful in limiting how I open things. For example, I can "Explore" for a file and drag it onto the window for my single instance of Excel and I get the desired result though certainly not by just clicking on the file as I'd prefer. BUT... an attachment from Outlook simply WILL NOT open that way. I have no choice at all but to accept the second instance. So I cannot, through great care and effort even simulate the way it should work without effort. I imagine I shall find other instances in which this is the case.

    Further, we often have pairs of files we need to open here and for it to be done by users with no interest in learning the least thing about Excel. Until now, we could set up a couple icons, label one "Whatever-First" and the other "Whatever-Second" and they'd handle THAT flawlessly. Now that is going to open them in two different instances of Excel and quite fail to make one available to the other. Yay.

    And this back and forth business... New version of Excel? Oh yeah... it reverses the decision made on this in the last new version. So we have to go back and forth with this issue and when people have different versions... Spreadsheets are supposed to be quick-ish and simple-ish solutions to problems, especially ad hoc problems, not a new source of problems!

    They won't make simple, obvious changes to functions (like CHOOSE()... we ought to be able to give it a range and it would choose from the elements of the range... but it treats the range itself as an element... if we want to select from the cells in the range, we have to type each cell or its contents individually: i.e.: should be CHOOSE(3,A1:A4) returns A3's contents, but now we get an error and must use, say, CHOOSE(3,A1,A2,A3,A4) to get A3's contents) because, oh my, it could affect millions of users and their now "legacy" work. OK. Pretty valid, as a starting point. BUT... how is it something like this which is extraordinarily basic to usage can be changed willy-nilly with not just one sea change, like I mention for CHOOSE(), but with each new version hitting the streets??? Back and forth, back and forth.

    Of course, given the reponse to our desires and comments (as opposed to the useless and mendacious "attention" they get), maybe I should just be grateful the back and forth could have some very small component of responsiveness to the other side of this argument... Grateful to the gods that I got a crust of bread three weeks ago rather than railing at them for starving the three weeks since?

    That's not acceptable.

    And it wasn't really responsiveness to preference and utility that the other side claimed/claims anyway. It was that multiple monitor situation. Those folks railed for a long time, like we are now, and some still do because they still have versions that do what we like, and never really got a response. People with a "real problem" got a response, and the rest of us got slotted into a group called "whiners" and, you know, you can never content a whiner so why try at all, right?

    Well, since the last comment was meant to clarify so that some don't provide comment arguing for the opposite of the idea (Hope I haven't somehow misunderstood myself... could be I need to find a different suggestion to follow and support... by now I could be arguing against myself!) further clouding the issue though MS clearly understands each side of it and would be indulging in mendacity if they claimed we don't even know what we want.

    What I want is at the start of this comment: one instance with natural opening via all the ways Windows and Excel provide for opening files yielding them all open in the one instance and the ability via, among other possible ways, of forcing multiple instances by using Ctrl-Click on the Excel icon. Only intentionally opening a new instance would do so, not that being the default. (Hope I'm in the right place to support that... it'd be embarrassing as well as non-productive if I'm not...)

    Roy commented  · 

    Good Lord. Some loser keeps putting posts in with the "F" word for two out of every three words (you've seen them by their dozens), and I put "S a t a n" (do I need to say "without the spaces?) in and THAT gets asterisked out???

    This place is ****. I live in a world filled with ****-a-holics. Find a 12 Step program and work it you clowns. Just... do NOT come around to me in whatever step that "make amends" step is. Just live with that blot on your soul.

    Roy commented  · 

    Just how hard a concept is this? This has even more support than separate Undo stacks for each open file.

    And the opposite choice is just as supported.

    So... Jesus... OPTIONS... a simple setting in Options people, pick the one you want.

    Pick Always SDI and that's what you have. Pick Always MDI and that's what you have. Slight extra for the MDI folks: you can always CHOOSE to open a new instance of Excel and then open a file in it and so have SDI when desired.

    And then it's all over. Everyone is happy. Each gets his desire.

    My world was fine until IT overwrote my Excel 13 with Office 365 (32 bit even instead of 64 bit... who even thinks like that?). Now I am back to the evil world of SDI. Lived in that world four to two years ago, then lived in heaven, now today I'm back watching for ***** to come striding by.

    OPTIONS. Man o man people, just how hard is this?

    Roy supported this idea  · 
  3. 1,130 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Hi all,

    We have been working on this, and wanted to get your feedback on how you see this feature fitting into your workflow. We have a quick one question survey for you to give us your thoughts on the look and keyboard shortcuts we have at this time. Please see here for more: aka.ms/datePickerSurvey

    Thanks!
    Blake, Excel Product Team

    Roy supported this idea  · 
  4. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  5. 2,094 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks everyone for all of the passion about this suggestion! The number of votes has increased greatly in the last couple months and we’re taking notice! We’ve got a bunch of other Excel endpoints behaving this way already and we’re evaluating getting it done in the Windows versions sooner based on the number of votes it gets – so keep the votes coming!

    Eric Patterson (Program Manager – MSFT)

    Roy commented  · 

    The built-in bias to using Excel has to be overcome. For us really old folks, the rough equivalent was pushing buying toward Compaq (and then Gateway, then Dell, largely) ratheer than costly and underpowered IBM PC's. It was summed up for folks thinking of attempting it as "No one ever got fired buying IBM."

    1. There has to be a reason to do it or there's no point in broaching the subject at all. So list out the reasons you and others have. Pick the compelling ones. "Excel don't care no more than Honey Badger used ta" is not compelling to the bosses.

    Compelling reasons include a noticeable cost or degradation of efficiency that results in identifiable monetary losses OR not being able to satisfy a customer or to even offer a service due to Excel.

    2. There has to be an alternative. Maybe more than one, but more means confusion for them. They will get "the bottom line" on alternatives as they apply to the compelling reasons, but are looking to YOU for the decision on what to offer them. To be fair to them, that's their job, not doing the original work.

    So go in with a full decision made on one, maybe two alternatives. Connect directly to all the compelling reasons. Show the cost vs. their costs. The bosses think Excel is free. And a huge number of people do come with Excel experience and skills. Yes… "um, to a degree..." but it is Excel high schools teach, and Excel with a lot of Google hits when searching on a problem. They are aware of this, and training has a cost. Ignore it and they'll have to wonder what else you're ignoring.

    3. Be clever regarding alternatives. They don't have to be spreadsheets. You might have six compelling cases that DO need addressed but the solution might be to take them out of the spreadsheet world. Could off-the-shelf software address one or more? Would MS's PowerApps or similar solve things? Perhaps a real database? A program written for you? Just a front-end for the Excel data store? Perhaps breaking the problem into parts. Is data acquisition and low-level use is suitable for Excel but the analysis needs PowerApps or a front-end. Does it really require some things be mixed together? Maybe just change the literal problem areas leaving the main body in place.

    YOU must figure that out for your presentation and present proper costs and difficulties. Show how your chosen solution not only directly saves money, but is the basis for efficiency that will present opportunities in the future, either altogether new ones, or enhanced current offerings. Avoid utterly correct "it'll pay for itself in 9 months" points: no one believes them anyway, and the costs you will often be looking at here are going to be labor costs. Those people are still going to be working there, just on other things, so the bosses know that money will still be spent anyway. Perhaps show how they can spend the time saved on a particular new capability or two.

    Along with front-ends and other bespoke software, off-the-shelf software, and such, consider environments. Not just SharePoint and its ilk, but, as an example, there are programs out there which use regular programming tools and languages, then create spreadsheets for you that accomplish your programming. They might support the 300 most used Excel functions, but on the other hand, reporting of data is usually incredible compared to Excel's capabilities, not to mention bringing together disparate sources. Reporting, by the way, is simply presentation of data and usually can be offered for use as if the user were in the spreadsheet data itself. No need to look for smooth scrolling, data validation, user rights and access... the list goes on. And changes are written back so... Many multi-user problems just go away.

    So I'm saying make it a real project to push your point. Do it right, remember bosses are there to approve the well-thought-out work of their subordinates. Not do it themselves. So do it, be right, and a large part of convincing them is accomplished.

    Final thought: AVOID offering to trial your solution, especially in bits and pieces. You have the entire overhead of learning the solution well enough to even decide on the best approach, the entire overhead of learning the tools you will use, and then turning all that over to the person who will do the nuts and bolts of it. A nightmare that will have some bug in it that everyone frets over. Showing it working at a colleague's place of employment perhaps, but don't go down in these flames.

    Roy commented  · 

    Cool. MS doesn't even have to divide and conquer since we are perfectly willing to eat ourselves.

    By the way, Excel's purpose is to be an easy and general interface to programming a computer to solve your non-huge needs. (Like it or not, a spreadsheet is a programming interface.) Things that one cannot or for whatever reason, will not, bite the bullet on and have a purpose-built program written for. Then, like most things of any value in life, they grow.

    So for a webiste with this one's (stated) purpose of asking Excel for new features or imporvements, it is wrong of folks to step up on a soapbox and preach to all around about how certain things simply should not be done. Find a website for that kind of thing. Well, find several, maybe several dozen: people who like that kind of thing tend to be utterly religious about their "thing" and you will be thrown out and purged from the sites soon after some tiny opinion that is at odds with the master ("little Hitler") of the site.

    And all of that has no place here. This is for what one would like to see added, removed, or improved. Not for hassling folks for the things they must do (you know, at the places that do have lots of Indians instead of all chiefs?).

    Excel is cell-based and MS has been clear about that and some of the things that means. Of course, that does not mean things must be done one way and never another. My most common use, for example, that is affected here is plowing through data that has poor input controls. So, like 10,000 rows in a column might have 745 oddnesses to chivvy into shape, not just 13 main ones and another 10 or so here and there ones (think street addresses or names). Moving down the rows, visually, looking for new ones to automate a fix for works best when the rows click along smoothly so my eyes can keep focus on the same point (like an old-time astronomer comparing telescope pictures side-by-side to see if anything moved). If a row somehow has two or more lines when most have one and it leaps into view rather than smoothly bumping up, that flow is shattered.

    Another big use of this idea is I prepare things for others to enter data into. It's a simple fact that they seem to prefer, and do better with, forms that scroll up smoothly so the labels for each data entry cell do best when they are all the same number of lines:

    Today's date

    not

    Today's
    Date

    if they are mainly one line, or the latter if they are mainly two lines. Then things scroll up "smoothly" visually, no jumping 2-3 lines sometimes. This is a human thing. You design something like Excel, or a public park, making the best choices that occur to you and then take input and make changes as possible to better suit what users really turn out to want. Which can evolve too. No one ever (should have) said it's simple. Even the preachers in here don't seem to disagree with the basic desire here.

    But guys... the preachers are welcome, just not the preaching. There's no place in a site with a purpose like this one's for "well, you shouldn't be doing that anyway": people are, people will continue, it's often not theirs to choose, and they are Suggesting improvements based on their realities.

    Roy commented  · 

    4 I have a fever:

    The behavior you hate in #2) can be turned off. Go to File | Options | Advanced. The very first choice there is to turn Enter's movement off completely, or to assign it ANY of the four basic directions. You'll want to set the first six of Advanced's options every time you get a new computer with a new installation of Excel.

    If you occasionally do desire Enter to work that way, you can always turn it back on for however long it is needed.

    Or highlight the cells you wish to move between (doesn't have to be contiguous either) and pressing Enter will move you from cell to cell in the highlighted area. Until you forget and press an arrow key... Experiment with that if you use it for multiple row/multiple column blocks at the same time to make sure it goes as expected. HOW the cells were highlighted can make a difference, and ALWAYS makes a difference if things are not contiguous.

    If you define a range, you can select IT and only move between those cells. Useful for both data entry and data flushing (choose the range and hit Delete, your data entry cells are fresh again). Especially good for widely separated cells. Again though, how they are selected (order, whether highlighting on the fly, or typing the cell names when setting up the range) will make a difference in what order you move through them and that funny thing Windows does with filenames where one enters a string of, say, four filenames and it does them 2-3-4-1, not 1-2-3-4? Same thing happens here so put the cell you want to start in after selecting the range LAST when setting it up.

    Weird, eh? But mostly, just shut that horrid behavior off with the first option in File | Options | Advanced.

    Roy commented  · 

    Live in the shallow waters then my friend. Your offspring will evolve into sentient life while mine will still be sharkbait. I guess. Reading more than one or two lines without an insult in them to liven up the mood is a chore.

    Live long and prosper y'all.

    Roy commented  · 

    I have LITERALLY never touched, nor even seen a Mac in my life. Unless I saw something from the corner of my eye passing the one Apple store sort of in the area while walking in that mall. IPads and so on, sure, but never a Mac. I did use a 2e back in 1983 for a few weeks one summer but that was not a Mac.

    In any case, the Mac OS allows pixel by pixel scrolling of anything on the screen. MacOS Sierra, version 10, as an explicit example according to the last last answerer to this StackExchange question: (worth reading all the answers here for tips on how to get some of the needed functionality)

    https://stackoverflow.com/questions/7200000/can-i-make-excel-scroll-smoothly-without-using-middle-click

    "user3524289" last answer, 9/12/2017

    So instead of having to live with Excel's "snap to a cell, always" approach, Mac users can scroll in the way desired here. Because the operating system has the functionality and Excel doesn't override it. They see a good thing and ride it, not override it.

    As to Excel's institutional concept, the following is a volunteer moderator whose base reaction is "I'm afraid this is not how Excel works. The anchor point is always the top left corner of a cell." which reads like "So it is written, so let it be done." to paraphrase a little.

    Lower (open the replies, and go down toward the bottom, not the couple repeats along the way) and she... well, you have to read it, the circle of pointlessness won't close until you do:

    https://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-can-i-change-the-scroll-bar-in-excel-2010-to/967abb36-a0d9-4156-be15-ac154b1d6dc9?auth=1

    As to Mac users, from all I have read here and elsewhere, they do get this one thing, but suffer the absense of a bunch of functions() and more. So I don't really envy them.

    As to Macs again, since computing has moved to the "buy and use a toaster" stage, or at least significantly closer, many of my reasons for hating Nanny Apple are just "so yesterday"... but they are still a vile company with a vile corporate culture and that never changes. I will never own or use an Apple product. It's a sacrifice, no doubt, they are nice pads and phones and pods and so on, but someone has to draw a line and suffer if it brings suffering.

    Excel internally uses a tiny base measure for sizing everything, one that according to Allen Wyatt (no citation from his site, search there is pretty whack) is something on the order of 1,880 or so units per inch. But not exposed to us via any aspect of Excel, not even VBA. They have the internal measure in place to enable programming that references it, but are not interested, it would seem, in taking advantage of that.

    A workaround, a bit beastly, that few mention, would be to copy the cells one needs to examine against each other to a word processor and examine them there. Too bad no one has written an IDE to write Excel formulas in, complete with at least coloring of text in a better way than Excel does, the ability to indent and so on, all the lines you need, not three, all the possible IDE bells and whistles, then to let you click a "copy" button that collects it all and let you put it back into Excel with "F2, paste, and be happy."

    (Mac or Apple fanboy? Even Lot's daughters weren't more wrong than that.)

    Roy commented  · 

    No doubt.

    Got that.

    I comprehend.

    Excel uses the Mac's operating system to do precisely this. How or why is not the real point. That's how it does it. It is not written into Excel.

    Ergo my conclusion.

    Make your own. It's a free country.

    Roy commented  · 

    @David Filisan:

    It works smoothly on the Mac because the operating system provides for such smooth, small increment movement.

    Windows does not.

    Excel uses operating services for scrolling rather than its own programming for it. Hence, smooth on a Mac, obnoxious on Windows.

    There's no likelihood that (relying on the operating system rather than programming for it as a feature) will change, so until Windows provides the smooth, small increment movement required, Excel on Windows will continue as is.

    Roy commented  · 

    (Sigh...)

    Roy wishes... IF PowerApps was as easy as Excel, and IF it were a one price for all, like Excel, and IF it didn't have its own amateur limitations (apparently, most database sources are not handled properly and have a limitation of about 5,000 records...)... well, IF all those things, especially the second, I might be...

    Two things though, one new and possibly of use sometimes:

    1) A reminder that this works nicely on the Mac because the OS provides the service. There's a very good chance this will never happen inside Excel, but rather depends upon the Windows folks adding fine scale movement to Windows. Not only would that be a different programming interest group to inflluence, but there could be legal issues, or worse, to get around legal issues (given the monster Apple is, the monster MS cannot just "Disney" this competition by besetting them with dozens of lawyers attacking or defending, depending) Windows might end up with a less graceful feature. Still, better than this!

    2) New thing... I came across a Stack Exchange Super User post last night that has some interest for me, and maybe others. The gist of it is that Excel jumps the screen based upon the height of the row that the cell selector is in. How does that help?

    Well, if you have religious type feelings on the subject of merged cells, this cannot help you. If you have the tons of practical reasons to hate them, this probably can't help you.

    But if you can live with them, and can use a merged set of cells without a lot of trouble, AND you can navigate with the cell selector NOT in merged cells, you could be in business.

    Picture a five column table, A-F, and the big row maker is in column A, if it matters. When you enter any big row, the screen jumps. But if the big row was whatever, say 23 rows, and the big cell was a merging of all the rows in that cell, say A101:A123, AND you insert a narrow column that will not have merged cells (I figure the other four columns would have to have their cells merged too, to match the offender cell), and you navigate using the narrow inserted column, it will work naturally, going up a little by a little. The navigation can also be done by mouse and goes row by row as one clicks, like it does now. I cannot test it fully because I live in a happy world where my monitor is too tall to let 409.50 points (546 pixels, 1 pt = 4/3 px, What? Why is that?) take the whole height. Looks like it solves the jumping, and the never seeing the bottom material (without F2-editing the cell to see it).

    Obviously, hugely limited, and who wants to go about making all those cells (ALL columns for proper display, probably) to make it work. Not to mention how formulas would work and propogate... And could it even work right with Tables? And all the other difficulties merged cells bring.

    But maybe someone can use it. I might point out the Super User Answerer was bringing a technique from Google... seeing if it would work in Excel... and then reporting it.

    First sign in the wind of the, oops... "AN", not "the", surely?... intellectual shift from Excel?

    Roy commented  · 

    Folks, this is not "the" place where they come for things to do. Nor is it "a" place. 99% of the things that do end up happening happened because they were going to anyway, had some other internal motivation (like how to push you to PowerApps), or...

    because large enterprise users, who blithely pay subscription fees without any real critical attention at any level outside accounting, and who might decide one day to just walk away, and use something else. (I don't mean other spreadsheets necessarily, alrhough it could include that, but rather biting the bullet and properly programming a function removing a swath of their need for Excel which could lead to big drops in subscriptions — the last thing MS needs is for their business here to become a churning, roiling mess like cable companies ended up having: that doesn't play well with financial markets, or pride.) Eventually that might mean subscription revenue loss, but even more, those folks will never be forced over to PowerApps and a tripling of their subscriptions.

    So the last poster has a HUGELY good point, not just here, but for all the popular Suggestions here:

    Make it an Enterprise argument. NOT 1,904 users out of 800,000,000 million users worldwide, 1,904 representatives of enterprises of all sizes, arguing for this on the basis of dollars and how they represent 5 or 58 or 2,847 users via their enterprises.

    By the way, for just the price of $8 or so dollars a month, for every possible user plus each designer, for every important shared workflow spreadsheet you currently have for every month of every year, forever, you could have a PowerApp for each one... PowerApps don't have this scrolling problem, nor does it have any other of the problems we find at this site! It's just money folks...

    Roy commented  · 

    The Mac solution is not in Excel. That's why they have a solution and we don't in Windows.

    The operating system provides and Excel just rides along for free, so to speak.

    I'm perfectly happy for Windows to solve the problem...

    Instead, all 12 people who use touch screen monitors are given new "Ink" things to do. Priorities, you know.

    Roy commented  · 

    By the way folks, consider how you ever found this "forum." WHEN you even learned it existed.

    I've used Excel for 28 years now and only learned of it a year-ish ago, have to look at my posts and votes, maybe two years?

    Consider there being 750,000,000+ users of Excel around the world and how many of them each of our votes represents. My estimate is in the 10,000-100,000 people are represented by each vote we make here. Willing to allow down a factor of ten if folks insist, but... I don't think I'm off by too much and it may be on the high end, nearer to the 100,000.

    I understand MS would say, no, we have 749,998,200 happy, content sheeple, but that simply isn't true. Why do they count sheeple anyway? We are all people, with hopes and dreams, favorite football teams, children and other pets, to consider us sheeple is insulting, at best.

    Ahh, mendacity and talking about things that neither matter or exist... I have a future as an official response guy in here!

    Roy commented  · 

    You don't have any neighbors in vans or rivers nearby do you?

    Handmade is always best though, try to see this as an opportunity to let your artisanship shine in an increasingly factory-made world! You will see this as a blessing, someday, really...

    Hopefully not a big blessing though, 'cause with BG's benevolent monopolies happenin' (find it on YouTube), Ms will soon be running life itself andif you have a BIG blessing, you could jump scroll right past it and never experience it all...

    C'mon MS, that "oh we're on it NOW" post above is, sadly, now a year old and I'm still seeing it and not the solution...

    Roy commented  · 

    @Anonymous:

    Lol, yep, a joke, no doubt. To slightly adapt Roy Clark (RIP man), if it weren't for bad jokes, we'd have no jokes at all... Gloom, despair, and agony on me...

    Just yesterday I was starting a bit of research on another SORE point getting the same mendacious treatment and at a MS site (Answers.something.or.the.other.MS.com), the MS person charged with responding to the question cheerfully suggested the asker go to this site and weigh in his vote in the other SORE point's main line...

    And of course, it has no meaningfull response, just mendacity like the above (same cut-and-paste source?), from the same kind of year, year and a half ago time period.

    As if. Ah well, first world problem, right? If I were in Rwanda, someone would've been along already to cut off my arms and legs with a machete and r*pe my children next to me as I laid there dying. So I DO understand my fortune in having this complaint vs. other complaints I could have. But it still does not excuse this treatment by MS.

    Even a simple, straightforward message of "Get bent" along with the closing of the suggestion's comment ability would be a step forward, to somewhere, which would be better than limbo.

    Roy commented  · 

    What is quite surprising about the lack of response by them is that they love to trot out the

    "Excel is not presentation software like Word, Power Point, Publisher, etc. It is for and about serious work with numbers."

    line they use so much. Usually hesitation with that line only comes when asking why I can't make a cell 1.08" wide in normal editing. Then, of course, it somehow IS all about presentation.

    Their internal measurement system, used for presenting things, apparently has about 1800 units per inch. Clearly such a granular control could easily offer true scrolling, not "Jump! Jump! Jump! Jump! (Every screen jump)"

    (Sorry House of Pain, had to change a word there.)

    Oh, and MS guys and gals... it ain't hard for a couple CPU cycles to be used for checking monitor resolution. Even with multiple monitors.

    Roy commented  · 

    I used to paste long, long formulas into tall, wide cells so I could see the whole thing. Once upon a time. Since you can't see more than three lines in the formula editor.

    Gave up on that years ago when they often pushed other cells off the screen and once in a while were bigger than the screen so became a nightmare to get any value. I've used Notepad since.

    It's not even that though, just so obnoxious that they jump all over the place and leave you in a worthless situation.

    And the Mac lets you do it right because the operating system has the required pixel by pixel feature, apparently. Yay. Everything else of use has been copied back and forth by the two companies, so why not this one? I don't care if it's Windows that fixes it or Excel, but it's a mess.

    We're all wrong though, right? All we have to do is reduce size... go to say 50%, right? If one could even read the cell then the way text is butchered when downsizing. We're all just whiners... Wanting special treatment when there's already a feature no doubt planned just for this... All smoke, no fire. I fell so ashamed.

    Roy supported this idea  · 
  6. 676 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.

    Thanks,
    Steve (MS Excel)

    Roy commented  · 

    I noticed, in the experimenting for the last post, that as rule never "exists" for the user with no "applies to" defined. It will at least default to the selected cell, set up the rule, and present it fresh and new with at least that selected cell in the "applies to" box.

    One cannot get it to take anything not a cell address of some kind. If it does not resolve as a range, it is ignored, life moves on.

    But if the value for it could be given, and be pretty unacceptable, BEFORE there is a "previous value" for it to restore... I wonder if they considered this possibility. After all, it's been around long enough to pre-date VBA and Excel might've figured there was no avenue to try to make it happen. Might not have closed off the VBA route.

    So if one creates a CF rule with VBA but specifies a NR for the "applies to" data, I wonder if Excel would take it and keep it, presenting it at least one time via the CF tools in the Ribbon. ('Cause it would probably literalize it then, though maybe looking at it and closing without making any change of any kind...)

    If so, and if it evaluated it and it really worked as a CF rule, that would do what we want. Creation and changing to a different NR would all have to be done with VBA, not the amazingly obtuse Ribbon tools, but it would be what we want. Not how we want it, but what we want. Especially if changes to the range in the NR were acted upon in the area of effect for the CF rule.

    Roy commented  · 

    I'll make an important point about this "they're formulas" thing, then a wee little counterexample, then I'm done with that because this is not a constructive, and therefore worthwhile, argument.

    Important point:

    However Excel looks at it, even if it regards what IT itself calls NR's as formulas instead, it makes NO difference. If it takes the entries at face value and a range is a range is a range, then it works using the range, if a range was defined. If, like the INDIRECT() function, there is a resolution process because it looks at all of them as formulas, then it would resolve some as ranges and go forth and resolve the rest as not-ranges and give an error. (Of course, either way supposes some resolution process, but either way, an actual range is acted upon, immediately, changing the CF value from the NR name to the range it resolved, and non-ranges are simply ignored with the "applies to" box's previous value left unchanged.)

    So the whole thing just doesn't matter.

    Sidenote: The "applies to" box uses the "=" too. Are we to suggest that it too has a formula in it? I tried a SUM() for the range it was using, but it just ditched that and restored the previous value. It is perfectly happy to function with a value of "=$A$1:$A$12,$A$1:$A$12" (that second string is an actual duplicate, and is not resolved by Excel as already covered by the first range string, then removed so clearly there is no effort to do more than list the cells).

    So in this we see Excel using the "begins with an "=" character" yet Excel looks for a range, not a formula.

    And now the wee counterexample to the main contention:

    If the NR's are all formulas because they begin with the "=" character, then FORMULATEXT() ought to return those formulas as text. It should look at the value stored for the NR, see it is a formula, and return the text of the formula. It does not. Instead, it reads that value, resolves it to a range, then acts upon that reading the range's values creating the matrix {1;2;3} and applies FORMULATEXT() to that. So, not seeing the NR as being a formula.

    The only way out of that is to suggest that it queries the Name Manager concerning the NR, and the Name Manager gives it the range that the NR has been defined as. But... then the "'Excel Name Manager 'black box'" is giving a range to CF, not a formula and therefore CF is never having a formula to operate on, only an true range. In the way of saying that last, it is being given "A1:F4", not "=A1:F4".

    Either path above means CF always gets a true range to work on, if one exists for the NR, and could therefore let us use NR's. And if not, it simply ignores the entry and restores its previous value. So whether it is truly a formula or not, it truly does not matter because CF always has a true range, not formula, to work on.

    Fin.

    Roy commented  · 

    @Peter Collins:

    Yes, exactly. Each avenue I mentioned seems to lead to complications. Except the simple one. No checkboxes, no locking, just a simple:

    1. Excel sees there is a change.
    2. It applies the change to anything expressed literally.
    3. If the change was in a portion specified by a NR, it essentially ignores it because the NR itself did not change.

    Done deal. Specify a NR in the "applies to" box and unless an edit changed the NR itself, there is no effect on the CF.

    And, of course, Excel always retains and presents the NR itself in the "applies to" box. It would NEVER change it to a literally specified range for ANY reason.

    And that would be all that was needed. If one were coming at it from the other direction, one would simply not specify a NR. Or perhaps one could be entered, maybe in quotes (so they have to do the work, not us!) and Excel would know to convert it to a literal specification. That way they could enter things easily (and accurately: especially for complicated, multi-part ranges), letting Excel take it from there. And we could have real NR's used and retained.

    Speaking of using NR's, they can be built to be relative (A1 vs. $A$1) which leads to lots of problems, usually, where CF is concerned (one of the two other problems I find with it) but might have good uses too. So it'd be nice for Excel to retain that when literalizing the NR's in its application of the rules. Maybe... 'cause the weirdness that leads to are still there. But maybe.

    And if NR's could be used, they could be dynamic via formulas, for any interesting uses that might have. The dynamic ranges returned could be returned as absolutes ("Dynamic absolutes"... that sounds wrong! But it isn't really.) in the formulas and so be useful in part of the way the last paragraph mentions but without the problems caused by relative addressing.

    Roy commented  · 

    @Jaime Segura: that's just not correct.

    There is a practical problem for Excel vis-a-vis NR's and CF.

    Say you have a NR for cells A1:A10. You set up CF that applies to cells A3:A9. Over time, whatever way it happens (and there are plenty of ways), the CF comes to apply to cells A1:A10. This is sheer coincidence, there was never an intention to apply it to the NR, as such, it just, this moment, applies to a collection of cells that includes that range/NR.

    If Excel noticed and "collected" things by changing the reference to the NR, havoc might ensue. Over time, not necessarily this second, but as more of the "over time" changes occur, that would have led to a different set of cells that the CF applies to.

    If it uses the NR as long as it can, but then drops it when something conflicts (perhaps one drags away a cell from the A1:A10 area), then... what really was the point in ever showing it applying to the NR? Why do it?

    Our desire is coming from the other end of things and it makes perfect sense from our take on things: we want a permanent, easy way to ALWAYS have the CF apply to some given area. Something is dragged away? It loses its CF. We only want the CF to apply to the NR. Period. Don't want Excel to apply it to that plus the odd cell here and there that comes to have it. Just the NR. So it makes perfect sense: this is why Excel would do it.

    The two points of view are not really reconcilable, directly. Either you want the CF to apply to a given area, period, or you want to allow it to expand and contract and have isolated areas too. Obviously, there's a sliding scale here but at some point in between, the two approaches lack compatibility of a direct sort.

    If, however, Excel allowed the use of a NR (along with any other way of specifying what to apply the CF to), AND provided a checkbox that let you "lock" the reference, that would work for both sides of the coin. The area of application could still change, if the NR changed its range, or perhaps a row is inserted, but it would be because the range, however specified, changed. Call that "organic growth" (or shrinkage). At this moment, I think most can see how this could start to shatter too... so keep it simpler, perhaps: perform the locking by only applying it to NR's used in the "applies to" information. Specify a range or cell directly and nothing related to it expands or contracts, and if it is grabbed hold of and dragged off, it is lost from the "applies to" area. Drag a cell out of a NR portion and it loses the CF, but the place it was dragged from keeps it. (Contrast with a directly specified range which loses the cell permanently.) No need for a checkbox.

    So it would simply see a change made, then change any directly specified references. But it would make no change to any NR specified, so when done editing the "applies to" list, the NR would still be there, complete and unaffected while any other specified cells might have gained or lost, as appropriate.

    At the moment, I almost never use CF because of this and two other obnoxious "qualities" it has. Fixing this would go a long way toward making it useful to me.

    But it is NOT because NR's somehow don't exist.

    Roy commented  · 

    "Bolted" is a good word. Bolted in place though, not really on.

    Here's some wonderful behavior that needs to go away. Name a Range, say I1:N10, place a condition on row 1, once in place, edit the "Applies to" to the name of the Range. It atakes it, but permanently converts the Named Range to its absolute coordinates.

    Not good. But... given that if I drag a row out to somewhere else, it's going to Balkanize the applied to range (drag out row 7's cells and drop them on the right cells in row 17 and it now applies to $I$1:$N$7,$I$8:$N$10,$I$17:$N$17... forgive me if I drop "$" here and there), I guess this is better than Balkanizing my separately Named Range. But it shouldn't happen. It should stay applied to the Named Range and, AT MOST, add the new location of the dragged cells.

    Sure, you MIGHT want those dragged cells to carry the format with them, though dragging has to be a fairly primitive thing nowadays, it's not 1992 anymore, BUT you cannot possibly want to lose the format in their original location unless you dragged the entire Applied to range. Sure, some unusual situations could exist, but... "unusual"...

    So why Balkanize the original Applied to range at all, and therefore, since you won't be doing that, why not allow the Named Range to stay instead of converting it? Because, clearly, they convert because they intend to Balkanize, and accept that in their very souls. (I know, but whatever passes for souls in them.)

    Worse, I drag the cells back where they came from. Does it evaluate and restore the simple range? No, it replaces the Balkanized range portion with a new Balkanized range portion. Sigh...

    OK, standard complaint stuff, I suppose.

    But say I Copy and Paste that set of cells. It does NOT carry the conditional format with it. But further BIZARRE behavior: if I Copy and Paste|Special and tell it to carry the format, it will. OK, that's not the bizarre part. The bizarre part is that now simple Copy and Paste DOES carry the format with it. Can't turn it off.

    I'll grant that last (can't turn it off) might not be intrinsic to Conditional Formatting. You get a brother behavior with copying outside material and pasting it into Excel depending on how you last had your delimiters set for Data Import. And similarly, that can't be undone except by closing and re-opening.

    But why does it turn on at all? I don't mind "sticky" behaviors when they are documented AND can be turned off when done with them. Avoids needing dedicated shortcut key combos or macros for simple but short-lived tasks. Still... bizarre.

    And really, why?

    I wonder how some of this plays out with deleting of rows and columns with attendant re-insertions, or unrelated insertions. I bet the order gets to be important.

    But the whole function is so cr*ppy that I literally never really make use of it. It's just too d*mn cr*ppy in particular and on the whole.

    So I feel a sense of loss and empathy, but mostly sympathy for those suffering trying to use it.

    Ugh... and that whole "mostly we do this" relative thing that it really doesn't apply according to strict rules, but rather slightly flexible ones...

    "Bolted on" also applies in that it's not truly formatting and have fun trying to read it and use it with VBA. It's laid on as if it's something your monitor is doing, not Excel. Which might be an interesting business idea: a program that intercepts Excel's (or any other's) feed to the monitor and allows you to interact with it when it's still data, before it's pixel hash. And that can save its work somewhere in the file, or with a brother file. I bet someone else would be happy to toss in an app that does real conditional formatting (and other things) with Excel material (now reduced to a "fluffer" role while the apps downstream of the Interceptor do your real work).

    Once the Interceptor existed that is. Very different programming one would think, but once it exists and is in place, the app community that uses its services could become very varied and quite rich.

    MS is clearly pushing us out of Excel except as a rough data store (for now) and into PowerApps (lots and lots of subscription money and over-purchasing of licenses along that pathway). They are slowly, for now, removing functionality that is not "on point" for this.

    So now? Maybe they will do something. After all, the Admin comment that literally promised nothing is only 3½ years old...

    Good luck guys. I voted and I'm sure ol' Steve there is waiting with baited breath to count that vote and get "We" to prioritize accordingly.

    I still won't be tainting my ten foot pole touching it.

    Roy commented  · 

    Absolutely with the Named Ranges.

    Two things to remove:

    Example setup: Range is given as A1:E10 (which it ALWAYS changes to $A$1:$E$10 and THAT needs to go away too). Test cell is the D column. D1 controls the formatting for all five row 1 cells, and so on down the table. I copy a cell from the D column, one from INSIDE the table, not AX348, and paste it on D1 and now the range covered is modified to exclude cell D1. It is considered in the test, but not for the formatting. That is just... poorly conceived. And monstrously ruinous. In any case, a cell should be able to take any pasting and while its "real" formatting would take on the copied cell's "real" formatting, but all that has nothing to do with the conditional formatting engine's work which is applied afterwards, like a layer. It never "belongs" to the cell and so should be utterly unaffected and should make no changes in its behavior. Certainly it should not delete the cell from the range affected!

    Second thing and some related guff associated is implicit intersection with a relative position touch in the operation of the formatting. If I say, with the selected cell being D1, cell D1 is to be tested (say... =D1<>""), no $ characters, and put material in the other four cells, they all take the formatting. If I then edit the rule to be the range I've been using, it instantly changes to be NOT "if cell D1 is such and such" but rather that the third cell from the starting cell is to be tested for the starting cell (D1 controls A1, E1 controls B1, F1 controls C1, etc. Change the condition to be "D$1" A1 and B1 take the formatting but not the rest (in row 1), and B1 is back to looking at E1, and so on. There's no question of what the active cell is after the rule is set up, pick one outside the range and it's the same, so it is looking at the EXPLICIT thing you typed and taking it relatively (this is more complicated and literal implicit intersection also takes place). You type it literally as something and it is still "interpreted." there doesn't seem to be much a priori reason to be this way. After people all get used of course, maybe... but it sure makes it obnoxious...

    Roy supported this idea  · 
  7. 249 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for the suggestion Levi! We’ll be taking a look at this along with some other asks around conditional formatting. It’s a big help to see the things with the most votes, particularly within areas like formatting. So please keep the votes coming for things you want us to do sooner!

    Thanks,
    John [MS XL]

    Roy commented  · 

    Oh, and an Add-In could create those NR's one specifies if they do not already exist. Further ease our pain.

    Roy commented  · 

    I don't use VBA much, and when I do I'm more like a "hunt and peck" typist than a "touch" typist, so to speak. So I don't know the tools it has for addressing CF. I DO know that CF is not attached to cells in the way normal formatting is, i.e.: the tools for formatting and working with formatting don't even reveal its presence, apparently.

    However, the places that say that last part seem to indicate it CAN be addressed, so perhaps there is a reasonable minimum of tools. If so, it seems an instruction to define a range to apply the CF to would be near the top of the "these be basic" list.

    Assuming so, one could create NR's for all the "applies to" boxes that could change, or simply all that are in one's CF (I know, that'd be a bear for some folks' CF-ing, but even still, this would greatly simplify their making sure the ranges stay right), and write a macro that changes the "applies to" for all rules by applying the appropriate NR to each.

    If a standalone macro, one could run it any time, especially right after a change one made that one feared would change things. It could be called from an OnOpen macro to make sure they are set right upon opening the file.

    So whatever happens, running it would simply reset them all to their NR's: I know Excel will instantly convert them, but who would care at that point? They'd be right, either way. Oh, "drag and drop" orphans would be lost, but again, who among us cares? We are not the people doing that.

    Someone with the skills and time could write an add-in that might list the rules interactively, let you define the NR's to use and where, and provide the macro to update them whenever along with an option for OnOpen.

    And someone at Excel could do that too, offered along with distributions the way the Analysis ToolPak is (Why would anyone ever not want that loaded? Why is it an option to load rather than to unload when first installing?).

    Roy supported this idea  · 
  8. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  9. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    THIS function would be the engine forcing the wrap function to run again and again: so NO NEW work would need done on the wrap functions. For all Excel would know, nothing unusual happened.

    So FIND() runs to an error on argument 1 in the list, then i sheld open to run again on argument 2, and so on, only being allowed to close when the first success is found, or all items failed and the error is to be returned. NO new programming for FIND() though, as the LIST() function does all the necessary re-running before returning control to FIND().

    Roy commented  · 

    Jeez, and typos too. I blame this entry box.

    Roy commented  · 

    Oops... if they do this one, the very first error entering it is in the above... dropped a ")"...

    Roy shared this idea  · 
  10. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    If you mean how do you make its color red in the formula editing bar, you can't. Excel completely controls what happens there and gives us nothing.

    If you mean how to make its color red if it is the result of the formula, and is teh value currently displayed in the cell, you just need to change the cell's number format a "custom format" (absolute last choice in the list of kinds of number formatting available, then edit it as so:

    #,##0.00;[Red]-#,##0.00

    (Where I put the "#,##0.00" strings you would put whatever your chosen format is. The magic is done after the semicolon — ; — when you put in the "[Red]" part.)

    Excel gives us four basic formatting divisions for numbers: the first is positive numbers, the second is negative numbers, the third is zero, and the fourth is text and you tell Excel a section is complete using the semicolon. So:

    positive;negative;zero;text

    However, you can actually use them more particularly to your taste, that is just the built-in idea. For example, the first part is usually called the part for positive numbers but it is really a comparison: [>0]. Excel is just trying to be helpful: you can put any other comparison there instead but have to spell it out, so to speak. So your first part could be [<100] followed by a format, and your second could be [>=100] and so on (you might have the first use the color Blue and the second use Green: positive values would then be either Blue (modest positive values) or Green (high positive values) achieving the effect of conditional formatting without using the nasty, messed up beast they call conditional formatting) and then use the third area for negative number formatting and so on.

    All that can easily be found on the internet. Just touching on it lightly to give you the incentive to look it up. It is a very handy thing, especially for the purpose you are asking about.

    Also, not often mentioned are two other things on the subject. If you use the TEXT() function, you can include this kind of idea in the format specified. The second, I guess I won't really go into as it's a bit complicated, but there is a really nice feature for international spreadsheet use.

  11. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Actually, never noticed the example file before.

    @Narendra: No need to "--" the LEFT() function as its result from this data is a true number if the data is a true number (and the way it is crafted, much of the point is to allow it to be exactly that), or, while returned as text Excel will still evaluate it as a number in any formula relying upon it.

    Also, whether text or number, the way things are formed for the example, the comparisons work as text OR numbers.

    Same idea for the YEAR() formula you show. No need for the "--" trick.

    That said, one WOULD need to know whether the result would be returned as number or text for setting up the comparison: if number, use IF() to compare to a number (say, 2020), if text, it has to be compared to a string (say, "2020"). So the need for the "--" trick is to simplify the comparison rather than using something like an OR() to test both ways at once, or even testing the kind of value (ISTEXT(), etc. to pick the comparison, to show how ridiculously complicated one could get. The "--" neatly handles all that with great simplicity and works on the returned data whether it is a number or text.

    @Alex T.: That comparison being done with all the IF()'s (column M) that need added to every year... you can simplify it amazingly using a VLOOKUP() on a table with the "year break" values in column 1 and the year to return in column 2. Use "TRUE" for the last argument and set up the table accordingly. VLOOKUP then performs all the comparisons so you can drop out all the IF()'s.

    Roy commented  · 

    Or use string functions if the data is well-formed, or uniquely so for the string of choice.

    But we do it SO often... it should be a feature, not a series of workarounds and data conditioning.

    How will they ever do computing the way Sci-Fi does, just talking to computers, if everything has to be spelled out, debugged, tested, then bug-fixed every use and all data requests have to imagine every way data could be adulterated?

    Roy supported this idea  · 
  12. 4 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Just press (and release) "Alt" by itself and those go away wiuthout mouse activity, etc.

    Chances are you are using the shortcut and not releasing Alt in time causing it to "happen" again, displaying the available shortcuts which are in the displayed ribbon region.

    You can press Alt anytime, once, by itself, to see the same thing, and press and release it once again to make them go away.

  13. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Just press (and release) "Alt" by itself and those go away wiuthout mouse activity, etc.

    Chances are you are using the shortcut and not releasing Alt in time causing it to "happen" again, displaying the available shortcuts which are in the displayed ribbon region.

    You can press Alt anytime, once, by itself, to see the same thing, and press and release it once again to make them go away.

  14. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    No, not in "modern" Excel, but the press and initial blogging surrounding these upcoming spill functions claimed implicit intersection was to be removed as part of a shift from CSE to spill, the implication being of a deprecation of the existing functionalities when they, presumably, interfere with the new spill functions.

    I am not saying it is gone at the moment.

    His problem in the file he presents is due to the current, continued existence of implicit intersection. So I am hardly saying it is removed currently.

    And NO, neither of the things you say about his formula are so. And "@" won't even remain in the formula, it is removed when pressing Enter.

    Roy commented  · 

    Excel currently uses, and has for a VERY long time, something it calls "implicit intersection."

    If you had the data on the result page, in cells A4:B9, it might be more obvious to you what is happening. Pretend it is in those cells. Then:

    Cell F6's formula EXPLICITLY tells Excel to look in the table A4:B9, and starting at the upper left-hand corner (A4), go to the 0th row, then over to the 2nd column, and report what it finds. The fact that it cannot go to a "0th" row (it COULD go to a 1st row) is what turns on the idea of implicit intersection.

    (If, for instance, you did use a "1" in place of the "0" for the first lookup, it would give the desired answer of "Marks" and if you adjusted that to be dynamic (say, used " ROW()-5 " instead of a typed number, it would march down your table of data row by row). But you have to do some (usually minor) tricks like " ROW()-5 " for that, sometimes, though not often, harder tricks. Using the "0" allows you the advantage of implicit intersection doing the work for you.)

    Once you tell Excel, as you have, to use implicit intersection (in this case, by rows, though one could easily have a situation in which it would be by column), it looks into the data table NOT beginning at the upper left corner, but at the same row as the row your formula is in.

    So your H6 formula does not look to A4 and then go some rows down, some columns over. It goes to row 6 in the data which is the 3rd row of the data, not the 1st row, and then goes to the 2nd ("2") column. That is how it returns "78", not "Marks". And the other rows do the same.

    First pitfall then, is that as soon as you reach row 10, the formula will not be able to find a row in the data that is on row 10... because your table only goes to row 9. So it begins returning errors. This happens too, if you use rows above the table's lowest row number. In fact, this might become very obvious to you if you copy all the formulas you have in column H and paste them in, say, F2. The F4 has "Marks" and so on, while there are errors in F2 and F3 above them, as well as in F10 through F21, below the right area.

    Implicit intersection is a terrific help, though it is very surprising the first couple times, and since it is subtle, one may not even realize it is happening the first few dozen times it occurs. It ought to be taught reallllly early on: one can go mad trying to fix a perfect use of a function, nothing broken, nothing to fix. Just has to be used differently.

    Sadly, Excel has been telling us that with this new batch of array functions (their "spill" functions), they are removing implicit intersection too. Hopefully that is not so as quadrillions of formulas rely upon it!

    Lastly, for YOUR use here, if your INDEX() formulas must be offset from the data (data is rows 4-9, your output is rows 6-11), you will have to code in the row it should look at yourself... do not use the "0"... It will not be hard since your data is uncomplicated and well-behaved and you want to use it in a straightforward manner. Just use " ROW()-5 " and it will not use implicit intersection because you will be telling it to use exactly what you typed.

    Key element here: using "0" for the row number argument (or for the column argument) tells Excel to use implicit intersection. Using anything else for those parameters will work in the exact manner it tells Excel to work.

  15. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  16. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  17. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  18. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  19. 1,584 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    @Futski:

    Just tried that, out of some interest, eh? Edited the CSV file, saved it with NotePad, opened in Excel and it completely ignored them.

    Roy commented  · 

    @orgen:

    Actually, for that problem, the TEXT option is precisely what you DO need.

    Base changing functions for anything "past" Decimal require your input to be text. So being imported with that option set for the column will accomplish exactly what you need.

    Suggestion's point is still very, very desirable.

    Roy supported this idea  · 
  20. 42 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
← Previous 1 3 4 5 11 12

Feedback and Knowledge Base