Simple and Detailed Guide to Creating Options in Excel

Create options in Excel to make your work and study easier with the feature of filtering data entered into the cell according to the pre-set list. With this, your documents will be consistent and easier to track data, convenient for editing and summarizing.

What is creating options in Excel? Benefits

Creating options, also known as Drop List or Dropdown List, is a useful data format for users. Some benefits of it can be mentioned as:

• Create a category with options to be quickly entered.
• Support easy declaration and attendance with pre-set options.
• Convenient for managing business such as creating a warehouse management list, import and export…
• Suitable for collecting and summarizing large amounts of information.

How to create options in Excel with Data Validation

When creating options or creating a Drop list in Excel, you can use the Data Validation tool. The steps are very simple, just a few steps below:

Step 1: Access the Excel application and select the Excel file you want to create the option list.

Step 2: Select the cell you want to create the option > Select the Data tab, then select Data Validation.

Step 3: Continue to select the Settings tab, then open the Allow section and select List.

Step 4: Move the mouse to the Sources section to select the cells you want to choose for the list. Finally, just press OK to create the option in Excel.

Now the result will be as shown in the image below. With this method, you can quickly create options according to your needs in your Excel file.

How to create a Dropdown list in Excel based on an existing list

In addition, you can also use the method of creating options in Excel linked to another list. The step-by-step guide will be right below:

Step 1: Enter data linked to each other as shown in the following image. For example, the data Provinces, City names belonging to each province.

Step 2: Perform the operation of naming the Provinces as follows:

Select the range of cells that is the city of any province > Right click and select Define Name > When the dialog box appears, you need to enter the corresponding province names.

In the above steps, you should note that the names of the provinces set must match the entered province names and there should be no spaces.

Example:

From cell B1 to B7 set the name QuangNinh

From cell C1 to C5 set the name HaiPhong

From cell D1 to D7 set the name ThaiBinh

Step 3: Choose the cell you want to create the option > Select the Data tab > Then click Data Validation.

Step 4: When the dialog box appears, click Setting > Move the mouse to the Allow section > Select List.

In the Source section, you enter the formula =INDIRECT(SUBSTITUTE(C11;” “;””)) then click OK.

After that, the option list will be linked to another list that you have created. The displayed result is the dropdown list in the City cell will depend on the data in the Province column that has been entered before. For example, if you enter Ha Tinh, you will have choices that are cities belonging to Ha Tinh.

If you want to copy this command to the cells below, just move the mouse to the bottom right corner of the result cell and drag down to apply to the cells below.

How to remove options in Excel

If you know how to create options in Excel, do you know how to delete this format or not? The answer will be in 2 simple steps below:

Step 1: Perform the operation of selecting the cell that has created the option format, then select the Home tab.

Step 2: In the Editing section, click Clear and then select Clear All to delete the created options in Excel.

Conclusion

Above is the instruction on how to create options in Excel shared by FPT Shop. Hope this useful information will help you easily complete your work and study. If you are looking for good-priced and best-selling computer products, come to FPT Shop for advice or reference immediately at the following link