Thursday, May 23, 2013

GP 2013: Why are my SmartLists not formatted when exported to Excel?

Just recently I have been seeing a number of questions around Microsoft Dynamics GP 2013 Smartlist exports to Microsoft Office Excel not being formatted correctly - let's rephrase, the correct word to use here is "nicely". The issues usually involve leading zeroes being dropped (in some cases) and currency amounts being exported with 5 decimals and no currency symbol.

Exported Payables Transactions smart list

To make a one swipe statement here, this behavior IS NOT a product bug - despite the inconvenience.

With the introduction of the Web Client in Microsoft Dynamics GP 2013, the development team needed the ability improve the performance of Smartlist exports over the browser. To gain this dramatic improvement in performance (which is also experienced when using the rich client), exported Excel smart lists were voided of formatting.

There's an undocumented Dex.ini switch that provides limited formatting to Smartlists, thus maintaining export performance:


You can find more information on this Dex.ini switch here:

Undocumented DEX.INI switch cuts down SmartList export times to Microsoft Office Excel

If you are not going to deploy Web Client, then this should be fine as a workaround. However, this switch causes the Web Client to error out with the following message when attempting to export to Excel:

Web Client error exporting smart list to Excel with SmartlistEnhancedExcelExport=TRUE 

So there you have it!

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


Anonymous said...

Does it matter what version of Excel? I have a client using Excel 2010 and the switch doesn't seem to be working.

Tina said...

Useful tip!!

hyou91 said...

Thank you, very helpful.

Anonymous said...

Wow, after adding:

to my Dex.ini file, the Excel export runs SUPER fast. It's a huge noticeable difference. Too cool!

By the way, I did this in GP 2013 and GP 2010 R2. Works for both!

RanjitCharles said...

This was more than displaying the correct number of decimals for us. In the Inventory Purchase Receipts Excel Export from smartlist (GP 2013), Qty received values greater than 1000 were exported with just the first digit. Eg. a received quantity of 1600 is exported as 1.00, 4032 was exported as 4.00. The Dex.ini setting took care of this problem. Thanks Mariano!

go away said...

Problem with this or at least what I have noticed is that if one column is blank it moves the data on the right to the blank column on the left.

Microsoft Dynamics GP said...

This article was very helpful in answering allot of questions.Thanks for posting.

Dave said...

We are also having the problem that when a column is blank it moves the data to the left...has anyone figured out a solution to that issue?


Anonymous said...

But my user has a column with multiple currencies. If the Currency Symbol is removed after export, how would you know which cell refers to which currency?
I don't think this is very useful.

Mariano Gomez said...

You will need to export the currency Id as well.


Anonymous said...

Just wanna ask what will you do if it happen in GP Web while SmartlistEnhancedExcelExport=TRUE can't be used in GP Web. You have another solution?

Thanks before.

Pam Robertson said...

I just submitted a suggestion to fix the problem where the columns shift on the Excel export if you use the SmartlistEnhancedExcelExport=TRUE and the first column is blank. If you're experiencing that error, you can vote for the suggestion here: