• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Conditional formatting for Subtotal rows|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Conditional formatting for Subtotal rows|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Conditional formatting for Subtotal…
sp_PrintTopic sp_TopicIcon
Conditional formatting for Subtotal rows
Avatar
Jessica_1

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
August 18, 2017
sp_UserOfflineSmall Offline
1
August 29, 2017 - 4:18 pm
sp_Permalink sp_Print

Hi,

I have a pivot table in an Excel spreadsheet which manages Resource Allocation for a team, I highlight the total FTE (Full Time Equivalent) in RED if the allocation is over 1.1 (110% allocation) and YELLOW if it's less then 0.9 (90% allocation) in the subtotal row for each individules.

It has been working fine for many years until now (I noticed the change last night to be exact) that instead of only conditional formatting the subtotal rows which I selected, it changes the selections to the whole area after I set the formatting. If I open an old archive file for the Resource Allocation, the conditional formatting looked fine, but as soon as I refreshed the pivot table, the conditional formatting changed to cover the whole area, does anyone know what has changed and if there is a way to get around it? 

Many thanks in advance for your help.

Cheers!

Jessica

sp_AnswersTopicSeeAnswer See Answer
Avatar
Derek Brown
Basingstoke, United Kingdom
Member
Members
Level 0
Forum Posts: 19
Member Since:
January 18, 2015
sp_UserOfflineSmall Offline
2
August 29, 2017 - 8:13 pm
sp_Permalink sp_Print

Could you be applying the conditional formatting ONLY to the pivot table?

There is a way to overcome problems with pivot table conditional formatting. It is an old YouTube video but the method still works in later versions of Excel:

sp_AnswersTopicAnswer
Answers Post
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
August 29, 2017 - 8:17 pm
sp_Permalink sp_Print

Hi Jessica,

It's very difficult to say without seeing the file, but there might be a clue in the difference in the radio button options given in the two screenshots; the 3rd option in the top image says 'All cells showing 19-Jun values for 'Resource' and the bottom image says '...values for 'Status'.

It looks like the values area for the second screenshot are based on a different field.

Mynda

Avatar
Jessica_1

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
August 18, 2017
sp_UserOfflineSmall Offline
4
August 30, 2017 - 2:34 pm
sp_Permalink sp_Print

Hi Derek, 

Awesome, it works, thank you so much for the tip, really appreciated....Wink

Hi Mynda,

Thanks for your reply, very good point. The "Resource" is the subtotal column, the "Status" is the column just before the value columns. The pivot table value field contains many columns (one for each week for 12 months). I know it would be easier to see the file when doing diagnostic, but due to data privacy issue, I can't send the file.

The top image is when I was applying the conditional format, the 2 image was what it looks like when I went into the conditional format afterwards to check, the excel change that part without my "consent", really strange.

Let me know if you are interested and I can make a sample file to show you exactly what is the issue.

Thank you again to both of you, have a great day!

Cheers!

Jessica

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
5
August 31, 2017 - 4:33 pm
sp_Permalink sp_Print

Why  ROWS can not be highlighted, ONLY COLUMNA "A"   is Highlighted after applying CONDITIONAL formatting?

Pls refer attached

Avatar
Derek Brown
Basingstoke, United Kingdom
Member
Members
Level 0
Forum Posts: 19
Member Since:
January 18, 2015
sp_UserOfflineSmall Offline
6
August 31, 2017 - 11:15 pm
sp_Permalink sp_Print

The example that you provided does not appear to be a PIVOT table and you have only applied conditional formatting to Column A - starting in Row 4.

Therefore the solution to the original question is not relevant to your worksheet - just use normal Conditional Formatting (CF) techniques but applying the CF to Columns A - K.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
September 1, 2017 - 2:46 am
sp_Permalink sp_Print

Hi David

As Derek said, you need to use the "normal" CF.

See if this meet your needs.

Sunny

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
8
September 1, 2017 - 11:55 am
sp_Permalink sp_Print

Thanks Sunny. Yes, the formula fulfilled the requirment.

Appreciate if you explain a bit the formula oyu used in the CF.

=IFERROR(SEARCH("ttl",$A4&$B4&$C4)>0,FALSE)

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
September 1, 2017 - 4:33 pm
sp_Permalink sp_Print

Hi David

Instead of checking each cell for ttl (the formula will be very long and messy), I joined them together with $A4&$B4&$C4 and then use SEARCH to look for the position of ttl in the concatenated text. SEARCH will return the 1st position number if ttl is found. Otherwise it will return an error #VALUE!. To make it return a TRUE instead of a number, I use >0 (more than 0). I then use IFERROR to convert any error to FALSE if ttl is not found.

Hope this is clear.

You can read more about SEARCH and IFERROR here:

https://www.myonlinetraininghu.....-will-find

https://www.myonlinetraininghu.....orkarounds

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
10
September 4, 2017 - 11:51 am
sp_Permalink sp_Print

Thanks Sunny again, fully understood.

You always have clever trick to get wise adn comprehensive solution for complication case.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Rashid Khan
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27793

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.