10 Replies Latest reply on Nov 10, 2015 7:56 AM by pooja.gandhi

# how to color cells of a pivot table based on specific condition?

Hello got a pivot table showing the sales by category, sub-category and year

now I want to color the cells under 2014 in green if value is greater than that of 2013 otherwise red

• ###### 1. Re: how to color cells of a pivot table based on specific condition?

Here's solution in attachment

1 of 1 people found this helpful
• ###### 2. Re: how to color cells of a pivot table based on specific condition?

this colors the values however I want to color the cells' background

• ###### 3. Re: how to color cells of a pivot table based on specific condition?

Take a look at this workbook (sheet 6) as this should solve what you're trying to do.  You'll need this calc:

IF ATTR(DATEPART('year', [Order Date])) = 2012 AND LOOKUP(SUM([Sales]),0)>LOOKUP(SUM([Sales]),-1) THEN 'Green'

ELSEIF ATTR(DATEPART('year', [Order Date])) = 2012 AND LOOKUP(SUM([Sales]),0)<LOOKUP(SUM([Sales]),-1) THEN 'Red'

END

And then color null as white (double click null in color legend then double click it again in the pop up; choose white)

• ###### 4. Re: how to color cells of a pivot table based on specific condition?

I can't, for some reason, center the label where I want it.. but I think this is what you are looking for?

• ###### 5. Re: how to color cells of a pivot table based on specific condition?

so what are you doing here?

use bar chart and not pivot table?

can someone tell me what are you trying to do step by step?

• ###### 6. Re: how to color cells of a pivot table based on specific condition?

Yes, I'm using a Bar chart to color the cell...

I changed your Align variable to a value of 1, and then fixed the axis on each column to start a 0 and end at 1. This gives the bar 100% of the column.

From there, I just added the labels and colors the way you had before.

Hope this makes sense, let me know if not.

• ###### 7. Re: how to color cells of a pivot table based on specific condition?

You can actually do this easier way. Before you start dragging pills to the view, change the mark type to 'square' on the marks card. Create a blank calculation by entering just the quotations "" in the calculator editor. Then drag sub category to rows, drag the blank calculated field next to it on rows. Using the size shelf adjust the size of the squares until they fill the entire cell. Click on the little arrow on the blank pill and uncheck show header. Next drag category and year(order date) on columns. Exclude the years you do not need. You may need to adjust the size shelf again depending on how the cells look. Then drag the formula:

IF attr(datepart('year', [Order Date])) = 2014 and sum([Sales]) > lookup(sum([Sales]),-1)

THEN 'Green'

ELSEIF attr(datepart('year', [Order Date])) = 2014 and sum([Sales]) < lookup(sum([Sales]),-1)

THEN 'Red'

END

on color and set the compute using to table across. Edit the colors and set the null to white so only 2014 column has colors based on your needs.

2 of 2 people found this helpful
• ###### 8. Re: how to color cells of a pivot table based on specific condition?

yeah this is it thank YOU

• ###### 9. Re: how to color cells of a pivot table based on specific condition?

I got an idea!!

can we make this more generic

I mean not only for 2014 and 2013 but rather with Year and previous one

is this possible?

• ###### 10. Re: how to color cells of a pivot table based on specific condition?

Yes, if you create a parameter to accommodate all the integer formatted year values, you can actually embed the parameter int he calculation so the color columns will shift based on the selection made in the parameter.

Instead of hard-coding the year values, insert the parameter:

1 of 1 people found this helpful