In Microsoft Dynamics GP 2013 and going forward, you will have to use one of the methods below to format the data:
METHOD 1: Format in Excel each time.
Format the currency field directly in Excel to two decimals. To do this:
1. Open Smartlist and export the data to Excel.
2. In the excel spreadsheet, right-click on column heading for the column you wish to format, and select Format Cells...
3. In the format cells window, select Currency and set the Decimal places to 2.
4. Choose if you want a Symbol displayed or not, and how you want negative numbers to appear. Click OK.
5. Save the spreadsheet.
METHOD 2: Use Export Solutions in Smartlist.
Create a formatted spreadsheet in Excel and use Export Solutions in Smartlist to reuse the same spreadsheet as a template to export to. To do this:
1. Open Smartlist and export the data to Excel.
2. In the excel spreadsheet, right-click on column heading for the column you wish to format, and select Format Cells...
3. In the format cells window, select Currency and set the Decimal places to 2.
4. Choose if you want a Symbol displayed or not, and how you want negative numbers to appear. Click OK.
5. Enter a file name and location to save it to. Click Save.
6. Open the saved file again in Excel. (It should be listed under File | Recent). The line numbers are listed in the left-most column. Right above the ‘1’ for line 1, you will see a blank cell. Right-click on this blank cell and choose Clear Contents. This should clear all the data from the spreadsheet. Close the spreadsheet and save your changes to create a blank template or worksheet.
7. Now in Microsoft Dynamics GP, set up the export to be able to use this formatted spreadsheet going forward. To do this:
a. Click Microsoft Dynamics GP and click Smartlist.
b. In the top menu-bar, click on Smartlist and select Export Solutions.
c. Key in a Name in the Name filed in the upper right side of the window.
d. In the Document field, browse out to the formatted spreadsheet you created.
e. Change the Application field to Excel.
f. In the Works for Favorites section, expand what Smartlist and mark the ‘checkbox’ to the left of that Smartlist(s) you want to use with the saved spreadsheet. You can mark as many of favorites that you want to use this formatted spreadsheet.
g. Click Save. Exit out of the Export Solutions window.
h. Refresh the Smartlist window.
I. Select the Smartlist and now you should now see a drop-down arrow under the EXCEL button, where you can select to use the formatted spreadsheet you made, (or the ‘Quick export’ will be the old way showing all 5 decimals.)
j. Select the name of the formatted spreadsheet you created, and the data will export to Excel, where the currency column will now be formatted to two decimals.
k. Click FILE | SAVE AS and save the spreadsheet under a new name, to leave your template empty.
Note: Create as many formatted spreadsheets as you need and use Export Solutions to link them to the appropriate Smartlist.