Share via

Pivot Table & Disconnect Data Source

Anonymous
2019-11-19T18:12:36+00:00

Is there a way to disconnect the data source in my Excel (version 365) AND retain the pivot table functions with the current date?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-11-19T19:35:12+00:00

    Pivots don't refresh until you refresh them, so a pivot created yesterday on yesterdays data will still show the same today even though the underlying data has changed.

    You could protect the sheet to prevent a refresh.

    If you want to keep yesterdays pivot and have a new one for today, you could Ctrl+Click&Drag to copy the sheet tab and then refresh the copy.

    Short of coping the data as values to another sheet or protecting the sheet seem to be your main options if I have your needs correctly understood.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-11-19T18:31:04+00:00

    You can untick 'Save Source Data With File' in Pivot Table Options - is this what you mean?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2019-11-19T18:47:24+00:00

    Thank you for the prompt response.  However, the suggestion did not produce the results I had hoped.   I am trying to save a historical view of the report.  Meaning, I want to disable the data source and have a working pivot table with a snapshot of today's data.   When I open the report tomorrow, my data of course, will be updated to reflect tomorrow's numbers. 

    Currently, when I disable the data source, the pivot table functions (i.e., filtering, expanding rows, etc.) do not work.

    0 comments
  3. Anonymous
    2019-11-19T19:48:22+00:00

    This was very helpful.  Thank you for your advice.

    0 comments