+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

  1. #1
    Forum Contributor
    Join Date
    11-18-2009
    Location
    Winnipeg
    MS-Off Ver
    Excel 2003
    Posts
    203

    Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    I'm trying to apply conditional formatting to a Pivot Table using these instructions: https://www.ablebits.com/office-addi...g-pivottables/

    I've tried to find other ways to do it but they all give the same general steps....one being to "click the Formatting Options button which appears at the bottom right of the range" but I don't have that button.

    Any ideas? Is there another way to access this options menu?

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Contributor
    Join Date
    11-18-2009
    Location
    Winnipeg
    MS-Off Ver
    Excel 2003
    Posts
    203

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    Here you go. I want it to look at the "Invoice Type" and change the colour of each column according to what's there.
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    I have in the attached used conditional formatting in a pivot table.
    Attached Files Attached Files

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    This one is better

  6. #6
    Forum Contributor
    Join Date
    11-18-2009
    Location
    Winnipeg
    MS-Off Ver
    Excel 2003
    Posts
    203

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    I copied the formulas you used in the Conditional Formatting, but it's not expanding to new data when the Pivot Table expands. How can I fix this?
    Last edited by kmlloyd; 03-05-2016 at 09:57 PM.

  7. #7
    Forum Contributor
    Join Date
    11-18-2009
    Location
    Winnipeg
    MS-Off Ver
    Excel 2003
    Posts
    203

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    I've attached it with more data. The conditional Formatting doesn't expand when the table changes.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    I even turned the Invoice Data range into a table then changed the Pivot table source to that table and it only partially works. The range for conditional formatting should change according to the table's change in size but it doesn't

    I next expanded the range for the Conditional Formatting to exceed the data in the Pivot table (row 2000) and that works. So, make the range in Conditional Formatting something that will not likely be exceeded any time soon.

    The repeating labels are created by right clicking in the Pivot table column that you want repeating labels in and then choose Field Settings. On the Layout and Print tab select Repeat item labels and click 0k.

  9. #9
    Forum Contributor
    Join Date
    11-18-2009
    Location
    Winnipeg
    MS-Off Ver
    Excel 2003
    Posts
    203

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    Thanks Expanding the range worked perfectly...the only problem is, every time I refresh the data, for some reason the range in the Conditional Formatting changes. It changes it from columns A:H to A:G. Any ideas? G is the total column that recalculates...could that have something to do with it?

    It happens when I change the table set at the top...the Invoice Month and / or Year.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting on a Pivot Table - Formatting Options Icon not showing

    There is formatting in the totals column that appears to interfere with the conditional formatting when the table is refreshed. I have tried several things to try and override this but have been unsuccessful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 09-04-2015, 02:47 AM
  2. [SOLVED] Change pivot table default options (more than formatting)
    By ThirdFret in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-19-2015, 03:43 PM
  3. Pivot Table not showing the value filter options - only Top 10!
    By abhi.ko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2014, 07:31 PM
  4. [SOLVED] formatting pivot table report (borders automatically showing)...
    By Perplexed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2014, 02:29 PM
  5. Conditional icon formatting in pivot excel 2010 to stay with refresh
    By newbieexcelgirl in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-19-2012, 04:22 PM
  6. Replies: 1
    Last Post: 03-05-2012, 06:20 PM
  7. Conditional Formatting W/Icon Sets in Analysis Services Pivot Table
    By puffster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2011, 03:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07471 seconds
  • Memory Usage 9,159KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (10)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_attachment
  • (10)postbit_legacy
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1