Feedback by UserVoice

Roy

My feedback

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

    They already are decimals, from beginning of existence to deletion.

    When they LOOK like times, they are just being displayed as such.

    To see the decimal version, just format the cells to display in any number-type format you like. Or just General.

    Since they already are decimals, you can do math with them regardless of their displayed appearance. So 5:14 am + 11:13 am = 4:27 pm, whether displayed as times, or as decimals.

  2. 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  · 
  3. 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 does, in a way, provide a mechanism for this.

    Not a setting, per se, like we would usually look for and like the decimal thing you mention. Rather one needs to open a spreadsheet, then make this and any other changes to it that one would like to have be in every new spreadsheet, and finally, save it as the Normal template that Excel bases each new spreadsheet on.

    Details can be slightly different from version to version, mostly in where the template is.

    It really ought to be more accessible, and wouldn't several templates to choose fom when opening a new spreadsheet really be more interesting? Or check a box to only have the normal one created if not interested. But no... not here... (sigh...)

  4. 6 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Sergei Baklan makes an awfully useful point.

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Hi Joseph,

    We are now shipping a feature called Sheet View on Excel for the web that can help you sort and filter while collaborating with others. You can accomplish your tasks in a Sheet View without others impacting your workflow. Learn more here: https://techcommunity.microsoft.com/t5/Excel-Blog/Sheet-View-is-generally-available/ba-p/1032576

    We’ve started the rollout to users on Excel for the web and will be available to all Excel for the web users within the coming weeks.

    Thanks!

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

  10. 1,695 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  · 
  11. 786 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  · 
  12. 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.

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

    We’ll send you updates on this idea

    Roy commented  · 

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

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

    We’ll send you updates on this idea

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

Feedback and Knowledge Base