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.


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

6 Comments/Reflections

Hong Cai

Hong Cai Aug 2019

Quite clear and useful guide!
Paul Benstead

Paul Benstead Sep 2018

Very useful examples that illustrate the article well. I hadn't come across Treemaps before so will be exploring that feature of Excel 2016 later.
Svyatoslav Zaitsev

Svyatoslav Zaitsev Sep 2018

Very informative and helpful article in area that usually treated as "common sense" knowledge. However in reality it is a core knowledge of any accountant.

Detailed step-by-step explanation and Excel functionality description can help in repeating the proposed actions.

I do almost the same activities therefore I find nothing new. Nevertheless I find the article very helpful.

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.

You may also be interested in: