Sunday, April 22, 2012

PCLaw and Excel

When you run reports in PCLaw, the normal practice is to print them and/or save them. The report toolbar at the top also displays an icon for Excel, which allows you to convert the static report information into a spreadsheet.

PCLaw reports already have a lot of variables available to you, especially if you select the advanced options. But exporting the report into a spreadsheet allows you to manipulate the data in ways limited only by your imagination.

To get the most out of this feature, you need to have a good understanding of how to write formulas. The “Help” in Excel is very good, and there are many online forums that can assist you even further. The idea is to develop standard formulas for the results you want. You can then copy this formula from your old spreadsheet into a new one, thereby instantly obtaining the new information that you want.

I use these worksheets often for error correction. If you want to know what is different on two reports, export both of them into spreadsheets. I usually delete the columns I do not need, keeping only those columns will help me.

For example, try comparing the trust bank journal with the trust bank general ledger. Doing a True or False comparison with the “Entries” numbers in both reports will quickly locate the missing or extra entry. You can also sort the entries from largest to smallest, thereby allowing you to quickly locate a number that only appears on only one report.

As always, I invite your comments and suggestions for future post topics. Next Week – PCLaw and an Error Correction Riddle.