Feedback by UserVoice

Roy

My feedback

  1. 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  · 
  2. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

  3. 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  · 
  4. 3,379 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    @Anonymous: yes, just click on the blue link under the "How can we improve..." header at the top of this page ( ← Excel for Windows (Desktop Application) ). When you get there, just above the first Suggeestion showing, you will see "Hot Ideas" "selected"... just pick the one next to it: "Top" and it will show them by number of votes.

    Desire for Python alongside VBA and the couple of other languages you can program macros in is the top one with almost twice this ones votes. This one is #2.

    Let me get something clear though, in what you said. I think you might be onto something major here. Science could probably be vastly improved if I understand the concept. Not to mention safety in crossing streets and more:

    You seem to be saying that past results from an action are reliable indicators of future results from those same actions. If that's true... science would be a lot easier and more accurate, this could be a whole new method from whatever they do now, that's actually catchy, someone should trademark it: "scientific method"... maybe with a "the" in front of it. And if crossing streets in traffic is dangerous one day, it seems to say it would be dangerous any day so maybe wait until traffic is cleared or stops! I like this.

    Sadly... it would also mean we shouldn't expect j*cksh*t out of this update... 'Cause none of the others ever led to anything, not even j*cksh*t. I don't like that!

    But you're dead on. Until they show the situation is somehow different, it will just seem like one more lie in a short litany of lies. (Not short because they only had so many lies in stock and had to ration them carefully, but rather because they couldn't even be bothered to lie 'cause that's too much effort.) I get the feeling someone aced his job interview by saying "One thing I can bring to the company is that User Voice site... you guys haven't used standard web techniques and I'm full of them (you know, like full of...). If I'm hired, I'll pretend to be several different project managers and fire off a batch of update comments that either say 'It's in planning stages" or 'F___ off, it ain't never happening' and that will get the sheople agitated and commenting, and basically reinvigorate their imprinting and hopes so they don't ditch your products. Basically, the standard 'owner of a website placing 25 product endorsements on the site to give the illusion the garbage he sells has performed wonderfully' fraud. But without the honesty of there at least being some basic garbage that would be sold 'cause we aren't really offering anything at all." "You're hired!"

    An error occurred while saving the comment
    Roy commented  · 

    @I got a headache: I don't personally remember such a sweep, I've got at least 4, maybe it was 5 of these things that the same Steve K person has updated this afternoon.

    What I'd like to think is that there's a new approach being taken and one person is in charge of all of these, and presumably more, with the company actually taking a plunge forward. ("New approach" in that all management approaches cycle, broadly, but would certainly be new here.)

    Lol, I know... but I actually do hope that's what it means. Bad knews on the conditional formatting front, but good news on the others. "Planning" in the past has meant "It'll be five years before we don't update this thread again" but maybe a new approach... Could just be three years before they don't update us again. Could be... dare I be one of Pavlov's dogs and say it?... could be, there's a hope for actual results in a meaningful time frame?

    I'm too old for five more years so I won't have that disappointment as I'll be dead. An extra incentive to choose hope over experience, for me at least.

    (One of the experiments Pavlov is supposed to have done was after conditioning the dogs, to apply the stimulus until it no longer worked. It's said that for some of them, that took over 10,000 times. With people, I think part way along that path, you get reclassed as "Sheeple" and it's a different experiment. Or should that be "Sheople"... No one ever liked the latter, but it makes more sense. Now I'm wandering, maundering, acting like an old folk... or a MS "planning" team...)

    An error occurred while saving the comment
    Roy commented  · 

    @M: It will certainly be this decade... if only in the sense of "tomorrow never comes" ('cause when it "does" it's then "today" and tomorrow is still about a day away). So absolutely "this decade" even if that is next decade or...

    Be hugely nice to see it happen though.

    An error occurred while saving the comment
    Roy commented  · 

    I don't doubt the good possibility of any of that.

    However, when someone is a grown adult and rattles off the company line when he has actual evidence to the contrary in front of him, just ignoring it, that's called "lying" and he is a liar.

    If he's 5yo and rattles off some horrid cr*p his parents said about some minority, that doesn't make him a racist or a liar. But 25? 35? 45? And still saying that same comment he repeated at five, when he ahs a ton of evidence to the contrary? Yeah, then he's a racist, and because the evidence he's ignoring shows him to be wrong, he's a liar.

    You either put integrity over the raise and nice life for you and your family, or you don't. I won't presume to make that choice for anyone other than myself, not even for children. But once you make the choice, you either still have integrity, or you do not. He does not.

    His choice, his selection of things people think about him. I hope he enjoyed the new pool, or perhaps it paid for a kid to go to college.

    An error occurred while saving the comment
    Roy commented  · 

    Denial. It ain't just a river in Burundi anymore.

    An error occurred while saving the comment
    Roy commented  · 

    Long as I'm here, every version of Excel I've used has had this issue. Every single one.

    An error occurred while saving the comment
    Roy commented  · 

    The animosity shown the developers is due to a couple things.

    1) They don't have to lie. They may need to in order to keep their jobs, but they don't have to. Since they choose to do so, they offend us. Insult us. Ask to be ill-considered.

    2) They are the only conceivable employees of MS who actually might be reading the comments. Billionaire Gate$ isn't reading them. Neither is anyone in between. Maybe they don't either. But they are the only conceivable people who are. So they are the only point through which one might try to make the point of desirability here, or if not that, the degree of feeling on the subject.

    As to "just use Google Sheets"... well, we don't all have that choice, now do we? Boss wants Excel, he buys (rents, whatever) it and that's that. I kinda tend toward the view that quitting my job, and starving and dying is more of a win for Mr. Gate$ than for me.

    Thanks for the re-sizing idea. For the 1 in 1,000 times that it applies to the particular way this rears its ugly head some given day, I'll do that. Done it before, will do it again. But that is a teensy bit of the problem, so I'm not realllllly gonna take down my vote forget this Suggestion ever existed.

    An error occurred while saving the comment
    Roy commented  · 

    @Mark Gibb

    It IS a Windows scrolling issue. The Mac OS offers a fine scrolling that Windows does not and the Excel people did not lift a finger to make their precious "who in the world wouldn't want it snapping cell to cell" line not mendacious. If they'd had to lift a finger to let it work with the OS feature, then Mac Excel would be snapping along today instead of having what we ask for here.

    As to being fanboys, I've never, in 33 years of using modern computers been in the presence of, seen, or touched a Mac product. Their very "we know best and want paid three times for it" approach offends the life out of me. I have seen iPhones and I did use an Apple 2e for three weeks in the summer of 1983, but that's the closest I've come to Apple products at all, much less the Mac. If I were President, I'd break that company (and Microsoft) utterly. The attacks would be from any angle any clever lad in my administration suggested and they'd be relentless until the pig scum were working out of garages again.

    I am in no way a fan boy. The fact about their OS is a fact about their OS. YOU can like it or not, you can deny or not, but you cannot change that fact.

    None of which excuses them. Again, if snapping cell to cell were as important and utterly logical as they say it is, endlessly, directly and through shills (MS MVP's), they'd've lifted a finger and made Mac Excel not to be able to scroll even with the operating system offering it.

    But they didn't. So clearly they are lying when they say again and again no one would want it any other way... lying knowing it not to be true in and of itself, not simply because three thousand people found THIS forum and said yeah, we do hate it. Lying, lying, lying.

    But while the "team" here contributes to and abets that and so is a pack of liars as well, the odds are they have been told internally to ignore us as the OS will either shamelessly copy the Mac OS again... oh, um, will write that in... someday or won't and so we either will, or won't, get it someday.

    But it won't ever be because the Excel "team" did it for us. The BEST we can hope for in that respect is that they actually are, while lying to us to maintain the corporate line, pushing for it to happen. Internally.

    Oh, and the pixel-type scrolling they have is in no way what we Windows folk get with mouse scrolling and such. Also, it is available throughout, not just with a decent mouse driver. And what is on the screen stays put when scrolled there, as you work with it, rather than scroll to that with a scroll bar or mouse, then snap right back to usual the moment you try to edit it or click anything in the window other than that scroll bar. Very different things.

    Excel doesn't even like to admit they figure things at the pixel level because the hue and cry to at least expose that to VBA would deafen folks. I wager that is largely because it wouldn't matter anyway: position something very carefully by pixel with VBA after hours of trial and error to make it perfect just to have it snap to some other position the second someone clicked anywhere in the working window... pretty pointless.

    But it's Windows that doesn't "pixel" — if I had a program that would benefit from it, I'd bite the bullet and spend the money coding it in. Because... MS can't say hey guys, don't spend the resources 'cause the Windows team is gonna do it someday. Others, working for MS, probably don't get the luxury of making that choice.

    But they don't have to lie to us furthering the company line. Sadly, they are perfectly willing to do so.

    An error occurred while saving the comment
    Roy commented  · 

    All of that is off-topic. And done.

    There is "effetive" woirkaround for almost anything Excel does. Not necessarily "totally effective" or "cost effective" or "doesn't leave garbage behind for people to forget to clean up" effective, but something can be done for almost everything. Hence how it has survived for over 35 years.

    But more to the point: Dude, just what do you suppose this entire site is about?

    Don't answer, I don't want to read it. Pretty much anyone else though, sees it as a place they can make useful suggestions for improvement and perhaps have someone at MS listen and take... effective... action.

    In keeping with that, there is "another side of the coin" on which we see positive, hopeful people get more than a little frustrated at MS's non- or mendacious-responses.

    We do all the kludges you might ever propose and more that you don't even know. But we hope to work for a better world in which these things no longer need addressed. And get frustrated. Still, we did (probably) get XLOOKUP out of it, so we have that going for us...

    An error occurred while saving the comment
    Roy commented  · 

    Yes, this and other workarounds are well-known. One can even go so far as to edit the file to add eight (8) lines at the top that clearly have text in the columns of interest. That's literlally all the records Excel examines to decide how to format a column, so then it could be clicked and opened and still work out, after deleting those faux records.

    But all of it involves extra work and work that a casual user (like... a BOSS) doesn't care to do, usually, or even understand how to do.

    It also won't work with the email attachment example... unless one saves it out first... in which case it isn't an email attachment anymore so...

    This isn't a trivial thing, nor is it a problem with Excel handling CSV files. Excel reads them just fine, it's just that when it then presents the data (separate task from opening it) that its presentation engine screws almost all of us. There is then, the broader issue of that presentation engine being fixed to no longer do that... and no longer strip leading 0's from numbers... and not assume that a string of digits longer than 15 characters is still a number and truncate it at 15 digits permanently losing the additional characters... ALL things it does BEFORE EVER ALLOWING you access to the material whether it be imported, typed in a cell, pasted, whatever.

    So that point, off-topic to this suggestion, is something needing addressed regardless of how there are half a dozen workarounds, one of which will usually apply.

    But worse, a simple code fix seems all that would be needed for Excel to simply handle CSV's like any other file being opened, TXT files for example. For it to simply open it as is. But one hopes they decide to address the issues mentioned with the broader fix of changing the pressentation engine to leave the "how do I proceed with this muck" up to the user. Still, just a little code to not open the CSV files, or really, any files probably, without at least offering the Import wizard as it would for a TXT file.

    An error occurred while saving the comment
    Roy commented  · 

    @Raymond Gota Toudji:

    Well, no.

    For CSV import, Excel immediately imports without accessing any Data Import wizards or functions.

    Which gives you no opportunity, EVER, for interaction of the sort you mention. Open a TXT file, sure, but open a CSV? No.

    The only way at present is to open it via a query. Then Excel lets you apply the query, or perform its steps, before presenting the material for use. That allows you to do all you suggest, and more, for that matter. The "more" is an awfully good reason to open files using queries. (These replaced the old Data Import Wizard quite a long while ago, and do a good deal more than it ever did.)

    However, in many situations, users are essentially clicking on the file to open it (clicking, drag-and-drop, a macro opens it, and more ways). For all those precise actions by users or macros, the file is simply being opened and Excel takes over whether you like it or not. That's tens of billions of macros needing rewrites alone, and some of these things simply can't be fixed at the user level, even when that includes the internal development level for a large company. MS could, of course, and that's what's being requested.

    (An example of such is opening one straight from an email. Just can't do it except Excel opens it directly and puts it through this mess. Oh... there's saving the file somewhere, then using a query, but then one has to clean up afterward too, as well as naming it in the meantime. Just to open it, that simple little thing, instead of having the quick wizard you mention to quickly and easily do it.)

    Ah well, could be the above is way more than anyone here cares to see. But it could be a few who have not already might go to this issue and lend their votes...

    An error occurred while saving the comment
    Roy commented  · 

    Quite so. And why would it be any different from any other human thing, especially a money-driven one?

    But a point to nitpick about: we speak as if there is a team of 38,293 coders fully supported by a massive team of development specialists for all aspects of the work and marketing in turn fully driven by humans in a hugely developed managerial bureaucracy of a scale one could proerly expect from a company with a >$1,000,000,000,000.00 market cap, the largest ever, even inflation and PPP adjusted. Even the Romanov's are only thought to have been worth a hair over $500,000,000,000.00 in today's money. Look where that got them: Lined up against a basement wall and shot to pieces, then dumped into a pit of a grave. "Dance with the one that brung you" is often a better recipe for success when there are no further vistas to move to that can make your current "sitch" seem like a little, backwoods pond.

    (Sorry, speaking to Excel-ers here... I guess that'd be either >1E+12 if typed as I just did, or October 16, 2739708032 A.D. if imported: either whether you like it or not...(yeah, nearing the year 3 billion)

    So it amazes and disappoints us that things users clearly would value have no interest to Bill-ionaire Gates and his minions. "Clearly" because if they DID casually survey their 800,000,000 estimated customers here and there and got a hundred thousand OMG NO! responses, they could easily mention that here and we could move forward accordingly. Not doing so suggests, AMONG other things it suggests, that that information is either not existent, for no clear reason, or not in favor of the status quo.

    It leaves us with the clear idea that very little e$$ort is being put into the product sionce it pours billions annually into their coffers with such a level of neglect and they cannot really see it improving through improving their product so they just don't.

    But have the br*ss b*lls and core mendacity to actually create this site and play "ring the doggie's bell and see if he is still salivating" game every year or two on some of these suggestions.

    We find it aggravating. AMONG other things we find it to be.

    An error occurred while saving the comment
    Roy commented  · 

    @Pascal: It's "official" alright, but personally, I think MS uses these User Voice forums (there are others, equally futile), hoping for some "golden nugget" idea someday, as well as a way to defuse certain aspects of user frustration (good luck, eh?), and finally, to give developers (not there's, but MVP's and training sites as well as Adin makers) ideas.

    But no, I don't personally think they take these Suggestions into account directly. Anything they do "here" is almost certainly (my opinion) something they were doing anyway.

    I must say though, I'd like to reiterate my own opinion that there is a place for JUMP! JUMP!™ scrolling, and I do find it useful once in a while, for various reasons. Once in a while. So while I want smooth scrolling AT ANY COST applicable to the situation, if they kept a way for the JUMP! JUMP!™ scrolling to be accessible relativly easily, that would be cool. Even if the smooth scrolling were the very accessible thing and the JUMP! JUMP!™ scrolling were the default, at least we'd still finally have smooth scrolling.

    (As always, just kidding with the ™ stuff... I don't want MS to read this comment and go "Oh my, what a sweet label to use, we'll have to keep that bug, er, feature, for 10 more years so we can milk, er, market that label...)

    An error occurred while saving the comment
    Roy commented  · 

    Ahh... at least we're all learning to droll better, commenting here.

    An error occurred while saving the comment
    Roy commented  · 

    Ahh... Mr. Gibb...

    Like the Brothers Gibb, your words are far more eternal than just one day!

    (Sadly, in this case.)

    An error occurred while saving the comment
    Roy commented  · 

    @Futski

    "Famous" not "infamous." Small difference, but real.

    Excel's scrolling with the scroll bars is provided by Windows, most certainly, as it has gained capability and yet Excel's shills still say why would anyone want it different than full cell jumping. Mouse button scrolling too. Why would Excel ever provide this and still maintain the arrow key jumping about situation? Surely their shills would bray about that capability and say something like "Yet the arrow key nightmare is still available to those who find it important as Excel wishes to serve all their market."

    It was simply a choice once upon a time when it didn't matter much. Made the wrong one for the long haul even if it was am azingly right for the moment.

    Other programs likely don't start from that kind of beginning choice. They don't, for example, force a word processing document to jump up a line at a time when... oh wait, they do... And graphics programs, perhaps, they deal in pixels which Excel refuses to do even though it does so itselff.

    In any case, all that doesn't matter on the Mac because the operating system definitively provides a smooth scrolling feature that a program would have to program to intentionally override to remove its capabilities from the user. And Excel, well, we all see they just don't do "extra" programming so they didn't override it and a Mac user can use that semi-version of Excel MS provides with the smooth scrolling we want.

    All small differences, but real.

    The day might be past when MS apes anything a given competitor, like the Mac OS, comes up with and then brays all over the press about how they have it too. They still ape things, and probably most things are like the old things, are and were talked about widely in the industry before happening so it maybe is more achieving things second, not aping, but they no longer seem to bray about it, perhaps hoping to lose the image (reality) of not being cuitting edge, and not being as good.

    So hopefully someday soon, Windows will get this capability, to some extent, and like magic, Excel will just not lift a lazy finger to override it.

    Tell you what would do it: if someone wrote a program that had it in it, very noticeably and usefully, and that program becaame important and a good money-maker, Windows would immediately put all the necessary features into the operating system making paying that company onerous and cutting their legs off, probably putting them out of business. It'd suck for them, but we could pretend it never happened and enjoy our smooth scrolling. It's just hard to imagine what kind of program that could be so... wait, maybe a spreadsheet program...

    An error occurred while saving the comment
    Roy commented  · 

    Yeah. No. Not right at all.

    I, for instance, have never been in the same room as a Mac. I'm certainly NOT advocating any kind of switch. That's absurd on its face.

    A thing is what it is.

    An error occurred while saving the comment
    Roy commented  · 

    It's not even a bug.

    It's just an aspect of the OS's behavior. The Mac version does not have the issue because Apple wrote smooth scrolling, pixel scrolling if you will, into the OS. Yay. For us.

    Even then, if Excel actually valued this "feature" to the extent some of their Shill Sites™ claim it's worth, they would have written new code to re-create their Drunken Sailor Scrolling™ overriding the precious Apple provided OS... oh... yeah... no one, even MS would dare do that and unleash the fanboys...

    So here we are with them claiming on their Shill Sites™ that no one could ever want, use, or value such a horrible concept as not moving full cell by full cell, yet they can't find it in themselves to preserve such a sacrosanct thing in the Mac version with a wee bit of code to overcome the OS feature. What's that called... ah, Mendacity, thy name is MS. Or if one prefers strict accuracy in one's language usage: Bald-Faced Liar, thy name is MS.

    (Yeah, I know I'm signed in and they can find me.)

    (Heck guys, I use Google too and we all know they've ALREADY found us. In fact, I figure it's just a publicity issue that Google bothers making people sign in to things rather than just saying "Go ahead and do stuff Jonathon. And c'mon man, just stop with the pretending Clark isn't Superboy.")

    An error occurred while saving the comment
    Roy commented  · 

    Sure, AND for wherever the screens sits, for it to REMAIN sitting there when one releases the scroll bar or stops with the mouse wheel. Rather than snapping to the cell again.

    An error occurred while saving the comment
    Roy commented  · 

    Because we're funny, the way we act all like maybe it could happen, then salivate as it's promised and talked up and never delivered, then rage, then get all hopeful for another moment.

    Then rinse and repeat.

    Their Christmas parties would not be as much fun without the UserVoice Gag Reel.

    So yeah, that's why. Fits available data to a "T"...

    An error occurred while saving the comment
    Roy commented  · 

    Well... there's supposed to be about 800,000,000 Excel users around the world, so they probably figure about 40,000,000 to 50,000,000 votes to be something that should be addressed.

    Judging by how much they seem to address other Suggestions, that seems plausible.

    An error occurred while saving the comment
    Roy commented  · 

    Lack of smooth scrolling also makes improvements/innovations like cells that can be larger then their row/column's settings possible.

    Consider being able to have a cell taller, or reaching farther down than its rowmates. This could be winked at by letting cell contents "spill" in the new terminology ('cause "run over the cell edge" doesn't sound pretty enough) downwards until hitting a cell border for a cell with contents, much like text contents can run past the right border presently. Fully in place, not just "buzzing the tower" implemented, one could maintain the religious basis of Excel with identically sized cells in each row/column, yet allow for improved display.

    Personally, I'd like the option to let those cells overlay anything I tell them too, hiding those contents with their own contents. Several things I could do with that.

    Anyway, nothing of the kind can ever be possible if those cells cannot be read because nearby cell-sizing causes portions of them to always be off-screen, up/down/right/left, some portion always off-screen.

    And cell's displayed in the current herky-jerky manner would do precisely that. So things of this kind, wished for elsewhere and not even thought of as related are in fact very related.

    Of course, either way, Distributed Indent still has a bug that lets you lay its contents overtop neighboring cells. Can't take that away apparently, no smooth-scrolling or not no smooth-scrolling. Wise choice MS, wise choice...

    An error occurred while saving the comment
    Roy commented  · 

    @Gilles:

    Thanks, I have been wondering how it is with it.

    Also, for sure, not Mac vs. PC. I've just noticed previously people jump to the conclusion I am a "Mac fanboy" when I mention how the Mac has this because of the Mac operating system with Excel simply needing to ride those coattails. So any Mac comments were meant to cut that off before it started. (I really have never even seen a Mac in 35 years of using desktop computers. Did use a II-e for several weeks in 1983 and my wife has one from her childhood in the basement somewhere. No Macs though.) Oh, and to attempt a little humor with it too.

    We should NEVER divide and conquer ourselves! Never make it easier for MS to point and say "sure, they gots them some votes and comments, but half are hating it." An honest opinion that does oppose, or that wants the dialogue to shift to a more "this could happen someday so we should shift, maybe, to thinking real implementation; that the period when voices 'off point' are not weakening the issue, but rather strengthening it by saying 'just how do we want this to be when presented and have to be lived with' " kind of path are fine, I believe (so I presented one), but divisive "You're a pig, and have relations with pigs" screeching is to be avoided no matter how mild it begins because: 1) It lets the folks at MS who don't want to lift a finger do just that and, 2) It always gets nasty with us killing and eating our own.

    Definitely not a Mac vs. PC thing, though we do all envy THIS feature in the Mac...

    An error occurred while saving the comment
    Roy commented  · 

    Bummer.

    I looked at my notes and it was Distributed Indent and formatting then of at least one character in the text in the cell. And checking following my notes, it turns out it still happens. I must've not tested right when I thought they fixed it.

    So someone changed something but no one fixed it. Or... maybe it has existed decades. I guess I don't know.

    So ignore my attempt at rebuttal about making changes at a fundamental level...

    Oh, if interested in something pretty bizarre:

    Not only do the cell contents extend to WAY past the cell border, that first one in the attached pic is just two words, but see how it breaks up? The "363" you see is the cell next to it. I increased the cell height so it can format centered up and down and not be written over by the extending contents. But see also the 1294 below it, that you can see is simply written overtop of. Well, you can see.

    An error occurred while saving the comment
    Roy commented  · 

    OK, I wasn't THAT curious about what goes on in the head of a Mac user! Yikes. Did western civilization fall at the end? That was dark and... well, I supposed they think "artfully twined" where "twisted beyond belief" pops into MY head...

    J/K. Lol, that was good Anonymous! Hope it's flawed somewhere in there though.

    They HAVE mucked about with the text engine side of things. There was a very strange bug in the Justify Distributed formatting that caused cell contents to be wildly wider than the cell if you also did at least one edit in the cell's text contents. That disappeared about... 3-4-5? monthly revisions ago. So someone DID something to cause it, then someone fixed it.

    Of course, that could be the burn that causes them to never touch the beast again, but I sincerely think they are actually in the process of making some pretty fundamental changes and trying to carry them back to fundamental parts of the program 'cause they are buggy or kludgy bolted on.

    And I think those efforts are tying up a fair amount of payroll so many other things are getting short shrift. And they are also being looked at as being the new bolted on stuff AFTER the fundmental change stuff is done.

    But I'm just making a wild guess I guess. I'm not a mind-reader, sadly. (Or luckily, 'cause I like Walmart being beastly to everyone who can't vote for lawmakers or who is a small polity compared to the other 300 million of us and thereby saving me 17¢ on stuff. I'd hate to not be able to go there and read some of the customers' minds accidentally...) But I really do think they have something like that going.

    Pardon me a second, gotta refill the Kool-Aid pitcher, getting thirsty.

    Here's hoping something good comes of this suggestion though.

    An error occurred while saving the comment
    Roy commented  · 

    I got a headache:

    We differ on the first sentence. I do. And for a very sound and useful reason. Though as I do say, it's really the idea of moving a set, reasonable amount per down arrow so the next material pops up EXACTLY where the previous material did kind of thing. Cell by cell if it has to be that to achieve it. I have been called an odd duck, but folks, my blood is red, I've never been in a room with a Mac, I'm not a communist, and I think "tldr" should have the "l" replaced with an "s" (for "stupid"). I am simply NOT even close to being the only person who has conflicting needs and wants both capabilities for the PRECISELY same usability concerns all of us who are asking for smooth scrolling have.

    Ask a Mac Excel user sometime if their having smooth scrolling (from the operating system, not Excel) means Excel can't scroll cell by cell, and if he has both, ask him if he'd like to give one up for no real reason other than to see it, like the world, burn.

    I can't. I don't know any Mac users. But let me know. I am curious. (I mean, as curious as anyone can really be about what goes on in the head of a Mac user. But a little, anyway.)

    I do NOT want to have to count 1-one thousand, 2-mississippi, then pause a pregnant moment, and release the down arrow to achieve that.

    But I do want scrolling in those cases to be untied from slavish the cell by cell movement conceptual basis.

    Anyway, your last point is, of course, the thing they are talking about in the old saying "some days the bear gets you, some days you are gotten by the bear." MS just don't care.

    An error occurred while saving the comment
    Roy commented  · 

    Since we are getting near to MS actually considering some work on this (looks like only 6-7 more years before they think about where it might fit into the "road map")…

    I DO want some scrolling by cells. Just not always and not in a horribly "children with big, thick kindergarten pencils" way.

    When normally scrolling TO MOVE ABOUT I am fine with scrolling up/down a cell at a time or over a column at a time. More exactly(!): I am fine scrolling approximately one default cell height up/down and one default column width right/left. NOT 7,208 points (I know, 409.5 or whatever, this is exaggeration for effect and clarity) and not not 43 screens of material right/left because one column is 32 inches wide.

    Moving about in the spreadsheet, I'm good with about 1½ screen inches right/left at a time and about ½ screen inches up/down at a time.

    However, when scrolling to work with material, two situations, more or less, arise for me. One is where all the rows, say, are identically sized and I down arrow once and the next row moves up to put itself EXACTLY where the last row was so my eyes note the change did occur and begin evaluating again.

    Both the first thing, moving about, and this, would be MADDENING to lose if MS replaces current scrolling with, say, pixel scrolling, like 3 pixels at a time. I'd lose functionality in a huge way and HATE it.

    So there's that, and it is real functionality no one really wants to lose except where he can have only one or the other and his private circumstances push him to the pixel scrolling. Otherwise, no one wants to lose either of the above.

    BUT there is one other side of this second thing and that is when not all the rows are the default (or some "regular" size) height, similarly for columns, some being MUCH wider than others. And this is the case a whole freaking lot.

    In those cases, I'd like pixel scrolling (of some fineness) but more especially, at least, to divorce the scrolling from cell by cell to something more like the ½ inch of screen height at a time kind of thing. As you know, so that I can read a cell from top to bottom, or left to right, in a stably changing environments like I would scrolling in Word, say. No part permanently refused to me just because it is more than a screen high (what's with the 409.5 pt thing anyway… I know the last can't happen, exaggeration for clarity, etc… but still, why can't one be pleasing even to Mad Max's Lord Humungous?… I know, he's dead, but again…)

    And reading in cell is worse: limited to one or three lines at a time with even more horrible jumping and the last 2-3 versions ruined some of the text handling in reading a cell that way so there's that, and at least half the time Alt-Enter has been pressed in a cell navigation is locked up and you can't even edit it… it's just worse all around than reading it from outside the cell.

    So… how to have both? I propose a refinement to the request/suggestion: a button to click (AND a Ctrl or Alt shortcut for those of us who hate the ribbon and having to hunt all over to find a thing… oh, silly me, that ended when they took out the menus… everything is a single click away above your work, always right there… well, not… ribbon = menus sheeple, it's the same thing, only it takes up more screen than the menus!)

    OK… back on point… a button in easy reach or a shortcut easily typed, and the scrolling mode would switch from cell by cell, column by column, to a smooth default of, say, ½ screen inches up/down, and 1½ screen inches right/left. Maybe a second button to get pixel scrolling.

    Couple other things on it. I'd like the column by column thing to just go away. Columns are sized oddly almost always, so it is far more definitive than row by row. Also, I know "screen inches" could be taken too literally, though Excel can read your monitor's resolution from Windows even when screens are moved about and work with the spirit of that. If I can't have the mix from above, I'd rather have the default this, default that movement I talk about. If they do it in Windows, like how it's done on the Mac, not in Excel, I'd still like the default amount of movement thing, or the current herky-jerky as the norm with a Ctrl/Alt shortcut to easily turn on and apply the Windows smooth scroll when needed. Shooting for the best of all things, not an either/or that sucks either way or the other.

    I wonder how it works when you set two windows on a file, and synchronize their scrolling and one comes on a row that is, say, 12 lines high while the controlling window has a normal-size row? 'Cause if they handle that by moving the secondary window view up a single line-ish amount, really maintaining the synchronization, then completely as the next row has to come up, and so on, then maybe that could be built on as the engine to replace the herky-jerky thing. Easier to do should mean sooner to do…

    An error occurred while saving the comment
    Roy commented  · 

    Indeed sir, you have the right of it!

    I have often noticed MS paying more attention to those who act grown-up and... oh...

    Never mind...

    (If only it were so though... that the secret to getting everything in life were to just act all grwon-up... "Act" that is, ain't bein' no sell-out! The man may have the power but he ain't got my soul!)

    I mean, you know, for anything north of, say, a couple million dollars, he could but I hope he never offers... Bend-over money isn't too far north of losing your soul money, and that might be a slippery slope (ba-RUMP-BUMP... had to capitalize RUMP there, right?), and I sure don't want to go that far north!

    I agree, we can't be eating each other. That's Divide and Conquer done by the dividees and conquerees. But some humor, hard to see how that hurts. The truth in it... well, the truth hurts sometimes Jobo, but MS has lots of tissues to mop up the tears.

    Say, wonder if MS likes us having a place to vent and he's now a [Deleted User] 'cause they thought he'd hurt that? Though that WOULD require paying attention to us enough to notice...

    An error occurred while saving the comment
    Roy commented  · 

    @Anonymous:

    That's uncalled for!

    Experience has shown me politicians are esssentially all ******** verminous scummy bottom feeders from whom I should have NO expectation of anything valuable or thoughtful of my needs.

    Microsoft on the other hand, has... oh...

    Never mind sir. Carry on.

    An error occurred while saving the comment
    Roy commented  · 

    Guys...

    Careful now... some lines should never be crossed...

    Are...

    Are we saying... User Voiice needs its own User Voice suggestion site?

    I don't know if I can be that Meta... I wasn't that Metal back in the 80's, not sure I can be that Meta now...

    An error occurred while saving the comment
    Roy commented  · 

    @Futski

    Indeed, Spot on. SPOT on.

    There are even MS moderated sites, though with volunteers (I understand) doing the authoritative answering, which literally say this is the only way anyone using a spreadsheet could actually want it — gosh, a spreadsheet is cell-centric by definition, who could ever want it to scroll up a partial cell instead of a whole one?

    Granted, that WOULD be aggravating, if one couldn't and got fooled thinking the whole cell is showing and it's not... but then, if your material forces a cell over what, 409 pts is it?, then you might easily think you're seeing the whole thing when you're not... but THAT'S all right, that is, that's just something different you understand... 'cause then that'd be an unreasonable maximum cell height limit and it just isn't!...

    Hmm... maybe hoping to always be able to display a full cell was how that limit got hard coded in to begin with. Yay.

    Needs fixed! 'Cause it ain't a good thing now.

    An error occurred while saving the comment
    Roy commented  · 

    @Sean Rosario

    It works on the Mac because the Mac OS offers a smooth scroll feature that Excel taps into.

    For whatever reason — whatever stupid reason — they can't or won't get the Windows OS people to produce the same feature so they can tap into it.

    So we suffer.

    You'd think developers would have been braying for a similar feature for decades so they wouldn't have to write it themselves for their programs. Apparently not, or it's been as ineffective as our hopes here have been.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.)

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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?

    An error occurred while saving the comment
    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...

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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!

    An error occurred while saving the comment
    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...

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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  · 
    An error occurred while saving the comment
    Roy commented  · 

    And this is apparently a feature available in the Mac version, so it's not like it is not doable.

    Oops... bad Roy... maybe that is a feature of the Mac and therefore available without much effort to the Mac version programmers. "Bad Roy"? Well, forgetting that such a nice feature is NOT, apparently, part of Windows...

    And therefore, this might not be so easy for the Windows version prgrammers, and they "why" of it could be a wee bit embarrassing to MS in general.

    But it would still be very handy.

  5. 109 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for submitting this. It’s a very good idea, and we’re considering ways in which this might be implemented. Please keep the votes coming, so we can see just how useful this would be, and properly prioritize this against other potential features work.

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

    We’ll send you updates on this idea

  7. 6 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  · 
  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. 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  · 
    An error occurred while saving the comment
    Roy commented  · 

    It's not VLOOKUP() that is confused. It works just fine with the commas in a sheet name.

    The confused part of Excel in your examples is their "Intellisense" help system. THAT is what is telling you that you are editing a different parameter of the function.

    STILL something it'd be nice if they fixed since the Intellisense help is very, very handy to have for a couple reasons, probably more than a couple, but at least two that matter a lot to me anyway.

  10. 4 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  · 
  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 supported this idea  · 
    An error occurred while saving the comment
    Roy commented  · 

    Interesting insight into Paste|Special|Transpose with formatting pasted.

    It must place and paste each cell in the range of cells copied as an individual item, rather than noting common elements. Even though when making the thick border, one almost certainly chose "Outside" rather than marking the four required lines individually.

    Makes one wonder then, why the pasting of non-contiguous material can't paste it in the same relative positions in the output rather than bunching it up together and actually refusing to do it when it can't figure the requirements out there.

  12. 517 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 Jessica. We’ve got a few other suggestions on the site that are similar with scientific notation, etc. We’ll take a look at this area, and pay special attention to any cases that get a lot of votes. So please keep the votes coming to help us do a good job at prioritizing asks like this!

    Cheers,
    John [MS XL]

    An error occurred while saving the comment
    Roy commented  · 

    @Anonymous: In the meantime while waiting for retirement, I mean for them to fix this, you CAN import CSV's with the old Import Wizard which did allow you to specify a column's type BEFORE import. Not nearly the same as it coming up just by clicking the file to open it, but "in the meantime"... I repeat a Comment I made for a different Suggestion:

    I've lucked across the Dead Sea Scrolls of opening a CSV in "the old manner" in the second answer in a Stack Exchange (Super User) answer:

    https://superuser.com/questions/1574847/how-to-import-external-csv-text-file-without-table-formatting

    —— Slightly modified, just the last step ——

    (This is different from the version of the old wizard that is presented in the Text to Columns feature in that it still works to do the actual importation, not just to break up already imported or existing data. Same tool, just allowed to act at the moment of importation instead of only after the damage is done.)

    Basically, go to Options and make sure that in the selections under the Data heading (at the bottom of them in the "Show legacy data import wizards" section and make sure the third one (at least, required for this... but I've always checked them all, eh?) is selected.

    Then, to use the old import wizard the way we all remember it working, go to Data in the Ribbon, choose the first choice on it: "Get Data", and choose "Legacy Wizards" from the dropdown menu.

    Here's a nasty foolishness on their part: Pick "From Text (Legacy)" from the dropdown new menu. The foolishness comes from how many people won't know that isn't just .TXT files, but several types of such including .CSV files (and, well, literally anything you think you'd like to try this on for that matter). Anyway, pick it and use the file browser that pops up to find your file and select it.

    NOW you finally have the old wizard, functioning precisely as we remember it, and BEFORE it opens the CSV file so BEFORE it destroys information forever. Do like you always did, select "Delimited" and move on, make sure "Comma" is a selected delimiter (really, make it the only one to avoid hassles), and then the magical step... click "Next" to get to the step in which you tell Excel how to import the various columns: as General (it will hammer you if it can find a way), Text, Date (for various layouts), and Skip. (If you are using a different delimiter, you can do a couple other things, but they are not germane to CSV importation.)

    Finally, click "Finish" and you're... well, almost home free.

    Here's where you'll probably diverge from the Super User answer which assumes you want a continuing connection and a Table. If you do, just choose options as you wish, or don't change anything, just click "OK" and you have a nicely imported Table from the CSV which keeps leading 0's and so on (if you marked that/those column/s to import as Text).

    If you JUST WANT A FREAKING BLOCK OF IMPORTED DATA, do one last thing: Uncheck the "Add this data to the Data Model" checkbox (just above the lower left corner's "Properties" button). Now your import will truly be like the old days, just a slick import of the CSV's data with you having had the ability to force the importation approach for any column you wished to do so for. Had date-like data but they weren't dates? You marked them Text and now you're a WINNER! Ruined data is your fault only, like it should be!

    An error occurred while saving the comment
    Roy commented  · 

    @Alex: don't say things like that!

    Now those team members, when they see this for the first time on the Christmas Party Whiner Roll will look at each other and as one pour some powdered testosterone on the backs of their hands and snort it with a flourish that would have done them proud in the '80's and whip their heads up as one and roar "RAWRRRRRRR Science b*tches kiss my *ss!!! Kowtow to your masters! All will bow to the mighty ones!"

    It just isn't good to be their entertainment and THEN provide extra entertainment!

    An error occurred while saving the comment
    Roy commented  · 

    The problem is that MS looks at these and says " 'November 27, 1908' votes... WTF??? Don't pay attention to fools like that. They wanna someday not be childlike and really vote so they can be counted, then we'll pay attention."

    Welcome to the "November 27, 1908" of us, will lassek.

    An error occurred while saving the comment
    Roy commented  · 

    They ARE paying attention to this forum: note the new text appearance in the comment creation box. (Not text handling, just the font is different.)

    Of course, it could just be an unexpected jacking by a silent software upgrade doing a "no by your leave" major change via a minor little thing to them, you know, I hear SOME renegade, thoughtless software companies do that...

    Unfortunately, updating this yet NOT updating even their comments on the subject/s, suggests a LOT of neglect on the "producing" end of the concept. "Oh, we'll collect a lot of input, so you don't go anywhere, but you know, we won't really do anything with it... you understand don't you?"

    The stunning result in Pavlov's work was the part where, once an animal was conditioned, how many thousands of times the stimulus would elicit the reaction even though no reward was EVER again provided. The rest of his work was interesting, sure, but that part was sttunning.

    Ding-ding, ding-a-ling, Master made a little comment 3½ years ago, don't go switching to Google...

    So, sadly, yes, other forms of pressure are likely needed and some might be more effective. I don't use social media myself, but most-a-y'all surely do, and those avenues might be helpful, if only to educate people about this forum and the idea of voting.

    Consider the 800,000,000 (estimated) users of Excel and the 280 (nope, not even one comma in that) people actually voting here... I'm sure MS looks at this as more of a suggestion source than any kind of urgent need, "gotta act and act now!" before the press gets wind of this, kind of forum.

    Even the Python suggestion with 5,000 votes and a survey conducted getting over 10,000 responses, all acknowledged in the Admin comment, only merits a "so we'll certainly think of this now and then" encouragement.

    I, for one, must encourage other avenues, even if they seem to be off-focus, because SOMETHING more is clearly needed.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    You can achieve your goal using Conditional Formatting.

    Set the NORMAL formatting for the column to the format you wish for the numbers — this will apply any time the condition is not met. Go into CF. For the formula, you will want to check the contents of a cell one below the current cell that Excel is checking on. So you want to use relative referencing ("D2" rather than "$D$2") AND you want to select a cell in the column beffore entering the CF function. So say that the column you want to use is column F, select a cell in it (F1 would be easiest, but it doesn't matter), and then enter the CF through the Ribbon. Tell it you want a new rule and pick to use a formula to control the formatting. Say you selected cell F324 earlier. You will want to use cell F325 (the one below the selected one) in your formula:

    =F325=""

    Enter a formula like the above but using the cell address for the cell one below the one you selected (entering this is why selecting F1 is easier).

    After setting the "rule" (test to make), choose your formatting. This is where you would pick the currency format you desire.

    The test formula will check that the cell below the one it will format if things test out is empty. If it is, the cell being tested for is presumably the last cell in the column with a value. If the condition is true, it will apply the currency format and it will leave the cell alone if it is not.

    This simple test ("rule") would also format ALL the rest of the cells you let it apply to BELOW the one you want currency formatting in. But they are presumably all empty so no one would notice as there is no displayed value showing so no one would see any number formatting either. That would not be the case if the formatting were different, say making the background red, because that would be visible even without a value in a cell. So this is "good enough" to do the trick, but not elegant.

    If you would like to be closer to "elegant", you could use this formula:

    =AND( F325="", F323<>"" )

    which tests whether the cell above has content that would be displayed and only formats the cell if that is the case. Not that elegant matters so much, really, but in this case, if you wanted to do extra someday, like add a color to the cell background, say, the formula to only do it to the one cell would already be in place so you'd just have to edit the formatting to include the extra characteristics.

    Do note that this is NOT what you ask for in the Suggestion because it isn't really position based, rather it is using knowledge about how the material being worked with is constructed to simulate a position based result. Just offering a workaround that might help you until MS takes up your suggestion. 'Cause... they take a LONG time even on the ones they eventually tell us to pack sand on, much less the ones they do put into the program.

  14. 936 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for supporting this feature. We won’t be able to in the near future, but we understand that it would be a nice improvement. Even though it seems like a straightforward idea, it is quite complex since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.

    We’ll continue tracking votes for this suggestion.

    An error occurred while saving the comment
    Roy commented  · 

    Three situations:

    1) A Named Range which would never have rows/columns inserted or deleted.
    2) One which would, but both of these are "defined" like A1:R300.
    3) A "dynamic" Named Range in which a formula is used to define the (changing... the very point of such a thing) range.

    First, since ALL CF is applied and never affects ANYTHING else (without VBA and even there, you don't do it the usual way), not only should CF be calculated and applied AFTER all the rest of a calculation is performed and Excel is ready to paint the screen, but at that point, all things are known, including any changes in Named Ranges and so CF would see nothing dynamic about them.

    Not even to mention that some already calculated and finalized Named Range that comes up as, say, C34:AN3988, HAS to be easier to apply the rule to, knowing that range, than something like:

    C34:AN325,C1328:AN1533,C1536:AN1537,C542:AN543,C2934:AN3421... and so on for 800 little Balkanized mini-ranges that actually overlap and are not in any particular order

    Second, let's assume they actually fit the CF calculating in the middle somewhere... no matter how implausible that seems, and no matter how unworkable that seems. Maybe something in the way of each page is calculated and CF applied to it, then the next page, going back and redoing it all if pages, gosh, affect each other. Might be that... be pretty stupid, but my memory is hazy and CF might have come in when spreadsheets were still single sheet things and perhaps they kept that, just cobbled multiple pages together and lived with it. Still, a complete, already known range has to be easier to use than those disjointed, overlapping monsters.

    But they are so proud of how intelligent a thing the modern calculation thinking process is and how it learns the spreadsheet and self-optimizes as it goes. Not much to brag about if that wonder-process fits CF in before the end, but MS would not be the first braggart to brag on something that added up to "not much to brag about."

    So either it does fit in at the end of things so the message today is mendacity at best and bald-faced lying at worst, or their calculation techniques are both ill-advised, and pathetic.

    It seems more likely to me that it actually has to do with CF itself, and CF vs. ranges, dynamic (including SPILL) and static in formulas. The latter can't really be it as that will be an issue regardless of any work in this arena. So with CF itself. It still makes no sense as it still has to be easier to work with a nice Named Range than those Balkanized ranges one gets over time. Not to mention relative and mixed references.

    So it pretty much sounds like lies.

    Unless. Unless they actually intend to ditch CF altogether and escape the whole mess, along with other baggage CF's oddnesses carry into the matter. If close, no need to fob people off, but if the project got delayed, you gotta do something. Could mean they are replacing it under the hood or rolling CF into mainstream formatting, and want to keep people onboard... that's the happy ending. Could mean CF will just disappear.

    So perhaps good news. Or perhaps bad. But surely liesl

    An error occurred while saving the comment
    Roy commented  · 

    @Anonymous: Mighty good point!

    I'd hate to risk they'd stop there, but it would be a super big thing even if they did.

    An error occurred while saving the comment
    Roy commented  · 

    Conditional formatting has no follow-on in calculation: Nothing depends upon it. It ought to be applied last since it does not feed into anything else. (It might itself depend upon plenty of cells, but that's a very different thing and not germane here.)

    Being applied last, it would know what the value for any Named Range is, dynamic or not, since affecting nothing other than what we see on the screen it could not influence the value of anything.

    There is NO function or menu item that looks to a CF and then acts to affect the traditional elements of a spreadsheet: it is a "dead end" in this respect. Since it should be generated last in a calculation, when all values are known and only display needs performed, it isn't actually "quite complex" in any way, shape, or form.

    If we are to be told that it fits into the calculation parade in some other manner, well, that's just stupid. Ought to be fixed itself. But I am sure we are NOT to be told something like that because MS never bothers with actually explaining a "get bent" message to the users.

    Stupidity isn't attractive. Or useful.

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

    No, no confustion. You stated:

    "The problem with using VBA or Macros is that the code is stripped out when the file is emailed."

    and that was it. Beginning and end of comment. No modifers, no "by organizational policy." Nothing. Just that and only that. The statement, as stated, says it is done by the programs in question, not by someone's choice.

    I'm not sh*tt*ng on you for it, just that's what you said and I pointed out it is NOT correct. Leave a thing like that uncorrected and not only will it keep coming up as a set fact (even though it is not), confusing this issue, but some people will act, or rather not act, thinking it is set fact.

    No confusion at all.

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

    Sure, sure. Lots of folks cannot use macros by organizational choice. And by theory as well when boss types want to have some ability to delve the spreadsheet to assure themselves it is "correct" and can be trusted. Since few seem to have any VBA skills, they cannot evaluate a macro for this purpose and so the spreadsheet has to be accepted as holy writ.

    (You presented it as a feature of Excel and I was addressing the fact that that wasn't so.)

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

    That would be an organizational thing that you might be able to overcome. Or possibly some option set in Outlook or Excel, but I've not heard of one.

    It is certainly NOT something that HAS to happen. I can email macro-containing spreadsheets without them being stripped and I am doing nothing special. And that's with working for an organization that definitely makes some perverse "higher level" choices for everyone.

    Seriously, a macro to reset the conditional formatting has to do so very, very little actual work that one might just set it to run automatically on every little action taken and one ought never really notice it, ever. Might want to modify that a wee bit to not risk interaction with other macros, but...

    (Re-setting 250 rules might task it. But setting them on a range of 300,000 cells is the same work as a range of 4 cells. Though I suppose Excel would re-calculate to make sure it's displaying according to them so... that could be quite a burden. But one could test for the "noticeable point" before dialing it back to On Open/On Save/Button to let the user choose when/etc.)

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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.

    An error occurred while saving the comment
    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  · 
  15. 743 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Good news. We are in the planning stages for improving the Unhide Sheet dialog box so that you can select multiple sheets and unhide them at the same time.

    Thanks for supporting this suggestion.
    Steve K [Excel]

    An error occurred while saving the comment
    Roy commented  · 

    Awesome. Nice to see something that has clearly been needed acted upon.

    Well, 'planned upon' anyway.

    An error occurred while saving the comment
    Roy commented  · 

    It's so tantalizingly close too. All the architecture is there, or literally almost all. A selection box is available with the hidden sheets listed... IF ONLY we could select more than one at a time... it seems like such simple code to add.

    A teeny, tiny piece of code... and standard stuff too, where the current code flouts usual practice of:

    1. Double-click on a single one to instantly act on it ignoring all others, or
    2. Ctrl-click on individual items to make out a selection list one by one, inside the box, or
    3. Shift-click to select several contiguous choices

    so someone had to not only decide to do it against the norm, but to do the work of carefully coding to do so instead of copying and pasting standard selection code. (Sigh...)

    Roy supported this idea  · 
  16. 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  · 
  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. 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  · 
  19. 242 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    I don't wonder so much as I think I've got an inkling.

    A little while back I decided I'd never come up with a really compact splitting workaround using the ideas that came to mind so I thought I'd better internalize the FILTERXML() technique. I need to learn it to make the web scraping ability work nicely so that added its own impetus.

    One thing I'd not run into in examples until properly researching it was the ability to pick which element you'd like to split out, the eight word in a sentence for example. But in considering an example of that, I realized something odd about FILTERXML()'s parameter use.

    =FILTERXML(A3,"//outer/inner[5]")

    To pick the element to split out, one constructs the Xmlpath portion to include a [digit/s] addition to the normal simple path. The other place we see this kind of construction is in Tables, choosing a header, for instance. It's not identical, hence "inkling"... because the header label includes a preceding comma: ,[a] for example not just the [a] .

    That gives me to think (so... a "th-inkling"?) that since the phrasing similarity suggests that it is on purpose, perhaps they intend to adapt the FILTERXML() function to be a SPLIT() function someday after they resolve whatever problems they have extending the SPILL functionality to the Tables functionality.

    So, a new additon to the "For what it's worth" category.

    An error occurred while saving the comment
    Roy commented  · 

    The UDF in a Named Range thing, just create the thing, then when happy with it (all polished), copy the sheet to the new workbook template so the Named Range work is copied into it without error, then delete the sheet and everything new will include the function you created.

    And no macro status.

    They'll be even better once the LET() function is broadly available. Single name works, easier to maintain. Man... when I was a kid, those clicker toys only clicked, they didn't even usually add on an extra "toy" layer by painting them like ladybugs or whatever. Then in the '80's I saw some that did that, made them toy-ish even when not clicking them. Now LET() is coming! Life just gets better every day!

    An error occurred while saving the comment
    Roy commented  · 

    With text in column A and which element of it to return in G1:
     

    =IF(OR($G$1<1, $G$1>COUNT( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1) )),
    "Error in which element to select.",

    MID($A6,

    1 + IF($G$1=1,0,
    INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1-1)),

    -1 + INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1) - IF($G$1=1,0, INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1-1))

    ))
     

    As I have it for actual use, I put a large part of the four basic elements into a Named Range so it looks cleaner, but I will probably put it all in (you can do what is effectively a UDF inside a Named Range so I can pass it the parameter of the cell (G1 used here) that tells it what element to obtain.

    It is hardcoded for a "space" as the delimiter, but that can easily be upgraded to allow one to be selected. It will return anything between the spaces which is good and bad, but that's the general effect everyone faces: how do you get it to return precisely what's there, but also, gosh, expect it to know you wanted "pig" not "pig,"? However, it is set to drop the absolute last character of a string, expecting a sentence and therefore ending punctuation (I set this one up to operate on text, so... not as generalized as it could be, yet), and not desiring the ending punctuation to be included. Easily modified if that IS desired, or it's used on text for which that condition isn't the case.

    An error occurred while saving the comment
    Roy commented  · 

    The following can find a desired instance number in a delimted data cell:

    =MID(SplitSource,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement-1)+1,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)="$",1,0),""),SplitDesiredElement)-SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement-1)-1)

    It just looks brutal. Lots of repetitive stuff.

    One could add error checking to make sure the SplitDesiredElement value does not exceed the count of elements.

    Since the elements are simple and repeated a time or two, it lends itself to simplification of appearance via Named Ranges yet would still be easy to maintain.

    I suppose ways around the problem have existed for years. I just don't know them.

    It has two basic components, to find the n-th (desired) delimeter, then subtract 1 for the one preceding it, then to return what's in between. Could be altered to allow more than one segment to be returned. 1st, last, any particular one, a count, all can be achieved via COUNT() acting upon the element inside IFERROR().

    It would fail if a delimiter were the first character, and if there is more than one contiguous delimiter (like in "abc $$ def$gh" if the "$" were the delimiter).

    The internal array produced is of the positions of the delimiter so it can feed directly into anything working off position. A multi-character delimiter is possible if one uses LEN(SplitDelimiter) for the early "+1" and very end's "-1" as well as the length of return for the first and last MID()'s but not the middle one.

    Anyway, possibilities seem to exist in it. Someone usefully simplifying it would be handy. Until MS programs in a proper function!

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    None of that happens to me. At all.

    Version 2007 (Build 13029.20344 Click-to-Run)

← Previous 1 3 4 5 19 20

Feedback and Knowledge Base