Conditional formatting - Apply to named ranges
Currently if you apply conditional formatting to a named range (i.e.. a range named via the Name Manager or a table style named-range), the value in the "Applies to" column of the Condidtional Formatting Rules Manger will revert the named range to its absolute cell reference.
eg. "Applies to: =Table1[Column3]" becomes "Applies to: =$C$2:$C$7".
Apart from being more difficult to read, it creates other complexities. e.g. if I happen to copy/paste a cell into this range, it creates a mess in the rules manager, creating a new rule for each cell pasted.
e.g. if my named range is 'Table1[Column3]' which corresponds to cells $C$2:$C$7, and I copy say cell C2 and paste it into C8, then in the rules manager another line is for some reason added with the same rule. So now I have one rule for range '$C$2:$C$7' and another one for '$C$9'. Why?!
Having one line for the named range would make things so much more simple.
Graham
shared this idea
Thanks for supporting this feature. We won’t be able to offer this in the near future, but we understand that it would be a nice improvement. Even though it seems like a straightforward idea, it is quite complex. Old versions of Excel will not support the new behavior, so there would need to be some hybrid approach that will be suitable for old and new versions. Also, named ranges can be dynamic, meaning that the range might change and conditional formatting rules would have to be adjusted when the workbook is calculated. There are lots of cases to consider and deal with properly.
We’ll continue tracking votes for this suggestion.
261 comments
-
Anonymous
commented
I agree with @ChrisC, & would suggest that when there is a lot of people raising the same question (do research on the internet on this subject to see how many pages it answers) with many people struggling to implement workaround this shows there is a need for a fix. I don't want a workaround. I want simple: in the range of the list of CF rules, why can't we have that the range name is not transformed into fixed range (=myrange is transformed into its value $A$1:$B$100 for example) and why can't we avoid excel to create new rules each time we insert a row in a structured table that is very nice and simple with 1 rule and thus Excel would not split this rule into 2. This is messy.
-
ChrisC
commented
Implementing this would be a great efficiency gain for users. It is a shame that Excel conditional formatting still doesn't honour it's own powerful feature of named ranges. Please consider this particular use and see how powerful it would be.
AllTableHeaders = Table1[#Headers],Table2[#Headers],... etc.
Create a conditional formatting for the headers. In my case I want all calculated columns to be noted by a different style of the head of that column. Easy to do with =ISFORMULA()
Now apply this condition to AllTableHeaders
-
Hảo Lưu Tuệ commented
Since everyone is sharing workaround (again) I think I'd share mine too. This one has very limited usecase, but I hope it help those in similar situations.
Assume that you only need CF to impress your bosses with fancy looking reports. They only look at it, maybe sort and filter something (otherwise just export PDF), but do NOT EDIT anything.
So you just put everything in a separated sheet and use simple formulas to copy to the reporting sheet (=RawData!A1, =RawData!B1, etc...), then format, create filter, CF rules, whatever you want on this sheet, then lock and protect the whole sheet. When you (or some other poor soul) need to edit, just edit the data sheet without any CF. That's it, very simple, right? Well... not exactly, MS will never let your life be that simple...
There's a catch: when a cell is locked and protected, sorting and filtering are disabled too. So you need a workaround for this workaround (fantastic): https://techcommunity.microsoft.com/t5/excel/allowing-filter-in-protected-sheet/m-p/2023688/highlight/true#M84736
TLDR:
1. Put all data in a separated sheet
2. Use formulas to copy to the reporting sheet (=RawData!A1, =RawData!B1, etc...)
3. Select the area (in reporting sheet), format, create filter, CF,...
4. Keep the area selected > Ctrl+1 > Protection tab > Check "Locked"
5. Keep the area selected > Review > Allow Edit Ranges (yes I know this sound stupid) > New... > type a name (Title) and password (the same password used for sheet protection)
6. Review > Protect Sheet > type password (make sure it's the same password in step 5) > check "Sort" and "Use Auto Filter" > UNCHECK "Select locked cells" (otherwise they can be edited because step 5) > (optional) check "Format columns" and/or "Format rows" to allow resizing columns/rows
7. (optional) in step 4 you can also check "Hidden" and hide the data sheet to cover your track (from less savvy users). When you need to edit, just unhide the data sheetPros:
No script
No more CF bs
Cons:
Again, limited usecase
Annoying popup when attempt to select locked cells
Can be slow for huge sheet
A workaround of a workaround (but you're using Excel, I assume you're used to that by now)
"Your report is broken, it doesn't let me edit" - your boss, every time -
anomalous
commented
Here is a bunch of workaround scenarios for this - hope it's useful to someone
-
Roger
commented
Second request:
On Aug 29, 2019 Lawrence Dunn said "I've attached a text file containing a thrifted version of my VB module".
But, there is NO attached file to that entry. Where do I find it? -
anomalous
commented
This (very simple) VBA User Defined Formula can be used to avoid all that row() and column() checking...
Function NW_IsWithinRange(CellReference As Range, RangeReference As Range) As Boolean
If CellReference.Row >= RangeReference.Row And _
CellReference.Row <= RangeReference.Row + RangeReference.Rows.Count - 1 And _
CellReference.Column >= RangeReference.Column And _
CellReference.Column <= RangeReference.Column + RangeReference.Columns.Count - 1 Then
NW_IsWithinRange = True
Else: NW_IsWithinRange = False
End If
End Function -
anomalous
commented
I have a solution to this - you won't like it but it works, it doesnt need VBA. I've used it for years without any problems, it doesnt noticably increase the file size or impact calculation times (insofar as I have noticed). My preferrewd solution would be for MS to enable =indirect() or Named Ranges in the range selection. But my approach does work reliably.
Apologies if someone already posted some variant of this solution, I havent read through all the comments (there's just too many of them).
So the solution is to shift the definition of the range from the *range definition* to the formula logic.
Firstly using a blank spreadsheet, create a conditional format set the range to cover $A:$XFD - ie every cell on the worksheet - then using a formula to determine which cells to format, maybe use this horror - you'll need a blunt instrument to crack this not ;-)
=AND(ROW()>7,ROW()<10,COLUMN()>4,COLUMN()<10,NOT(ISBLANK(A1)))
and set the format to be something obvious - perhaps a red background
enter a bunch of random text copy stuff anywhere and I guarantee you wont have any duplicated Conditional Formatting.The other way is to (also apply your conditional format to every cell in the entire worksheet, but use a separate sheet as a *map* of where the Conditional formatting should apply. I used a few other variations on these methods - but they all amount to the same thing - apply your conditional format to ever cell in the sheet and have overly complex formulae - I attached a simple example worksheet with 2 examples show - you'll work out the rest...
Cheers - NigelW
-
zojos
commented
Using named ranges is one (good) possible solution to this problem.... but a solution there must be. It is just intolerable that a adding rows within a range that has CF should cause such chaos in the CF rules.
-
Lawrence Dunn
commented
@Roger, @Andrew Ashurst & @Futski
I had not seen Celia Alves solution previously (Thanks Futski for the link). I've now watched her youtube presentation of the solution.
The solution that Celia is using is not too different to my own.
So Celia is setting up a CF 'master' in one sheet/tab that the user does not touch so it has no risk of being corrupted, & then copy's that master CF into a range on a separate sheet that the user is using & manipulating.
My approach was to write the CF rules directly into a macro, & run the macro to clear the CF & reapply when the rules become corrupted.
Both approaches use macro (VBA) to replace a corrupted CF. The difference is whether you prefer to create your CF in the normal CF tool, or create them directly in the VBA. Both approaches have their pros & cons.
For me, I'm reasonably comfortable writing VBA, so it made sense for me to write my CF rules in VBA directly. If someone is not so comfortable with VBA, then they may be happier using the normal tool to create the CF rules - but they still need to be comfy enough with VBA to create the code that is going to copy the master CF range to the users data range.....both solutions require a bit of VBA knowledge.
One advantage of writing the CF rules directly into VBA, is that it makes it pretty quick & easy to copy & paste a chunk of VBA copy & modify one of the parameters. For me, this is quicker & easier than using the normal CF tool once you already have the basics of the rule in the VBA code to copy/paste.....but for others, they may prefer continuing to use the CF tool to create their CF rules.
-
simao
commented
@Andrew Ashurst
There is alrady one for tables, few votes
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38601631-conditional-formatting-tables -
Futski
commented
@Andrew Ashurst, regarding the post from the "Portuguese/Brazilian" lady, I think you were referring to the following post from Celia Alves on Oct 22, 2019. It is definitely an interesting solution that I had not thought of before.
=======================
Celia Alves commented · October 22, 2019 5:29 AM · Flag as inappropriate
I also use VBA to solve this issue. I usually make the macro run on Save so that the next user finds the file properly set up.This video explains how I do it. If you watch it, please read the video description that includes important updates.
https://www.youtube.com/watch?v=pC98JKrfC3g
======================= -
Andrew Ashurst
commented
oh, I looked in the previous admin posts from ms, apparently "TABLES" are structured tables and are included, at least according to Steve of MS 5 years ago, in this proposition. Who knows? Another 5 years to maybe find out I suppose.
-
Andrew Ashurst
commented
As ms can be somewhat lawyerly in its interpretations of these requests perhaps we should start a new proposition "Conditional formatting - Apply to TABLES". If it doesn't already exist (no time to look now). I've found before with other issues that the vote is being split in effect as there are several threads really asking for the same thing but in slightly different ways.
I preferred another solution to Laurence's (his and it are back there in the posts somewhere). His looked complex and hard to fathom how it was doing what it was doing to me. I'm sure it works though. The other solution was a simpler approach but worked well enough for me (posted by a lady with a Portuguese/Brazilian name I think).
-
Roger
commented
Laurence Dunn, on Sept 10, said he attached a "blank Excel workbook that contains the VBA code"... Where is this file - how do I find it?
-
Andrew Ashurst
commented
There are a couple of macro/vba partial solutions posted by users in the previous comments here. One of them worked ok for me - at least it was a lot easier than trying to fix cf manually abeit not as easy as one day we hope it will be when ms fix this.
Aside from that I'd like to echo Joe's comment a few days ago. I think this thread started before Excel introduced "TABLES" (it would have been useful if MS had given them a more differentiating name so that those who don't use "TABLES" might realise that those who do are talking about something specific in Excel and not just any ordinary tabular arrangement of cells that haven't been defined by the creator of the workbook as a "TABLE" (perhaps a "mesa" or a "plano"). Anyway, the point is I think this conversation started when there were just ranges and named_ranges. But, now there are "TABLES" and it is there that I agree with Joe that MS should concentrate it's efforts to fix or improve cf.
-
Brenda
commented
OMG! This is exactly the issue I am having. I have a large data sheet and now I have multiple rules that were automatically created. Its taking forever to delete the extra ones and fix the original ones. Now this is an added step for me evry time I work on this spreadsheet to avoid accumulating extra rules.
-
Anonymous
commented
yes please
-
Joe
commented
> since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.
Maybe start with reference to tables? I think it would be enough for majority of the users.
-
R Lien
commented
I want to flag for blank cells in a table to encourage users to complete the table... conditional formatting? Not if I add additional rows or columns to the table.
I'd hoped this was fixed for years... guess not. :( -
Lawrence Dunn
commented
@Steven Soeder - well said!
I'd also like to add, that for each one of use here, who are probably the more advanced Excel users in our respective organisations, there are probably 100+ others in our organisations that have been struggling with Conditional Formatting's range fragmentation habit for years.
The folk posting & voting here are merely the very tip of the Iceberg.
I'm a Senior Engineer at Ford Motor Company & am only around 18 months away from having 3 decades of experience of nigh on daily Excel use. Conditional Formatting is one of those tools thats relatively easy for many users to initially setup & it provides a convenient means to highlight certain cells/rows/columns/ranges etc, but the average user will return to amend, or add new rules after a few weeks & can find the simple CF rules they set an absolute mess & slow & hard to rectify - I suspect most probably just give up.
