data
4 replies
Sample discussion: I imported a small CSV of fictional expenses. When I drag Amount into Values, Excel chooses Count. Some amounts have spaces and a currency symbol.
Check whether the Amount column contains text or blanks. Clean a copy of the imported data, convert valid amounts to numbers, then refresh the PivotTable and choose Sum in Value Field Settings.
Keep a few known totals to check the result after conversion. For repeated imports, Power Query can apply the cleanup steps consistently; select the correct locale for decimal and thousands separators.
My sample amounts were text. How do you validate imported amounts before sharing a report?