Drop down list in excel can help you to prepare clean data

 1,954 Total views

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.Connect on Linkedin

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.
Drop down list in excel
Step 1: Drop down list in excel
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
Drop down list in excel1
Step 2: Drop down list in excel
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.
Data validation pop up
Step 3: Data validation pop up
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.
Source of list
Step 4: Source of list
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.
Drop down list1
Step 5: Drop down list
Step 6: You can configure an error message if irrelevant data is entered in the system.
Error message configuration
Step 6: Error message configuration
Step 7: Drop down list appears in the desired cell in excel sheet. Please see below screen shot.
Drop down list
Step 7: Drop down list
Step 8: If you enter any other data which is not in the list then system will throw error.
Error message
Step 8: Error message

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.


Vaibhav M

Author: Vaibhav M

Vaibhav M is providing training in SAP PM, MM and SD for last 11 years. He has total 17 years of experience. He is certified in SAP PM and SAP MM. In SAP, he has a 100% placement record so far. His expertise includes S4 HANA, SAP PM, SAP MM, SAP SD, and MS excel. His industry specialties are Chemical, Automobile, Electronics and Infrastructure.

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!