Feedback by UserVoice

Roy

My feedback

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

    We’ll send you updates on this idea

    1 comment  ·  Excel for Windows (Desktop Application) » Other  ·  Flag idea as inappropriate…  ·  Admin →
    Roy supported this idea  · 
  3. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    You CAN do that. What you do is to Select All cells and format them ALL as unlocked/unprotected via the normal formatting dialogue box. Last of the tabs, make sure both the Locked and the Hidden checkboxes are unchecked.

    Then do the opposite for the cells you wish to have Locked: format them by checking the Locked checkbox. Leave all other cells alone.

    Now set the sheet protection and it will protect everything you checked Lock for, and nothing else. By the way, as formatting, it copies with a cell, so do it, enter a formula, then propogate the cell and its formula via pasting or filling and they too will be ready to be protected when you set the overall sheet protection.

    Yeah... that's pretty backwards feeling too, isn't it? Not only do you have to tell Excel to protect the cell, you have to tell it again and really insist this time...

    So..., it feels really backwards, really obtuse, but it works and is not a workaround, just a functionality operated from a backwards standpoint.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    It does. Scroll to the bottom of the Custom formats list and yours will be there along with any other Custom number formats you used in that spreadsheet. Ones you don't want there, maybe experiments perhaps, can be deleted so only the needed additions show.

    They are not then available from spreadsheet to spreadsheet, but creating 30 new ones a day and having to type it 30 times does not sound like the many "and over" instances you are talking about so it seems like this'd solve that.

    Be nice if that list of added Custom formats was kept outside the spreadsheets, in Options somewhere, so it could then be available to all spreadsheets, new or old.

    (I bet that's the source of the lack too: each added format gets a childish label like "34" which causes no problems so long as it's only in that spreadsheet and no other. Do a list outside the individual spreadsheets though and Excel could encounter on sheet where "34" is a bare time and another gets opened in which "34" was used for a 6-decimal number with "horse" added to the end. Cause no end of trouble. But they could overcome most of that if they did it like this but "jumped a level" on the childish labeling so they ensured uniquness and hence no conflicts.)

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    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.

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Sergei Baklan makes an awfully useful point.

    Roy supported this idea  · 
  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 supported this idea  · 
  11. 2,811 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!

    An error occurred while saving the comment
    Roy commented  · 

    Good point about flagging offensive posts. Like yours.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    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.

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

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

    No, no confustion. You stated:

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

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

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

    No confusion at all.

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

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

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

    An error occurred while saving the comment
    Roy commented  · 

    @Alek

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

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

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

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

    An error occurred while saving the comment
    Roy commented  · 

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

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

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

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

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

    An error occurred while saving the comment
    Roy commented  · 

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

    Important point:

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

    So the whole thing just doesn't matter.

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

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

    And now the wee counterexample to the main contention:

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

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

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

    Fin.

    An error occurred while saving the comment
    Roy commented  · 

    @Peter Collins:

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

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

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

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

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

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

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

    An error occurred while saving the comment
    Roy commented  · 

    @Jaime Segura: that's just not correct.

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

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

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

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

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

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

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

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

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

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

    An error occurred while saving the comment
    Roy commented  · 

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

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

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

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

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

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

    OK, standard complaint stuff, I suppose.

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

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

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

    And really, why?

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

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

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

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

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

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

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

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

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

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

    An error occurred while saving the comment
    Roy commented  · 

    Absolutely with the Named Ranges.

    Two things to remove:

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

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

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    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.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

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

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

Feedback and Knowledge Base