Feedback by UserVoice

Roy

My feedback

  1. 515 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  · 
  2. 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.

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

    We’ll send you updates on this idea

    I’m happy to let you know that we are in the planning stages to make Excel for Windows scroll without jumping to the edge of the cell so you’ll be able to scroll smoothly. We don’t yet have a target date, but wanted to tell you about our plans.

    We appreciate all the votes and comments supporting this suggestion.

    Thanks – Steve K [Excel]

    An error occurred while saving the comment
    Roy commented  · 

    @(different)Anonymous: Was checking my memory on that Word point raised and tested it. When I delete the marker, it changes nothing in the way the text and formatting either before OR after.

    It DOES join the paragragh with the one following ('cause nothing is telling it to end the paragraph anymore) and pressing ENTER restores the separation and recreates the initial marker.

    Which differs from my memory in that I used to be able (did this a lot maybe twelve years ago to ten years ago in a different job) to reformat a paragraph by copying the one I liked and pasting over the exisiting one. (That actually has no obvious effect at all now. Guess they were right and I should've fought through the buttons/Ribbon to use Styles on every single one instead of just a simple copy and paste (over). But then, I haven't needed it since so...)

    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.

  4. 934 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  · 
  5. 740 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  · 
  6. 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  · 
  7. 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  · 
  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. 238 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  · 
  10. 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)

  11. 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't because Ctrl-I, R was never a command to do that in Excel. THAT was a command to Italicize the font, then type an "R".

    The shortcut you clearly desire was ALWAYS Alt-I, R (and its corresponding Alt-I, C). Which still works.

  12. 4 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  · 

    DATEVALUE() actually simply needs a text version of your date to work, for instance. hardcoded dates present no issue, just need double quoted in the formula. So DATEVALUE("11/1/19") instead of DATEVALUE(11/19/19).

    But then, date material from outside a cell can almost always just be referenced. If it is in cell A6, one just puts A6 in the formula and it's all good. No need to textify something just to return it to being a value.

    As I think on it, the way I read the Suggestion, and I say it that way because it seems it could mean a couple things... not too clearly written..., the way I read it sounds incredibly like the desire is for Excel to examine numerical inputs, as it does anyway whether you like that or not, and compare them to the pattern for dates, say, that a date in the default Windows setting the computer has and if it matches, consider it a date. Which Excel already does do, like it or not, it will whack your entry even if it is not meant to be a date unless you prepare for it.

    But a second thing in it seems to be wanting to be able to formulaically specify a format for the dates. Mind you, that's almost available with TEXT() with the difference being that TEXT() will format it, but turns it into text rather than leaving it a real date. Mainly, one supposes, because that's actually its purpose, the turning to text, not the formatting of it, which is just a furtherance of the use, not a point, precisely of it. Well, that's circular really, as one does it when one wants a particular formatting, so...

    So the real issue would seem to be desiring a particular family of formulaically applying a format. Why stop with this little corner? Why not ask for a general formatting via formula ability? And not just number formats either. Why not be able to specify everything that can be adjusted in formatting? Color of text, background, fill, borders, heights and widths, font, number formatting, whatever.

    Think big, and the pittance once does get is bigger too.

    An error occurred while saving the comment
    Roy commented  · 

    (Sigh...) Would be awesome.

    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  · 

    The examples in your spreadsheet all have one thing in common:

    Even though their cells have date formats, the actual data in the cells is simply text. So Excel looks at it and sees the last character/s is/are numerals and figures it has a way to increment your Fill, namely, to increment the vlaue of the numerical TEXT at the end of the string.

    If you had an actual date (a numerical value that the chosen date formatting DISPLAYS as text similar to your strings, if desired), it would increment properly using Fill.

    Not a bug. It's just there's a difference between entering text and entering dates. Regardless of the formatting. Format a as currency and type text into the cell and the same thing happens. Of course, there it mightn't be as obvious since almost the whole string would be numerical while your examples are about half non-numerical, half numerical.

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

    We’ll send you updates on this idea

    Great suggestion – thanks again for taking the time to put it on this site and for the thoughtful followup comments. This is pretty related to some other work we’ve got going and already has a fair number of votes, so we’ll work on getting plans in place now and hope to get started on this soon.

    Thanks,
    John [MS XL]

    An error occurred while saving the comment
    Roy commented  · 

    Oh yeah, no doubt. Two things would be nice:

    1) A format one could set on a cell such that, if the cell were blank, either literally (even if used in the past it's now blank) or formulaicly (it's result is an empty cell, i.e., it displays nothing), then the cell would present to the rest of Excel as a true NULL, the same as a never-used cell presently does.

    2) A function one could wrap arounda cell address or subformula elements that, if the data at that cell address, or the result of the subformula, were a blank/empty value, it would return a true NULL as its result either for the final result or as input into whatever other formula elements wrap it.

    The first is what I figure would be the most useful for many things I do, especially for sorting. But the second would have a ton of uses too.

    Unfortunately, I think they have to change something pretty low level in addition to anything else that seems logically likely. Consider: what process exists that leaves once used, but no longer used, cells marked somehow as having different contents (i.e.: having some contents even if actually empty vs. simply being actually empty) than a cell that has never been used? And variations in between, of course. And think of how many functions maybe rely on this difference for what they do. In fact, the reason for persisting this unfortunate circumstance might even be that some important functions would fail, as currently implemented, if the difference went away. Well, maybe.

    But the two things above would go a long way toward solving every problem I ever had involving this sad "feature."

    Just realized, subject to actual trial, but pretty likely, that SORT() on a cell address or a subresult in a formula seems likely to not be useful if the results could sometimes be non-empty and plural (an array of subresults) as it might actually sort them which might be undesirable in some circumstances, probably most circumstances. Still, even with SPILL functionality that might not be too often, unless they do get around to enabling lots of presently "unable to SPILL" functions.

    An error occurred while saving the comment
    Roy commented  · 

    Say folks, not a socially connected fellow here so I have to ask: what's the present "type" for a five year anniversary? Isn't it "silverware" now? Like, knives would be silverware... The "Excel Team [MSFT] (Product Owner, Office.com)" comment telling us this is "PLANNED" (you know, like we "plan" to solve the Year9999 problem before 9999 AD, but... not tomorrow...) is coming up on its five year anniversary as the last lie we've been told. We don't want to miss that.

    Not complaining... five years of silence after a lie is much better than occasional further mendacity aimed at stringing us along. MS's idea of "nothing"... which is still somehow different from a true NULL kind of nothing in which they wouldn't have told the initial lie either.

    Yeah, looked it up. Silverware.

    An error occurred while saving the comment
    Roy commented  · 

    Nope, sadly, it does not.

    An error occurred while saving the comment
    Roy commented  · 

    A new thought on the value of a true null available to formulas...

    That ignorant SPILL error. One doesn't always want a 12,000 element result to be fully displayed... Criminy.

    One might just want the single "head of the beast" displayed so people know where it is, or perhaps 10 or 20 cells of it. One might place a blocking formula to end that display.

    The idea being that the formula would work as intended, display what it can, put a little green triangle in the corner of the cell, and let us move on. I just created a SPILL formula that could fill 12,000 cells, or 37... do you think I didn't notice it didn't? Geez...

    So... two types of blocking cells to consider:

    1) Cells that, unfortunately, are just in the way. One COULD place an activator cell by the SPILL formula, like "Type "Show" to see full list." and if a person types "Show" (or, say, anything at all, just not an empty cell there anymore), then all the identified blocking cells could have been noted and fixed with an IF() wrapped around them that gives this NULL result and the SPILL formula would no longer be blocked, instead would fill there and right on past.

    One can dress up cells around that which would distract with the hoary IF() with a "" result, but NOT those in the way of a SPILL formula. But a TRUE NULL could.

    2) The other kind could be the opposite, not legacy or just awkward material but rather intentionally placed to limit the SPILL. Say I want the first 14 of those 12,000 cells to show. Or 33, Or however many someone types in a nearby cell for the purpose. Perhaps I place formulas below (or to the right, whatever is appropriate) which watch that cell with an IF() that results in perhaps a blank (just the formula with the "" result), thereby stopping the SPILL SPEW™, but that can shift if an appropriate entry is made in the activator (um, deactivator I guess) cell, shift to that true NULL so now the results can SPILL onward.

    Various ways to implement that, from a few hundred cells under it with the formula, and X-number of them NULL out letting the display SPILL into them if "X" (value X) is typed in the cell, to slick ones. Even brutal approaches like 12,000 formulas and you go down to the row you want things to end at and type an "X" one column over. Slick or brutal though, the point is, when the condition is met, a given cell would be treated as a true NULL by Excel so suddenly the number of the results that are displayed would increase (or decrease back when cleared).

    After all, by the way, MS stresses that the only place with an actual formula is THE cell the formula was entered in. The rest is just display. Hmm... even Conditional Formatting could have helped here if they'd only asked me first. But no, they had to pretend I didn't exist and just bull their way through...

    But yeah, a true NULL could allow formulas that give a benefit by acting for all that Excel's engine knows, as if they didn't exist in a given cell. And that'd be handy with these SPILL functions.

    An error occurred while saving the comment
    Roy commented  · 

    A null value would be different than the current blank.

    "Blank" being what you get if you, for example:

    1) Enter ="" in A1, the copy and paste values back on it, or to some different cell
    2) Have a formula in A1 that returns "" as its result.
    3) Have some value in A1, then select it and hit Delete or Clear Contents

    and some other things.

    Anyway, Excel is currently able to distinguish between a cell that has a history but is currently blank in the English language sense of blank: literally nothing entered in the cell, and those cells which have never, ever, had a value in them. So it should currently have no issues distinguishing, in program, between types of "blank" or "empty" it encounters.

    Alright, you have A1 which has never had an entry, ever. Presently, "=A1" would return a 0 in whatever cell or formula it is in. "=ISBLANK(A1)" would return TRUE. Put "=F10" into A1 and now A1 would return 0, =A1 would return 0, but ISBLANK(A1) would return FALSE (after all, there's a formula there now). References to any of these cells would produce currently expectable results.

    Now delete the entry in A1 and you're back to the original. However, Excel can still see that though it is blank, it has been used and will act accordingly, saving information for it and doing things like sorting it into a different place than it would if it had never been used. (Ever delete the last 10,000 rows of a data set and find Ctrl-End still sends you to the last cell in them? That is Excel still realizing they've been used. Just blank, not empty.)

    Now close all that and open a new spreadsheet, then put the formula I mentioned in B1 (assuming a NULL() function now exists). Excel would return a truly empty cell result in B1, not a zero. That result would be precisely what is thinks about A1 itself since we have never entered anything there and it's really empty. So =A1=B1 would return TRUE as the value of the never used A1 and the NULL() function's result of a true null is the very same thing. If you enter =B1 in D1, D1's result would be a true null (as in "empty, never used") as well. So "empty" from your choice of that or zero.

    Notice the effect of the formula was to pass onward a null value for B1 even though it has a formula in it. Of course, there are functions in Excel, like FORMULATEXT(), that need to return the underlying material, but they already are programmed to ignore the result and evaluate the underlying material. A Table would also have to look at it this way, else one might have odd effects if a formula in the first cell of a row looked utterly unused to the Table. Or maybe not. But whacky things like that could crop up with issues. Don't mind a wait while things like that are gotten right. DO mind a wait if it's just "you're only 671 people out of our 800,000,000 person user base so..."

    An error occurred while saving the comment
    Roy commented  · 

    Well, first off, the current situation has sucked for 30 years. I'm completely willing to spend the next wrapping "=A1" to get "=IF(ISBLANK(A1),NULL(),A1) and have a result that Excel treats as it does never used cells. NOT A PROBLEM dude.

    Second, Most of us asking for this almost certainly do NOT have any misunderstanding about the return and therefore how we'd have to use it.

    Third, part and parcel of any real solution would be for MS to treat any blank as it treats never used cells. Presumably, it stores only a cell address and value (in a simplified version of things) and would simply have to not store such if blank. "Blank" = empty: either never used, or contents deleted or cleared. "0" would need to be treated as an actual value, not all the absences of a value as well as an actual entry of "0". Since what's stored likely includes more information than I imagine, things used internally, not just formatting and values and such, the simple version is unlikely and Excel would have to make provision for a value of NULL and handle it however works best for them. Then deleting/clearing contents would set that value, not just leave no value at all.

    But fourth, we'll all of us be pretty happy if they just work out the FUNCTION asked for rather than a fuller solution. Nice if they did the whole enchilada but if we could just have the function and Excel acted properly (or at least "as expected") on its use, we'd be really happy.

    "Properly" ("as expected") would mean things like sort a set of data and NULLs appear at the bottom of the sort result just like never used cells would rather than as a separate grouping, above those.

    Also, considering sorting, it is clear Excel can already distinguish between current NULLs (never used cells) and blanks, but also it can clearly already distinguish between 0's and blanks as well. So since it can already distinguish all three "values" and so MIGHT be able to already do the underlying work the function would need.

    No one's even asking for perfection, just better, by this much.

    An error occurred while saving the comment
    Roy commented  · 

    Thanks Majid Mj.

    I'd be more outgoing with my appreciation if it actually worked, but...

    An error occurred while saving the comment
    Roy commented  · 

    Be sure to vote here too:

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9095062-give-us-a-proper-null-worksheet-function

    The good news is that even after 3½ years, they STILL plan to do it, that hasn't been downgraded! Talk about slim threads but... Hope Lives!™©℠®♪

    (Maybe that last one♪ will get the RIAA to take our side. Tenacious folk! Even Disney, the original Lawyers-Я-Us™©℠®♪, doesn't try throwing lawyers at THEM.)

    An error occurred while saving the comment
    Roy commented  · 

    Hey guys, take heart. Even though they've had 3½ years to have a change of their own hearts, they still consider it PLANNED... no downgrade!

    What's the gift type for four year anniversaries again?

    An error occurred while saving the comment
    Roy commented  · 

    Rather than changing 50 functions, or only someone's half dozen leaving the others to hobble on, we need a true NULL.

    Just that, and it's all done.

    If I will populate a region using formulas, then PASTE|SPECIAL|VALUES to clean the formulas out, but need cells to really be empty (NULL's), I currently have to use a string the data would never contain like "MMM" instead of "" and Find and Replace afterward with nothing. THEN I finally have a true NULL back in the cells. (Ugh, don't even get me started on how, when first doing this, decades ago, I figured the string "***" would NEVER be in any of my data... great, never was, but then the boss sent one of my spreadsheets to someone outside the company...)

    I have needed this for DECADES now. Plural. I am not interested in tinkering with a function
    or two, maybe half a dozen popular ones. I need this for sorting. I would have used more dynamic formulas a LOT earlier if this had not been an issue, but it's mainly for sorting.

    Do you have the least idea how much time I've spent over the years JUST answering and explaining to users of the spreadsheets (bosses mostly) why sorting did not work out as expected, how to solve that, etc? Every second wasted and often left a suspicion in their minds that the spreadsheet was flawed, so it could be VERY flawed.

    Not to mention the time wasted with the "MMM" and F&R.

    But it's NOT limited to sorting something you've done and maybe could have done differently (somehow, when it's YOU telling ME how I should choose a different method, that's all fine, but if I tell YOU some other way is better, there might be a million reasons you would offer why not, but usually people settle on "I don't want to do it differntly, I want to do it this way," and that is sufficient... but it just meant I was stupid when I used it — not pointing fingers at anyone here!, just sayin').

    No, not limited to those at all: Importing data. Nulls in, say a CSV or TXT file, come in as "" type entries. So EVERYONE importing anything in which that can occur, then adding data, ends up with the problem. Again, the sorting issue. Cell counting issues. How many items in a column. And so on.

    Unless the import is sorted, column after column, the collection of NOT NULL's found, and deleted so you have true blanks, true Null's. Up for a little bit of work today boys? Importing 38 columns with this one. Sort and so on away me hearties!

    So I need a true NULL available in formulas AND in the data import process. Once I have it, no function will give me any trouble. For anything legacy in all the spreadsheets out there, until it is upgraded (or until forever, eh?), there's still good old "". It is ALREADY in ALL those legacy works, so nothing will be broken.

    Now, from that angle, I do understand the let's tinker with my favorite function approach. Instead of upgrading one's work, which admittedly might never happen, I imagine lots of folks would like their favorite don't-work-functions to suddenly work. Nice, for that I guess. Nightmare for most of us and not on point for the sorting and data import aspects at all. But I guess I get it from that slightly askance viewpoint.

    Give me a real NULL, available in formulas, and as a value for a NULL in imported data. Give me PRECISELY NOTHING. That's all I ask for: PRECISELY NOTHING. And lots of it.

    An error occurred while saving the comment
    Roy commented  · 

    Especially to Lori, but in general:

    The USE CASE is needing a cell without data to BE a cell without data to EVERYTHING.

    Until that is the case, nothing has been solved.

    And if you want to use the result of "nothing" as valuable data to what you've written, then don't add the NULL() wrap.

    Or... with luck, the null would be a complete, true, default in all cases blank and you would set your flag to have it be non-blank... sigh... no, that last would break tens of millions of in use spreadsheets. But the rest of us deserve this and future spreadsheets could use it instead of old workarounds thereby spreading world peace and allowing all children to go to bed by 9:30pm so the beauty contestant queens could finally be happy and choose other answers. This would benefit the world!

    An error occurred while saving the comment
    Roy commented  · 

    @Lori:

    He wants a FUNCTION(), something we can wrap other functions in and produce a NULL if the output of the wrapped formula is "" (with perhaps an option in the NULL() function itself to do so if it is "0" also?), OR to give as an element of something like the IF() function.

    NOT looking for a general underlying change to Excel so that all such give a true null as the default. Everyone understands a 30 year base cannot permit that kind of approach.

    Actually, a middle between the nice first part and the rather extreme general default might be a page option (why can't "pages" be generally formatted directly rather than by selecting all first?) that works like the display 0's option. Then charts could act upon the data as displayed (blanks treated as nulls, if so selected), regardless of the literal cell non-content. Or not. An option in setting up the chart.

    (That could also be done solely in the chart's options, but as a page display option, it could let Excel treat non-content cells as nulls WITHOUT having to wrap results in a NULL() function. If one cannot use that due to 30 year base constraints, one simply wouldn't.)

    An error occurred while saving the comment
    Roy commented  · 

    @Lori:

    How would this break anything for you? YOU simply would not use it. The rest of us would.

    Instead, you'd have the rest of us contort ourselves, making UDF's and having to overcome VBA-resistance in many places we send spreadsheets to? Making poor folk doing charts continue to use workarounds for the reason that you'd have to use it because it existed instead of simply... not using it yourself?

    I am clearly missing the logic here.

    Roy supported this idea  · 
  15. 2 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  · 

    FILTER() does not have a "reference" form while XLOOKUP() does. That alone would make this a good thing.

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

    One way to do that autofill is to enter the date as a date, not text. Then format it:

    mmmm d, yyyy

    so it displays exactly as you have it above except... not capitalized...

    Then using FILL will increment the dates, as shown, with them displaying as:

    November 1, 2019
    .
    .
    .
    November 22, 2019
    .
    .
    .
    and so on.

  18. 718 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  · 

    @Futski: You've never seemed like a trollish person so I gave your comment some credence. Did NOT work out. However, something in it, about the paste dialog box, made me think you meant that horrible "extra options" thing that can pop up if you Ctrl-C something and (whichever) hold Ctrl too long, or double clutch on Ctrl.

    I've been meaning to try shutting that thing off for a long time, figure (hope) it's in Options somewhere. Tried to get it anyway, see how V worked then. Didn't. Just failure. Since that's the only thing I ever saw pop up pressing Ctrl after a paste, well...

    Still, wanted to get rid of it once and for all so I got after searching Options. Before finding it (on a backburner again, so maybe I'll never find it, if it even exists in the settings), I came across an option in Advanced that spoke to this: File|Options|Advanced|Cut, copy, and paste — first thing in it: Show Paste Options button when content is pasted.

    Lo and behold... when checked, this does indeed work. I notice that one has to be very proper about it being a three step shortcut. Pressing Ctrl-V the second time rather than Ctrl, then V, makes it not work out (likely due to the first paste placing everything there, the second doing the same, then Ctrl and V removing the second one's formatting, but since the first one's is the same, the end result is a straightforward paste, not PSV... understandable, but one has no saving throw it seems; not a problem, just something to be aware of).

    So, not a cup of water at all. Still, I personally prefer the context menu, V thing, unnatural or not, as simpler, though if I had a lot of times I wanted those other options, I'd probably go this way so they were there by habit.

    Still, it does not help one reallllly talked about aspect here: user training. All of these require more out of lazy bums, I mean users, than they usually will give.

    Which leads to the idea of a setting a file can carry with it. Lots of other choices a designer might want to carry with a file as well. But MS has never given in to that. For instance, one cannot use Precision As Displayed as a rounder in, say, something payroll related, because no one else's Excel will be set to that and the file cannot carry the setting for itself. Some settings DO actually carry themselves out across spreadsheets, but usually when a given sheet has some particular setting that Excel sets for the Excel instance's entire session by the first (overtly) opened file's setting. R1C1 or A1 style addressing being an example. Might be one could convince Excel to allow a default paste option in a file to dictate a session's settings that way, since other things like that happen, but I doubt it. And they will apparently never consider something that can dictate behavior once opened as, related to the ones they do do, I think some behaviors they cannot have be mixed. So just like no R1C1 for file 1 while using A1 for file 2, no regular pasting for file 1 while having PSV for file 2.

    But... if they expanded formatting just a little, conceptually, perhaps it would be something one could set generally, a default setting, so the cells being pasted would paste according to their setting... or according to the target cells' setting, or hmm... maybe the source or target cells are of mixed format here... or you know, those possibilities could really stack up. And I think they don't want to fight that complexity. Choosing one just transfers the question, so to speak, so some folks here would be happy, but many others upset the "wrong" choice was made (Anyone else think it would be fun to write "wrong" as "rong" to extra-emphasize the wrongness? Squirrel... back to the point...) So honestly, I don't think they will ever accede to this request due to the complexities it would introduce that they would have to handle and probably anger just about everyone with how the implementation did work.

    But yes, change that Options setting and this Ctrl-V, Ctrl, V idea DOES work. Happy day!

    An error occurred while saving the comment
    Roy commented  · 

    @Ctrl-V, Ctrl-V: the thing for me is this doesn't work. No matter how many times I try it again after seeing other people extol its virtues. It flat does not work.

    I wonder occasionally if it is a Windows thing. Like some people talk about starting a separate instance of Excel using Alt-Icon click while that does nothing for me and Shift-Icon click does. But then I always remember those "hack" videos that tell you the way to avoid soggy pizza in a microwave is to put a cup of water in which will fill the air inside with moisture and that will somehow make the pizza slice crispy and yummy rather than even wetter. Never found a setting in Excel or Windows that "enables" this Ctrl-V, Ctrl-V action either. And after thinking about pizza for a while (I admit, I have an issue with food appreciation...), I then see someone mentioning how counterintuitive the shortcut is as it puts in formatting, then removes it if doubled down on.

    Seems like that cup of water in the microwave...

    Best shortcut I've come across, and use as much as possible now, is that button on the right side by Control that usually brings up a context menu like right-clicking does. Press it and then V, together, or separately in that order, and you get PSV.

    But it is still unnatural to me after a year or two of trying it. Alt-ESV is still what my fingers itch to do. And I've always avoided the Windows Key and that context menu key like the plague, mostly due to using older, better, keyboards (my opinion, but we all know "my opinion" is equivalent to fact, no matter who is using the phrase, so...) which do not have those keys. So it feels unnatural, but is becoming better. If I could overcome that (1st world) handicap, it would be good enough for my uses. My other oddity that makes me wish it were more natural to me is I hate constantly shifting from keyboard to mouse and back. Even wish there were a button on the keyboard that performed a left mouse-click (after all, that context menu button is more or less a right mouse-click so why not?).

    (Oh, I don't blame Excel program runners for that one. Everything else, but not that one.)

    I do especially like the way one can press the keys separately when using it rather than it having to be a key combination. Nothing against key combo shortcuts, but this is pleasant too.

    But today I move the Ctrl-V, Ctrl-V thing into the soggier pizza category, permanently. I don't care to hear about it ever again.

    An error occurred while saving the comment
    Roy commented  · 

    Wyn Hopkins in https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/34353574-short-cut-key-for-paste-just-value (same idea) pointed out that many keyboards have a "Right-Click" key to the right of the spacebar.

    If so, you can press "Right-Click", then V to paste values in a simple keyboard shortcut.

    It takes a LOT of effort to overcome habits of the past enough to give it a real try (Alt-E-S-V is very ingrained in me), but once you do... it's a magical, zen-like experience...

    "Right-Click", V. Or together like Ctrl-V if old habits are just too hard to break.

    Thank you Mr. Hopkins!

    An error occurred while saving the comment
    Roy commented  · 

    I must say, after a few weeks of remembering to try Mr. Hopkins' Right-Click key, V... it's magical.

    A wonderful world!

    An error occurred while saving the comment
    Roy commented  · 

    I like Mr. Hopkins' Ctrl-V-V. Not only would it be easy (even easier than Alt-E, S, V, Enter) but I picture it as holding Ctrl down for both V's, not just the first (seems obvious, I know, but...) and enabling the program to watch for TWO letters while Ctrl is continuously held down opens a whole new world of shortcuts. And while few people give a tinker's da(r)n about anyone else's favorite idea for a "desperately needed" shortcut, but the truth is we need more shortcuts.

    And unless someone's going out on the limb of total, and I mean FROM THE GROUND UP TOTAL, restructuring for programs' shortcuts, we need a new batch to use. Ctrl or Alt held down continously while two (ooo... or... more?) keys are pressed offers a way forward in which logical batches can be assigned rather than picking the needed 2 or 4 from the few combos left. (Logical as in the Shift-"Key" for inserting something, and Ctrl-"Key" for replacing paradigm, that kind of related batch of needs.)

    Roy supported this idea  · 
  19. 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  · 
  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  · 

    Actually, you can.

    The technique is to open a Reply (or whatever) email and attach the file. (I would just drag it from one email and drop it on this one.) That keeps you from having to save the file somewhere in your regular file structure and having to delete it when done and sent.

    After you have it attached to this email, you may open it (although Excel will open it in an entire new instance of Excel so it will be harder to copy and paste between it and files open in any already running instance) and edit to your heart's content. Save it when done and close the entire Excel instance it was in which will both close the file for you AND close the instance so it stops using memory.

    You can do this with any file, by the way. Sourced from anywhere: another email, your hard drive, a network drive, etc. I do it with PDF's a LOT and somewhat less so with Excel files while hardly ever doing it with other files. But that's probably due to my workload, not the file types!

    So the drag and drop as an attachment thing avoids the new file in your storage and having to run it down and delete it afterwards problem and editing it AFTER it is attached takes care of how to edit it before sending.

    Bear in mind, it is stored in your storage in the original email's little bit of Outlook's .PST file, and when you attach it to the new email (for this or for any other reason) there's a new copy in that email's little bit of that .PST file. So two copies in your storage either way. And if you Send it to six people, archiving the Sends then that's six more copies... so if the goal is to save storage (not for me, for me it's that hassle of saving it out, then later remembering to, and actually deleting, it later thing), it won't matter a lot either way.

    (Maybe... as part of the .PST file, the files take the space they say they do. As standalone files, they take the entire minimum space chunk your drives and file system manage. For mine, that means a 15 byte NotePad file takes 2 Mb. But as part of the .PST, that 15 byte file would take something near 15 bytes. So 20 copies in Outlook would probably take far, far less space than one standalone file would. It's always something ,eh?)

    So attach it to the email, edit, then save, close the file by closing that new instance of Excel, and Send.

← Previous 1 3 4 5 19 20

Feedback and Knowledge Base