How can I sort data and still keep my Conditional Formatting?

Question

How can I sort my excel sheet and still keep my Rules?

Current Layout: Columns Needed: Infinite Rows Needed: A-G Currently

Locked Header A1-G1 Each labeled for different info. Example: A1 is Labeled "Discipline"

First Column Rules:

A2: =$A2="BCBA" Applies to: =$2:$1048576

A3: =$A3="OT" Applies to: =$3:$1048576

A4: =$A4="PT" Applies to: =$4:$1048576

A5: =$A5="PSYCH" Applies to: =$5:$1048576

A6: =$A6="SLP" Applies to: =$6:$1048576

A7: =$A7="SLPA" Applies to: $7:$1048576

A8: =$A8="SW" Applies to: =$8:$1048576

When I sort using A-Z or Z-A sort the rows lose their rule.

Goal: Be able to sort any column A-Z & Z-A but to have the rows follow the assigned rule in column A

I have done this in the past but for some reason this new excel sheet will not comply! :(

Answer 1

To it the same way, but apply the rules starting the first row.

Select A1

Open the CF manager

Change the range for each rule applied to =$1:$1048576

Your rules changes to

=$A1="BCBA" Applies to: =$1:$1048576

=$A1="OT" Applies to: =$1:$1048576

=$A1="PT" Applies to: =$1:$1048576

=$A1="PSYCH" Applies to: =$1:$1048576

=$A1="SLP" Applies to: =$1:$1048576

=$A1="SLPA" Applies to: $1:$1048576

=$A1="SW" Applies to: =$1:$1048576

Now you can sort the data any way you want.

Andreas.

Answer 2

Hello Khristina,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

If you want to sort your data in Excel while keeping your conditional formatting rules, you can do so by using absolute references in your conditional formatting formulas. Absolute references are cell references that don’t change when you copy or move a formula. In Excel, you can create an absolute reference by adding a $ symbol before the column letter and row number.

In your case, it looks like you’re using relative references in your conditional formatting formulas. For example, the formula =$A2="BCBA" uses a relative reference to cell A2. When you sort your data, the reference to cell A2 changes, which can cause the conditional formatting rule to be applied to the wrong cells.

To fix this issue, you can change your conditional formatting formulas to use absolute references. For example, instead of using the formula =$A2="BCBA", you can use the formula =$A$2="BCBA". This will ensure that the conditional formatting rule always applies to the same cell, even when you sort your data.

Best Regards, IBHADIGHI

Answer 3

Counter question: Where is the problem supposed to be?

The screenshots are from a German system, sorry for that, but your rules are so simple so IMHO that should not be an issue:

"PT Nr 2" is not colored because your rules said it should color "PT" from row 4 and below.

Now let sort by "Nr" ZA and we get:

Your rules are intact and in this case "PT Nr 9" is not colored, due to the same rules!

From my view there is no issue at all, Excel works perfectly fine!

Andreas.

Answer 4

When attempt to use this formula I get this issue. What should try?

Answer 5

I appreciate the reiteration that I do not know what I am doing lol.

I am trying to color code medical disciplines that I can alphabetize by column depending on needed information.

What do you suggest?