Improve the Conditional Formatting Manager
So that we can easily manage conditional formatting. While we're at it, the way conditional formatting is treated when cutting / copying and pasting etc could be reconsidered.
[Following examples added by Dan [MS] for clarity on 10/20/2015]
* name rules
* add a comment to rules
* more easily see what rules apply to what range
* see which rules can be consolidated b/c they are similar
* etc.
Levi Bailey
shared 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]
62 comments
-
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.
-
Tina
commented
We have been asking for movement on the conditional formatting manager for 4.5 years now. I honestly don't think MS is reading any of this .... "excel.uservoice" isn't being listened to.
-
shan
commented
PROVIDE AN UPDATE MS TEAM!!
This is ridiculous. As many, many, many people have commented, at least implement the 1 thing that SHOULD be EASY:
1. ALLOW RE-SIZE OF THE CR WINDOW.
I cannot believe I have subjected myself to typing in CAPS LOCK since this causes so much grief for us all on a daily basis.
2. ALLOW FOR COLUMN RE-SIZING within the CF window.
3. ALLOW FOR USING ARROWS TO NAVIGATE THE FORMULA BAR in the CF window without having to remember to press F2 first. - seriously, why the **** is this even a thing in the first place?
Additional asks:
5. Allow for duplicating of rules
6. Allow for multiple conditions in a formula in teh CF window rather than having to set 5 different rules with 5 different formulas to have a range of success display (a pretty common business need) - e.g. Red, Orange, Yellow, Green, depending on what data displays in the cell.
- People of the internet, if there is a way to do this, please let me know as I would be forever greatful! -
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?
-
Ryan Jewell commented
Seems that someone forgot to finish CF in Excel. Did the OG coder keel over?
Know how I edit my long conditional formatting equations? Here is what I have to do:
1) Copy what's in the "small" entry box.
2) Paste into a blank worksheet. Do all the editing there.
3) Recopy and paste back into the CF entry box.Why? Well, among other things, you cannot edit the middle part of a long CF equation! Seriously...the box is so small. ARROWS do not work to move the cursor (it types cell references!!!!), so you have to do it surgically with your mouse, which is about impossible. Guess what happens...it zips RIGHT past the middle of the equation, so it's as if you can only edit the beginning and ending parts! Ridiculous.
-
Anonymous
commented
Very good list Andreas. Fully agree with it 100%
come on excel! listen to us!also, if you could toggle 're-order by selected formatting if true' then it'd be easier to rejoin fragmented rules into 1 again.
-
Andreas
commented
Fully agree with Kevin:
CF is very powerful but seems left behind. Here is my list of needs for CF.
- Make window resizable
- Don't fragment rules when cut/copy/paste/insert/delete rows
- Make formulas work like regular formulas (error checking, auto complete, result preview, volatile only if formula used is volatile)
- Allow referencing of named ranges and structured table reference
- Make search/find feature look inside CF formulasmaintaining many CF is a nightmare!
-
Eckhard
commented
Errors (--) and deficits (-)
-- Conditional format causes crashes when applied within a structured table.
- Applies to" allows the specification of name ranges, but converts the named range to coordinates.
The loss of the Named Area is very annoying because you can no longer model efficiently. - In contrast, the reference to the named area remains the same during data validation.
- Styles (Custom styles) are very helpful for the systematic formatting of Excel. However, they are missing in the "Conditional Format" function. There, the benefit would also be very great. -
Stephan Keller
commented
At least make the dialog window resizeable asap, yes, I mean ASAP!
It can't be that hard to implement that.
When I see all the other changes MicroSoft implements to the UI of Office365 (of which, not all of them makes Office365 more user friendly), it just seems to me, that they set wrong priorities on their backlog of tasks.
How many votes must a suggestion receive, until it will be implemented? -
Kevin Osborn
commented
CF is very powerful but seems left behind. Here is my list of needs for CF.
- Make window resizable
- Don't fragment rules when cut/copy/paste/insert/delete rows
- Make formulas work like regular formulas (error checking, auto complete, result preview, volatile only if formula used is volatile)
- Allow referencing of named ranges and structured table reference
- Make search/find feature look inside CF formulas -
Phil
commented
Please also include protection of conditional formatting from copy/paste actions in protected cells
-
Anonymous
commented
why do we bother?
this will clearly never get done, it seems.
even the simple bits that would be so quick (e.g. window re-size, or re-order by format)...
yours frustrated
me -
KBL
commented
Please make the formula text window re-sizable. With long formulas, its hard to see
-
R
commented
TOTALLY AGREE, IT IS STILL PAINFUL EVEN IN 2019. SMH
-
Jay
commented
At a bare minimum, make the window resizable. This is a quick, simple win that shouldn't be held up by any 'stretch goals' for the complex management of conditional formats.
-
Ron Kaminker
commented
Conditional formatting is so cumbersome to use.
In the rules manager,
a. you can't see all of the ranges to which it applies
b. it is really difficult to edit
c. when you move cells, you have no idea which cells are being included/excluded from the conditional formattinge.g. I have the below, for my conditional formatting
=$E$124:$N$129,$E$298:$N$298,$D$290:$N$297,$E$36:$N$37,$E$107:$N$122,$E$277:$N$289,$E$296:$N$296,$E$195:$N$198,$E$132:$N$182,$F$199:$N$199
-
Christopher Hazel
commented
The reason I mentioned the comment is that it's probably low hanging fruit. I've had reason to play around with CF in VBA recently and I don't remember the specifics of the object model for CF but the fact that they can be listed in a dialogue box gives you the first clue that they can be iterated as a collection of objects. I figure adding a comment property to an object that already exists in the object model shouldn't be a big deal, and adding the contents of that property to the iteration in the dialogue box should be similarly pretty easy.
-
Roy
commented
Oh, and an Add-In could create those NR's one specifies if they do not already exist. Further ease our pain.
-
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?).
-
Nick
commented
Conditional formatting is powerful but a mess. It is easily the thing that makes a 1 hour edit for me a whole day hair pulling affair. So many good comments and suggestions here. Giving back to the community, I found saving a bespoke word Doc (per range, tab, workbook depending) works well. Have a table in there with all the c. f. cells and formulae and formatting. Find+replace helps with duplicating similar rules or ranges. Yes this is an additional step, but it has saved my sanity. Otherwise, great product, MS! (edit: does anyone else's rule formula cell ranges change to some random cell miles away from where I'm working? Drives me nuts).