?
Solved

Conditional formatting on Pivot Table

Posted on 2014-10-31
4
Medium Priority
?
116 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Accepted Solution

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

Please visit below may be its useful for you

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

SwAn
0
 
LVL 26

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 26

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn the basics of using filtering and sorting in Excel 2013.

741 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