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

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    You are seeing a common problem with data sourced from somewhere outside your control, or from a poorly behave program.

    Your "471420" is actually preceded by the Unicode character 8237 which is not, of course, a number value. (It's a zero-width character. It takes no display space. You CAN, however, always press F2 to edit the cell, hit HOME to start at the beginning, and use the right arrow key to move... right. Watch closely and when you encounter these the arrow key makes the cursor beastie flash as if it did your key press, but then seem to remain in the same place. In fact, it just encountered one of these.)

    Since it is a string containing at least one character not usually associated with a numerial value (things like commas, periods, the "E" or "e" used in scientific notation, that kind of thing), the cell is treated as text. Nothing that is done which lets the zero width character remain will change that. So SUM() treats it as 0. VLOOKUP() would fail to find it if searching for just the numerical version since, well, it doesn't match, eh? It will sort oddly as well.

    But this is not a bug. It is bad data provided by something. If it came from web-scraping, I guess that's your fault. Anything else is bad programming by the program that provided it. Or you might have some passive-aggressive scum working for or with you that is screwing you. Of course, he's just trying to up your game, right? He doesn't need beaten badly or fired, right?

    No bug though.

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

    We’ll send you updates on this idea

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

    No, it is not.

    Multiplan/Excel was competing with Lotus 1-2-3, and 1-2-3 had a programming mistake (not actually their fault, they sort of were doing the same thing Excel chose to do) in this matter so Excel (Lord, I wonder if they remember those non-Mighty days?) found it expedient to program their date system to simulate the mistake existing in 1-2-3.

    Mac users have a different date system altogether simply to avoid this whole issue. Not address it head on and damn the consequences, we're Macs!... No, they just avoided it altogether by starting dates that can exist, so to speak, in 1904, avoiding the leap year mistake being made vis-a-vis 1900.

    So no one really had any courage and we live with the results today. And clearly, will for thousands of years to come.

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

    We’ll send you updates on this idea

    Roy supported this idea  · 
    An error occurred while saving the comment
    Roy commented  · 

    Experimentation shows it must be a somewhat different thing than you are deducing.

    When I do exactly as you say, I indeed cannot even click on the single letter "friendly" name. But that's because there is this arrow beastie for my cursor that is entirely reminiscent of the cursor change that is so annoyingly hard to get when trying to slect a table row in Word. (You have like a 2 pixel wide spot to see it. Maybe that's hyperbole, but not by much.)

    So I get that for 1-2 characters of friendly name, but at about 3, and almost always by 4 characters (not with 4 "l" (lower case "L") characters), I can click on a bit of it and get the hyperlink action.

    More tellingly, if I use a single "l" character but format the cell to indent by three (three "whatever" Excel indents by), the friendly name is moved far enough right in the cell that I CAN select the friendly name and go to the desired place.

    Another telling point is that if I do this with no Table, just a cell picked out of the blue, there is no strange cursor and no difficulty at all.

    So it is clearly an issue with Table programming in particular, not the hyperlink function. Needs fixing. Given that cursor and that (theoretically) how one gets it in Word when trying to select full rows or columns in a... wait for it... Table, it suggests Excel programmers did not make a solid effort with Tables, rather opting for doing some minimum to adapt Table funcitonality that Word already had programmed.

    Admirable, the connection so as to make the two heavyweights more interactive, one supposes, but not so much admirable the laziness or incompetence.

    Meanwhile, whatever the cause, it does need fixing!

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

    We’ll send you updates on this idea

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

    Used to be able to do that, treat the top row of a block as labels and the left column as well. then just:

    =cow pig

    (if those were a column and row "label") to get the intersection's value. You can still do it but have to define Named Ranges. Ad hoc choices, or choices arising from selecting or "building" the column or row header/label to use are not on the table anymore.

    Of course, Named Ranges let you use blocks rather than single cell intersections, but we used to have both!

    Without this, implicit intersection is a pale shadow of the past. Sadly, when they first announced the "spill" functions, they also said implicit intersection was going away. Not just be unnecessary but going away. They have not kept at pointing that out so maybe it won't, but if it does or is, you can count on never seeing this, in a function or just as general functionality. Sadly.

    Got my vote though!

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

    We’ll send you updates on this idea

    Thanks for the suggestion Levi! We’ll be taking a look at this along with some other asks around conditional formatting. It’s a big help to see the things with the most votes, particularly within areas like formatting. So please keep the votes coming for things you want us to do sooner!

    Thanks,
    John [MS XL]

    An error occurred while saving the comment
    Roy commented  · 

    It'd be nice to be able to select a style vs. building the formats for every rule piece by piece each rule. Would cut down on the proliferation of ever so slightly different formats in a spreadsheet, if nothing else.

    Be nice too, to be able to link a list (a "series" I guess) to those built-in CF's, rather than building a several rule set for every variation that arises. Grading scales, for instance. Soooo many different scales, and a big lower end compared to the slices for grades higher than "F"... my bet is that applies to a few million non-grading data sets.

    You know, about that F2 thing, if editing a cell formula (normal editing) required us to hit F2 a second time after entering the edit mode or seeing it act like it does in Conditional Formatting or Named Ranges, we'd still be using 1-2-3 for DOS and Quattro Pro for Windows. Although, conceptually, in a way, we DO have to do that... arrowing around from cell to cell, then hitting F2 to make the mode change to arrowing about inisde a cell... that's actually precisely what we do do isn't it? For the love of all things holy... well, I guess those two programs were doomed to die at the feet of the monopolist.

    An error occurred while saving the comment
    Roy commented  · 

    @Ryan Jewell:

    Actually, you CAN edit normally in their little box. You just have to know something they don't really stress anywhere: if you have entered the box you can press F2 and suddenly all the normal movement becomes possible. You can mouse about if desired as well, but the arrow keys now work. The F2 key is a toggle in this situation, so hitting it again restores the horrid mouse-only approach.

    Most of us likely do do any halfway long or complicated formulas outside, then copy in. However, that presents some issues one must be aware of. The first is that unless you carefully go through the formula first, you probably don't have all the absolute addressing you could want. You must consider that before pasting in. The second is that you may not have the relative addressing you need unless you carefully consider two things: 1) partial absolute addressing, and 2) precisely where you were (what cell) when you wrote the formula. The first of those is fairly obvious when you think about it, it's just you have to think about it. The second is more complicated.

    The second can also affect the first, by the way. The second creates trouble in the following manner. Say you want to affect cells starting with A1. You write the formula in C2. Any relative addressing in the formula will affect a cell offset from the current cell by -1 row and -2 columns. So when you paste it into the CF formula box and save, then expect it to apply to A1, you'll find it applies to something like XFC1048575. The trick here is to write it where convenient, C2 in this example, but then select the cell that precise writing should apply to, THEN enter the CF manager and paste that into the formula box. Then the references should be fine.

    With thoughts like that in mind, yeah, why ever write anything other than simple conditions in that miserable little box, eh?

    An error occurred while saving the comment
    Roy commented  · 

    Oh, and an Add-In could create those NR's one specifies if they do not already exist. Further ease our pain.

    An error occurred while saving the comment
    Roy commented  · 

    I don't use VBA much, and when I do I'm more like a "hunt and peck" typist than a "touch" typist, so to speak. So I don't know the tools it has for addressing CF. I DO know that CF is not attached to cells in the way normal formatting is, i.e.: the tools for formatting and working with formatting don't even reveal its presence, apparently.

    However, the places that say that last part seem to indicate it CAN be addressed, so perhaps there is a reasonable minimum of tools. If so, it seems an instruction to define a range to apply the CF to would be near the top of the "these be basic" list.

    Assuming so, one could create NR's for all the "applies to" boxes that could change, or simply all that are in one's CF (I know, that'd be a bear for some folks' CF-ing, but even still, this would greatly simplify their making sure the ranges stay right), and write a macro that changes the "applies to" for all rules by applying the appropriate NR to each.

    If a standalone macro, one could run it any time, especially right after a change one made that one feared would change things. It could be called from an OnOpen macro to make sure they are set right upon opening the file.

    So whatever happens, running it would simply reset them all to their NR's: I know Excel will instantly convert them, but who would care at that point? They'd be right, either way. Oh, "drag and drop" orphans would be lost, but again, who among us cares? We are not the people doing that.

    Someone with the skills and time could write an add-in that might list the rules interactively, let you define the NR's to use and where, and provide the macro to update them whenever along with an option for OnOpen.

    And someone at Excel could do that too, offered along with distributions the way the Analysis ToolPak is (Why would anyone ever not want that loaded? Why is it an option to load rather than to unload when first installing?).

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

    We’ll send you updates on this idea

    Roy shared this idea  · 
  12. 9 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  · 

    Ahh... if only Excel had such a thing. But it doesn't.

    PPT, sure, but not Excel.

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

    They'd be most useful in Tables due to the manner in which new rows are populated with formulas when being initiated.

    Outside of Tables, they'd not be much use in a pre-populated area as they'd take on a creation moment value that wouldn't really match a first-use situation. If one knew already what to put there, they'd be unneeded, so either way... In a user-created situation, like a VBA initiated creation, a simple line or two of code would solve that as it could enter the values desired when creating whatever is being set up. Only if the user is copying and pasting or writing the formula directly would they help then.

    Perhaps handier would be a broader thought: a FREEZE() function that calculates once, then never again. Tricky to logic out, but inside an IF() it could test for things like completeness of a record (for example, a full row of data vs. a not yet full row of data, in whatever manner made sense to the creator), and once the IF() found itself executing the FREEZE() option, it would calculate and never do so again.

    Well, something like that. But there ought to be a way to make DATE() or TIME(), at least, calculate once and never again without user intervention via some manner of editing or pasting over the cell contents.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    My God yes, this has aggravated me for decades.

    Roy supported this idea  · 
  16. 66 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's been literally a year and three months since announced and currently all those nice functions have not "spilled" even to all Insiders, much less to the world.

    (Let's hope they are actaully trying to get them right and not that they have unsolvable issues, yes?)

    Until then, let's hope for a simple, easy to do, solution that should have been here 25 years ago.

    Also, a number of those functions won't help in many instances (not necessarily this one, but many) since they have to "spill" and many uses won't have room for that. One of those, hoepfully intractable rather than unsolvable, problems, one hopes. An inability to be the sole cell the formula is in anyway rather than requiring the whole potential range of cells the data would take if in all of them, when the concept of a menu, say, suddenly appearing and dropping open is decades old, means fitting into existing as well as future work isn't possible. I'm not leaving 2,500 cells, say, empty just so a function can spill into them, sometimes yes, sometimes no. So...

    An easy to create, needed, should have been here 25 years ago, function like this seems very desirable.

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Great suggestion, thanks David! And thanks to other people who took the time to clarify/comment on this one. There’s definitely room to tighten this experience up in a number of places. We’re getting a lot of traffic on the site, so please keep voting for the things you care about most to help us do a great job of prioritizing.

    Best,
    John [MS XL]

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

    We’ll send you updates on this idea

    Hi Joana & others,

    Thanks for logging this suggestion. Yes, being able to protect your queries would be quite useful indeed for delivering solutions. We’ll prioritize this based on the interest, so if you’re reading this and haven’t voted as yet, please do so.

    thanks

    Ashvini Sharma
    Lead Program Manager
    Excel

    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