The problem is in the data format: 1C often exports numbers as text. It can be fixed in 2 clicks.

Step one: select the column and click on the pop-up message "Number stored as text", choosing the option "Convert to Number".

Step two: if the error indicator didn't appear, create an auxiliary column with the formula =A2*1. Copy and paste these values back as numbers.

Such format errors are a common cause of failures in pivot reports. We have covered all key XLOOKUP settings for working with data from any sources.

🔗 Source: excel.rf