This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Technical

Data mining your general ledger with Excel Logo aicpa

  J. Carlton Collins, CPA |   Free |   AICPA |   01 Jan 2017 |   Journal of Accountancy

Learn how to use Microsoft Excel to unearth and interpret the wealth of data hidden in the general ledger.

Topics covered:
  • Management accounting: Technical: Accounting information systems: Information systems environment, Foundational
  • IT management & assurance: Technical: Information management: Data management & analytics, Foundational

3 Comments/Reflections

Madhura Weerasinghe

Madhura Weerasinghe Oct 2017

A very useful article and I learnt new techniques of data mining. I usually do pivots of selected GL accounts but I now realised incorporating all GL accounts and pivoting them gives more meaningful picture of the performance.

However things which I did differently were;

1. I selected the whole worksheet by going to the very top left corner and copy and then paste special values only into a new Excel worksheet which helped to remove formatting and formulas (convert to numbers).

2. I've deleted empty rows, zero values etc by putting filters at the top heading rows then filter by empty or zero value raws, instead of putting numbers to each raws.

The new techniques I learnt were;
A. Repeating numbers in each transaction raws by pressing F5 then Goto. This help to reduce time on copying and pasting data.

B. Put data into multiple worksheets then pivots using "Add this data model". This reduce risk of data corruptions when incorporating large volume of data.

C. Powerpivot: Very useful tool when pivoting large volume of data and it's secure.

I am thanking for the author for sharing these new tricks. These tricks certainly save time on gathering data and it's helpful for in-depth analysis.

Best regards
Madhura Weerasinghe CPA, ACMA
Christopher Stevenson

Christopher Stevenson Oct 2017

Excellent article covering a lot of the methods I use when Data Mining and a few more that I was unaware of but will certainly use in future exercises.
Alan Makins

Alan Makins Jul 2017

Interesting. Will be creating new G/L chart of accounts soon. Need to consider potential to mine data in defining structure.