Excel Top Tip #15 - How would you solve these 6 challenges in Excel?

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

Hello

Imagine you’ve managed to convert your bank statement data into Excel but it’s all jumbled.

Maybe your columns are combined or spread out; perhaps your rows only represent a line from your PDF rather than each transaction. Maybe headers, footers and other unnecessary content like ‘page totals’ ‘brought forward’ are still present when you want them removed.

Tidy data leads to tidy analysis; if your transactions are in order, you’re equipped to make better strategic decisions and inform your client on how best to proceed.

Before getting started, first consider:

- How many other documents do you have that haven’t yet been processed into Excel? If all the documents were put into Excel, could you organise all the documents together? Will this make the process more streamlined, or cause additional challenges? - Are all pages in the same Excel file, with the same issues that need correcting? If the pages are suffering from varying column structures, you may be better off splitting the Excel document by sections with consistent issues in batches. - How did you complete the work to get your PDF data into Excel? Did you use a generic PDF reader or extraction tool, or even copy and paste? Depending on the volume of data you have, a more specialised tool may extract your PDF bank statements into the rows and columns of Excel correctly without the need for manual manipulation. This is something to bear in mind for the future!

How would you solve these 6 challenges in Excel?

Problem: Your transactions are combined into too few columns Solution: Data -> Text to columns Problem: Your transactions are spread amongst too many columns Solution: TEXTJOIN() with delimiter=“ “ to add a spacer value between all cell values Problem: Your transactions are spread over too many rows Solution: Aggregate rows around missing amounts (nb. top or bottom balance) using IF() Problem: There are unnecessary headers/footers Solution: Data -> Filter or Sort, to group the extra rows together and remove them Problem: The dates aren’t recognised as dates in Excel Solution: Format dates (or amounts) using DATE()/MID()/TEXT() Problem: Dates are combined with other text in the description column Solution: Identify dates/amounts from description using MID() or SEARCH()

Next month we’ll deal with cleaning data for Excel analysis, for example checking that the dates are valid, or summarising activity by day, month, quarter or year. Staying sharp and using the correct features of Excel to save yourself time when organising data is well worth the effort. As always, forward this tip to any newcomers - Excel tips are rarely taught and will help get them off to a good start! David - The StatementReader Team