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

    An error occurred while saving the comment
    Roy commented  · 

    Wow. It USED TO persist, but somewhere along the way, it stopped that. (Sigh...) As you can maybe tell, I prefer the default and only use it now and then.

    Oh, also, as one would expect, this is available in Find and Replace, as well as Find, since they use the same functionality.

    An error occurred while saving the comment
    Roy commented  · 

    The default setting for Excel does exactly what you mention. It looks only at the "formulas" (as it puts it but really it is looking at any text ("constant") entry as well as the individual parts of actual formulas). It does NOT look at their resulting values.

    However, there IS an easy to locate when you realize where it is setting that will do just what you want. It will persist as well, as long as the Excel session is open, but each time you start Excel, you will need to remember to change it.

    When the Find and Replace box is open, the lower right has a button labelled "Options" and you want to click that. The box enlarges and there is new material in the lower left corner. Three dropdown choice things and two checkboxes.

    You want the lowest dropdown selector, and may note it currently says "Formulas", but click it and select "Values" from the choices.

    That's it, now it will find formula results that match your search criteria as well.

    One related item: the second of the two checkboxes. By default unchecked, it allows any part of a cell to trigger a result, checked it requires the entire contents to match your search string.

    In the unchecked condition, searching with the default "Formulas" will find an entry of "1000" AND an entry of "=1000". If you check the box, it will NOT return "=1000" because the "=" is not part of your search string. So you can distinguish between those kinds of contents, if you ever want to.

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

    We’ll send you updates on this idea

    29 comments  ·  Excel for the web » Formatting  ·  Flag idea as inappropriate…  ·  Admin →
    Roy supported this idea  · 
  3. 339 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  · 

    @shan:

    Fully understandable on their part mostly, but not really here as this is an actual MS product from a purchase they made nine years ago. And conceptually, tools FROM MS that help make spreadsheets MORE reliable... well, they ought to fall all over those!

    Spreadsheet reliability and accuracy are big issues these days.

    Could be worth asking. They may say no, but that's probably the worst they can do, right?

    An error occurred while saving the comment
    Roy commented  · 

    @shan:

    MS has a web page showing how to activate their addin and a minor amount of info about how to use it at:

    https://support.office.com/en-us/article/analyze-a-workbook-with-spreadsheet-inquire-5991e8fa-f1c1-401a-ae3f-469384ae3e3b

    They have much more info about using it at:

    https://support.microsoft.com/en-ie/office/what-you-can-do-with-spreadsheet-inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

    (Both of those are MS sites, in the Support.Microsoft.com family.)

    UNFORTUNATELY...

    This was an addin offered around the time Excel 2013 was current and somewhen along the way, Excel removed the addin from downloading.

    On the following website, MS says it is still available for "...Office Professional Plus and Microsoft 365 Apps for enterprise editions." And that even if you have access in theory, IT people where you work may have removed access to it:

    https://support.microsoft.com/en-gb/office/turn-on-the-inquire-add-in-6bc668e2-f3c6-4729-8ce1-75ea20aa9d90?ui=en-us&rs=en-gb&ad=gb

    So...

    If you follow directions on adding it and it is not showing in the dialog box AND you have a suitably (expensive) option filled (expensive) version of Excel (expensive), you might ask your IT people if they have removed access to it and if that was intentional or just a careless setting of a setting that they can fix.

    Failing that, a web search might locate someone with a copy available for download. Be sure to look for a copy with dates more recent than Excel 2013-time just in case it was originally pulled (for release of Excel 2016) due to problems interacting with the update. The version available (for rich people and monopolists) would presumably fix anything like that if anything like that even ever existed.

    But barring those two things, this is not for the hoi poloi.

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

    We’ll send you updates on this idea

    Thank you for taking your time to suggest and vote for allowing interaction with Excel while in Power Query Editor window. We do think that this suggestion has merit but we don’t think that we’ll be able to devote time to it in the near future. We’ll continue tracking votes for it.

    Guy Hunkin
    Excel Team

    An error occurred while saving the comment
    Roy commented  · 

    Shift - Shortcut Icon works too.

    But such things would leave a person shy here as Chris Newman needs the file open in the original so could not open it in the second/third/twentieth instance.

    However, one can open multiple windows for a single file. I have not tested this with PQ so conceivably one could open 30 windows on the file and they all get Bogarted by PQ so none of them can be seen. Hopefully not. Since they should themselves also be SDI (yuck) windows, one should be able to size things on a single monitor or drag the "see" window to a second monitor while using PQ in the original one on the first monitor and then see both views, PQ and the file. Interactive too, maybe, and likely nicer than screenshots though whether it is better than screenshots I cannot say.

    The multiple instance of Excel thing used to be really useful now and then. Open three files with the same name, different directories? Use multiple instances. Couple other nice things. But since only one application can have a given file open at a time and SDI makes Excel and Windows see the separate instances as utterly foreign to each other programs, it seems unlikely it would let the second one open the file too.

    Maybe if the file were set for sharing. Excel shouldn't care that the others are all "on the same computer" as they are just fully separate instances on the same computer.

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

    While you are waiting, you can force the in-cell behavior to work out by adding a wee bit:

    =MIN((ROW()=ROW())*1)

    The TRUE is returned in the Row()=Row() parentheses, then the *1 forces it from TRUE to a 1 and MIN() can act on it properly inside the cell while you do each part with F9. Since Excel eeks out the right behavior when calculating on its own, it might not be of too much interest. But it is useful for the same purpose elsewhere, and even could be helpful for this kind of looking into a formula for where it fails.

    By the way, Excel does this a LOT with its interim results in a formula. Returning an internal array like {"1","2"} instead of the expected {1,2} can dynamite a formula when Excel does not, as it does with this one, figure out how to get the right result from those internal strangenesses.

  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  · 

    You can use:

    =TRANSPOSE(SEQUENCE(4,3,1,1))

    to generate the exact thing you use as an example, and modify it for similar production.

    The only trick here is since you are having to use TRANPOSE() (because SEQUENCE() seems to want to go across, then down instead of down, then over a column, etc.), you have to do "columns, rows" in your SEQUENCE() function instead of the Intellisense help nudge for "rows, columns"...

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

    Alright, so the trick for constructing a partial table was eluding me, but not anymore!

    Stuck on using FILTER() which I just plain like a lot, but it has the problem of taking the "upper, leftmost" address and "lower, rightmost" address from any and all given when trying to set up just some columns and dynamites you by using the full range from the one to the other including ALL columns in its return. Then you have to restrict which ones some other way.

    INDEX() is that way. You can specify the oclumns via the headers which you have at least five ways to do. Putting the work in Named Ranges and using something like:

    =INDEX(Table,SEQUENCE(ROWS(Table),1),AllowedColumns)

    will limit your ClerkTable to only the columns in the AllowedColumns name and that can be as simple as:

    ={1,3,4,5,7,8}

    if you then use that to get the "real" header names. No problem doing that either.

    Then FILTER() can be used to return just the set of records desired. It allows more than one condition for inclusion and they can be set up as an OR instead of an AND so you wouldn't have to do some all people in a city return to get Jim and Abdul records in the same return table. This allows setup of reports as well as working with actual data (add/edit/delete type actions via controlled forms).

    I say "then" but it seems likely the easier route, computer work-wise, is to do the FILTER() on the source data, then INDEX() on that rather than the other way around.

    Not great for security though as one could try a couple ways to draw excess information. Using INDEX() first means the excess simply isn't there to be drawn out. Then FILTER() for the work.

    In any case, it all gives you a full table return if desired. 2-D, naturally. Andone can use any desired tool upon it, VLOOKUP() included.

    To minimize file size and keep the data from being included in any version of the worker's file that could be taken on removable media or emailed, etc, one could write several macros that, for instance, create and populate the work table on a hidden sheet when the file is opened, and delete it before any save can be performed. So the data only exists while in use and never when being saved. Smaller file size, very small being very possible, and no data transferrable since it never exists anywhere accessible to the user.

    An error occurred while saving the comment
    Roy commented  · 

    By the way, if data security is important, you can have your actual table of data, then use FILTER() to create a Spill Table™ wherever convenient, protect them both (hide, protect, etc., use FORMS for data adds, edits, and deletions). The Spill Table™ would be used for the USER's Spill Table™.

    The data could never then be touched by a user if the user did not have permissions for alterations via your Forms. It might be hard to limit what a user sees with standard table layouts, but if you add columns that store various data your Forms save with each record creation or alteration, one could be able to simulate various permission schemes.

    The user's Spill Table™ would be like traditional filtering but without the current hassles that presents as one filter changfe affects everyone (presently). So even just for that point, the idea has use.

    The user's Spill Table™ can be sorted via SORT() according to his taste and needs. It can be further limited by a slicer-like concept for each column.

    Your forms could verify the record asked for is present in the user's Spill Table™ so that would cut off a huge way of accessing records they have no rights to.

    One problem with this simple version is that FILTER() seems to return ALL columns for each record returned. One can get past that by creating the intermediary Spill Table™ by using FILTER() column by column for the columns of the source table one wishes to present, then use FILTER() on that intermediary Spill Table™ in the manner described above. It's just the formation of the intermediary Spill Table™ that is different.

    One could obviously have various intermediary Spill Tables™ to serve a variety of users and needs.

    But yeah, it does seem careless of them to have not allowed for 2-D spilling.

    An error occurred while saving the comment
    Roy commented  · 

    If you are looking to recreate an entire table elsewhere,

    = whole_table_range

    will do it. (For "whole_table_range" enter whatever you use to reference the table, cell addresses, a Named Range, a Table name, whatever.)

    (If you prefer a formula to a simple range, then

    =FILTER( whole_table_range, whole_table_range=whole_table_range )

    will do it.)

    If you want to do a lookup and return ALL the potential matches in the order they appear in the source data, rather than the entire table as above, then

    =FILTER( whole_table_range, whole_table_range=your_lookup_value )

    will do just that.

    So either the whole 2-D table, or a 2-D table of lookup results, are easily placed elsewhere without copy and paste.

  8. 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  · 
  9. 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  · 
    An error occurred while saving the comment
    Roy commented  · 

    It'd be a lot nicer though to be able to just do it directly instead of using these workarounds! So you have my vote either way.

    An error occurred while saving the comment
    Roy commented  · 

    I could be misunderstanding what you have in mind, but I shall bull through anyway...

    The usual technique for AND-ing is to multiply one array (adjusted to a 1-0 format) by another (also adjusted. So TRUE's times FALSE's or any of the other three combos producing an output array of 1's and 0's to multiply a data array by giving the final answer or input forward in your formula.

    The key to AND-ing is the multiplying. One or more FALSE's in the source/s will give a 0 (failed) while all TRUE's result in a 1 (matching the idea with AND).

    The similar idea for OR-ing is to ADD, not multiply. Then, even a single TRUE will ensure a non-zero result. Since Excel really looks at 0 as FALSE and ANYTHING else as TRUE rather than only 1 as TRUE and all other results leading to errors, 12 TRUES will result in 12 but it will still be seen as just TRUE in the end by Excel so the output (answer or feed forward) will be TRUE even if only 1 source item is TRUE. Which is the idea with OR.

    If that is not on point, disregard it! If it is, then the takeaway is:

    OR: use ADDing for the input arrays
    AND: use MULTIPLYing for the input arrays

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

    You CAN highlight text within a cell at present, have been able to do so for aa LONG time.

    Press F2 to edit in the formula editor and highlight the text you wish to format differently from the cell formatting. Right-click and edit to your heart's content. All the possible font formating is available.

    Make 2 letter 36pt while the others are 12pt. Bold some. Italicize. Superscipt and use strikethrough. Like Prince, go crazy, get nuts,

    For a pretty interesting effect, Format the cell for Horziontal: Distributed (Indent). Enter three words with spaces between them (or more words). Drag the border of the cell to allow them to all display on one line, not wrap. Then edit the last word, at least, just one letter will do, a color change is easiest. Watch how it suddenly jups to overflow the cell border. Well, stuff does that all the time, right? Nay. Put some text in the cell to its right, should curtail that spillage, eh? Again: NAY. It goes right overtop whatever's there! Been so for a while. MS. just never fixes it.

    Important part is, it can't happen without editing as you describe above, some (at least one letter in this case) of the material in the cell, formatting it differently from the rest.

    (Oh, now stretch the width some more and watch the spillage widen out.)

    I've used Excel for what you describe for 15 years.

    I will make a last note though: It does NOT work if you have a formula in the cell. Period. At all. So you can't edit text that appears in a formula, perhaps an error message, in this manner. You can achieve some effect with conditional formatting but it's still an all or nothing thing: no "STOP!" in red followed in the same error message with "Call Jim." in black.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Also, for the more complicated ones, building your expression via concatenation (any of the various ways) can work wonders with things that you can find a pattern even in just parts.

    For instance, for the shown example, you might enter the following in a row of cells in the columns needed:

    ="A1*" & CHAR( 64+COLUMN() ) & "1"

    It will add 64, plus the column number (2 for column B) and get some value (66 in this case) CHAR(66) is "B" so it then "unions" the three elements (A1*, B, and 1) to give "A1*B1" (without the quotes, of course. Then a formula like TEXTJOIN() can combine all of those in the range you copied that to. It can even use "-" for a delimter to make this one even easier.

    Finally, you copy and paste the value of that result, then press F2 to edit in the cell, and copy the whole string. Go immediately to where you are forming your formula and paste it in.

    Prepare for that by starting with ' or just no "=" and "write" it in words (like ' prices*quantities - 4% of prices*quantities...). Then as you have finished building the component strings, paste the in replacing the word version of what they are, so in my example here, you'd paste something in place of "prices*quantites" in two places. Use spaces between things to make them easier to separate in your mind as you do this.

    Things like my example's "4% of", I'd actually suggest writing "properly" (finished version (" *.04 " or " *4% " (yes, the second one works and is more obvious when maintaining the formula over time)).

    And some bits would be MORE work trying to do this with. Just do the main parts, and when it looks like the rest would be easier to simply type in, simply type them in. Finally, remove the ' or add the "=", whatever you did to keep it from being a formula as you worked on it.

    Hopefully all was done as needed, went well, and Excel does not claim an error exists!

    I've used this for some very long concatenation strings before TEXTJOIN() came along and hardly ever use it now. But if the problem is formed of patterned parts that are long enough to not just be simply typed, this can be a dream.

    But algebra first!

    An error occurred while saving the comment
    Roy commented  · 

    Well, the EXACT formula you have shown can be manipulated algebraicly to be:

    =A1 * [B2 - (SUM(C1:AP1)]

    Of course, it might be a simple representation of what you mean and not a much more complex example of what you really face, but one should always remember algebra is your friend even in Excel.

    When appropriate... it won't help much with the average VLOOKUP() or any at all with TODAY().

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    And if it "already does that"... just badly or ineffectively so that I've not noticed its Herculean efforts on my part... then I'd like it to do it better. Much better.

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

    I'm guessing this is "Alt-Enter" in a cell in the US version.

    And yeah, it'd be REAL nice to have this fixed. My own experience with the US version is that the amount of extra space is not a seemingly arbitrary amount, but either doesn't happen or a FULL line's worth of extra space is put in.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    The usual reason for something like that is that the string functions, like LEFT() is, give a TEXT result and it turns out that the data in the VLOOKUP() table is formatted numerically. Even then, Excel nowadays often works it out.

    The second chronic difficulty is when the lookup table is populated from outside data sources, web sources, "web-scraping" in particular being frequent problems. (Web pages contain a ton of characters that help arrange the data how you see it and these come along with the data, depending on how one imports it. Some are characters you can see in the cell, others never display but they are there. Even actually (not just thinking you were successful) removing them all still leaves Excel thinking the cell is TEXT, although no text remains AND you reformatted it and F2'd it or did the Paste|Special|Multiply trick.) Lots to look at there, and the CLEAN() function is usually useless. The failure would be that the cell does NOT actually match even though it LOOKS like it does. Particularly, when Excel sees a perfectly cleaned cell like that which it still sees as TEXT, it never tries to "work with it" like above and power through it so it yields the error.

    Good things to check on.

    Of course, without seeing actual data in the actual spreadsheet, even just 2-3 cells of it, one cannot narrow it down.

    But there is no chance it is a bug. None.

  20. 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  · 
1 2 5 7 9 20 21

Feedback and Knowledge Base