Tech World Me

  • Home
  • Technology
  • Software
    • Apps
  • How To
  • Top Sites
  • Contact Us
How To Software

How to create drop down list in Excel?

by Arun Kumar March 28, 2022 0 Comment
 How to create drop down list in Excel?

Excel is the world’s most popular software program that helps every user to create a data sheet. And it also allows everyone to organize, format and calculate data with the help of formulas by using a spreadsheet system. Microsoft Excel is created by Microsoft. You can do many things on excel like creating company data, balance sheets, To-do lists and much more. It’s fully beginner-friendly and anyone who is new can understand its function in just 4 to 5 days only, not advanced only basic. But on this topic we will discuss excel drop down list means how to create drop down list in excel in just a few seconds.

What is excel drop down list?

A drop-down list is a list of choices that appears when you click on the menu and it shows you many options in the list. On excel drop down list is a data validation function that allows every user to choose or select an option from a list. It’s the best or excellent way to give a user so many choices with a predefined list. You can use this to create your excel list interactive or while you want to create a form where users choose their name and fill the form.

create excel drop down list

How to create drop down list in excel?

To create a drop down list in excel we have 3 methods. All methods are quick and you can create your drop down list within a second. Let’s check out what’s the steps:

  • By using Data from Cells
  • By entering your data manually
  • By Using the OFFSET formula

By using data from cells

In this example we will change this list into drop down list let’s see how can I do this:

copy data from cells in excel
  • Create the list you want to show in your drop-down menu.
drop-down list on excel
  • Select the area or cell where you want to create a drop down list.
  • Then click on Data and then Data Validation.
data validation on excel
  • A data validation pop up appears, click on Allow and then select List.
data validation list on excel
  • After that, a source field option is left.
  • Click on it (Source field) and then select all list items with the help of your mouse and then click ok.
create drop-down list

Point to remember: Make sure that you checked the In-cell dropdown list by the way it’s checked by default by if not then check it otherwise your excel drop down is not showing.

By entering your data manually

On the upper point, we discussed how you can select your drop-down data from your excel sheet. Basically, cell reference works as a source field. But you can also add your excel drop down data manually by entering your data in the source field.

  • Select the area or cell where you want to create a drop down list.
  • Then click on the Data and then the Data Validation option.
  • A data validation pop up appears, click on Allow and then select List.
  • Again source field is left but now you don’t have to select your drop-down list items from the other excel sheet. Because we have to learn how to add data manually.
  • Click on the source field and then write Agree or Disagree by adding a comma to separate text (If you don’t add a comma on each text then they will align on the same line that’s why we add a comma to separate text).
  • And then click on OK.
manually create drop-down list on excel

This will also create a drop down list but here we add our data manually. Don’t forget to add commas to every word to separate your text to the next line.

By Using the OFFSET Excel formula

As we already know how to create drop down list in excel through data cells or enter the excel data manually. But now the turn is by using the OFFSET excel formula to create a drop down menu list on excel. Any formula on excel returning a list value can be used to create a drop down list. Let’s know how to create drop down list in excel by using the OFFSET function formula.

  • Select the area or cell where you want to create a drop down list.
  • Then click on the Data and then the Data Validation option.
  • A data validation pop up appears, click on Allow and then select List.
  • Now we don’t need to select drop-down list data from the other excel sheet or also we don’t need to enter the drop list data manually. But now we will create our drop down list by using the OFFSET function formula.
  • Click on the source field and enter =OFFSET( and then click on the first text on your drop-down list and then write 0,0, and the number of your list. In my situation, I have 12 items in my drop-down list so I have to enter 12 after the 0,0. And my final formula is =OFFSET($A$2,0,0,12).

=OFFSET( – This is the starting formula.

$A$2 – This comes when you click on the first word of your drop-down list.

12 – In my case, I have 12 items that’s why I add 12 (If I have 50 and more items on my drop-down list then I will write 50 after 0,0).

The Syntax of OFFSET function is: =OFFSET(reference, rows, cols, [Height],[Width])

  • And then click on OK.
create drop-down list using formula

This will also create a drop down list but here we use the OFFSET formula to create our drop down list. When we create our drop-down list by using the OFFSET formula we don’t need to put a comma after each word.

How to copy-paste drop down list on excel?

Now you well know how you can create drop down list by using data from cells, entering data manually, or by using an excel formula. But now we discussed how you can copy or paste the drop down list on excel. Now you think it’s very easy just CTRL + C to copy and CTRL + V to paste but if you do this then it will also copy your data validation. But if you want to copy the drop-down only not the formatting then here are the steps on how you can do that.

  • First copy the cell that has already a dropdown.
  • Select the cell where you want to copy your dropdown.
  • After that click on the paste option on the top right corner. If the paste option is not showing make sure you are at the Home option.
  • And then click on paste special.
  • A paste special box appears and checks the validation box in paste options.
  • After that click on OK.
copy paste drop-down list in excel

Choosing this method to paste your drop-down menu only not the formatting of the copied cell.

How to find all drop down lists in excel?

When we create many drops down lists on our excel sheet then it’s hard to find where it is. So to easily find where we created our drop-down list there is a very easy method. Let’s see how we can do this.

  • Open your excel sheet and make sure you are in the home section.
  • Click on the find & select option on the top right corner of your screen.
  • And then click on the Data Validation.

Data Validation has two options: first is, All and the other one is the same.

All have selected all the cells that have a data validation rule applied.

Same select only who have the same data validation rule.

  • And then click OK.
  • Now this will show you all the cells that have data validation rules applied including a drop-down list.

You may also like

  • How to use Google Docs professionally? Full Guide.
  • Some famous Google apps and their real use.
  • Google created Google Doodle game for their users, Play & enjoy.

Final Words

Excel sheets are used everywhere. All companies use excel sheets to make a report of their work as well as their budget report. Using this drop-down trick on our sheet is a good or excellent way to make a good impression on our client or any user. Using a drop down list makes our excel sheet interactive. I hope after reading this you know how to create drop down list in excel.

FAQ

How to add drop down in excel?

Select the area where you want to add drop in excel > Data > Data Validation > Allow > List > Source.

Total
0
Shares
Share 0
Tweet 0
Pin it 0
Share 0

Share This:

Tags: how to copy drop down list how to create drop down list in excel how to create drop down list in excel by using formula how to create drop down list in excel manually how to create drop down list in excel with cell how to create drop down list in excel with color how to create drop down list in excel with multiple selections Techworldme
Previous post
Next post

Arun Kumar (Website)

administrator

Leave a Reply Cancel reply

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

100% Pure Organic Sugarcane Jaggery
Latest Posts
Technology

Full stack development career in 2023

December 23, 2022
Technology

3 Essential Benefits of Virtual Data Rooms for Your Business

December 20, 2022
Technology

The Most Common Errors on Computers

December 8, 2022
Apps

The Best Anime Streaming Apps For Android

December 8, 2022
hostiger
Categories
  • Apps 40
  • How To 81
  • Software 10
  • Technology 97
  • Top Sites 29
© Copyright 2023. TechWorldMe. All Right Reserved.
  • ←
  • Contact Us

    Contact Form

  • Instagram