How drop down list in excel can help you?

Almost every SAP consultant had made this mistake.

SAP Consultants spent lot of efforts in preparing Master Data templates and transaction data templates in implementation projects and roll out projects. Because they have to upload the data into SAP at the time of cut over. But while uploading the data they come across lots of error. They spent lot of time to fix the errors. They sent the data load file again to the user and ask them to provide correct data. Like this the cycle of data cleaning goes on and on and consultants lost their valuable time.

There is a solution you can use to avoid this problem as long as you actually put it into action.

The solution is drop down list in excel. Drop down list in excel is one of the solution which can compel core team and end user to prepare correct data in load template. This improves the quality of excel sheet.

For example: If Plant in your project is ‘ABCD’ and user made a typo and provided ‘ABCS’ [‘D’ and ‘S’ keys are near to each other in keyboard]. If you have configured a Drop down list in excel then system will throw an error if user select wrong plant. Automatically user will enter correct values. This will save not only the time but also the efforts of entire team.

Step by Step process to configure Drop down list in excel

Let us learn the step by step procedure to create a drop down list in excel. Drop down list in excel provides the user a facility to enter correct data in master data template. If data is correct then it will be an error free upload. here is the procedure:

Step 1: Let us create a excel sheet with Material, Plant and Storage location as header.

Step 2: Place cursor in just below Plant heading. Exactly where you want the drop down list. Then go to “DATA” tab on excel header and click on “Data validation” button. Correct sequence of operations is given in below screen shots

Step 3: After clicking on “Data Validation” button under “DATA” tab a new pop up will come where you have to select “List” from drop down” to declare that you are creating a drop down list. Please see the below screen shot.

Step 4: As you select the list you have to enter the source (location) of that list in excel sheet. Please see below screen shot.

Step 5: Select the data which you want to show in the drop down list. Once you select the list of data it automatically updated in the source field. Please refer below screen shot.

Step 6: You can configure an error message if irrelevant data is entered in the system.

Step 7: Drop down list appears in the desired cell in excel sheet. Please see below screen shot.

Step 8: If you enter any other data which is not in the list then system will throw error.

You can see above that relevant error message is populating.

In this way we can create drop down list in excel and create a relevant template. This further reduces the errors in data.