Stop Excel giving incorrect SUM value
I use Excel for keeping a record of my various bank accounts and I update my worksheets regularly from the appropriate online banking websites. Sometimes the data is available to download in a compatible CSV format, but for others I have to copy from the web page and paste as ‘unicode text’ to preserve the columns. After doing this, I occasionally found
that the sheet gave an inaccurate balance that did not correspond with the true balance for that account. Following much head-scratching, I discovered that while copying the unicode text across to the spreadsheet, the ‘£’ symbol was also being entered into the columns formatted as ‘currency.’ The amounts entered appeared correct and looked exactly the same as all the previous entries in these columns, which all displayed the expected ‘£’ symbol. It was only by chance, as I was selecting cells in these rows, that I noticed the corresponding values in the formula box occasionally displayed the ‘£’ symbol, whereas most other entries only showed the numerical value. This extraneous ‘£’ was causing the errors when trying to calculate the SUM of the cells in those columns, so I used the Edit/Replace function to strip them out. I now make sure to strip out all these extra symbols after pasting in any additional transactions.