Conditional formatting of single column of pivot table

J

JD1702

New Member
Joined
Oct 27, 2024
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2013
Platform
  1. Windows
Hi all
I know my way around Excel but have never really used Pivot tables much until now.
I've created one which seems to do what I want but the conditional formatting is causing me trouble.
I have a number of names (rows) and a number of different types of training (columns) and the pivot table pulls in the latest date for each person for each type of training. That bit works ok.
I want to apply conditional formatting which will highlight when specific training is out of date, but each type of training has a different duration. So far, I can only apply a single date to the whole table, whereas I want to have a separate rule for each column.
Is this possible?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Select one PivotTable cell and click Create Rule. You will see a dialog box like this

Here you choose what to create a rule for: for the selected cell, the column where this cell is located, or a third option.
 
Upvote 0
Thanks, but that doesn't work for my table.

All the columns are training categories but I don't have the option where you pointed to to select a specific one
 
Upvote 0
Welcome to the MrExcel board!

We would be required to guess what your table looks like, how we know the duration for each type of training, how we tell from the table if one is out of date etc.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Explain again with reference to the sample data and give a few specific examples of cells that should be highlighted and why and cells that should not be highlighted and why
 
Upvote 0
Ok, I've had a go with that add-in, and hopefully this will help:
Training_Summary_test.xlsx
BCDEF
1Max of DateColumn Labels
2Row LabelsAd hocInitialRefresherRefresher 2y
3Alan01/01/202401/05/202401/04/2024
4Annie01/01/202401/05/202401/04/2024
5Beth01/01/202401/05/202201/04/2024
6Dawn01/09/202401/01/202401/10/2024
7Jamie01/09/202401/01/202401/03/2024
8Joe01/01/202401/05/202401/04/2024
9Jonny01/08/202401/01/202401/04/2024
Training Summary


In this table, the raw data (dates for each type of training for any particular person) is from another sheet and the labels in C2:F2 are types of training, again taken from another sheet:
Training_Summary_test.xlsx
AB
1Training CategoryUpdate Frequency (days)
2InitialNone
3Refresher365
4Ad hocNone
5Refresher 2y730
Training Categories


What I would like is for each column in the top pivot table to have conditional formatting according to the update frequency days in the second table. E.g. the pivot table should highlight red for Beth with Refresher in E5.

Ideally I'd like any dates within the required frequency to be green, and for Initial or Ad Hoc training, as long as there is a date they can also be green.

Hopefully that makes it a bit clearer.
Thanks for the help!
 
Upvote 0
Is this what you wanted?
Book1.xlsx
HIJKL
2Row Labels Ad hoc Initial Refresher Refresher 2y
3Alan01.01.202401.05.202401.04.2024
4Annie01.01.202401.05.202401.04.2024
5Beth01.01.202401.05.202201.04.2024
6Dawn01.09.202401.01.202401.10.2024
7Jamie01.09.202401.01.202401.03.2024
8Joe01.01.202401.05.202401.04.2024
9Jonny01.08.202401.01.202401.04.2024
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K3:K9Cellcontains a blank value textYES
K3:K9Cell Valuebetween DATE(YEAR(TODAY());1;1) and DATE(YEAR(TODAY());1;1)+Sheet2!$B$3textNO
K3:K9Cell Value<DATE(YEAR(TODAY());1;1)textNO
I3:I9Celldoes not contain a blank value textNO
L3:L9Cell Valuebetween DATE(YEAR(TODAY());1;1) and DATE(YEAR(TODAY());1;1)+Sheet2!$B$5textNO
J3:J9Celldoes not contain a blank value textNO

Book1.xlsx
AB
1Training CategoryUpdate Frequency (days)
2InitialNone
3Refresher365
4Ad hocNone
5Refresher 2y730
Sheet2

Here is a screenshot of the rules manager. Note that the rules apply to the PivotTable and to specific columns.
 
Upvote 0
JD1702 said:
Hopefully that makes it a bit clearer.
Click to expand...
It does, thanks.
Without more sample data though I can't be sure to create your pivot table and see what happens when data is changed and the PT is refreshed, but try this singe rule applied to C3:F?? making sure the last row is plenty big enough to cover any likely row increase (I have used row 20)

JD1702.xlsm
BCDEF
1Max of DateColumn Labels
2Row LabelsAd hocInitialRefresherRefresher 2y
3Alan1/01/20241/05/20241/04/2024
4Annie1/01/20241/05/20241/04/2024
5Beth1/01/20241/05/20221/04/2024
6Dawn1/09/20241/01/20241/10/2024
7Jamie1/09/20241/01/20241/03/2024
8Joe1/01/20241/05/20241/04/2024
9Jonny1/08/20241/01/20241/04/2024
10
Training Summary
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:F20Expression=AND(C3>0,TODAY()-C3>VLOOKUP(C$2,'Training Categories'!$A:$B,2,0))textNO
 
Upvote 0
Thanks both.
I had asumed there would be some straightforward method but it looks like it has to be done by formulas.
Thanks for the suggestions, I'll have a go getting them applied.
 
Upvote 0
JD1702 said:
I had asumed there would be some straightforward method ...
Click to expand...
I'm not sure if what I suggested does what you want, but if it does, one conditional formatting rule applied to one range of cells is pretty straightforward I would have thought.

Anyway, see how you go and post back if you need further help. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Conditional Formatting applied to the whole column in Pivot Table?
Replies
0
Views
370
GoodInSheets
G
M
  • Question Question
Add "x" to a matrix
Replies
2
Views
206
Monikbor
M
M
  • Question Question
Pivot Tables & Dates
Replies
0
Views
63
mikeymay
M
G
  • Question Question
Timeline on Pivot Table not working
Replies
1
Views
366
Sergius
S
R
  • Solved
Conditional Formating Never apply
Replies
3
Views
127
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top