Solved

Conditional formatting on Pivot Table

Posted on 2014-10-31
4
95 Views
Last Modified: 2015-07-28
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
Comment
Question by:Steve Brown
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Swatantra Bhargava earned 500 total points
ID: 40416170
Hey

Please visit below may be its useful for you

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

SwAn
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40416294
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
 

Author Comment

by:Steve Brown
ID: 40417049
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40417636
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn how to find and create templates in Excel 2013.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question