Feedback by UserVoice

Jon Wittwer

My feedback

  1. 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
    Jon Wittwer commented  · 

    Long formulas are very difficult to edit within the Rules Manager. So, either allow the formula field to be enlarged, or change the behavior of the left and right arrow keys, so that when you press left or right it scrolls the field rather than inserting a +Ref into the formula.

    An error occurred while saving the comment
    Jon Wittwer commented  · 

    A "Duplicate Rule" button would be a VERY useful new feature for the Rules Manager. I've wasted quite a bit of my life on the the task of duplicating rules the hard way (going to Edit Rule to copy the formula, then creating a new rule, pasting the formula, then editing the format, editing the Applies to, and then re-editing the formula after realizing that all references changed because I didn't have the correct cell selected).

    An error occurred while saving the comment
    Jon Wittwer commented  · 

    I'd like to see a "Consolidate Rules" button included in the Rules Manager that would automatically consolidate all identical rules.

    That may be too difficult to implement if it isn't easy to determine whether rules are identical or not. So, I've added the following as a separate suggestion (Make "Paste and Merge Conditional Formatting" the default Paste behavior) for helping prevent the problem of having dozens or hundreds of split/fractured/duplicate rules.

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/19676413-make-paste-and-merge-conditional-formatting-the

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

    Like Roland, the biggest issue I have with conditional formatting is that a lot of unnecessary separate rules are created when copying/pasting. For example, inserting a row and then copying formulas/formatting down is okay, but copying the row and inserting the copied row creates a redundant set of rules and splits the ranges in the original rule sets. This shouldn't happen, and it really needs to be fixed.

    Another Issue: If you have a Table with a conditional formatting rule that depends on a different column, trying to move that column via cut/insert can crash Excel.

  2. 884 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
    Jon Wittwer commented  · 

    This is an excellent suggestion! If the other conditional formatting suggestion for avoiding splitting rules doesn't end up getting done, then using a named range in the Applies To field would provide the way to avoid causing duplicate rule sets when copy/pasting rows and columns.

    Jon Wittwer supported this idea  · 
  3. 17 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
    Jon Wittwer commented  · 

    Excellent suggestion. You could also modernize the formatting options by using css-style formatting syntax. For example:

    =FORMAT(B3,"font-size:0.9em;font-weight:bold;text-decoration:underline;font-style:italic")

    Using css-style formatting syntax (or a syntax that was easily translated to css) would make it able to be compatible with Excel online.

    Superscript could be done with css (via vertical-align:top and font-size:0.75em).

    For that matter, why stop at just font formatting? You could create a function like =FORMATCELL("css-syntax") that would format the background color and cell borders as well (making sure to allow cell border width, not just color). If you wanted the cell to contain a formula, then allow FORMATCELL to have the syntax =FORMATCELL(formula,"css-syntax"). That would be awesome!

    Oh, and while we're at it, how about a new TEXT function that allows you to specify custom number formats such as "[red]0.00;[color10]-0.00;0;@"

    Jon Wittwer supported this idea  · 
  4. 730 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 again for taking the time to put it on this site and for the thoughtful followup comments. This is pretty related to some other work we’ve got going and already has a fair number of votes, so we’ll work on getting plans in place now and hope to get started on this soon.

    Thanks,
    John [MS XL]

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Jon Wittwer supported this idea  · 
  7. 51 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon Wittwer shared this idea  · 
  8. 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
    Jon Wittwer commented  · 

    I would like the function to handle daylight saving time (DST), meaning something like CONVERT(datetime,"Mountain","Eastern"), but that would not be trivial because of the change in the laws regarding DST over time. The function would need to be more than just a table of UTC offsets. Having a function that was able to handle DST in date/time conversions would be much more powerful than just applying a simple UTC offset, because handling DST is difficult to do in a spreadsheet with just formulas.

    Jon Wittwer supported 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

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

    We’ll send you updates on this idea

    Jon Wittwer shared this idea  · 
  11. 26 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
    Jon Wittwer commented  · 
    Jon Wittwer supported this idea  · 
  12. 649 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    When using Paste or Insert Copied Cells, conditional formatting rules should be merged by default. The paste behavior is currently inconsistent. For example, if you copy/paste a single cell, then rules are merged, but if you copy/paste multiple cells, then rules can become fractured or split (meaning that duplicate rules are created for the newly pasted cells).

    There is already a precedent set for using merged conditional formatting as the default paste behavior: Google Sheets has been working this way for a long time and it works well.

    Fractured or Split CF rules can be a serious problem. Consider the case where you are using one of the built-in data bar options to visually analyze data (see the attached screenshot). The sizes of the data bars are relative to the range of values in the Applies To field. If you insert a copied row or use other methods of copy/paste that cause the original CF rule to become fractured or split, then the relative sizes of the data bars will be messed up and lead to incorrect visual interpretations.

    The "Paste and Merge Conditional Formatting" (or the equivalent option via Paste Special) is not something most users will know about. It also only merges rules when pasting within or overlapping a range (not when pasting contiguously). The rules should be merged when pasting rows to the end of a table (an example of pasting a contiguous range), or when using Insert Copied Cells to append rows to a table.

    GS merges rules even if the ranges are not contiguous, meaning that if you copy A1 to C1, then the CF rule would apply to "$A$1,$C$1", and I think that should be the default behavior because it more closely models user intent (consider the data bar or color scale example).

    If merging non-contiguous ranges is a problem due to a limit on the number of separate ranges that can be listed in the "Applies To" field for a rule, then perhaps the automatic merging would apply only to contiguous ranges (cells pasted within, overlapping, or adjacent to the original rules).

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

    We’ll send you updates on this idea

    Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!

    Thanks

    Ashvini Sharma
    Lead Program Manager
    Excel

    Jon Wittwer supported this idea  · 
  14. 144 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
    Jon Wittwer commented  · 

    If the only type of conditional formatting is a gradient based on the series value, then color will just be a redundant dimension (because the relative sizes of bars or positions of data points already show the value).

    However, if you can create a rule set that uses a formula where references are relative to the series value range, then you can do some powerful things, limited only by what you can do with a formula.

    Consider the following series for a column chart:
    SERIES("Name",Sheet1!$A$1:$A$10,Sheet1!$B$1:$B$10,1)

    The following might be a rule to color the bars of the chart a certain color if the category name contains the letter "g"
    Rule: =NOT(ISERROR(FIND("g",A1,1)))

    The following rule might be used to color the bars red if the value in column C is "high" (for example, if column C represented a priority level)
    Rule: =(C1="high")

    An idea for the GUI: In the Conditional Formatting Rules Manager, the "Show formatting fules for:" field could include a list of the valid chart series in the worksheet such as "Chart 2:Series1" ... which would allow you to easily add and edit multiple rules for that series.

    Jon Wittwer supported this idea  · 
  15. 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]

    Jon Wittwer supported this idea  · 
  16. 1,038 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks again for all the passion on this issue – we hear you and we’ll get someone on the team to dig in to the issue. I’ve seen a few related sub-issues while scanning over the comment section for this one, so we may reach out to a few of you for clarifications. Thanks again for all the votes, and keep them coming for the issues you care about!

    John, Excel

    Jon Wittwer supported this idea  · 
  17. 1,136 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    Jon Wittwer shared this idea  · 
  19. 72 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon Wittwer shared this idea  · 
  20. 7 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon Wittwer supported this idea  · 

Feedback and Knowledge Base