FRxBuzz.com

Most of us think about using Excel for either pulling statistics into a report, or for exporting a report for further manipulation. But there’s more. Here’s what I do to use Excel’s powerful capabilities while creating an FRx report.

Spellcheck: most charts of accounts have spelling errors. Pull your chart into Excel and spellcheck it.

So many charts are in all upper case. Makes it look like you’re SHOUTING. Use Excel’s ‘Proper’ function to capitalize the first letter of each word. You’ll also need to copy, paste special values, then review for those that need manual editing. Your reports will end up looking a LOT better.

Use Excel’s Concatenate function to indent. You’ll enter your descriptions in one column, some blank spaces in another column, and Concatenate in the third column. So much easier and faster than using the spacebar.

Find & Replace: FRx does have find and replace functionality, but I like Excel’s a little better, and if you’re already in Excel anyway, why not take advantage of it if you need it.

In some cases I use the Left, Right, or Mid function to strip the natural account to come up with a list of main account numbers.

Finally, I really like to use Excel’s pivot table functionality to review charts of accounts. Why review? You might want to compare charts of accounts between companies to ensure that they are exactly the same. If they’re not, that effects the FRx design. Although most of the time it’s simply a mistake that needs to be corrected in the GL. At any rate, a pivot table is a good way to get a summary view of the chart. It might look a little strange, but normally all you’re after is the items that stick out like a sore thumb.

For more information on any of these Excel functions, refer to Excel’s online help. Also, when you’re through manipulating your data in Excel, you can just paste it into FRx and be done.