6 Ways to use Excel to create better FRx reports
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 impacts 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.
Jan,
I have found your new training manual to be refreshing and very useful. We often think we know it all, but there is always something new to learn. I have found everything I need so far in it, and it has been so useful. I especially love the additional reading page, as you provide so much additional information on graphing, automatic updates, linking and using excel, and ways to make reports look so nice and colorful. The managers really love that. Thank you so much.
John Lindgren
Thank you very much!!!
Here’s the link in case anyone is looking for it:
https://www.frxbuzz.com/frx-training-manuals/
I live in San Diego CA. Just got wind of an opportunity to use Frx but my experience is way back in 2001. Can you help me with some cd that walks through the basics again. Thanks
Sure, I can help walk you through the basics in a 1 hour web meeting for $185, but you might be better served by buying my FRx self-study training guide(s), one or both depending on how much you want to learn. Good luck…Jan
Hi Madam!
Can you please guide me, How I design consolidate report with different COA, we have 4 different companies.
Regards
MS
Hi MS…whether FRx or MR, the technique is the same. Take a look at Help for all the specifics, but here’s an overview in MR: you’ll add more Link to Financial Dimensions columns like the one in column J. You’ll need one for each chart that’s different. You’ll also have to have a tree, and in it you’ll specify which company uses which Link column. It’s not hard, but it’s not the most straightforward design either. Good luck. Jan
PS. Be sure to get the report correct for ONE company first before you add the links for the additional charts of accounts.