Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Conditional formatting on Pivot Table

We have an Excel 2010 spread sheet with around 45,000 rows which is formatted into a Pivot Table which currently has 8,500 rows grouped into groups of 9 rows.  Two of the rows in the table are titled 'Balance' and 'Difference', as below:

Balance            3,000       3,000       3,000
Safety Stock      0       0       0
Difference      3,000       3,000       3,000

I am trying to find a way to format the background of all lines with 'Balance' with one colour and the lines with 'Difference' in another colour whilst leaving all other lines as white background.  I have tried using conditional formatting with a formula =ISNUMBER(SEARCH("Balance",$B:$B) but to no avail.

Does anybody have any suggestions?
0
Steve Brown
Asked:
Steve Brown
  • 2
1 Solution
 
Swatantra BhargavaTeam LeaderCommented:
Hey

Please visit below may be its useful for you

http://community.qlik.com/thread/76045

SwAn
0
 
ProfessorJimJamCommented:
Select the whole used range and then go to conditional formatting then put this formula and select a color
=($B2="Balance")

then same follow for =($B2="Difference") and select another color.
0
 
Steve BrownTechnical DirectorAuthor Commented:
It sort of works, but not if I use the cell reference of $B2.  Strangely if I use $B7 for "Balance" and $B1 for "Difference" it seems to work, but then neither formatting survives a data refresh despite the 'Preserve cell formatting on update' being ticked on the PivotTable Options, Layout & Format Tab.
0
 
ProfessorJimJamCommented:
look,  first of all i do not know where your start range of your data is.   when you select the whole data, what is the start.  is it B1?   A1?  what is it?

can you attach a dummy example file. so that i set up the working formula in it?
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now