Category: Data Manipulation

I do data cleaning day in day out.  We all do.  This technique from Leila Gharani on You Tube is just so awesomely simple and effective you just wonder why you haven’t thought of this before.   It works on the …

Really good, simple data cleaning technique from Leila Gharani Read More »

Tagged with:

This post is about a common business problem of grabbing messy data from a transactional document and updating a dataset to collect history.  In my previous life, I was in Commodity Export Marketing, so I am using the ‘real world’ …

Grabbing messy data with VBA Arrays Read More »

Tagged with: , , , , ,

OK so this post is the 2nd part of my recent post on how Excel can automate data entry to your accounting system.  See here for Part A.  This example concerns automating entries on your bank statement you can reliably identify.  …

Save time using Excel Templates to upload to your accounting software – Part B Using Autofilter for bank entries Read More »

Tagged with: , ,

I have been putting off blogging about this topic because it’s unwieldy to explain and  involves work done many years ago that I would do better now.   I cringe a bit seeing an Offset formula now which I would only …

Save time using Excel Templates to upload to your accounting software – Part A The Cash Book Read More »

Tagged with: , , , , ,

This blog post is about how I approached a major job I took on about two and a half years ago in doing major alterations to a crucial, complex, legacy spreadsheet for a large Brisbane based company with 16 entities …

Business Model ‘Renovation’ Read More »

Tagged with:

In past posts I have talked about a ‘database’ approach or how an Excel  data analyst approaches data in their spreadsheeting or financial modelling.  Anyone from an IT background would say of course.  But I came from an accounting and …

Organise data like an Excel Analyst Read More »

Tagged with: , , , , ,

OK imagine this – you have done the right thing as a budding data analyst and created your sales data down the page so that you can take advantage of Pivot Tables which likes dimensions of data going across in …

Transposing Data with INDEX Read More »

Tagged with: , , , , , ,