Tip of the Week #77 – Working with Data in Excel

PrintVis Tip of the Week #77 – Working with Data in Excel

This week Software Developer Rikard Hult shares some very useful best practices for working with data in Microsoft Excel. 

Excel is the place to correct data before import it to NAV. You don’t want to import bad data. Here are a few tips for working with data in Excel.

Filter

Mark your data area or just the header row, click on Filter under Data tab.

You now have the option to filter or sort on all the columns with just a few clicks. Click on the arrow button in the column you want to work with, and you can sort or filter out data. Use the Clear filter menu choice from the dropdown menu to reset the filter, or the Clear button under the Data tab in top of Excel.

Remove duplicates

To avoid duplicates when importing you could use the Remove Duplicates function in Excel. Mark the data area, click on Remove Duplicates on the Data Tab. Decide on what criteria you want to remove duplicates; In this example all the duplicates on description are removed (Row 6).

Text Length

NAV has a fixed length on different fields. If you are working with Configuration Packages in NAV you could hover the column head and get a tool tip with information about the allowed text length.

You can use Excel to count the length of the cells; Create a formula to count the length. Simply insert one column, write the formula =LEN and click on the cell in which you want to get the text length. Leave the cell, click in the lower right corner of the cell and then drag down to the last line.

Leave the cell, click in lower right corner of the cell and simply drag down to the last line. Now you have all the length and can work with the data!

Thank you Rikard!