Likes Likes:  0
Results 1 to 3 of 3

Thread: "Change Data Source" Pivot Table Excel 2016

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "Change Data Source" Pivot Table Excel 2016

    I first Create Table (where the data is =$A:$B, incl headers).
    A B C
    1 Date Animal
    2 11/01/17 Dog
    3 11/02/17 Dog
    4 11/03/17 Cat
    5 11/04/17 Dog
    6 11/05/17 Cat


    Then, I create the Pivot Table.

    D E F G H I
    2 Count of Animal Column Labels
    3 Row Labels Cat Dog (blank) Grand Total
    4 11/1/2017 1 1
    5 11/2/2017 1 1
    6 11/3/2017 1 1
    7 11/4/2017 1 1
    8 11/5/2017 1 1
    9 (blank)
    10 Grand Total 2 3 5

    Next, in Columns A & B, I add a couple more dates and animals, but it is NOT reflected in the Pivot Table.
    A B C D E F G H I
    1 Date Animal
    2 11/01/17 Dog Count of Animal Column Labels
    3 11/02/17 Dog Row Labels Cat Dog (blank) Grand Total
    4 11/03/17 Cat 11/1/2017 1 1
    5 11/04/17 Dog 11/2/2017 1 1
    6 11/05/17 Cat 11/3/2017 1 1
    7 11/20/17 Dog 11/4/2017 1 1
    8 11/21/17 Cat 11/5/2017 1 1
    9 (blank)
    10 Grand Total 2 3 5


    So, when I look online, it says to click on the Analyze tab, then Choose Data Source, and make the proper change. I already have the "ants" surrounding the full Columns A & B, so there is nothing to change. So, I changed the table to $A$1:$B$20 and now it works. Is this a bug? Sometimes, I might know how far to go down to; other times, I just want the whole column.

    Lastly, how can I remove the "blank" column from the pivot table?

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "Change Data Source" Pivot Table Excel 2016

    Hi Mark,

    Any changes that you make to cells in your datasource range aren't reflected in the PivotTable until it is Refreshed.

    You can Right-Click anywhere on a PivotTable, then pick Refresh.
    Using Excel 2016

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Posts
    405
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Change Data Source" Pivot Table Excel 2016

    The Blank is there as you are selecting a Row with no data in it. Your data is A1:B8 but you are selecting A1:B20. You need to change it or filter the blanks out.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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