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.
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
Recent PostsSee All
Revisited starter script from January 2021: Split Excel file into separate files Excel is essential, and Python is the future - forcing ourselves to practice the latter by automating some of the commo