MIP Ideas Portal

Excel transaction report output without MIP formatting and no blank row 2

When I export a transaction report to Excel, it is almost always for analysis. There are several formatting issues that have to be addressed to make the file useful:

1) Delete row 2, which is blank. This eliminates the gap between captions and data for using Excel data features like sort, pivot table, subtotal, etc.

2) Change output amount to single column format, credits as negative numbers. For any sort of pivot table work, this is essential.

3) Eliminate any and all of the MIP formatting, including underlined cells, cell formatting for left/right/top/bottom, in favor of no formatting. Just allow Excel to use natural formatting and the user's default font. It is exceptionally annoying that MIP buries styles in the file so that MIP formatting reappears and increases the cleanup work.

4) Dates come into Excel as text. Date fields should be numeric so they will naturally sort correctly. I have to convert the information from text to date or any sort on a date field makes no sense. For example, 1/1/17 will be followed by 10/1/17.

5) Add to the report account type. It would be enormously helpful to have MIP label Revenue and Expense accounts so that I don't have to do that manually.

6) Add to the filter Transaction Entry Type. There are times when I do multi-year balance sheet research and I have to eliminate system generated balance forward amounts manually. It would me great to just set a filter for "N" type transactions only or any other transaction type.

I spend an excessive amount of time doing data clean up every day. I find that macros are useful for data clean up, but the nature of the task changes often enough that macros are not really an efficient answer.

  • Phil Symonds
  • Jun 12 2017