Excel Top Tip #16 - Test yourself cleaning data in Excel? (part 2)

Here is the email sent to all subscribers on 18 November 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

Jumbled transactions and invalid values make your analysis less reliable, as you may miss key areas that need further investigation.

Luckily making some minor changes to your transaction data in Excel will power up your analysis, and aid the presentation of the data and your findings to show off your Excel abilities to your team (and further afield).

How would you solve these 5 challenges in Excel?

Problem: The columns are organised in the wrong order

Solution: Select a column and right click to move/group/hide columns as you require

Problem: The dates might not be valid

Solution: Sort your transactions by the date column to identify issues, then correct dates with DATE()

Problem: I don’t trust the amounts

Solution: Sort the amounts to identify extreme numbers, use SUM() and conditional formatting to highlight errors between the debits, credits and balance figures

Problem: I need separate receipts and payments columns

Solution: Split amounts into separate columns with IF(), <, > and *-1

Problem: I want to analyse activity by day/month/quarter/year

Solution: Split dates into separate columns showing the day/month/quarter/year, then use SUMIF() to summarise the transactions

And now you can answer the most common questions that your peers ask on organising bank statements in Excel - for other questions check here for past editions of FInEx, or drop us a message today:)

David - The StatementReader Team