5 Replies Latest reply on Jul 5, 2013 10:45 AM by Kris Erickson

# Conditional Formatting per cell by calculation

Dear all,

I have a question to the calculated fields. In my analyses I want to colour all cells of a spcific column green which are 10 % higher then the 14-days-average and all cells of that column that are 10 % below that line in red. It is no problem for me to get the 14-days-average and colour all cells in that row, but I just want that one cell to be coloured.

Maybe an example makes it easier to understand:

I have different departments and their sales per day. Now I want to show in which department at which date the sales per day are higher or lower than  the 10 % of the 14-days-average, by colour these cells different.

I hope this is enough, if not please let me know what you need.

David

• ###### 1. Re: Conditional Formatting per cell by calculation

David,

This is an extremely common issue for people coming from Excel that want to replicate that functionality.  Fortunately, some insanely smart people have come up with a method called "Dual-Axis Crosstabs."  I wrote a blog post about them.

http://breaking-bi.blogspot.com/2013/02/formatting-individual-columns-in.html

FYI, I didn't come up with the idea.  I'm not nearly so vain as to refer to myself as insanely smart .

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 2. Re: Conditional Formatting per cell by calculation

this works perfect, it is a little bit complicated but is really fine for me.

Thanks a lot for your help!

Just one more question according to that:

Is there any chance to get the header of the columns at the top? Or are they always at the bottom of the sheet?

Thank you.

David

• ###### 3. Re: Conditional Formatting per cell by calculation

David,

That's a great question and I am surprised that I did not include that in the original blog post.  It's a method called "Label Forklifting."  At it's simplest, you turn each column into a dual-axis chart instead of a single axis chart.  A simple example is attached.

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 4. Re: Conditional Formatting per cell by calculation

Again, thank you very much! This works perfect. Not so funny to do this for 20 different columns but it works

Have a nice day

David