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

Recent Posts

See 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

Message us or

Call us on +44 (0)20 3287 8283

Mon to Fri: 8am-8pm

Weekends: 10am-6pm