View original (French)

"Ghost" data in Excel spreadsheet

Gamp -  
ALS35 Posted messages 1034 Registration date   Status Member Last intervention   -

Hello,

So here it is, I'm tackling an activity report as the end of the year approaches. My team and I fill in a table all year long with all sorts of information. We did it last year for the first time and it went well; we were able to extract all the information.

At the beginning of 2022, we deleted the 2021 data and reused the table.
But this year, things seem to be getting complicated: the data extracted by the pivot tables does not correspond to what I can count myself. Some show details of elements that no longer exist in the table.

Even simple formulas like =SUM give different results if I do them directly in the table or if I copy/paste the values into another sheet to compile them.

The problem is that I no longer know which numbers to trust, and redoing everything manually would take a crazy amount of time.

Note that I am working on Excel 2007.

Does anyone have a solution to get to the bottom of this problem?

I would be happy to provide more details if needed!

Thanks

5 answers

  1.
    ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147
     

    Hello everyone,

    By default, a Pivot Table retains deleted data.

    If you don't want this, you need to go to the Pivot Table options and in the Data tab of the window that opens, set "None" for "Retain items deleted from the data source." Close and reopen your Excel file.

    To be tested.

    Best regards

    3
    1. Gamp
       

      Hello,

      This solution interests me a lot! Unfortunately, I don't feel like I have this option, maybe because my version of Excel is too old.

      I'm attaching an image to illustrate my point, I hope it helps!

      0
      1. ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147 > Gamp
         

        Hello,

        Well yes, you do have the option and apparently it's set to "None" (Number of items to retain per field).

        What we see in your image is a difference for March and December; if you can't provide an anonymized file, it's up to you to check your database for those months. Be careful of any unwanted spaces, and the years in the dates. Did you do your calculation by hand or with a SUMIF or COUNTIF or SUMPRODUCT function, ... There must be a reason.

        Best regards

        0
  2. yclik Posted messages 3874 Registration date   Status Member Last intervention   1 609
     

    Hello

    The data extracted by the pivot tables does not match what I can count myself.

    Is the pivot table using the same data range?

    0
    1. Gamp
       

      I'm not sure I understand the question, but I would say yes. The pivot table takes the data from the sheet I'm recapping and doesn't find the same values.

      0
  3. Willzac Posted messages 281 Status Member 15
     

    Hello,

    If we have the file in hand, we could maybe help you.

    PS: Go to cjoint.com to upload the file and share it with us.

    Best regards

    0
    1. Gamp
       

      Unfortunately (and I forgot to mention) I work in a care center, so the data belongs to patients and I cannot share it... which complicates things, I am well aware!

      1
  4. LatelyGeek Posted messages 1774 Registration date   Status Member Last intervention   550
     

    It is undoubtedly simply that the data range upon which the pivot table is based is no longer correct. To check this, simply click on "Change Data Source" and verify that the proposed range is correct. If not, modifying it should resolve the issue.

    It’s worth noting that the Table feature (with the nice stripes) is there to prevent this kind of problem... It's the only interesting new feature in Excel in 15 years, so let's use it!

    0
    1. Gamp
       

      It's definitely good to know, I'll try to look into it when I have a moment to spare!

      Thanks for the advice, by the way;)

      0
  6. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     

    There is also (and especially) a "Refresh" icon!!!

    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)

    -1