+ Reply to Thread
Results 1 to 4 of 4

Pivot Table to analyse sales figures

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Pivot Table to analyse sales figures

    Hello,
    I'm currently using a Pivot Table to analyse sales figures.
    Attached is an example.

    I've grouped the Pivot Table by customer, then broken down by the individual delivery location as some customers deliver to more than one address.
    This is then sorted descending by total customer spend.

    The primary purpose is to compare the sales quarters against the previous year.
    In order to see the difference to show how the year is tracking, I have added some formulas in column G & H.
    The problem with this is that they aren't part of the Pivot Table so when I do any filtering of the PT these formula cells don't filter with it - they stay static.
    I have added a filter at the top of the PT as I would like to then be able to look at sales of particular areas, but this isn't going to work if my formula cells don't move with it.

    In addition to this, is there any way of getting rid of the < > date selections? I have filtered them out of the column & row headings but I would ideally like them gone altogether as they contain no data.

    Any advice on this would be much appreciated.
    You may even think that a Pivot Table isn't the best way of analysing the data in this way.

    Kind regards, Mel
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Pivot Table to analyse sales figures

    Look into using GETPIVOTDATA() when referencing a range within a PT. This updates the formulas from the values within the PT automatically.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    Christchurch, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Pivot Table to analyse sales figures

    Do these cells then move with the Pivot Table if a filter is applied?

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Pivot Table to analyse sales figures

    If using GETPIVOTDATA(), the data pulled is based on table headings. So if you apply a filter that does not contain a heading that the particular GETPIVOTDATA() formula uses, then it will return no data. Otherwise, it should still pull correctly.

+ 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: 4
    Last Post: 03-27-2013, 05:56 PM
  2. Replies: 1
    Last Post: 09-20-2012, 06:05 PM
  3. Pivot table figures come up in $
    By Markiemark in forum Excel General
    Replies: 0
    Last Post: 10-31-2011, 12:35 PM
  4. Analyse sales by month of proposal
    By tabira in forum Excel General
    Replies: 2
    Last Post: 04-30-2010, 08:48 AM
  5. Replies: 0
    Last Post: 04-18-2006, 02:10 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1