Feedback by UserVoice

Roy

My feedback

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

    We’ll send you updates on this idea

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

    Eric Patterson (Program Manager – MSFT)

    Roy commented  · 

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

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

    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.

    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.

    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.

    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…

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

    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.

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

    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.

    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.

    Roy commented  · 

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

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

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

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

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

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

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

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

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

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

    Roy commented  · 

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

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

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

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

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

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

    Today's date

    not

    Today's
    Date

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

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

    Roy commented  · 

    4 I have a fever:

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

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

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

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

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

    Roy commented  · 

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

    Live long and prosper y'all.

    Roy commented  · 

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

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

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

    "user3524289" last answer, 9/12/2017

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

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

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

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

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

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

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

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

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

    Roy commented  · 

    No doubt.

    Got that.

    I comprehend.

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

    Ergo my conclusion.

    Make your own. It's a free country.

    Roy commented  · 

    @David Filisan:

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

    Windows does not.

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

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

    Roy commented  · 

    (Sigh...)

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

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

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

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

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

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

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

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

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

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

    Roy commented  · 

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

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

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

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

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

    Roy commented  · 

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

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

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

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

    Roy commented  · 

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

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

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

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

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

    Roy commented  · 

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

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

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

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

    Roy commented  · 

    @Anonymous:

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

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

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

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

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

    Roy commented  · 

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

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

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

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

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

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

    Roy commented  · 

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

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

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

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

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

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

    We’ll send you updates on this idea

    Roy commented  · 

    No, COUNTIF() returns nothing unless there's an exact match. So if searching to see if the substring "hors" is in a cell containing "horse", one MUST use wildcards. It rather stupidly cannot find a substring on its own, but instead matches an entirety vs. an entirety, hence the requirement for the wildcard usage.

    So the person Phil describes, and whom we all know, has to consider wildcards before the string, wildcards after the string, and who knows, maybe wildcards all around volleying and thundering. But they don't ride into the Valley of Death, they call Phil.

    Not to mention how allowing for these possiblities affect the (should be) simple task of writing the sub-formula that might be the input of the COUNTIF() in a more complicated situation, especially not accounting for the "helpful" things people might do to aid you.

    Just need a simple function that does something at least one of MS's FIND() andSEARCH() functions should have done from the start. But no, they're essentially identical, right down to taking a start number rather than an instance number. Why couldn't one of them do each? But that's a different Suggestion I suppose. 'Tis a granular world. Identical grains, it seems, no variety in this nature preserve.

    But no, COUNTIF() is not really simple for many places the idea might be used. Not at all.

    Roy commented  · 

    @Anonymous: As applies to this Suggestion? Nothing is "wrong" with FIND(). Don't get me started on FIND() in general though.

    However, it:

    a) Will not take a range which one would hope the proposed function here WOULD do. Nor does SEARCH() take a range, so that's not on either.
    b) To use it in the manner you mention requires IF() handling, actually, WITH IFERROR() to handle both sides of its result.

    I know I do, and I assume the other voters would, prefer both of these things taken care of. If it could take a range, that'd solve a) and it would, in either case, provide a clear result to use without IFERROR() at least.

    Remember one thing about this "forum": it is about what we'd like, not about continuing to think any workaround, no matter how simple or convoluted, is preferrable.

    And, as Phil mentions, consider the many users with a more limited skill set in Excel. (Yes, FIND() is pretty basic, but No, non-straightforward uses involving it (like wrapping it in IF() and/or IFERROR() to aschieve what seems like a simple goal are not basic to them.) Or people for whom Excel is A tool and nowhere near their most important one, and for whom nothing is going to change that. Yes, they can kludge something together on the table saw using the miter gauge, but just want to go over to the radial arm saw and cut the thing. Forever. Yes, that's too bad for them, mostly, but this is where they, or others on their behalf, wish for their needs.

    And my own. I cannot use FIND() on a range and that will NEVER change, so my hope lies with a new function that can be "right" from the start. Because you may have noticed, functions never, ever change like this because of backwards capablity concerns. Even though MS could put the new functionality into arguments that come after current arguments sp that formulas written 30 years agp would not be affected.

    In fact, I would hope that applied to a range, it would return the cell along with the location in the cell, in a form that could be directly (not INDIRECT()-ly... so not returned as text for the cell reference...) passed into other functions in a formula. Perhaps as an argument that indicated which to return.

    This forum is our hope chest, not our nope chest.

    Roy commented  · 

    Or check length vs. length after substituting "" for the string.

    Sigh... they know the problem enough to work out a formal suggestion for a workaround, but don't just simply program the function.

    It could even just be a "last parameter" option in a function like FIND() (as in FIND("xx",a1,,"contains") or just true/false).

    You wouldn't believe the workaround I have to use for our shop software reports that come out simulating their look on printed page instead of columnar Excel files, then have to be converted (there is NO PDF conversion software that really works reliably). Just a simple CONTAINS() applied to 6-10 possible cells that might contain the string and associated data would simplify things incredibly.

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Roy commented  · 

    Good point about flagging offensive posts. Like yours.

    Roy commented  · 

    @ Matt Peterson:

    Apparently some people are also obnoxious sh*tbags too. Not to mention fantasizing shills. Say, did you ever hear of a person or organization that used BOTH versions of Excel?

    Roy commented  · 

    Lol, looking forward to that return to the old menu and button bar interface! (Not kidding, I wish... You know, at least it was honest about be a menu system, not a "Ribbon" instead.)

    Simplified Ribbon... freaking LOL, it's the old menu and button bar system... mighty MS advances like a juggernaut! If the "jug" in "jugger" is a little brown jug... And the beat goes on. Yeah, the beat goes on...

    Lord though... I'd love to have the honest menu and button bars back. Still, to tout this as something new and exciting? It's "Cat on a Hot Tin Roof" and Mendacity all over the place.

    Roy commented  · 

    Wow... looking at that roadmap for the web version... the web app's finally getting Copy and Paste!

    MS is really beginning to listen to us! Maybe using decimal values will come next.

    Roy commented  · 

    And clown make-up.

    Can't be the best unless you play the best: MS should BEG us to try alternatives, PAY alternatives, so their competition is the best and they can HAVE to be better. Oh, look, that idea's on the roadmap now! So... it ain't nevah gonna happen... (sigh...)

    Clown nose too. Listen, it even beeps...

    Roy commented  · 

    @Anonymous:

    Well, several reasons for the not shifting seem possible. Maybe a million others too, but I bet these operate to a fair extent:

    1) It's still a "No one ever got fired for buying Microsoft" corporate world. (Insert "IBM" and you have a "why" during the clone wars of the early to mid-80's. I suspect this one has been around as long as the "I'll never forget where I was when..." comments that date back to at least Pearl Harbor and surely before.) Bear in mind, it's not just "I won't go out on a limb" CYA think, many corporate to mid-size customers have a "relationship" they are "leveraging" with MS which they've paid, and are continuing to pay, large dollars for. ("Relationship" is sometimes rather like the one with Big Jim and the little scared guy in his cell... but these exist.)

    2) Google has a LONG history of not being the least, not the freaking least, inhibited about simply dropping a product if they feel... well, whatever they feel... simply... it isn't there anymore. It never existed in the wild world outside their servers and therefore it doesn't exist now. Sometimes you have a period to adjust, sometimes you DON'T. Lots of folks are at least dimly aware of this and those who are more so are simply not willing to risk it. "It" being their jobs, their company's wellfare, their jobs, having a solution that will work tomorrow too, their jobs, well, you get the idea. That inhibits a Google solution.

    3) Google is DEFINITELY "don't ask, don't tell" (the "tell" side of it) on a huge number of things but HUGELY SO when it comes to your privacy and in these cases, your data's privacy. They look at EVERYTHING that EVER comes through their machines, record anything they care too, analyze it all, and make money from it whether that includes sharing what any human being would clearly regard as private. 6¢ for giving away your secret formulas? Not worth seeking, but that company over there will examine the data for these nuggets and make the payments so why not? You betcha. I cannot imagine that that does not inhibit many corporate possible uptakers. Just a general, quiet (can't have the monster sue us) general policy of not using Google products or services. By the way, would YOU use Google's Password Wallet software offering? I thought not. (I won't either 'cause I made it up, but it was a chill-causer you're still shivering from thinking of how they could leverage that the way FaceBook leverages the "login with FaceBook idea, eh? Thinking of HAVING to use that Passsword Wallet or never login anywhere again... that's your heart shivering too friend.

    4) The usual... I know Excel, and I don't know Google's "thing"... I mean, I wish I did, but I don't. I can't seem to get a chance to learn it either... this could be where I get left behind... but it still means I can't productively make the shift no matter how much productivity I lose with Excel... good thing I'm nearing retirement, right? And everyone else knows Excel. Excel works seamlessly (LOL, good Lord, LOL) with Word and there's no way Google does. Yeah. Power Point too, like if I need to update the Excel or Word source, it updates the PP slide too... oh, no, wait on that... but... everyone knows PP too. If I want to hire someone for data entry, they're flumoxed by the Google interface, I get nothing for a while, they do know Excel, well, they know how it looks and how to enter data in it... so knowledge for crafting and maintaining spreadsheets, knowledge by the bosses for using their dashboards and such (if you ain't got time to learn it, picture telling your boss he has to learn it or die with the dinosaurs... firing is still a thing you know), lower-level workers using it, general "taught in schools, even tests are taken in Word" kind of points... there's a lot for Google to overcome and it isn't making an effort on that fron either.

    5) Google... you know, Biilionaire Gates ain't got a lot of sympathy from me for just about anything, BUT... My GOD the man has given an EXTRAORDINARY amount of that filthy lucre to charity. Continuing to. Google? Bezos? Buffet? Those Mexican cement lords? Billionaire Gates and by extension MS get a bit of a pass from me on things because the man does walk a (hu)man-size walk. I doubt many actively think about that, but I also bet huge numbers of us all are aware of it and give MS something of a pass on many things. (Lol, read some of my other posts here and elsewhere and you may need to remember there's a difference between pointing out room for improvement along with fears of nasty conspiracy-like marketing efforts vs. outright excoriation with a religous-liike intolerance for the thought anything could ever be improved along with a religious-like "anything I don't think is evil/wrong/sin/evil/wrong/sin" kind of idea.)

    I think those operate to a fair extent—debate is allowed—and that many have many other reasons.

    (Especially that "this may be where I may/will get left behind" one.)

    Roy commented  · 

    Folks, consider the following, as bad news on the "when it gonna be" front:

    Remember Spill functions? New, awesome, given to man by the modern Prometheus, Bill.

    Yep, in production, working, available to secret club members, and after they get to play with them and steal a march on the rest of us, they'd be pushed on out to the hoi polloi.

    So, working, oh ,maybe the special club was really another name for "pretty finished beta", could be a kink somewhere, want to collect some production feedback to perfect approaches taken before they were set in stone... set in stone forever, to judge by most things... but working, demonstrable, plan for them people, they'll be in your hands shortly.

    Well, that was an easy half a year ago. The production version was ready then, supposedly, just had to reward those special people, then we all get them. And half a year later, well, no one has them.

    How's this apply? Hmm... that green lump up there says "STARTED" which doesn't sound at all like "FINISHED AND READY FOR Y'ALL, JUST GOTTA REWARD THE SPECIAL PEOPLE FIRST." I have to say asking for a date would be a sly passive-aggressive move, but like all passive-agressive moves, it ain't budging anyone.

    Your answer is "more than the half a year mentioned above" away. ABSOLUTE MINIMUM. And that's if the green lump above changed to "IN THE NEXT MONTH'S BUILD RELEASE". So if you were really asking, not just poking the bear, it's gonna be a long time.

    Good luck though. Another thing I have to say is I just don't see how this functionality has any point whatsoever if this is not part of it. Telling everyone to collect around a single computer and shout out the next thing to do, hoping the person with the keyboard picks your shout next is not any kind of definition of "sharing" or of "multi-user" that I'm willing to acknowledge. MS: It's time to cut the Billsh*t and make grown-up functionality. Not even children would accept this "feature."

    Roy commented  · 

    (Sigh)

    And after patiently waiting a while, if they don't ever do it and push us toward the vast subscription revenue I believe they feel is much preferrable, vis-a-vis PowerApps, well, I'm not averse to taking the plunge and changing to Sheets.

    My only fear there is Google has a LONG history now of just out of the blue announcing a product used by millions is... done, finished, kaput, and will no longer be available. Just a fear, but they seemingly have NO hesitancy about such things.

    Roy commented  · 

    The practical difference with Google's product is that it was written much later than Excel's base can of worms.

    10 years ago, collaborative/team use of a single spreadsheet and collaborative use of things in general was would have been a have-to-have-it thing to build in from scratch along with general approaches that let one extend the ideas.

    Excel's base level programming was written when the idea of general interactivity even of programs on your own computer (your OWN computer!!) was so porrly supported that Excel and Word had their own programming for copy/cut/paste (Remember the "Spike"? Word still has it.) and drag-and-drop (different from copy/cut/paste in Office programs than most others) because their owner's own operating system was unreliable in the little support it provided for them. Writing collaboration and interactivity into that mess has to be harder than for Google and Sheets.

    There's no value judgment there, it's just something that is. So this kind of thing is natural in Sheets and has an easy time being added to or extended, but is a hard row to hoe for Excel.

    My hope is that Excel is, maybe, trying to re-write the base some, and give itself the basics for moving forward and that things like this will start to be achieved faster and better.

    You know, instead of messages like Admin's last becoming 4 YEARS rather than 4 MONTHS old and still nothing new. Like a variety of other Suggestions on the site (single file Undo stacks, for example).

    Less constructively, I have to wonder what "dogfooding" means. Gotta say it sounds a tad insulting, but maybe it wasn't. Curiously, I was recently charged with being a Mac fanboy, though I've never knowingly seen a Mac outside movie product placement. This has the same feel, and the same value, if so. Read some other commentary, you'll see I'm no fan of what I think they actually want us to migrate to.

    But since I think providing this is probably REALLY HARD considering the gallon of water they have to bolt it onto, (Vs. much easier for a modern program, so sure, why wouldn't Sheets have it? It'd be almost criminal if Sheets didn't.) I feel my dinging must be saved for other deep problems Excel has: Willing to take a patient personal user view, for a while. On this one.

    Roy commented  · 

    Hi folks. Since the site doesn't seem to have anyone looking, this is suggested more than once. Might want to add votes to this one:

    https://excel.uservoice.com/forums/274580-excel-online/suggestions/8192445-allow-for-personal-user-views-filters?tracking_code=e69e6219a94b94e4a026394466189d38

    Roy commented  · 

    I'll ding them for a lot of things, but I don't think a choice was made, not a direct one anyway.

    Excel is clearly done from its historical source of being meant for a sole user at a time. Never upgraded to be, at its core, a multi-user program.

    This feels like one of those things where the feature is added, and configured for programming, with that background in mind: never intended to be successful at handling multiple users working on the same file, at the same time.

    Oh, great if that works out when it comes along, but not even thought about when done, and not part of any planning.

    Now there are environments in common usage that are meant to allow exactly that, simultaneous usage of a spreadsheet. Sharepoint for instance.

    And hence the Scylla and Charybdis of the situation: Does one fix Excel so it can move forward as a multi-simultaneous-user program, a very nice "future-proofing" of a well-selling product? Or does one make changes in products like SharePoint to handle this, which has the advantage of not having to dive into the worms under Excel's hood as well as marketing advantages for direct competitors of SharePoint (though not indirect ones like Google who build into their spreadsheets)?

    Or a mixed bag? (I wager that would cross bureaucratic boundaries that just don't cross successfully...)

    THAT complexity: WHERE to solve it, I think that's the hold-up. The work they say has started, that would have been lay-out work, so to speak, where and what, not programming, etc.

    Given my belief, detailed elsewhere, they want to push users who do these kinds of things into the much more lucrative world of PowerApps, I think the push from the Excel side is do nothing to Excel, doing instead just what SharePoint would need to work with the likely scenarios available through PowerApps, and let the money roll in. SharePoint people would seem likelier to push for a bigger adjustment to their baby, solving the problem and keeping their product hot and ****. Their advantage in the argument would be their offering can tap a user base that needs to connect a lot more than PowerApps does, and simply, sort of. Once that argument is settled, if it exists at all, maybe we will get some progress on this and a variety of related multi-simultaneous-user issues one can find just in here, let alone on the rest of the internet.

    Perhaps we ARE seeing some early steps. All of this would work nicely, it seems, if Excel could know what to treat as a database and what to treat as wild, crazy, not-database material. It could then store the database material in a separate file in its collection that is presented to us as a single file, and use age-old database tools for record-locking and presentation (personal views/filters, and other kinds of filtering that are currently done haltingly at best). Doing so would allow exactly what people need here and a few other places on this site.

    Tables, formal Tables, like we now have, vs. the more "normal" data ranges that often aren't even Named Ranges MIGHT be an early step toward that. If a Table is defined, it can be safely treated, "under the hood", by Excel as an actual database and the things talked about in here can be handled like databases have since 1960. All hidden from us, but easily granting the needed functionality and also being easy to add to or modify for new needs or to re-choose when a poor choice was made originally.

    Maybe. It would be a nice early step, if step it is, and would offer tremendous hope for things to begin to roll out as if Excel were having a "little red sportscar second childhood."

    Roy commented  · 

    Looks like it will be a while...

    Look at how long they've been extolling "Spill Functions" and the new world the changes to enable them will bring.

    And that is programming they've finished enough to be in their version of Beta. This has only "started", whatever that might mean. (Someone's laying out a flow chart? Someone's writing code?)

    For a calibration on expectations, note that it took 3 years to go from "... we're planning to build it." (9-17-15) to first telling us "... has now been planned." and offering a survey. I'm thinking something shy of geological time scales, but... how shy? Who knows, eh?

    Roy commented  · 

    Well, they reallllly don't want you adopting Office 365 so much, since they figure you almost have to have it anyway for Outlook and Word.

    They want you solving the problems mentioned below with PowerApps. A new subscription for the author and then enough subscriptions to make good and sure you never run out for the material's users.

    So... one spreadsheet author and seven users? They gain NOTHING since you have an Office subscription for Outlook and Word regardless. PowerApp because Excel is too crippled to handle it? That's eight new subscriptions, every month, until infinity.

    A second spreadsheet? 12 users? Free, so to speak if Excel can do it, and it's functional in the work environment. But... it can't. It can't make you happy. So 13 more (that's right, those other PowerApp subs were NOT for any use of any PowerApp, they were for that single, sole, solitary application and nothing else. This is 13 NEW subs to pay every month.

    You have 17 of these multi-user spreadsheets and only going to PowerApps will fix your difficulties... gosh... a shame that. Hmm... say an author and seven users each, that's 126 new $8 or so subs a month, year after year... $1,000 a month in new revenue...

    Of course, it doesn't occur to them one might actually bite that bullet and buy some actual programming to solve a few of these, whether little apps written for you by someone local or off the shelf solutions.

    And by-the-by,how long until a programmer's price doesn't start at $100 an hour for a simple page on a website but at least ends when it's done? How long until MS's model here encourages programmers to "own" you forever? $100+ an hour for simple things still, but now they also get $10 a month per user for as long as the metering they build in (at $100+ an hour, yes, you pay them to meter it) says you use the software. They could NEVER do that, unless it is forced on the world from the top down.

    What do you get a man who has everything? (Like Bill-ionare Gate$.) No, no, he already has a Scrooge McDuck money vault building with a diving board to dive into his money. You get him fantastic new streams of revenue by busting our chops. Like this.

    Roy commented  · 

    Actually, if this really happens, and clearly it does (we tend to break apart files and work ,then bring it back together to avoid this and other problems, getting our own set of problems, of course), then this should be of enormous help to the average office's use of the ability.

    One need not be pigeon-holed to other authors' views. One would have his own. Perhaps several. And generic views could be arranged, not molded to a person, but a role. It could be very handy, even for an individual. Like we used to be able to do via Workspaces defined for roles, or people using the split apart file approach. (No more "Workspace" functionality... have to craft it, not just use it.)

    (In the end of your description, you talk about exactly when it would help you, not hinder you.)

    I like the thought that it could be used to add to the protections in Excel (the few, the proud...). To limit someone's access or the range of material he could harm in some dufus way, one could set up a view that limited his access, call it "Newbie" or what-have-you, if these were password enabled, it would be the only view of the file he could access and so the only material he could hammer. Not "protection" in the strictest sense, but still protective. One could bring a few of the split apart pieces back together under a scheme like that, and lose some of the difficulties that approach has.

    Roy commented  · 

    It'd be an interesting way to protect a spreadsheet too.

    Interesting to see an Admin comment less than a year, or four years, old. Almost makes you think they still look at the website now and then.

    Roy supported this idea  · 
  5. 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  · 
  6. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Use this custom format:

    +#,##0.00;-#,##0.00;0.00

    Or adjust to suit. Using the "+" does not even require the "\+" approach. Excel strips off the "\" from it. If you'd like to add a space after it, Excel's fine with that too (so instead of "+24" you'd have "+ 24").

    The real trick is to specify the first three kinds of formatting. Those are for numbers. If you don't, Excel will handle them normally AND then add a "+" to them. So -28 would be: -+28.00 and I bet THAT is not desired! If you like the idea of "+0.00" you can just specify the first two formats, positive and negative, and leave the last two, zeros and text, alone.

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

    We’ll send you updates on this idea

    Roy commented  · 

    Ah, and to be explicit, it does the import just fine, leading 0's and whatever. Sadly, it then feeds that through its "every bit of entry work you ever do" engine to decide how to store each cell produced. So that is where the real problem is, that engine that goes to work when you press Enter or Tab or mouse away after every bit of typing, and before it displays pastings, and so on.

    Not the import itself.

    Roy commented  · 

    Yes, the issue is multi-faceted and a hassle and annoying.

    Connor's idea and explanation are fine, and actually, most of us are forced to find the capability elsewhere, but it works fine.

    WHEN Excel lets it go into action. Which is does not with CSV files, hence my earlier reply to Connor. You simply never get the chance.

    If I type text into a cell it may widen to take it, but never, no not ever, for numbers. You get the annoying counter-explicit-format change to SN. Leading 0's are dropped. That's an ISSUE folks. Trailing 0's get dropped too. (If they didn't, one could reasonably easily apply one's own approach to the significant figures (religious war) needs, but you simply "can't even" (like the kid who could only speak the odd numbers) because information required for the issue is simply stripped away.

    Yeah, there's lots of "you ought've..." kind of stuff, but you know, if I EXXPLICITLY formatted a cell Excel should absolutely never, never, ever ovverride that and nothing should EVER be stripped from my entry. What if lower case g's were deemed unneeded by Excel? (Just some April Fools joke, hopefully, gone the next day) And "golf" was automatically, it's just GONE, stripped down to "olf"? No recovery, no restoring it while chiding yourself to remember it was to happen and plan ahead next time. Just gone. Zero may be special, but it's largely the same thing. Period.

    NEVER ever, in all the times I've seen this addressed here or anywhere else, have I ever seen a comment like "Um, I actually count on it to do that." Not freaking ever.

    So we have to physically widen columns, then coax the display back to numerals, not SN, we have to live with the lost leading and trailing 0's, we have text entries like Corin mentions that can be nightmares (yes, you see them and make them right, but import 20,000 rows and one's buried in row 13,485, and it creates a number that is not 30 magnitudes out of whack, but just maybe three or four magnitudes and a value calculated from numbers in the column is sudden VERY wrong, but in a "the total was seven times too high, but I never noticed that other than to note things using THAT total seemed a mite high" way, not "it was four billion times too high so I noticed something was wrong" way. "Silent errors" if you like. Who amongst us has a suite of conditional formatting and special auditing sums applied to his data inputs... and the time to check them thoroughly each time data is brought in? Alright, plenty of us make some attempt, but very few are able to make comprehensive checks to the problem Corin notes can kill ya.

    It's just all bad and we'd like it to not be. The Import Wizard had the wonderful feature of being able to tell Excel "nah, you just do it for me" by clicking "Finish" at any point. NOT available in pretty much any other "smart" features.

    No one claims to benefit from any of it and to prefer it. Lots of us have been annoyed or screwed. So why not get to work and fix it all?

    Oh, yeah, the real workarounds for the CSV problem pretty much involve the TXT extension replacing the CSV extension. That's a real good point by someone... could've sworn I saw a comment with that flash by here. Oh well.

    One thing else about Excel's handling of all this is that your settings in the Import Wizard vis-a-vis the delimiters affect a copy from another program, a browser perhaps, and paste into Excel. That's why sometimes you copy and paste something and it goes all into one cell, multiple lined, or it goes into several cells, but all one column, and some other times it goes one word-equivalent or so into lots of cells and rows. So you get a payment card email and usually just copy and paste the email's data into an input area then read the output to perform the charge, but no, today is after yesterday's work which involved importing and today that paste overwrites 12 columns instead of being 23 rows in a single column. And the user calls you, badly describes things, and thinks your spreadsheet is garbage, then is hard to lead through solving the issue. Thank you Excel. They could separate functionalities there too while solving this and I would not complain!

    Roy commented  · 

    @Connor

    Yeah, see, the thing is... for a CSV file Excel 99.9999% of the time opens it directly without ever going to the Text Wizard.

    At all.

    So, you know, we CAN'T do those things.

    Ball: still in Microsoft's court.

    Roy commented  · 

    You know, things should just simply be there as entered. Whetehr hand entered, dragged and dropped, imported, whatever, simply recorded as entered.

    Then I write a formula that references it and Excel reads it... and does whatever it can. If I use it as a number and Excel can't figure out how to use the character string as a number — AT THAT MOMENT AND FOR THAT USAGE ONLY — it simply gives an approriate error. And continues to leave the underlying character string, in General format if I didn't choose to apply some other, ALONE.

    Period.

    If it's the equivalent of "82happysanta9" and I try to add it to something, well, my bad. No errors simply because I had to format it as text to keep the whole thing. Simple, straightforward.

    I might also point out a niche advantage one would have here: since all the entered characters would be present, I, or Excel, could easily (relatively easily) calculate correct significant figures, ALWAYS. It would be so easy, they could easily program a format symbol to add to any numerical format it could work with that would literally, not just display-wise, change a result to only keep the proper significant figures. (I guess a second symbol would have to be available to mark some entries as constants so they get used properly in that.)

    With that, everyone could use their own silo's standards about significant figures and rounding to give the results they need.

    It's ridiculous... they say Excel has engineers in mind and then one of the most basic engines in it whacks characters from start and end of entered data with no workable way to prevent it. "Su data es mi data," sayeth the Excel.

    Roy commented  · 

    And that's a lie. You read it.

    Ciao. Forever.

    Roy commented  · 

    It wasn't a rant. And it responded to the question raised herein about wasn't the one of the two functions enough.

    Further, just don't comment in a mean way about it then. If someone does, I reserve the right to respond back from a position of having been insulted.

    They taught basic people interaction in kindergarten in my day. Sad you need a reminder or short course on this.

    Also, if someone doesn't care, they can say it. If they find it boring, they can say it. Or, of course, just keep it to themselves. They don't have to imply the issue was there being more paragraphs than they can bring themselves to read on any given topic, or just mine.

    Response from a position of having been insulted is perfectly appropriate when having been insulted.

    Meanwhile, rant on for no real reason since YOU haven't been insulted. I doubt MS minds when we eat our own instead of concentrating on their products.

    Roy commented  · 

    @i just:

    No problem. No need in this life to read things for comprehension. That's a primary school concern, right? Who cares after 2-3rd grade.

    Gosh... those hopeful "when will..." questions are really complaints? Who knew? (Those 3rd graders I guess.) Wow, that must mean the passive-aggressive epidemic is out of control. Soon it will rival the tl;dr epidemic.

    I'm not sure that as a people we deserve decent product features from MS. (Whew! Just three paragraphs this time!)

    Roy commented  · 

    @Patrick, et al (should be "et ALL" in this case): Not to be a Debbie Downer, but... remember when they first touted the new Array functions? Ooohhh... "Spill" fucntions... Remember how they went on and on and let websites show them in use... oooohhh... and said only special people had them for now, but they'd roll out soon and change the world?

    Yeah, like at least six months ago, a half year ago. Got them yet? Nay.

    So, (VERY) sadly, even if they told us this was done and would roll out immediately, I a'gonna have to say I'd expect an utter minimum of the same half year Spill functions (oooohhh... Spill functions...) have taken. And EVERY day longer they take, gotta add that to anything claimed about this.

    And...

    Ain't nobody claiming anything here is being taken seriously enough to be, well, anywhere on a path to the day when they can announce it is done and will roll out immediately. Not even six-months-immediately.

    So it looks like I shall die without seeing it happen. (The truth hurts.) So be careful with that name Patrick. Don't get pushed into a stance you have to live with because 'If this be worthwhile spreadsheet programming, golly, I'd make the most of it: Give me the numbers I typed or give me death!' could go badly. Definitely slowly either way.

    Roy commented  · 

    @i just: There is indeed a SEARCH(), probably because 1-2-3 had it, or had FIND(), and they were stealing everything Lotus did... I mean, they had to have Lotus' version for compatibility or business people whose bosses were paying for 1-2-3 wouldn't even try Excel for free. (That's a chilling thing for the economists... won't even try it for free, really free, not like internet or physical store free. I loved free Excel though till they killed all competitors and begfan to charge a ton. Sigh...)

    Actually, I WOULD value a SEARH() that could search a range of cells. (And that could begin with "instance 4" of the value being searched for!) My problem... I mean "use case" right?... is an idiotic software vendor whose idea of Excel export of their reports is spreadsheets that LOOK LIKE their reports. Not ones with a simple row and column presentation of the data. Worse, they are not in any way consistent about how the material goes into the report. So a value I need that is somewhere relative to another value that is unchanging... find it that easily?... nay... the two values could be in any of 10+ cells in one report, and not consistently either so I can't find the one and move consistently to find the other. Contortions are needed. If I could search a block, I could easily specify the range of cells (3-4 coluumns, 3-4 rows, but easy to define for each block) and get the value I really need. So a real Search function, not just a clone of FIND() would be NICE. Or if one used the "instance number" approach so you could specify, say, the 3rd instance, while the other kept the "starting location" approach so we would be able to do whichever was best suited.

    No, FIND() is not sufficient.

    Imagine my aggravation when the idiots led me on, then showed me their newest idea to create a lot of fake hits on Bing. "We got market share... we do!... what do we call our product again?... oh yeah, Bing... Bing has relavence! It does!"

    And then again a week ago after half a year had passed since I looked at that useless bit of garbage... 'cause I'd forgotten that's what it had presented before... only to find that was STILL the new and shiny special thing to excite us with. So never doing that again, not 40 years from now, if I live that long, 'cause it's just garbage.

    Roy commented  · 

    @Futski:

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

    Roy commented  · 

    @orgen:

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

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

    Suggestion's point is still very, very desirable.

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

    We’ll send you updates on this idea

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

    Thanks,
    Steve (MS Excel)

    Roy commented  · 

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

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

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

    Roy commented  · 

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

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

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

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

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

    Roy commented  · 

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

    Important point:

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

    So the whole thing just doesn't matter.

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

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

    And now the wee counterexample to the main contention:

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

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

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

    Fin.

    Roy commented  · 

    @Peter Collins:

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

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

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

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

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

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

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

    Roy commented  · 

    @Jaime Segura: that's just not correct.

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

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

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

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

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

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

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

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

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

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

    Roy commented  · 

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

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

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

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

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

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

    OK, standard complaint stuff, I suppose.

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

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

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

    And really, why?

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

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

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

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

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

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

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

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

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

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

    Roy commented  · 

    Absolutely with the Named Ranges.

    Two things to remove:

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

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

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

    We’ll send you updates on this idea

    Roy commented  · 

    It is requiring you to enter the value in hours:minute:seconds form even though your output will only show the minutes and seconds.

    For example, "0:27:31" and "13:27:31" will each display "27:31" because the hours portion is specified and Excel does not misinterpret the entry.

    But since it is looking for hours first, regardless of format, it sees the "27" as hours and the "31" as minutes and displays the "31" only.

    You don't have to use the "hh" part and have the ugly and distracting extra portion on display but you DO have to enter the hours.

    The "27.31" oddness is due to it doing the arithmetic: 0.31 * 24 hours = 7.44 hours. "* 24 hours" because it is, in this case, running afoul of another convention it uses which is the integer portion is days, not whatever one has in mind when formatting, and the decimal portion is time. So it sees that as 27 days plus 0.31 more days so the arithmetic above. 0.31 days is 7.44 hours. 0.44 hours is 26 minutes and 24 seconds. Hence the "26:24" you get.

    No great way around it. You can use a helper column and format it as text, enter the material as desired, then use a formula in the original column to select pieces from the entry and make a date out of them. But that, like most workarounds, sucks.

    What you really want, as a Suggestion, is to suggest that one be able to set, along with the literal format for a cell, an option to allow Excel to interpret data entered in the output format as "belonging to" the format, do "mm:ss" and you enter "27:31" it realizes you mean 27 minutes and 31 seconds and treats it accordingly instead of thinking you mean 27 hours, etc. Enter something NOT matching the literal format set, like perhaps "13:27:31", and it would act normally (act like it does now).

    But this behavior you show is intended. Excel feels it is YOUR shortcoming, not theirs.

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

    We’ll send you updates on this idea

    Roy commented  · 

    Or I don't want a summing cell for the data I selected. Just want to highlight a bit of it and see what those cells sum to. Sheesh. MS... c'mon!

    In any case being able to click there, select copy, and paste somewhere, anywhere else, would be super nice.

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

    We’ll send you updates on this idea

    Roy commented  · 

    Yeah. No doubt.

    Though I would say, if there's a (date) format (but in particular, anything other than General I think) set, I would NOT want it overridden. Just looking for typing "3/16/19" in a cell formatted General and having it be a proper date but look exactly like I typed it.

    If I want to type shorthand dates but have them come out a particular way, I'll set the format beforehand or afterhand.

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

    We’ll send you updates on this idea

    Roy commented  · 

    You have some mean aspects in your text which gives that observation vs. suggestion observation strong legs.

    I've used spreadsheets as long as you and I do have reason to not use them probably 20% of the time with the same lookup functions. And most of the times I do "use" them, I set up a Named Range that is either explicit so I literally use them or dynamic so I "sort of virtually" use them. Plenty of lookups are more ad hoc, boss is asking something I'm not writing into the spreadsheet, just getting him a quick answer.

    A thought about those "less skills" or "novice" users: how are they to become something else if everyone babies them? "Protects" them from finding a whole new area that they can pursue new knowledge in?

    As a general principle though, a sea-change function like XLOOKUP(), I'd like to see Excel minimize the "of course, you'll want this help and you'll always do this anyway" type things. Mr. Jelen's request for another argument in it, one to handle the "didn't find a match" problem that is 99% of lookup function errors, that's a good add, no "we'll make choices for you" stuff there, just handling a frequent problem. But this kind is asking to have Excel make choices I don't want it making. So I think it a feature I'd hate to see baked in. It subtracts rather than adds like Mr. Jelen's suggestion. Maybe a more practical thought on it: Mr. Barber and I don't seem to agree on much at all in this place, and I think he takes what one could term a "programmer snob" point of view fairly often, but a) he and I agree here so that's a thought-concentrator if you think about it, and b) most of the things I object to in his comments are not wrong, just not seeming to take the circumstances a person might be in into account. He always is, however, always advocating an approach that would help point novices (and upward) in a better direction. This suggestion is the opposite, essentially saying get lost, you're being left behind on purpose, too much trouble all-a-youse. That's not good Mr. 1958.

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

    We’ll send you updates on this idea

    Roy commented  · 

    The point is that Excel knows your system settings. So if you use a date literal, marked somehow so Excel doesn't cause more trouble overdoing things (perhaps with the double "double quotes"), Excel would convert it whenever calculating the cell (like it would, say, "123.00" becoming "123" in the cell) and replace it internally with a date number...

    Using the default date format to interpret it.

    From then on, it would show as what you entered but exist in Excel as the date number it prefers. The value Excel stores and uses would be analogous to normal cell formatting while the literal it SHOWS you in the cell would be analogous to in-cell conditional formatting.

    (They might even do it literally that way. Then it could be extended to all our other complaints about changing entered values, like the example above of "123.00" becoming "123" — we don't have memory and storage issues like in 1988 anymore.)

    Since it would be the date number (like "43208") to Excel, it would then show the user that date as a literal, but in the format that is THAT user's default format. No misinterpretations on Excel's part.

    Easy. And all those other issues that would be resolved by extending the use of CF-ing into in-cell material. (For Excel's use only, I do not picture extending it in a way that users could access it directly.)

    To recap again (I know, but this isn't 10th grade Composition and I am not a professional writer), Excel would immediately change the actual value being stored to it's own preferred whatever, probably the "43208" kind of value, show all users the date literal in the formula in their own default format, and use its own version when appropriate (most things).

    It'd probably need to make users extract portions of it via things like MONTH() rather than string functions so it didn't need to make a lot of decisions and store a vastly larger amount of information. But we'd understand that. Some might grouse, but they'd understand it. (Until 20-30 years from now when someone would say something about not being limited in storing or using those vast amounts of extra information like I did above...)

    Roy commented  · 

    Yes, even if we had to double "double quote" them, it would sure be nice... easier to type and easier to understand than something like ="1/15/2015"+0. (Or the "--" of course, any arithmetic operation will do. I'm used to the "+N("text comment you want in the formula")" so I usually add a zero rather than the "--" approach, but any arithmetic operation that doesn't change its value works.)

    People are used to "+0" or some othe number being used for offsets (like in ROW()-1 to account for a header row) so what I do just confuses them (which "--" does also). I'm all for having none of that anymore and this idea would not only eliminate one whole kind of source, but make for easier to formulas.

    As to Mr. Barber's comment, no, not a problem. First, one could picture this working with numerical literals that are formed following the system settings for the computer (unless overridden in Excel's settings) using numerals. So two legs good, four legs bad: 1/15/2015 good, Jan 15, 2015 bad. Second, if Excel can recognize a form as one types it into a cell, then it ought to be able to use the same programming to deal with a marked (like the double "double quotes" thought) date literal.

    I wonder why Mr. Barber's SUMIFS() uses the Date() functions when a literal would work just fine:

    ">="&"1/1/2015"

    Point of all of this really, is we want to suggest improvements, not live with second best and workarounds. So...

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

    One wonders just how this came about, what purpose was served where.

    Works with the ² which it seems to regard as "130" and by themselves, no need for a character to go with them. ODD. Consistent though so... what? And the generated 128, say, is really three digits not a single character somehow, LEN()=3 as well as being editable. And taking "m128" and filling down does not yield it, even in its original cell.

    I was thinking of the 130, for instance, as being 48 less than the character's CODE() value, but while writing below, I realized it is 128 + 2 and while 48 doesn't really suggest anything, 128 (2^7) does. and 131 is 128 + 3.

    But it does not happen with the nearby characters that are not "number-looking" just them. Tried about 20 others, including the later series of superscripted numerals. Those last did NOT do this. So it could be unique to these two, maybe.

    Superscripting a "3" next to the "m" using in-cell formatting yeilds a nice series like the second result option offered above. The numbers count up and down as appropriate.

    It also fills this way left and right (left = upward fill, right = downward fill for how the number part goes).

    Nothing I found about it suggests Excel regards it as a number except what Mr. Barber found. When it fills using ONLY the ² or ³, the filled in cells have numbers that are displaying as if text, but that are General format. They are in that limbo you can get when you try the Paste|Special|Multiply (by 1, or P|S|Add by 0) trick and... it doesn't seem to work. It DID, but did not finish it by forcing Excel to refigure how to display them. And when it happens, many functions fail (like VLOOKUP() if it presents a numeral vs. a numeral formatted as text). The fill cells are in that limbo right from the start.

    Interesting. But should be fixed. Or explained and its benefit expounded on.

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Roy commented  · 

    Part of the reason for someone to protect a formula is so that YOU, the user, do NOT see it. From reading about various protection elements over the years it seems likely MS is aware of this and the odds would be, given their pathetic protection features, that they would consider those users' proprietary concerns over yours, if only to not weaken/lessen and already sad feature set.

    They might even get sued if all of the sudden one of their major protection features was shot full of holes and spreadsheet creators felt they lost money due to it. A lot would not even be sellers of the spreadsheets, but rather bidders, for example, on public contracts. Documents, spreadsheets in electronic form included, are often available to the public and so their competitors could analyze their spreadsheets for "what's behind the bid" to better tailor their next bids to beat them.

    Who knows though, right?

  19. 518 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Great suggestion E Bow. Thanks for taking the time to tell us about changes you’d like to see – we’re listening. We’ll take a look at this one. As always, folks should keep voting for the things they like the most – we’ll generally be prioritizing things according to the number of votes they get.

    Cheers,
    John [MS XL]

    Roy commented  · 

    Along those lines Yaron, given the MS penchant for forcing "normal" windows on one, I guess to better push Windows vs. DOS. (Good Lord... but... yeah), one would think that anything that did NOT encourage people to use windows vs. full-screen program windows would be considered anathema at MS. Multi-monitor should just increase that penchant because now people could just really see that ol' windowing technology shine with 30-40 programs running at once! Yay! People would dump DOS like it was a lead brick!

    So one could wonder how this got past that "filter" and came to be left to lie for so long.

    Roy supported this idea  · 
    Roy commented  · 

    Yay, four year old Admin comment, bet this one is at the top of their list, no matter how many votes it gets, absolute top of their list, yes sir.

    When the age of the Admin comment (assuming there even is one) gets near to being written in digits, not text, in proper English, you know they care very much.

  20. 108 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 suggestions folks! We will be adding [if_not_found] as the 6th argument to XLOOKUP. The thinking is as follows:

    • It’s a reasonably common use case
    • The ISNA XLOOKUP approach cannot distinguish a “not found” from a #N/A returned from a valid match.

    - Joe McDaid [Excel]

    Roy commented  · 

    @George:

    Had that thought too when reading ththis suggestion. I imagine a lot of folks did.

    But having no match would be, by far, the most common error "in production" with a function of this kind. And testing particularly for that problem means having the calculation twice in a formula, complicating it, and having a small hit on performance for the times it does happen. (As in "if xlookup = error, then "", else xlookup.

    So having this particular error singled out seems like it would cut hugely down on complexity in the formulas so easier understanding and maintenance. And just less effort writing. The other errors really cannot approach this one's occurrence rate, even added together. I suspect they are actually not too common for anyone else either once outside the writing and vetting stage.

    So the particularity of having this error (only) dealt with inside the the formula itself seems pretty justified to me. And it reduces the ISERROR() testing to only needing to consider the other kinds of errors rather than have to test for this one separately and then lump the others together anyway, like one would using this, for handling.

    When thinking about it, I'd actually like all functions that need a match, but a match might not exist, to have this built in. Some can't fail, some kind of match will always occur, but I'd love to never have to trap this error for separate handling, on my own, again.

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

Feedback and Knowledge Base