Conditional formatting Excel 2007


A

Atri

Hi!

I am just a beginner of using conditional formatting and I hope that someone
can help me with a problem.

I have a big worksheet with many rows showing incidents reported by our
customers. The row includes different information about the incident
(incident-id, customer, data etc.)

Out of this I have made a pivot-table that shows volume of incidents from
the customers distributed by customer and week.

Customer Week1 Week2 Week 3 .... Week 33 Total Average (not
pivot)
CustomerA 30 60 4 .... 20 114
114/33
CustomerB 10 20 15 .... 30 75
75/33

We have a lot of customers and I am now showing the result for the 33 first
weeks of 2009. I want a conditional formatting that gives each week a colur
depending of the weekly result. My intention is to make customers with a
negative trend more visible. I have in a cell outside the pivot-table
calculated the average weekly volume of incide so far this year (total/33
weeks). The weekly volum is summarized by "Sum".

I have tried to define 2 conditional formatting rules that colur the weekly
result "red" if it is over the average and green if it is lower than average.
My problem is that the conditional formatting only works in the first
column. How can I define a conditional formatting the most easiest way? Can
I copy the conditional formatting in a way? I hope I don't need to define 2
rules for each column in the pivot-table.

Maybe I should solve my problem using something else than conditional
formatting?

I appreciate all suggestion!
 
Ad

Advertisements

A

Atri

Thanks a lot, Herbert! This is just what I am looking for. My boss became
very interested in using your solution when I showed him your proposal.

I have some 2 more questions for you and hope that you have time to answer
them also.

Question 1
We have about 400 customers. Must I define the 3 formatting rules for each
customer or is it possible to copy the rules from one customer to another
(for example Paste special/Copy format)?

Question 2
Will this conditonal formatting work even if I have several report filters
in my pivot-table? Our customers are divided in to different
business-sectors and I suppose that someone will prefer to see only customers
belonging to their own sector. We do also have different types of incidents
from the customers and this can also be relevant as a report filter.

Thank's a lot again!
 
Ad

Advertisements

A

Atri

Hello again Herbert!

Thank's again for your help!

I appreciate very much that you spend so much time helping me with a
solution. You explain the solution in a way that makes me understand how I
can solve my problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top