Excel - Conditional Formatting with symbols

up vote
0
down vote

favorite

I need to do formatting with flag in my table based on the next conditions:

1. Format in Green when I'm above the average otherwize paint in Red.


2. But if I'm above the average but have negative value, still format with red flag.

see my example

In the example I added we can see that in the CANDY column everything Ok.
In the SNACKS columns everything also OK, but in the DRINKS column OZI & MEIR are in green color although they are both have small values than the average.

How can I do this?
* I need to use symbols (or flags) and not fill all the cell with the color!

excel conditional-formatting

share|improve this question

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Is your example already made in Excel? If so, could you post which formulas you're using?
  – Unsolved Cypher
  2 days ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

favorite

I need to do formatting with flag in my table based on the next conditions:

1. Format in Green when I'm above the average otherwize paint in Red.


2. But if I'm above the average but have negative value, still format with red flag.

see my example

In the example I added we can see that in the CANDY column everything Ok.
In the SNACKS columns everything also OK, but in the DRINKS column OZI & MEIR are in green color although they are both have small values than the average.

How can I do this?
* I need to use symbols (or flags) and not fill all the cell with the color!

excel conditional-formatting

share|improve this question

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Is your example already made in Excel? If so, could you post which formulas you're using?
  – Unsolved Cypher
  2 days ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

favorite

up vote
0
down vote

favorite

I need to do formatting with flag in my table based on the next conditions:

1. Format in Green when I'm above the average otherwize paint in Red.


2. But if I'm above the average but have negative value, still format with red flag.

see my example

In the example I added we can see that in the CANDY column everything Ok.
In the SNACKS columns everything also OK, but in the DRINKS column OZI & MEIR are in green color although they are both have small values than the average.

How can I do this?
* I need to use symbols (or flags) and not fill all the cell with the color!

excel conditional-formatting

share|improve this question

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

I need to do formatting with flag in my table based on the next conditions:

1. Format in Green when I'm above the average otherwize paint in Red.


2. But if I'm above the average but have negative value, still format with red flag.

see my example

In the example I added we can see that in the CANDY column everything Ok.
In the SNACKS columns everything also OK, but in the DRINKS column OZI & MEIR are in green color although they are both have small values than the average.

How can I do this?
* I need to use symbols (or flags) and not fill all the cell with the color!

excel conditional-formatting

excel conditional-formatting

share|improve this question

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

share|improve this question

edited yesterday

edited yesterday

edited yesterday

asked 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 days ago

eliorb

11

asked 2 days ago

eliorb

11

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Is your example already made in Excel? If so, could you post which formulas you're using?
  – Unsolved Cypher
  2 days ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Is your example already made in Excel? If so, could you post which formulas you're using?
  – Unsolved Cypher
  2 days ago
  

  
  
  
  

Is your example already made in Excel? If so, could you post which formulas you're using?
– Unsolved Cypher
2 days ago

Is your example already made in Excel? If so, could you post which formulas you're using?
– Unsolved Cypher
2 days ago

add a comment | 

2 Answers
2

active

oldest

votes

up vote
0
down vote

You can edit conditional formatting just make first "conditional format" by "icon set" of each row then go "manage rules" and "edit rules" and change as photo.

share|improve this answer

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please re-check your conditional formatting because in my sheet shown is correct. check first image.
  – Shoaib Hossain
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  see my new example and why it is not OK
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
  – Shoaib Hossain
  2 days ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
  – eliorb
  2 days ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

It's will not works here, the average is -6.8 and we have value of -3. I need to paint this cell with RED (Because it's negative) but actually it's get GREEN flag.

SEE HERE

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

eliorb is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53370650%2fexcel-conditional-formatting-with-symbols%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
0
down vote

You can edit conditional formatting just make first "conditional format" by "icon set" of each row then go "manage rules" and "edit rules" and change as photo.

share|improve this answer

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please re-check your conditional formatting because in my sheet shown is correct. check first image.
  – Shoaib Hossain
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  see my new example and why it is not OK
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
  – Shoaib Hossain
  2 days ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
  – eliorb
  2 days ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

You can edit conditional formatting just make first "conditional format" by "icon set" of each row then go "manage rules" and "edit rules" and change as photo.

share|improve this answer

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please re-check your conditional formatting because in my sheet shown is correct. check first image.
  – Shoaib Hossain
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  see my new example and why it is not OK
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
  – Shoaib Hossain
  2 days ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
  – eliorb
  2 days ago
  

  
  
  
  

add a comment | 

up vote
0
down vote

up vote
0
down vote

You can edit conditional formatting just make first "conditional format" by "icon set" of each row then go "manage rules" and "edit rules" and change as photo.

share|improve this answer

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

You can edit conditional formatting just make first "conditional format" by "icon set" of each row then go "manage rules" and "edit rules" and change as photo.

share|improve this answer

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

share|improve this answer

share|improve this answer

edited 2 days ago

edited 2 days ago

edited 2 days ago

answered 2 days ago

Shoaib Hossain

125

answered 2 days ago

Shoaib Hossain

125

answered 2 days ago

Shoaib Hossain

125

125

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please re-check your conditional formatting because in my sheet shown is correct. check first image.
  – Shoaib Hossain
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  see my new example and why it is not OK
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
  – Shoaib Hossain
  2 days ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
  – eliorb
  2 days ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please re-check your conditional formatting because in my sheet shown is correct. check first image.
  – Shoaib Hossain
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  see my new example and why it is not OK
  – eliorb
  2 days ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
  – Shoaib Hossain
  2 days ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
  – eliorb
  2 days ago
  

  
  
  
  

I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
– eliorb
2 days ago

I already tried this - It will not work if the the average is for example -2 and one user have value of -1, This value is bigger then the average but it is a negative value and therefore I want to symbolize it with the negative color.
– eliorb
2 days ago

Please re-check your conditional formatting because in my sheet shown is correct. check first image.
– Shoaib Hossain
2 days ago

Please re-check your conditional formatting because in my sheet shown is correct. check first image.
– Shoaib Hossain
2 days ago

see my new example and why it is not OK
– eliorb
2 days ago

see my new example and why it is not OK
– eliorb
2 days ago

Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
– Shoaib Hossain
2 days ago

Check new photo it working. You should make condition for each row. Upload your file/test file i will check.
– Shoaib Hossain
2 days ago

Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
– eliorb
2 days ago

Note that in the second column in your example you have average value of -2,38%. you fill with negative color 2 cells (-3%, -10%) and 2 cells with positive color (5%, -1.5%). But this is not what I want. I must to fill the cell with -1.5% also in negative color because it's has a negative value (despite its value is greater than the average).
– eliorb
2 days ago

add a comment | 

up vote
0
down vote

It's will not works here, the average is -6.8 and we have value of -3. I need to paint this cell with RED (Because it's negative) but actually it's get GREEN flag.

SEE HERE

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
0
down vote

It's will not works here, the average is -6.8 and we have value of -3. I need to paint this cell with RED (Because it's negative) but actually it's get GREEN flag.

SEE HERE

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
0
down vote

up vote
0
down vote

It's will not works here, the average is -6.8 and we have value of -3. I need to paint this cell with RED (Because it's negative) but actually it's get GREEN flag.

SEE HERE

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

It's will not works here, the average is -6.8 and we have value of -3. I need to paint this cell with RED (Because it's negative) but actually it's get GREEN flag.

SEE HERE

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this answer

share|improve this answer

edited 2 days ago

Shoaib Hossain

125

edited 2 days ago

Shoaib Hossain

125

edited 2 days ago

Shoaib Hossain

125

125

answered 2 days ago

eliorb

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 2 days ago

eliorb

11

answered 2 days ago

eliorb

11

11

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

New contributor

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

eliorb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

add a comment | 

eliorb is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

eliorb is a new contributor. Be nice, and check out our Code of Conduct.

eliorb is a new contributor. Be nice, and check out our Code of Conduct.

eliorb is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53370650%2fexcel-conditional-formatting-with-symbols%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here