1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Exclude Some Row Labels from Pivot Table Calculation

Discussion in 'Business Applications' started by jessiew84, Dec 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    Hi all.

    I have an extensive workbook that I'm trying to work out a few kinks in for 2011.

    Currently I have a master data sheet that then filters through pivot tables to give more simplified information. Each pivot table contains the same row labels and values, but for different time frames. I have it set up to calculate some of the formulas for me automatically but there is one field that I need to exclude two of the ten row labels from. Currently I have a column next to the pivot table that I have calculating this weird figure but I'm wondering if there's a way I can have it be integrated into the pivot table in the event we add more row labels in the future.

    Is there a way to set up the pivot table formula to exclude a certain row label?

    I've included an example workbook. Let's say I'm trying to show the Grand Total for % of Cake Slices for Mon-Fri only. Is there a way to do this?
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    Normally you would just click on the dropdwon arrow for row labels and theres a set of tick boxs - so you can exlude certain row values - ie Sat & Sun
    I only have excel 2003 - so i cant use the version you posted as i need to have a later version to test
     
  3. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    Etaf - unfortunately unchecking one of those boxes will exclude the unchecked for all calculations. I only want to exclude one item from one of the six pivot table calculations.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    sorry I dont understand - you want to exclude sat and sun in the calculations

    what else do you need to exclude

    you only have one PVT table - when you create six are you using the same records/data area - often you are asked to do this to save space - what happens if you do not do this

    I have had numerous PVT tables and chart showing different data from the one data sheet - but i think i ran into trouble when i kept / based on the same data
     
  5. jessiew84

    jessiew84 Thread Starter

    Joined:
    Jun 24, 2010
    Messages:
    23
    For the example I posted, I need to show the total of cakes, the total of guests, etc. all for Sunday - Saturday...but only on the % of Cakes column do I not want to include the weekend. I have to have multiple pivot tables to show different time periods of the total data. (ie. The week, the month, the quarter, the year).

    Essentially it currently looks like this:

    [​IMG]

    ...but I would like for column F to not include Sat and Sun in the pivot table calculation. I can manually create a column, but if an item in column A is removed or one is added, it will not automatically add it to the manually created column.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/970088

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice