Solved

Conditional formatting on Pivot Table

Posted on 2014-10-31
4
103 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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Post a good COM tutorial 1 68
Simple Google Sheets Script question. 2 39
Protecting the "Command Text" Field in Excel's Connection Properties 3 37
Count with OR in Excel 7 34
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.

733 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