+ Reply to Thread
Results 1 to 9 of 9

How do I restore original Pivot Table data?

  1. #1
    Andrew C
    Guest

    How do I restore original Pivot Table data?

    How do I restore my complete data set for a pivot table? I have a table that
    has a grouping at the top (say group "A"), a series at the right which are my
    pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    (a city listing). Whenever I change the "data" selection at the bottom, the
    other selections disappear and I cannot get them back unless I undo. How do
    I move the selections around but still have access to my original data set?

    Andrew C

  2. #2
    JAdamJ
    Guest

    RE: How do I restore original Pivot Table data?

    I'm not sure I follow you completely, but try double clicking the gray areas
    that represent the data that you want to restore. It should pop up a box
    with a list of all of your items and you can click on them to unhide. Or you
    could try moving the data to the "top" (page field) and then choosing "(All)"
    before moving it back.

    "Andrew C" wrote:

    > How do I restore my complete data set for a pivot table? I have a table that
    > has a grouping at the top (say group "A"), a series at the right which are my
    > pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    > (a city listing). Whenever I change the "data" selection at the bottom, the
    > other selections disappear and I cannot get them back unless I undo. How do
    > I move the selections around but still have access to my original data set?
    >
    > Andrew C


  3. #3
    Andrew C
    Guest

    RE: How do I restore original Pivot Table data?

    Thanks for the help, and I'll try to explain myself better.

    My pivot table has letters/numbers going across for the COLUMNS. The PAGE
    is by vendor. Both of these I can hide and unhide and select without the
    original selections disappearing. However, my data ROWS are another matter.
    When I de-select ALL on my data rows on the table ( or data at the bottom of
    the chart) and click on one or several check boxes, I get those selections.
    When I click on the data box though to make other selections or all again,
    the previous choices and selections I had are gone. It is like if I reduce
    my data set, I cannot expand it again. Any help would be appreciated as I
    feel like I'm missing something basic but just can't figure out what is.

    --
    Andrew C


    "JAdamJ" wrote:

    > I'm not sure I follow you completely, but try double clicking the gray areas
    > that represent the data that you want to restore. It should pop up a box
    > with a list of all of your items and you can click on them to unhide. Or you
    > could try moving the data to the "top" (page field) and then choosing "(All)"
    > before moving it back.
    >
    > "Andrew C" wrote:
    >
    > > How do I restore my complete data set for a pivot table? I have a table that
    > > has a grouping at the top (say group "A"), a series at the right which are my
    > > pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    > > (a city listing). Whenever I change the "data" selection at the bottom, the
    > > other selections disappear and I cannot get them back unless I undo. How do
    > > I move the selections around but still have access to my original data set?
    > >
    > > Andrew C


  4. #4
    JAdamJ
    Guest

    RE: How do I restore original Pivot Table data?

    Sorry I misunderstood.

    When you deselect fields in the data drop down then they are removed from
    the pivot table (as you discovered). Don't worry, they have not been
    forgotten. You can right click in the table and select Show Field List. The
    field list will contain the fields that were removed. You can add them back
    to the data area here. Or, when you right click you can choose Pivot Table
    Wizard and then click the Layout button and add your fields back there.

    Hope this helps.

    "Andrew C" wrote:

    > Thanks for the help, and I'll try to explain myself better.
    >
    > My pivot table has letters/numbers going across for the COLUMNS. The PAGE
    > is by vendor. Both of these I can hide and unhide and select without the
    > original selections disappearing. However, my data ROWS are another matter.
    > When I de-select ALL on my data rows on the table ( or data at the bottom of
    > the chart) and click on one or several check boxes, I get those selections.
    > When I click on the data box though to make other selections or all again,
    > the previous choices and selections I had are gone. It is like if I reduce
    > my data set, I cannot expand it again. Any help would be appreciated as I
    > feel like I'm missing something basic but just can't figure out what is.
    >
    > --
    > Andrew C
    >
    >
    > "JAdamJ" wrote:
    >
    > > I'm not sure I follow you completely, but try double clicking the gray areas
    > > that represent the data that you want to restore. It should pop up a box
    > > with a list of all of your items and you can click on them to unhide. Or you
    > > could try moving the data to the "top" (page field) and then choosing "(All)"
    > > before moving it back.
    > >
    > > "Andrew C" wrote:
    > >
    > > > How do I restore my complete data set for a pivot table? I have a table that
    > > > has a grouping at the top (say group "A"), a series at the right which are my
    > > > pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    > > > (a city listing). Whenever I change the "data" selection at the bottom, the
    > > > other selections disappear and I cannot get them back unless I undo. How do
    > > > I move the selections around but still have access to my original data set?
    > > >
    > > > Andrew C


  5. #5
    Andrew C
    Guest

    RE: How do I restore original Pivot Table data?

    That does work to a degree. However, all of the formatting and row names
    have to be re-done if I want to add back the data fields from the wizard
    (gives it names of sum of XXX, count of XXXX). Also, if I add the rows back
    with the Show Field List it adds not data rows back, but columns and the
    table just turns into a mess. I appreciate your help but I know I'm missing
    something or forgot something as I haven't run into this problem before.
    --
    Andrew C


    "JAdamJ" wrote:

    > Sorry I misunderstood.
    >
    > When you deselect fields in the data drop down then they are removed from
    > the pivot table (as you discovered). Don't worry, they have not been
    > forgotten. You can right click in the table and select Show Field List. The
    > field list will contain the fields that were removed. You can add them back
    > to the data area here. Or, when you right click you can choose Pivot Table
    > Wizard and then click the Layout button and add your fields back there.
    >
    > Hope this helps.
    >
    > "Andrew C" wrote:
    >
    > > Thanks for the help, and I'll try to explain myself better.
    > >
    > > My pivot table has letters/numbers going across for the COLUMNS. The PAGE
    > > is by vendor. Both of these I can hide and unhide and select without the
    > > original selections disappearing. However, my data ROWS are another matter.
    > > When I de-select ALL on my data rows on the table ( or data at the bottom of
    > > the chart) and click on one or several check boxes, I get those selections.
    > > When I click on the data box though to make other selections or all again,
    > > the previous choices and selections I had are gone. It is like if I reduce
    > > my data set, I cannot expand it again. Any help would be appreciated as I
    > > feel like I'm missing something basic but just can't figure out what is.
    > >
    > > --
    > > Andrew C
    > >
    > >
    > > "JAdamJ" wrote:
    > >
    > > > I'm not sure I follow you completely, but try double clicking the gray areas
    > > > that represent the data that you want to restore. It should pop up a box
    > > > with a list of all of your items and you can click on them to unhide. Or you
    > > > could try moving the data to the "top" (page field) and then choosing "(All)"
    > > > before moving it back.
    > > >
    > > > "Andrew C" wrote:
    > > >
    > > > > How do I restore my complete data set for a pivot table? I have a table that
    > > > > has a grouping at the top (say group "A"), a series at the right which are my
    > > > > pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    > > > > (a city listing). Whenever I change the "data" selection at the bottom, the
    > > > > other selections disappear and I cannot get them back unless I undo. How do
    > > > > I move the selections around but still have access to my original data set?
    > > > >
    > > > > Andrew C


  6. #6
    JAdamJ
    Guest

    RE: How do I restore original Pivot Table data?

    The data area doesn't work the way you want it to. You either need to figure
    out a way to make it a column, row, or page field, or you could do what you
    want using vba. If you're not familiar with vba, then I wouldn't suggest
    attempting this type of thing as your first try. Sorry I haven't been able
    to help. Perhaps someone else will.

    "Andrew C" wrote:

    > That does work to a degree. However, all of the formatting and row names
    > have to be re-done if I want to add back the data fields from the wizard
    > (gives it names of sum of XXX, count of XXXX). Also, if I add the rows back
    > with the Show Field List it adds not data rows back, but columns and the
    > table just turns into a mess. I appreciate your help but I know I'm missing
    > something or forgot something as I haven't run into this problem before.
    > --
    > Andrew C
    >
    >
    > "JAdamJ" wrote:
    >
    > > Sorry I misunderstood.
    > >
    > > When you deselect fields in the data drop down then they are removed from
    > > the pivot table (as you discovered). Don't worry, they have not been
    > > forgotten. You can right click in the table and select Show Field List. The
    > > field list will contain the fields that were removed. You can add them back
    > > to the data area here. Or, when you right click you can choose Pivot Table
    > > Wizard and then click the Layout button and add your fields back there.
    > >
    > > Hope this helps.
    > >
    > > "Andrew C" wrote:
    > >
    > > > Thanks for the help, and I'll try to explain myself better.
    > > >
    > > > My pivot table has letters/numbers going across for the COLUMNS. The PAGE
    > > > is by vendor. Both of these I can hide and unhide and select without the
    > > > original selections disappearing. However, my data ROWS are another matter.
    > > > When I de-select ALL on my data rows on the table ( or data at the bottom of
    > > > the chart) and click on one or several check boxes, I get those selections.
    > > > When I click on the data box though to make other selections or all again,
    > > > the previous choices and selections I had are gone. It is like if I reduce
    > > > my data set, I cannot expand it again. Any help would be appreciated as I
    > > > feel like I'm missing something basic but just can't figure out what is.
    > > >
    > > > --
    > > > Andrew C
    > > >
    > > >
    > > > "JAdamJ" wrote:
    > > >
    > > > > I'm not sure I follow you completely, but try double clicking the gray areas
    > > > > that represent the data that you want to restore. It should pop up a box
    > > > > with a list of all of your items and you can click on them to unhide. Or you
    > > > > could try moving the data to the "top" (page field) and then choosing "(All)"
    > > > > before moving it back.
    > > > >
    > > > > "Andrew C" wrote:
    > > > >
    > > > > > How do I restore my complete data set for a pivot table? I have a table that
    > > > > > has a grouping at the top (say group "A"), a series at the right which are my
    > > > > > pivot table columns (1,2,3,...20), and data at the bottom which are my rows
    > > > > > (a city listing). Whenever I change the "data" selection at the bottom, the
    > > > > > other selections disappear and I cannot get them back unless I undo. How do
    > > > > > I move the selections around but still have access to my original data set?
    > > > > >
    > > > > > Andrew C


  7. #7
    Registered User
    Join Date
    12-13-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    2

    Re: How do I restore original Pivot Table data?

    As mentioned by Andrew C, the underlying data is still there but has simply been filtered out of your pivot.

    The simplest way I can think of solving your problem is as follows:

    1. Go to PivotTable Fields (if this information doesn`t appear when you left-click anywhere in the pivot you may have to right-click on the pivot table and check 'show PivotTable Fields')
    2. In there you will be able to see a list of all the field available in your dataset, the ones being used on the pivot will be highlighted in bold and have a check mark to their left.
    3. Check for selected items (as per item 2) and see which ones have a funnel icon to their right, these items have been partially removed from the pivot table, if you hover your cursor over the funnel, a down-wards arrow will appear. Click that arrow to see a dropdown list of items displayed in the current table
    4. Now you can either clear these filters individually (which may be valuable because the original pivot might have already contained filters you were not aware of) or go to Data>Clear (while still selecting the pivot table, this is quicker and will clear every condition on the table, you can also do it through Home > Sort & Filter > Clear)
    5. Once the pivot has been restored to the conditions you wanted, you will go to the furthermost right cell of tour table and move one further cell to the right (e.g., if your table header ends on cell R4, go to cell S4), then select all the cells to its left (it`s important that you select from right to left) and then all the way down to when the pivot table ends (you may choose not to include the 'grand total' row so that it shows whenever you are using your filter)
    6. Turn on the filter either via Data > Filter or Home > Sort & Filter > Filter
    7. Now you should be able to sort for your rows (cities) without removing rows/columns from the table. Please be careful when clearing the filters to make sure the pivot table is not selected, otherwise you will also clear the pivot table conditions, which you probably don`t want.


    Hope this helps.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,910

    Re: How do I restore original Pivot Table data?

    William, welcome to the forum

    Thank you for your detailed reply to this

    (did you notice that the thread is over 10 years old?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    12-13-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    2

    Re: How do I restore original Pivot Table data?

    Sorry, when I saw his post I thought it was from 2016, my bad xD.

    I found this post by accident as I was looking for something else and though I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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